暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

AWR 中的consistent gets - examination

原创 Anbob 2012-03-05
521
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
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论