Re: Explanation on consistent gets

Notice the correlation between these -

First execution - Higher LIO

> 238 recursive calls
> 44 consistent gets

Second Execution - Lower LIO

> 0 recursive calls
> 4 consistent gets

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.

lambu999_at_yahoo.com wrote:
> Hi all,
>
> Oracle: 9i on Windows
>
> After a clean, cold boot of the computer:
>
> 12:41:35 SQL> sho arrays
> arraysize 3000
> 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:40 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
>
> <and so on...>
>
> 12:42:56 SQL> select dbms_rowid.rowid_block_number(rowid) from dept;
> more...
>
> DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
> ------------------------------------
> 50450
> 50450
> 50450
> 50450
> <all 4 rows in one block>
> ----------------------------------------------------------------
>
> This SQL PLus is the only session running (after a total reboot) and
> this is the first SQL executed as soon as I opened the database. My
> question is why is the consistent gets 4 everytime from the second
time
> onwards? Why does Oracle have to fetch the rows at all in consistent
> mode when there is no *other* transaction running? Should it not be
> getting them in current mode? (For that matter why consistent gets
> initially?)
>
> Thanks a lot for all the people answering.
>
> Ram.
Received on Wed Feb 23 2005 - 16:46:46 CST