Storage Indexes - Oracle Exadata Recipes: A Problem-Solution Approach - page 557

10 xtr:=str||to_char(i)||' between ''A'' and ''B''';
11 else
12 xtr:=str||to_char(i)||' between 100 and 110';
13 end if;
14 dbms_output.put_line(xtr);
15 execute immediate xtr into cnt;
16 end loop;
17 end loop;
18 end;
19 /
select count(*) from d14.myobj_test where col1 between 100 and 110
select count(*) from d14.myobj_test where col2 between 100 and 110
select count(*) from d14.myobj_test where col3 between 'A' and 'B'
select count(*) from d14.myobj_test where col4 between 'A' and 'B'
... Output omitted for brevity
PL/SQL procedure successfully completed.
SQL>

4. When complete, a number of trace files will be written to the trace file directory on each

storage cell. Use the script in Listing 19-2 to display pertinent information related to
storage indexes, passing the DATAOBJ# as an argument as outlined.

Listing 19-2. ./lst19-07-storindsum.pl

#!/usr/bin/perl
# Name: lst19-07-storindsum.pl
# Usage: ./lst19-07-storindsum.pl -o [dataobj#]
# To do: do the pattern matching
use Getopt::Std;
use Text::ParseWords;
use Env;
sub usage {
print "Usage: /lst19-07-storindsum.pl -o [dataobj#]\n";
}
getopts("o:",\%options);
die usage unless (defined $options{o});
$hostname=$ENV{HOSTNAME};
$hostname=~s/\..*//;
$trcdir="/var/log/oracle/diag/asm/cell/$hostname/trace";
$p=0;
open(F,"cat $trcdir/svtrc*trc|") ;
while (<F>) {
$p=1 if ((/tabn/) && (/\{$options{o}/)) ;
$p=0 if /SQLID/;
if (/tabn/) {
($day,$tm,$x3,$x4,$x5,$x6,$x7,$x8,$x9,$x10,$x11,$x12,$obj,$tbs,$x15)=split(' ',$_);
print "Object: $obj $tbs $x15\n" if $p;
}
if (/rgnIdx/) {
($day,$tm,$x3,$x4,$x5,$x6,$x7,$x8,$x9,$x10,$x11,$x12,$rgnIdx,
$x14,$x15,$x16,$x17,$x18,$x19)=split(' ',$_);