Oracle Performance and Engineering

Someone asked an explanation for "too many" consistent gets first time against a simple table which spans only 4 blocks. On first execution, the Consistent Gets (or, LIO) is 44. On later hits, the consistent gets is 4.

The test case was more like -

12:41:39 SQL> set autotrace on
12:42:02 SQL> set autotrace on statistics
12:42:23 SQL>
12:42:24 SQL> select * from dept;
more...

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Statistics
----------------------------------------------------------
238 recursive calls
0 db block gets
44 consistent gets
4 physical reads
0 redo size
600 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
4 rows processed

12:42:33 SQL>
12:42:37 SQL> /
more...

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
600 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

12:42:56 SQL> select dbms_rowid.rowid_block_number(rowid) from dept;
more...

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
50450
50450
50450
50450


Why consistent gets initially?

This is interesting.

My answer -

Notice the correlation between these -

First execution - Higher LIO

Second Execution - Lower LIO
i.e., the 'extra' 40 consistent gets were due to the 'hard parse' - which caused 238 'recursive calls'.

If you'd trace the session and 'tkprof' with "sys=yes" (default is yes) - you can see the particular recursive calls. They are made to make sure the table exists, you have privilege to select the table etc etc.