|
-
Consistent gets clarificatin
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
12:42:56 SQL> select dbms_rowid.rowid_block_number(rowid) from dept;
more...
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
50450
50450
50450
50450
----------------------------------------------------------------
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...
-
2) One more clarification required.Eventhough all the 4 rows belong to the same data block,why 4 physical reads & 4 consistent gets required?
Because Full Table scan will read till the high water mark, no matter data is present or not.
Code:
SQL> select count(*) from a ;
COUNT(*)
----------
999
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
12 physical reads
0 redo size
412 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from a ;
COUNT(*)
----------
999
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
412 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> delete from a;
999 rows deleted.
Statistics
----------------------------------------------------------
3 recursive calls
1093 db block gets
18 consistent gets
27 physical reads
350888 redo size
678 bytes sent via SQL*Net to client
549 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
999 rows processed
SQL> commit;
Commit complete.
SQL> select count(*) from a ;
COUNT(*)
----------
0
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> truncate table a;
Table truncated.
SQL> select count(*) from a ;
COUNT(*)
----------
0
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
5 consistent gets
0 physical reads
96 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from a ;
COUNT(*)
----------
0
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-
after reboot, the sga is clean and only alloted the memory according to the init/spfile definition. And so, the first select sql has to be physically read from the datafile acquiring I/O and flashed to buffer cache.
And so, the second read from the same sql will only acquire less consistent gets because it reads data from buffer cache not from data file.
---------------
-
Did you notice # of recursive calls?
238 recursive calls - first time
0 recursive calls - 2nd time onwards.
-
Just to mention - consistent get does not allways mean that a read consistent view is maked by the undo segment.
Any time Oracle needs a block in consistent mode is called consistent get. That should answer why it does not takes blocks in current mode. Basically it's the same operation in this case, just is called consistent get since the buffer is needed in consistent mode
-
It's called consistent gets because in all queries Oracle gives a consistent view for Readers.
Whereas current mode are most used for DML operations. When we modify a row the row MUST be in the current state otherwise you are modifying something in the past which is ilogical and useless.
-
 Originally Posted by pando
It's called consistent gets because in all queries Oracle gives a consistent view for Readers.
There is only one user in the database and see the below test.
Code:
SQL> Alter system flush buffer_cache;
System altered.
SQL> set autotrace on stat
SQL> select count(*) from a;
COUNT(*)
----------
19
Statistics
----------------------------------------------------------
217 recursive calls
0 db block gets
29 consistent gets
14 physical reads
0 redo size
411 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
COUNT(*)
----------
19
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Why Oracle has to get a "Consistant get" while all the blocks are already in consistant mode so that a "db block det " will do? Is it the case that the "current" blocks reservered for any "expected updates"?
-
Hi
I suggest you to have a look at Concepts Guide Data concurrent and Consistency chapter. Also http://asktom.oracle.com/pls/asktom/...D:878847787577
In a query consistent gets is always shown. Query is a Reader, Reader always get consistent views which means consistent gets. The db block gets when we see in a query is from data dictionary, current mode must be used reading data dictionary because we always need the get the CURRENT state of the table.
-
The geeky coder in me likes this analogy (taken from Pando's link to TK's site):
for x in ( select rowid from emp ) --- CONSISTENT GETS
loop
delete from emp where rowid = x.rowid; --- CURRENT MODE GETS
end loop;
Assistance is Futile...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
