Exadata – Storage indexes to database objects

It was a long time ago when I started wondering how can I view storage indexes on cell nodes and map them to corresponding database object. As far as I know, there was no easy way/documented or not/, until now, to take one database object and list all storage indexes that you currently have for it on corresponding cell. There are some parameters for tracing storage indexes – _cell_si_expensive_debug_tracing=TRUE, _cell_storage_index_diag_mode=7 for example, but it’s not very comfortable to have such expensive trace all the time.

Geeking in that direction finally gave a result.

The magical command is the following one:
alter cell events="immediate cellsrv.cellsrv_storidx('dumpridx','all',0,0,0);

cellsrv_storidx is taking the following parameters:
dumpridx - dump storage indexes
all/griddiskname - dump for all griddisk/dump for specific griddisk
objd - data_object_id from all_objects
tsn - tablespace number – ts# from ts$
dbid - ksqdngunid from x$ksqdn

The good news is that this can be quite useful. The bad news is that the filtering on objd, tsn and dbid is not working. It always dumps all indexes. Or at least in my test cases it didn’t worked. If you are wondering from where I’m so sure those parameters mean that, just check the trace file:
Dumping RIDX summary for objd 75580, tsn 4, dbid -799445099
Actually here we can see even integer overflow for my dbid…

So lets try what we know till now – CATION – use at your own risk!:

CellCLI> alter cell events="immediate cellsrv.cellsrv_storidx('dumpridx','all',0,0,0);
Dump sequence #1 has been written to /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/log/diag/asm/cell/cell01/trace/svtrc_9721_49.trc
Cell cell01 successfully altered

CellCLI>

And then read the trace file:

2013-10-23 22:21:58.206996*: RIDX (0x7f360f4aec6c) : st 2 validBitMap 0 tabn 0 id {75577 4 3495522197}
2013-10-23 22:21:58.206996*: RIDX: strt 32 end 2048 offset 3730849792 size 1032192 rgnIdx 3558 RgnOffset 16384 scn: 0x0000.000fbc02 hist: 0x49
2013-10-23 22:21:58.206996*: RIDX validation history: 0:PartialRead 1:PartialRead 2:PartialRead 3:Undef 4:Undef 5:Undef 6:Undef 7:Undef 8:Undef 9:Undef
2013-10-23 22:21:58.206996*: Col id [1] numFilt 2 flg 2:
2013-10-23 22:21:58.206996*: lo: 61 61 61 66 0 0 0 0
2013-10-23 22:21:58.206996*: hi: 7a 7a 7a 6d 0 0 0 0
2013-10-23 22:21:58.206996*: Col id [2] numFilt 3 flg 2:
2013-10-23 22:21:58.206996*: lo: 61 61 61 65 0 0 0 0
2013-10-23 22:21:58.206996*: hi: 7a 7a 7a 61 0 0 0 0
2013-10-23 22:21:58.206996*: Col id [5] numFilt 4 flg 2:
2013-10-23 22:21:58.206996*: lo: 61 61 61 69 0 0 0 0
2013-10-23 22:21:58.206996*: hi: 7a 7a 7a 6c 0 0 0 0

We can see a lot of information in this block, but in our case, we are only interested in the following:
{75577 4 3495522197} – Data Object ID, Tablespace Number, Database ID
Col id [N] – Object Column ID

Now we can find the corresponding database object:

SQL> select ksqdngunid from x$ksqdn;

KSQDNGUNID
3495522197

SQL> select owner,object_name from dba_objects where data_object_id='75577';

OWNER OBJECT_NAME
X X

SQL> select owner,table_name,column_name from dba_tab_columns where owner='X' and table_name='X' and column_id in (1,2,5);

OWNER TABLE_NAME COLUMN_NAME
X X A
X X B
X X E

SQL>

The current example is easier to represent. But it’s true I find the opposite situation more applicable, to wonder if you have a storage index for this object/column and then dump and search in the dump file for dbid/objd. And still it is not the best way to do it… so if you have any idea about filtering by objd and dbid, please share.

PS: As Frits Hoogland mentioned below, its good to point that Exadata have more than one storage server, so in real life situations you shall check for storage indexes on all cell nodes.

Cheers,
Kovachev

About these ads

7 thoughts on “Exadata – Storage indexes to database objects

  1. I am working on blog articles describing how to get useful information out of the cell server, and will get to storage indexes.

    If the DBID column (which obviously is a signed integer) does not have the most significant bit set (in non geek-speak: is not negative), setting objd, tsn, dbid will generate a trace file with only the storage index information on the requested object (checked with OSS 11.2.3.2.1_LINUX.X64_130109).

    Another to point out is that a normal exadata setup has 3,7 or 14 storage servers, so in order to truly understand how much of an object is covered by a storage index, you should dump the information on all storage servers in the disk group.

    • Maybe I’m doing something wrong, so let me show what I mean:
      CellCLI> alter cell events="immediate cellsrv.cellsrv_storidx('dumpridx',all,75580,4,0)";
      Dump sequence #23 has been written to /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/log/diag/asm/cell/cell01/trace/svtrc_5601_57.trc
      Cell cell01 successfully altered

      CellCLI>

      Then the trace file contains:
      2013-10-24 13:57:22.190519*: Dumping RIDX summary for objd 75580, tsn 4, dbid 0
      .
      2013-10-24 13:57:22.190519*: RIDX (0x7f360f4b1008) : st 2 validBitMap 0 tabn 0 id {75577 4 2201331730}
      .
      2013-10-24 13:57:22.204583*: RIDX (0x7f360f4b5598) : st 2 validBitMap 0 tabn 0 id {75579 4 2201331730}
      .
      2013-10-24 13:57:22.209643*: RIDX (0x7f360f4aec6c) : st 2 validBitMap 0 tabn 0 id {75577 4 3495522197}

      If I specify dbid:

      CellCLI> alter cell events="immediate cellsrv.cellsrv_storidx('dumpridx',all,75580,4,3495522197)";
      Dump sequence #25 has been written to /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/log/diag/asm/cell/cell01/trace/svtrc_5601_20.trc
      Cell cell01 successfully altered

      CellCLI>

      And trace contains all storage indexes for all instances:
      2013-10-24 14:12:00.445605*: Dumping RIDX summary for objd 75580, tsn 4, dbid -799445099
      .
      2013-10-24 14:12:00.445605*: RIDX (0x7f360f4b1008) : st 2 validBitMap 0 tabn 0 id {75577 4 2201331730}
      .
      2013-10-24 14:12:00.464340*: RIDX (0x7f360f4ae0d4) : st 2 validBitMap 0 tabn 0 id {75577 4 3495522197}
      and so on.

      Also in the trace file we can see integer /the java data type/ overflow. For instance this is a perl representation:
      [oracle@oracle112 ~]$ perl -e 'print "enter dbid:\n";$dbid=<STDIN>; print unpack "l", pack "l", $dbid; print "\n"'
      enter dbid:
      3495522197
      -799445099

      So, I’m stuck in here. Any suggestions?

  2. I didn’t look close enough, I am sorry. The first few storage indexes in my dump where on the object/tablespace/database combination I requested. But once I grepped the trace file, I saw different combinations. So indeed, you are correct that using the numbers to filter does not work.

    It is interesting to see that a large amount of storage index information actually is filtered, which means there is filtering, only it’s unknown on what.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s