Dienstag, 7. Oktober 2014

As a huge friend of Oracles SQL trace I also see it's limitations as well. One of these limitations is visible in it's underlying infrastructure: the wait interface. It's only about waits.
There is another source of information as well, you could call it the DO interface, but the common term is session and system statistics. there Oracle counts what it is doing.
Unfortunately I do not know a simple way to show both realities in a common way.

At least for consistent gets I now can show (most of) them together with wait events.

Of course the additional event gives additional lines which will not be processed by those tools to parse SQL traces. I will not go into any detail of those additional lines - especially as I don't know about them to much. If you are interested I'd recommend Charles Hoopers blog.

I just grab lines which contain the string completed for block and rework their values so it looks similar to something the SQL trace parsers know. This is a very inexperienced piece of code!

This code should really be seen as an example, not a solution!
Out of a line likektrgcm(): completed for block <0x0000 : 0x0041560c> objd: 0x0000003e
it createsWAIT #47572859450312: nam='LIO - completed for block' ela=0 tablespace=1 block#=4281868 obj=62 obj#=62 tim=47572859450312

Probably the only magic is the Cursor and timing info which I just copy from the last SQL trace line above. I'd as well like not to use the keyword WAIT as for sure the process is not waiting in this case, but I have to still trick the tools.

With my modified tracefile from the statement above I create this report: