首页 » ORACLE » AWR 中的consistent gets – examination

AWR 中的consistent gets – examination

AWR
have 36,982.0 consistent gets examination per second.

“Consistent gets – examination” is different than regular consistent gets. It is used to read undo blocks for consistent read purposes, but also for the first part of an index read and hash cluster I/O. To reduce logical I/O, you may consider moving your indexes to a large blocksize tablespace. Because index splitting and spawning are controlled at the block level, a larger blocksize will result in a flatter index tree structure.

Steve Karam, OCM notes about “consistent gets – examination”:

“Consistent gets – examination are a different kind of consistent get that only requires a single latch, saving CPU. The most common use of a consistent get – examination is to read undo blocks for consistent read purposes, but they also do it for the first part of an index read and in certain cases for hash clusters.

So if you’re doing a query on a couple tables that are mostly cached, but one of them has uncommitted DML against it at the time, you’ll do consistent gets for the standard data in the cache, and the query will do consistent gets – examination to read the undo blocks and create read consistent blocks; this doesn’t necessarily save CPU unfortunately, because while the consistent gets – examination only acquire one latch, creating the read consistent data block also takes a latch.

However, I think that when you use single table hash clusters (or the new 10g Sorted Hash Clusters I mentioned once that automatically sort by a key so they don’t need order by) you can get a performance gain, because reads from the blocks of a hash cluster are usually consistent get – examination, therefore they only need one latch instead of two. ”

my e.g

--session 151

SQL> select sid,name,value from v$sesstat a,v$statname b where a.statistic#=b.statistic# and b.name like 'consistent%' and sid=151;

SID NAME VALUE
---------- ------------------------------ ----------
151 consistent gets 240
151 consistent gets from cache 240
151 consistent gets - examination 86
151 consistent gets direct 0
151 consistent changes 27

SQL> select * from testcur;

ID NAME
---------- ------------------------------
100 jack
200 jack
200 jack
1000 weejar
1000 weejar
2000 weejar
3000 anbob
4000 anbob
500 weejar
600 weejar

10 rows selected.

--session 152
SQL> update testcur set id=id+10;
10 rows updated.

--session 151

SQL> select * from testcur;

ID NAME
---------- ------------------------------
100 jack
200 jack
200 jack
1000 weejar
1000 weejar
2000 weejar
3000 anbob
4000 anbob
500 weejar
600 weejar

10 rows selected.

SQL> select sid,name,value from v$sesstat a,v$statname b where a.statistic#=b.statistic# and b.name like 'consistent%' and sid=151;

SID NAME VALUE
---------- ------------------------------ ----------
151 consistent gets 249
151 consistent gets from cache 249
151 consistent gets - examination 87
151 consistent gets direct 0
151 consistent changes 38
SQL> select * from testcur;

ID NAME
---------- ------------------------------
110 jack
210 jack
210 jack
1010 weejar
1010 weejar
2010 weejar
3010 anbob
4010 anbob
510 weejar
610 weejar

10 rows selected.

SQL> select sid,name,value from v$sesstat a,v$statname b where a.statistic#=b.statistic# and b.name like 'consistent%' and sid=151;

SID NAME VALUE
---------- ------------------------------ ----------
151 consistent gets 257
151 consistent gets from cache 257
151 consistent gets - examination 87
151 consistent gets direct 0
151 consistent changes 38

打赏

对不起,这篇文章暂时关闭评论。