Home » RDBMS Server » Server Administration » Consistent gets clarification (merged)
Consistent gets clarification (merged) [message #222500] Mon, 05 March 2007 03:44 Go to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi,

Can anyone explain about,
1. What is Consistent gets and db block gets?
2. When it'll happen?
3. How knowing its value helps in tuning?

Thanks
Consistent gets clarification [message #222504 is a reply to message #222500] Mon, 05 March 2007 04:13 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
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

<and so on...>

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

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

<all 4 rows in one block>
----------------------------------------------------------------

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...
Re: Consistent gets clarification [message #222528 is a reply to message #222504] Mon, 05 March 2007 05:21 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/198949/42800/?srch=consistent+gets#msg_198817

[Updated on: Mon, 05 March 2007 06:00]

Report message to a moderator

Previous Topic: How to know which tables are used by application
Next Topic: V$SGA size calculation
Goto Forum:
  


Current Time: Tue Dec 06 12:00:14 CST 2016

Total time taken to generate the page: 0.10052 seconds