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

■ the exadata storage cells will generate a fixed number of cellsrv trace files for storage index tracing, as
established by the cell server initialization parameter _cell_si_max_num_diag_mode_dumps . this parameter defaults
to 20, and oracle limits this to protect against filling up your trace file directory with hundreds or thousands of files.

Note

Search for the string RIDX in the trace file. RIDX stands for “region index,” as introduced
in the How It Works section of Recipe 19-1. The contents of the trace file below the RIDX
section will contain information about your region indexes (in other words, storage
indexes):

5.

2012-11-28 01:41:21.235089*: RIDX (0x2aacf77f1d64) for SQLID 81mxd5muz2543 filter 0
2012-11-28 01:41:21.237399*: RIDX (0x2aacf77f1d64) : st 2 validBitMap 0 tabn 0 id
{29336 12 2273376219}
2012-11-28 01:41:21.237399*: RIDX: strt 0 end 2048 offset 218594541568 size 1048576
rgnIdx 208468 RgnOffset 0 scn: 0x0000.0617dc23 hist: 0x92
2012-11-28 01:41:21.237399*: RIDX validation history: 0:FullRead 1:FullRead 2:FullRead
3:Undef 4:Undef 5:Undef 6:Undef 7:Undef 8:Undef 9:Undef
2012-11-28 01:41:21.237399*: Col id [2] numFilt 11 flg 2:
2012-11-28 01:41:21.237399*: lo: 4d 47 4d 54 5f 44 45 4c
2012-11-28 01:41:21.237399*: hi: 78 64 62 2d 6c 6f 67 31
2012-11-28 01:41:21.237399*: Col id [4] numFilt 3 flg 2:
2012-11-28 01:41:21.237399*: lo: c3 2 31 0 0 0 0 0
2012-11-28 01:41:21.237399*: hi: c3 2 5a 48 0 0 0 0
2012-11-28 01:41:21.243399*: Col id [6] numFilt 4 flg 2:
2012-11-28 01:41:21.243399*: lo: 53 59 4e 4f 4e 59 4d 0
2012-11-28 01:41:21.243399*: hi: 54 59 50 45 0 0 0 0

Here is how to interpret the trace file:

• Each line in the region index section is prefaced by a timestamp at which the trace file line was

generated. The top row contains the SQL ID for the SQL statement that searched the storage
index:

RIDX (0x2aacf77f1d64) for SQLID 81mxd5muz2543 filter 0

V$SQL to confirm which SQL statement was issued that generated this section of
the storage index trace file by running the following SQL statement:

• You can query

SQL> select sql_text from v$sql where sql_id= '81mxd5muz2543' ;
SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from d14.myobj_ordered where object_name between 'a' and 'm'
SQL>

tabn , contains your object information details in curly • The next line, which contains the string

brackets:

RIDX (0x2aacf77f1d64) : st 2 validBitMap 0 tabn 0 id { 29336 12 2273376219}