ANBOB™

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
        

            打赏
            

                
                
微信扫一扫，打赏作者吧～