OraFAQ Forum: Performance Tuning » db block gets, consistent gets

Home » RDBMS Server » Performance Tuning » db block gets, consistent gets

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

db block gets, consistent gets [message #123796]

Tue, 14 June 2005 18:14

rkl1
Messages: 97
Registered: June 2005

Member

when we run set autotrace on or similar execution statistics, what does mean by db block gets and consistent gets. Are they very much cpu bound. What we need to do to see lower numbers.

Report message to a moderator

Re: db block gets, consistent gets [message #123812 is a reply to message #123796]

Tue, 14 June 2005 22:49

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

consistent gets is the blocks in consistent mode (sometimes reconstructed using information from RBS)
So this reconstruction from RBS takes more resources (reads actually), which will end up as high consistent gets.

db block gets is the blocks in current mode (whatever it is NOW).

Your goal should be reducing the logical i/o's ( which is db block gets + consistent gets).
When you reduce LIO your Physical I/O is reduced.


scott@9i > set autotrace traceonly statistics
scott@9i > select * from emp;

14 rows selected.


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

--
-- Now opened another session.
-- did some DML on the table. Left the other session as-is.
-- Comeback to original session.
-- execute again.
-- you can see consistent gets is high.
-- This is becuase, this consistent gets is derived using the undo information.
-- THe changes done in the other session WILL NOT be reflected here, in this session.
-- So as to maintain this consistency, if needed, oracle will use undoinformation
-- to consider only the before image (that was before the update in another session 
-- which has not yet been committed).
--


scott@9i > /

14 rows selected.


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

--
-- Now issued a commit in other session.
-- execute the sql here.
-- you see, consistent gets are back to normal.
--  
--
scott@9i > /

14 rows selected.


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

Report message to a moderator

Re: db block gets, consistent gets [message #124413 is a reply to message #123796]

Sat, 18 June 2005 04:05

srraajesh
Messages: 63
Registered: May 2005

Member

In continuationto the thread

I have two versions of a queries

1 using indexes on both the tables but the consistent gets count is 360

2. Uses index on only one of the table but the consistent gets count is 15.

Which one is better??

Thanks
Raajesh

Report message to a moderator

Re: db block gets, consistent gets [message #124426 is a reply to message #124413]

Sat, 18 June 2005 06:30

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

the one with less consistent gets is better.

Report message to a moderator

Re: db block gets, consistent gets [message #160981 is a reply to message #123796]

Wed, 01 March 2006 04:36

lazycat
Messages: 57
Registered: November 2005

Member

good

Report message to a moderator

Re: db block gets, consistent gets [message #399992 is a reply to message #123812]

Fri, 24 April 2009 19:03

shrinika
Messages: 306
Registered: April 2008

Senior Member

Mahesh, I just got one question while reading this thread....
After comiting the another session, consistent gets count
is showing 19. Why is this? Once we committed, it should
read from buffer... not from rollback segment. Why is showing
19 when all the changes are committed for emp table...

I just tested this in my machine..I just restarted the database.
Physical reads 50 is fine. Since it has to read from data file
first time... Very first time, why it is making 100 consistent calls..
further run, it is constantly showing 8..

scott@orcl> set autotrace traceonly statistics
scott@orcl> select * from emp;

14 rows selected.


Statistics
----------------------------------------------------------
        515  recursive calls
          0  db block gets
        100  consistent gets
         15  physical reads
          0  redo size
       1397  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         14  rows processed

scott@orcl> /

14 rows selected.


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

scott@orcl> /

14 rows selected.


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

scott@orcl>

Report message to a moderator

Re: db block gets, consistent gets [message #399995 is a reply to message #399992]

Fri, 24 April 2009 19:57

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

It is NOT reading from RBS/UNDO.
quoting myself
>>consistent gets is the blocks in consistent mode (sometimes reconstructed using information from RBS)
Note "sometimes".
Information from RBS might be reconstructed if there is another session involved.
>>Why is showing 19
It is is what it is. It still needs to do somework to fetch data from buffer.
>> Very first time, why it is making 100 consistent calls..
>>further run, it is constantly showing 8..
The exact math used to derive this number is a little complex and depends on our version, block size and few more factors.
As you said,
First, it is read from datafile.
Further reads are much warm, from buffer.

You can easily test this without even bouncing the system.
http://www.orafaq.com/forum/m/165829/42800/?srch=165818

Report message to a moderator

Re: db block gets, consistent gets [message #400000 is a reply to message #399995]

Fri, 24 April 2009 22:44

shrinika
Messages: 306
Registered: April 2008

Senior Member

Thanks Mahesh.

Report message to a moderator

Re: db block gets, consistent gets [message #422916 is a reply to message #123796]

Mon, 21 September 2009 03:22

JuicyFruit
Messages: 34
Registered: September 2009

Member

What if I have query with such parameters?
It proceeds for about 10 minutes.

recursive calls 4605
db block gets 11
consistent gets 2915533
physical reads 65864
redo size 0
bytes sent via SQL*Net to client 3202
bytes received via SQL*Net from client 644
SQL*Net roundtrips to/from client 4
sorts (memory) 4588
sorts (disk) 1

Is consistent gets value too large?
and what about sorts (memory) and recursive calls values?

Report message to a moderator