Oracle Brain Teaser

While tuning a database server recently I started seeing some strange results in the v$ statistics tables. (namely v$sysstat, v$sqlarea, and v$sqltext).

I am aware of the issue of joining v$sqlarea and v$sqltext in version 7.3.3 but there is another issue which I cant explain.

I am running 7.3.3 EE on AIX 4.1.5 (both unsupported).

I have a very samll SGA (probably the smallest I've come across in 5 years).

The buffer cache is 200 buffers of 4K blocks (i.e. 800Kb). The total SGA is only 5Mb...!

In this environment this SGA seems sufficient because the database is rarely even queried. It simply acts as a set of lookup tables for an image retrieval system.

What I cant understand is that when I list CPU intensive SQL statements (i.e. Logical I/Os per executions), I am seeing some SQL which generates over 30,000 LIOs per execution. Now with a buffer cache of only 200 buffers, I would expect the hit ratio for this statement to be very poor.

However, the disk reads for this particular statement is less than 1000. This doesnt make sense to me.

Can anybody shed some light on this please....? I suspect that the stats may be wrong or store different information than the stat tables in more recent versions of Oracle.

Matt Received on Thu Aug 07 2003 - 03:51:50 CDT