Oracle index access path visualization

DtraceLio script by Alexander Anokhin has inspired me to do
some investigations in Oracle index access internals.

The main idea is to combine index tree structure with output of Dtrace LIO.

It also would be nice to combine LIO and PIO in a single output.
So I reworked the script a bit for my purposes.

PIO (phisical IO) has been mostly tricky part,

I’ve managed to found the following function in the call stack for Oracle process:

kd4obj(0xA8BCA000,0,8192,0)
kd4obj(0xA8E68000,0,8192,0)
kd4obj(0xA9D18000,0,8192,0)
kd4obj(0xA8430000,0,8192,0)
kd4obj(0xA86E2000,0,8192,0)
kd4obj(0xA8944000,0,8192,0)
kd4obj(0xA8BCC000,0,8192,0)
kd4obj(0xA8942000,0,8192,0)

it correlates very well with scattered read entries in the trace file.
So after some tryouts and guesses I come up with the following structure for the first argument of the function.

typedef struct kdbafr /* full relative dba */
{
ktsn tsn_kdbafr; /* a tablespace number */
krdba dba_kdbafr; /* a relative dba */
} kdbafr;

My addition was:

pid$1::kd4obj:entry
{
blk1 = ((kdbafr *) copyin(arg0, sizeof(kdbafr)));
tsn = blk1->tsn_kdbafr;
rdba = blk1->dba_kdbafr;
rdba_file = rdba >> 22; /* for smallfile tablespaces */
rdba_block = rdba & 0x3FFFFF;
}

pid$1::kd4obj:entry
{
printf(“%s(0x%X,%d,%d,%d) [tsn: %d rdba: 0x%x (%d/%d) ] \n”,probefunc,arg0,arg1,arg2,arg3,tsn, rdba, rdba_file, rdba_block);
}

and typical result is look like :

kcbgtcr(0xFFFF80FFB6EF5360,0,865,0) [tsn: 7 rdba: 0x1805816 (6/22550) obj: 77708 dobj: 77708] where: 865 exam: 0
kd4obj(0xB1EDE000,0,8192,0) [tsn: 41478 rdba: 0x1805810 (6/22544) ]
kd4obj(0xB10F8000,0,8192,0) [tsn: 41478 rdba: 0x1805811 (6/22545) ]
kd4obj(0xB13B0000,0,8192,0) [tsn: 41478 rdba: 0x1805812 (6/22546) ]
kd4obj(0xB1612000,0,8192,0) [tsn: 41478 rdba: 0x1805813 (6/22547) ]
kd4obj(0xB1896000,0,8192,0) [tsn: 41478 rdba: 0x1805814 (6/22548) ]
kd4obj(0xB1B2E000,0,8192,0) [tsn: 41478 rdba: 0x1805815 (6/22549) ]
kd4obj(0xB1B2C000,0,8192,0) [tsn: 41478 rdba: 0x1805816 (6/22550) ]
kd4obj(0xB1EE0000,0,8192,0) [tsn: 41478 rdba: 0x1805817 (6/22551) ]

So I guessed correctly for RDBA part but the 1st part is still unknown for me(it is definetely not a tablespace number 🙂 ).

I used
ALTER SESSION SET EVENTS ‘immediate trace name TREEDUMP level &obj_id’;
to dump the index tree structure and implemented a simple perl script to match dtracelio output with the tree structure.

Here is first results:

Single block access by unique index on the left and non-unique index access on the right side.

Unique_single
access by unique index

NonUnique_single
access by none-unique index

Bars above zero denotes index blocks access (height is a height of the corresponding index block in the index tree hierarchy), leaf index blocks are colored in red, bars below zero denotes table blocks access (-1 is LIO and -7 is PIO).

Looking into “raw” data for the pictures above you can see that for none-unique index access the leaf block has been accessed twice, it explains the “famous” difference in consistent reads between UNIQUE and NON UNIQUE  indexes (I would say actually it doesn’t matter for any real life scenario though)

LIO_IDX, 0x1400083, 4, 77795, branch
LIO_IDX, 0x14010cc, 3, 77795, branch
LIO_IDX, 0x14000c8, 2, 77795, branch
LIO_IDX, 0x1400086, 0, 77795, leaf, 0.2
LIO_TBL, 0x1804ee5, -1, 77794, table, , -1
PIO_TBL, 0x1804ee5,-7, , table , , , , -7
LIO_IDX, 0x1400086, 0, 77795, leaf, 0.2

PS

I’m going to continue publishing the further results of the investigation and here 1 picture from forthcoming results 🙂

PPS
I need to emphasize that db4obj doesn’t represent an OS level physical IO but rather a step of returning a single block to buffer cache from kd* level.

Direct_access

Advertisements

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