Home » RDBMS Server » Performance Tuning » difference between db block gets and consistent gets (merged)
difference between db block gets and consistent gets (merged) [message #198717] Wed, 18 October 2006 06:00 Go to next message
reena_ch30
Messages: 100
Registered: December 2005
Senior Member
Hi,

What is the difference between db block gets and consistent gets? Please explain with example.

Thanks
Reena
Re: difference between db block gets and consistent gets [message #198718 is a reply to message #198717] Wed, 18 October 2006 06:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As usual, please search before posting.
http://www.orafaq.com/forum/m/123812/42800/?srch=db+block+gets#msg_123812
Re: difference between db block gets and consistent gets [message #198724 is a reply to message #198717] Wed, 18 October 2006 06:12 Go to previous messageGo to next message
reena_ch30
Messages: 100
Registered: December 2005
Senior Member
Hi,

Does it mean, consistent gets are "Reads" from the undo segments and db gets are "Reads" from the db buffer?

Regards,
reena
Re: difference between db block gets and consistent gets [message #198728 is a reply to message #198717] Wed, 18 October 2006 06:30 Go to previous messageGo to next message
readytestgo
Messages: 5
Registered: October 2006
Location: Chennai
Junior Member
consistent gets - are the one that are to be reconstructed from the rollback segments.
db block gets - are the number of blocks obtained in CURRENT mode, directly from the db cache

For ex, if a data block needed by a user request is available in the db cache it is considered to be db block gets, whereas if the block needed is present in the db cache but has been modified by another user it has to be reconstructed from the rollback segment this is known as consistent gets.


RTGPerf,
http://groups.google.com/group/database-tuning
Re: difference between db block gets and consistent gets [message #198730 is a reply to message #198717] Wed, 18 October 2006 06:36 Go to previous messageGo to next message
reena_ch30
Messages: 100
Registered: December 2005
Senior Member
Hi,

Thanks for your response. One more thing. What would db block gets as 0, consistent gets as 0 and physical reads as 0 signify even with records are fetched from query?? Shouldn't atleast one of the parameters have value??


Regards,
reena
Re: difference between db block gets and consistent gets [message #198731 is a reply to message #198730] Wed, 18 October 2006 06:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Oracle documentation will define it as
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/stats002.htm#sthref4653

consistent gets = Number of times a consistent read was requested for a block.
db block gets = Number of times a CURRENT block was requested

>>Does it mean, consistent gets are "Reads" from the undo segments
Not always.
Consistent read( or read consistency) is the main concept here.
Say you execute a query. Oracle will fetch those rows (blocks) in a consistent mode only.
If those fetched blocks are not CONSISTENT (changes are made in during this period of fetch or there are some uncommited changes to those blocks), it is oracle responsibility to provide a read consistent data. To do so, Oracle will reconstruct the data from undo information and provides it to user.

the db block gets are the most current information from the buffer.
Re: difference between db block gets and consistent gets [message #198733 is a reply to message #198717] Wed, 18 October 2006 06:45 Go to previous messageGo to next message
reena_ch30
Messages: 100
Registered: December 2005
Senior Member


I execute a query "select * from emp" for the first time and re-execute it . (assuming no other user is connected).

As per the behaviour, i should get value in physical reads for the first time and then in db gets (since rows are retrieved from buffer)at the scond time with 0 physical reads.. Is that how it behaves??


Regards,
reena



Re: difference between db block gets and consistent gets [message #198736 is a reply to message #198733] Wed, 18 October 2006 07:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:878847787577
Re: difference between db block gets and consistent gets [message #198880 is a reply to message #198717] Wed, 18 October 2006 23:16 Go to previous messageGo to next message
reena_ch30
Messages: 100
Registered: December 2005
Senior Member
I could understand the current mode and consistent mode. But still not clear as to when would Physical reads have value? And what does value of 0 in consitent read, db block gets and physical reads signify?(even though rows are fetched from query!!).

Thanks
reena
Re: difference between db block gets and consistent gets [message #198893 is a reply to message #198880] Thu, 19 October 2006 00:50 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You get Physical Reads when the data is not in cache or undo, and Oracle has to get it from disk.

Not sure why all would be zero. These should appear even if TIMED_STATISTICS is not set. Show us the output.

Ross Leishman
Re: difference between db block gets and consistent gets [message #198894 is a reply to message #198717] Thu, 19 October 2006 00:59 Go to previous messageGo to next message
reena_ch30
Messages: 100
Registered: December 2005
Senior Member
SQL> show parameter timed

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE


And see the result of the following statement
SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'




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

SQL> 


[Updated on: Thu, 19 October 2006 05:13] by Moderator

Report message to a moderator

Re: difference between db block gets and consistent gets [message #198899 is a reply to message #198894] Thu, 19 October 2006 01:25 Go to previous messageGo to next message
readytestgo
Messages: 5
Registered: October 2006
Location: Chennai
Junior Member
I guess you are logged-in as sys user, login as someother user ex. scott then see the execution plan, you will get the correct execution plan.


RTGPerf,
http://groups.google.com/group/database-tuning
Re: difference between db block gets and consistent gets [message #198905 is a reply to message #198717] Thu, 19 October 2006 01:51 Go to previous messageGo to next message
reena_ch30
Messages: 100
Registered: December 2005
Senior Member
I logged in as scott and rexecute the query . Now i get 4 in consistent gets. Rows processsed are 14. can you justify as to how the figure of 4 has reached?? I would really apprecite it.

Thanks
reena
Re: difference between db block gets and consistent gets [message #198916 is a reply to message #198905] Thu, 19 October 2006 02:53 Go to previous messageGo to next message
readytestgo
Messages: 5
Registered: October 2006
Location: Chennai
Junior Member

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:865586003021


RTGPerf,
http://groups.google.com/group/database-tuning
Re: difference between db block gets and consistent gets [message #198931 is a reply to message #198717] Thu, 19 October 2006 03:56 Go to previous messageGo to next message
reena_ch30
Messages: 100
Registered: December 2005
Senior Member
referring to url http://www.fors.com/orasupp/rdbms/misc/423_1.HTM , following definition is given for consistent gets and db block gets.

CONSISTENT GETS: Number of blocks accessed in buffer cache for normal queries (SELECTs without for update clause)
db block gets:Number of blocks accessed in buffer cache for INSERT,
UPDATE, DELETE and SELECT FOR UPDATE.

Is this true? If yes, then for any query on a table, it should always be consistent gets. Am i correct??

Reena
Re: difference between db block gets and consistent gets [message #198949 is a reply to message #198931] Thu, 19 October 2006 05:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Is this true? If yes, then for any query on a table, it should always be consistent gets. Am i correct??
The said document seems to a metalink contetnt giving just another definition of what you already know.
WHy not try it? post the results.
Re: difference between db block gets and consistent gets [message #198952 is a reply to message #198717] Thu, 19 October 2006 05:30 Go to previous messageGo to next message
reena_ch30
Messages: 100
Registered: December 2005
Senior Member
yes.. It is working that ways. db block gets has value only incase of insert, update, delete operations.
SQL> select * from emp;

15 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1337  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)
         15  rows processed



SQL>  update emp set ename='TEST1' where empno=7934;

1 row updated.


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
        244  redo size
        624  bytes sent via SQL*Net to client
        542  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

  1* insert into emp(EMPNO) VALUES(9000)
SQL> /

1 row created.

Statistics
----------------------------------------------------------
          0  recursive calls
          3  db block gets
          1  consistent gets
          0  physical reads
        420  redo size
        624  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed




SQL>SELECT * FROM EMP;

statistics
------------------------------------------------------
      0  recursive calls
      0  db block gets
      4  consistent gets
      0  physical reads
      0  redo size
   1467  bytes sent via SQL*Net to client
    510  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     16  rows processed



SQL> DELETE FROM EMP
16 rows deleted.

Statistics
----------------------------------------------------------
          0  recursive calls
         50  db block gets
          3  consistent gets
          0  physical reads
       7464  redo size
        624  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         16  rows processed

[Updated on: Thu, 19 October 2006 05:35] by Moderator

Report message to a moderator

Re: difference between db block gets and consistent gets [message #198954 is a reply to message #198952] Thu, 19 October 2006 05:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> db block gets has value only incase of insert, update, delete operations
Because it is now fetching the changed information (changes after insert/update/delete). Right?
the previous definition we stated from oracle doc is:
>> the db block gets are the most current information from the buffer.

So what you think?
Are'nt both the same?
Re: difference between db block gets and consistent gets [message #198956 is a reply to message #198717] Thu, 19 October 2006 05:41 Go to previous messageGo to next message
reena_ch30
Messages: 100
Registered: December 2005
Senior Member
Yes..But it is more clear now.

Thanks
Reena
Physical reads, consistent gets, db block gets [message #324077 is a reply to message #198717] Fri, 30 May 2008 11:52 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Dear all,

I have bit confusion between consistent gets and db block gets First let me explain my understanding here..

Physical reads

Physical reads reads data from disk.

Consistent gets

Consistent gets gets the data in a block which is consistent with a given point in time. It reads data from the buffer cache through consistent mode. Consistent mode means that, oracle does not need to reconstruct the data from rollback segment...

db block gets

I believe, it gets the data from buffer cache.. I am not sure what is the exact difference between consistent gets and db block gets...

Any clarification appreicated...
Re: Physical reads, consistent gets, db block gets [message #324080 is a reply to message #324077] Fri, 30 May 2008 12:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just search. Topic is discussed too many times Smile
One such
http://www.orafaq.com/forum/m/198949/42800/?srch=consistent+gets#msg_198817

[Updated on: Fri, 30 May 2008 12:09]

Report message to a moderator

Re: Physical reads, consistent gets, db block gets [message #324284 is a reply to message #324080] Sun, 01 June 2008 08:50 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Mahesh Rajendran wrote on Fri, 30 May 2008 13:09
Just search. Topic is discussed too many times Smile
One such
http://www.orafaq.com/forum/m/198949/42800/?srch=consistent+gets#msg_198817



Hello Mahesh, Thank you for your response. I read above mentioned link and asktom thread too. The information is very clear and useful. I have one question. Let me jump into above link and ask the question. Thank you again.
Re: difference between db block gets and consistent gets [message #324286 is a reply to message #198956] Sun, 01 June 2008 09:16 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Dear Mahesh, thanks for all your input in this thread...

My question here is, when you run the query very first time, oracle fetch the data from data file and place in the buffer cache and send the result to client...

Here is the steps i am following...

1. restart the database(running from my home computer)
2. login the scott user and run the query.

My understanding is, oracle should take only physical reads when we run very first time. Since it is taking from data file...

below output shows that, it is taking consistent reads also. But the data never be in memeory first time. How come it is taking logical reads?? First time, logical reads should be zero..

Please explain

SQL> startup force;
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> connect scott/tiger@oraprod
Connected.
SQL> set linesize 50
SQL> set autotrace on
SQL> select empno from emp;

EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

14 rows selected.


Execution Plan
--------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE


1 0
TABLE ACCESS (FULL) OF 'EMP'

Statistics
--------------------------------------------------
250 recursive calls
0 db block gets
47 consistent gets
5 physical reads
0 redo size
520 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)
14 rows processed

SQL> set linesize 1000
SQL> /

EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876-
7900
7902
7934

14 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
520 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)
14 rows processed

SQL>
Thanks again.

Re: difference between db block gets and consistent gets [message #324292 is a reply to message #324286] Sun, 01 June 2008 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

The first time you also have 250 recursive calls in order to compile the statement.
The second time data are in buffer cache, so you only have logical io (consistent gets).
(I don't know what is your purpose of set linesize, keep it to 80)

Regards
Michel
Re: difference between db block gets and consistent gets [message #324619 is a reply to message #324292] Tue, 03 June 2008 06:19 Go to previous message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Michel, Thanks for answers. Here after i will format my posting.. Thanks again
Previous Topic: Metric Critical Alert - should I care?
Next Topic: View Performance tunning
Goto Forum:
  


Current Time: Sat Jun 22 21:57:23 CDT 2024