Custom parameters in Method-R Workbench

 Method-R Workbench is a leading tool to analyze many aspects of Oracles SQL-Trace files. 

It can use parameters in its commands and let users fill their values there. In the picture there are even 2 parameters, properly labeled and predefined. 

But when I run my own statement which also uses a "variable" of the same format, it only generates an error message:

mrskew --name='cell single block physical read$' --where='$obj==@OBJECT-ID@' --group='sprintf("%-16s %-16s", $p1, $p2 )'   --group-label='sprintf("%-16s %-16s", "p1", "p2" )'

Array found where operator expected at (eval 26) line 1, near "ID@)"
syntax error at (eval 26) line 1, near "ID@) "

To tell the Workbench how to use my variable, some entries in a xml file are required:

In the .method-r\workbench\9.5.2.0 directory, there is the file reports.xml. 

(This file gets copied from Workbenchs installation directory etc\config to your users directory if it doesn't exist.) Be careful to topy your changes whenever a new version of Workbench arrives!

By default it contains only one example which is also its documentation. To make my query work with a parameter, I add this XML block:

	<diagnostic label="Response time time by p1 and p2 call name for a given OBJECT ID filtered for only 'cell single block physical read$'">	<!-- This is the name that the application will use for the report. -->
		<commandline>mrskew --name='cell single block physical read$' --where='$obj==@OBJECT-ID@' --group='sprintf("%-16s %-16s", $p1, $p2 )' --group-label='sprintf("%-16s %-16s", "p1", "p2" )'</commandline>				<!-- This is the command line. The token @SQLID@ is defined on the following line. -->
		<param token="@OBJECT-ID@" label="Object ID" hint="obj=" default="-1"/>				<!-- This element causes the app to pop a dialog using the field label, data entry tip, and default value. -->
		<sample><![CDATA[
p1               p2                DURATION       %  CALLS      MEAN       MIN       MAX
---------------------------------  --------  ------  -----  --------  --------  --------
4239709683       2452482353        0.341465    7.9%     12  0.028455  0.005255  0.209957
4239709683       560937342         0.235909    5.4%      7  0.033701  0.010636  0.122802
3429896051       3039011478        0.224350    5.2%      4  0.056088  0.008659  0.186996
4156894774       425700738         0.221344    5.1%      8  0.027668  0.010023  0.077580
437579672        4090241319        0.177895    4.1%      9  0.019766  0.000978  0.072990
3429896051       3648465545        0.166973    3.8%      8  0.020872  0.009399  0.029454
4156894774       203886304         0.164699    3.8%      7  0.023528  0.002680  0.097812
4156894774       1302812366        0.160810    3.7%      8  0.020101  0.007712  0.080382
4239709683       2857786178        0.159647    3.7%      9  0.017739  0.005672  0.068118
3429896051       2540343800        0.146400    3.4%      4  0.036600  0.018622  0.074485
                        37 others  2.340060   53.9%    157  0.014905  0.000342  0.128323
---------------------------------  --------  ------  -----  --------  --------  --------
                       TOTAL (47)  4.339552  100.0%    233  0.018625  0.000342  0.209957

		]]></sample>																<!-- This is what shows up in the Sample Output tab as you scroll through the reports list. -->
	</diagnostic>

In the current (9.5.2.0) version, there is a bug in the example: the html element tooltip is used, but it should be hint!

With this modification, now I have my own action defined and the parameter can be added as expected! 

---

Special thanks to Cary and Jeff for explaining these details so I can document it. 

reading a LOB in another session

 Reading a LOB in Oracle is often quite easy: it's just a column in the query and all the libraries available today are doing the work necessary to make the LOBs content appear in the desired variable. What a nice world. 

Unfortunately it is still a LOB and requires special methods to fetch the data. And especially because this is covered by libraries, LOBs can be called slow. 

If fast access to many rows with many LOBs is required, leaving all the work to a single session might not meet the requirements. But LOBs also can be fetched in other sessions - kind of in parallel. 
(this must NOT be mixed up with parallel queries - instead they are separate sessions doing the LOB interactions while the main session processes the tables queries). 

As the specific implementation might be led by the requirements, my example here only shows what's possible. 

I have a very small table with only 1 row:

create table T (id number, B clob);

insert into T values (1, 'aäoöuüsß');

commit;

A small perl program selects the row in one session and reads it content in another one.
(please do NOT use this script as a blueprint!)

the relevant parts are

my $sql = "SELECT id, B LOB_LOCATOR FROM T WHERE ROWNUM = 1";
my $sth = $dbh->prepare($sql, { ora_auto_lob => 0 })
    or die "Couldn't prepare statement: $DBI::errstr";
$sth->execute() or die "Couldn't execute statement: $DBI::errstr";

# Get the first row
my ($id, $lob_locator) = $sth->fetchrow_array();
print "id: $id \n";

# Close the first connection
$sth->finish;
$dbh->disconnect;

print "ll: $lob_locator \n";
print " --- \n";

here the content was fetched in first session and printed to stdout ... 

 and

$dbh2 = DBI->connect("dbi:Oracle:host=$hostname;port=$port;service_name=$service_name", $username, $password)
    or die "Couldn't connect to database: $DBI::errstr";

   my $chunk_size = 1034;   # Arbitrary chunk size, for example
   my $offset = 1;   # Offsets start at 1, not 0
   my $data = $dbh2->ora_lob_read( $lob_locator, $offset, $chunk_size ) ;
   print "lob: $data \n";

where the LOBs content is read and printed. the execution looks like

[oracle@localhost ~]$ perl lob.pl
id: 1
ll: OCILobLocatorPtr=SCALAR(0x2b388a8)
 ---
lob: aaoouus?
[oracle@localhost ~]$

all fine here. 

To be sure no fancy session cache ruined my testcase, I enabled sql trace before with 

 exec dbms_monitor.database_trace_enable();

and so I got 2 tracefiles: FREE_ora_70047.trc with the SELECT

=====================
PARSING IN CURSOR #140276220704640 len=48 dep=0 uid=131 oct=3 lid=131 tim=22284540551 hv=1723986746 ad='61d7dbe0' sqlid='c2zwqytmc3wtu'
SELECT id, B LOB_LOCATOR FROM T WHERE ROWNUM = 1
END OF STMT

and FREE_ora_70050.trc with the LOBREAD

LOBREAD: type=PERSISTENT LOB,bytes=8,c=1934,e=1693,p=0,cr=1,cu=0,tim=22284648543
WAIT #0: nam='SQL*Net message from client' ela=3182 driver id=1952673792 #bytes=1 p3=0 obj#=4294967295 tim=22284651786
XCTEND rlbk=0, rd_only=1, tim=22284651919

all the files can be found here.
This shows how we can read a lob locators content in another session. That somehow conflicts with the documentation which states

A LOB locator points to the actual LOB contents. The locator is returned when you retrieve the LOB, not the LOB's contents. LOB locators cannot be saved in one transaction or session and used again in a later transaction or session.

 but I assume this explanation did care about session context, not clients. 

This little example shows that LOBs content CAN be read in other sessions when the lob locator was fetched in a main query and the LOB handling should be offloaded to other connections.