|
|
|
Re: difference between db block gets and consistent gets [message #198728 is a reply to message #198717] |
Wed, 18 October 2006 06:30   |
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 #198733 is a reply to message #198717] |
Wed, 18 October 2006 06:45   |
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 #198894 is a reply to message #198717] |
Thu, 19 October 2006 00:59   |
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 #198952 is a reply to message #198717] |
Thu, 19 October 2006 05:30   |
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
|
|
|
|
|
Physical reads, consistent gets, db block gets [message #324077 is a reply to message #198717] |
Fri, 30 May 2008 11:52   |
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: difference between db block gets and consistent gets [message #324286 is a reply to message #198956] |
Sun, 01 June 2008 09:16   |
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.
|
|
|
|
|