Home » RDBMS Server » Performance Tuning » db block gets, consistent gets
db block gets, consistent gets [message #123796] Tue, 14 June 2005 18:14 Go to next message
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.
Re: db block gets, consistent gets [message #123812 is a reply to message #123796] Tue, 14 June 2005 22:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10671
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
 
 




Re: db block gets, consistent gets [message #124413 is a reply to message #123796] Sat, 18 June 2005 04:05 Go to previous messageGo to next message
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
Re: db block gets, consistent gets [message #124426 is a reply to message #124413] Sat, 18 June 2005 06:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10671
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
the one with less consistent gets is better.
Re: db block gets, consistent gets [message #160981 is a reply to message #123796] Wed, 01 March 2006 04:36 Go to previous messageGo to next message
lazycat
Messages: 57
Registered: November 2005
Member
good
Re: db block gets, consistent gets [message #399992 is a reply to message #123812] Fri, 24 April 2009 19:03 Go to previous messageGo to next message
shrinika
Messages: 252
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> 


Re: db block gets, consistent gets [message #399995 is a reply to message #399992] Fri, 24 April 2009 19:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10671
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
Re: db block gets, consistent gets [message #400000 is a reply to message #399995] Fri, 24 April 2009 22:44 Go to previous messageGo to next message
shrinika
Messages: 252
Registered: April 2008
Senior Member
Thanks Mahesh.
Re: db block gets, consistent gets [message #422916 is a reply to message #123796] Mon, 21 September 2009 03:22 Go to previous messageGo to next message
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?
Re: db block gets, consistent gets [message #422917 is a reply to message #422916] Mon, 21 September 2009 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is normal if you ask Oracle to do a lot of work with lot of consistent gets and lot of recursive calls.

Regards
Michel

[Updated on: Mon, 21 September 2009 03:34]

Report message to a moderator

Re: db block gets, consistent gets [message #422919 is a reply to message #422916] Mon, 21 September 2009 03:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10671
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch8.htm#sthref1523
Re: db block gets, consistent gets [message #422921 is a reply to message #123796] Mon, 21 September 2009 04:05 Go to previous messageGo to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member

Thanks,
but how can I reduce consistent gets and recursive calls number?
Re: db block gets, consistent gets [message #422922 is a reply to message #422921] Mon, 21 September 2009 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
By reducing the work you ask Oracle to do.

Regards
Michel
Re: db block gets, consistent gets [message #422923 is a reply to message #123796] Mon, 21 September 2009 04:15 Go to previous messageGo to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member

yeah, I understand that:)

but what operation/query makes large amount of consistent gets and recursive calls?
Re: db block gets, consistent gets [message #422925 is a reply to message #422923] Mon, 21 September 2009 04:25 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reading (used/modified) buffers, calling triggers.

Regards
Michel

[Updated on: Mon, 21 September 2009 04:26]

Report message to a moderator

Previous Topic: Performance Tuning
Next Topic: FLUSH SHARED_POOL
Goto Forum:
  


Current Time: Mon Sep 26 02:34:17 CDT 2016

Total time taken to generate the page: 0.07518 seconds