|
|
| Consistent gets clarification [message #222504 is a reply to message #222500] |
Mon, 05 March 2007 04:13   |
gajini
Messages: 262
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...
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
