Explanation on consistent gets

From: <lambu999_at_yahoo.com>
Date: 23 Feb 2005 10:16:06 -0800
Message-ID: <1109182566.016893.141270@z14g2000cwz.googlegroups.com>

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 - 12:16:06 CST