Consistent gets clarificatin
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Consistent gets clarificatin

  1. #1
    Join Date
    Feb 2006
    Posts
    162

    Consistent gets clarificatin

    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



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

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


    ----------------------------------------------------------------

    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.

    1) 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?)

    2) One more clarification required.Eventhough all the 4 rows belong to the same data block,why 4 physical reads & 4 consistent gets required?


    Thanks a lot...

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    2) One more clarification required.Eventhough all the 4 rows belong to the same data block,why 4 physical reads & 4 consistent gets required?
    Because Full Table scan will read till the high water mark, no matter data is present or not.
    Code:
    SQL> select count(*) from a ;
    
      COUNT(*)
    ----------
           999
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             15  consistent gets
             12  physical reads
              0  redo size
            412  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> select count(*) from a ;
    
      COUNT(*)
    ----------
           999
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
             15  consistent gets
              0  physical reads
              0  redo size
            412  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> delete from a;
    
    999 rows deleted.
    
    
    Statistics
    ----------------------------------------------------------
              3  recursive calls
           1093  db block gets
             18  consistent gets
             27  physical reads
         350888  redo size
            678  bytes sent via SQL*Net to client
            549  bytes received via SQL*Net from client
              4  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
            999  rows processed
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select count(*) from a ;
    
      COUNT(*)
    ----------
             0
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
             15  consistent gets
              0  physical reads
              0  redo size
            410  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> truncate table a;
    
    Table truncated.
    
    SQL> select count(*) from a ;
    
      COUNT(*)
    ----------
             0
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              1  db block gets
              5  consistent gets
              0  physical reads
             96  redo size
            410  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> select count(*) from a ;
    
      COUNT(*)
    ----------
             0
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              3  consistent gets
              0  physical reads
              0  redo size
            410  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    after reboot, the sga is clean and only alloted the memory according to the init/spfile definition. And so, the first select sql has to be physically read from the datafile acquiring I/O and flashed to buffer cache.

    And so, the second read from the same sql will only acquire less consistent gets because it reads data from buffer cache not from data file.
    ---------------

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Did you notice # of recursive calls?

    238 recursive calls - first time

    0 recursive calls - 2nd time onwards.

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Just to mention - consistent get does not allways mean that a read consistent view is maked by the undo segment.
    Any time Oracle needs a block in consistent mode is called consistent get. That should answer why it does not takes blocks in current mode. Basically it's the same operation in this case, just is called consistent get since the buffer is needed in consistent mode

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    It's called consistent gets because in all queries Oracle gives a consistent view for Readers.

    Whereas current mode are most used for DML operations. When we modify a row the row MUST be in the current state otherwise you are modifying something in the past which is ilogical and useless.

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Quote Originally Posted by pando
    It's called consistent gets because in all queries Oracle gives a consistent view for Readers.
    There is only one user in the database and see the below test.
    Code:
    SQL> Alter system flush buffer_cache;
    
    System altered.
    
    SQL> set autotrace on stat
    SQL> select count(*) from a;
    
      COUNT(*)
    ----------
            19
    
    
    Statistics
    ----------------------------------------------------------
            217  recursive calls
              0  db block gets
             29  consistent gets
             14  physical reads
              0  redo size
            411  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              4  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> /
    
      COUNT(*)
    ----------
            19
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              3  consistent gets
              0  physical reads
              0  redo size
            411  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    Why Oracle has to get a "Consistant get" while all the blocks are already in consistant mode so that a "db block det " will do? Is it the case that the "current" blocks reservered for any "expected updates"?
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Hi

    I suggest you to have a look at Concepts Guide Data concurrent and Consistency chapter. Also http://asktom.oracle.com/pls/asktom/...D:878847787577

    In a query consistent gets is always shown. Query is a Reader, Reader always get consistent views which means consistent gets. The db block gets when we see in a query is from data dictionary, current mode must be used reading data dictionary because we always need the get the CURRENT state of the table.

  9. #9
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    The geeky coder in me likes this analogy (taken from Pando's link to TK's site):

    for x in ( select rowid from emp ) --- CONSISTENT GETS
    loop
    delete from emp where rowid = x.rowid; --- CURRENT MODE GETS
    end loop;
    Assistance is Futile...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width