Home > fundamental > autotrace的consistent gets

autotrace的consistent gets

November 14th, 2011 Leave a comment Go to comments

看到很多地方都有这样一个公式:

LIO=db block gets(current gets)+consistent gets

最近的一个小实验让我发现这个公式不是很严谨。具体如下:

SQL> set autot traceo
SQL> select * from n2;

100000 rows selected.

Elapsed: 00:00:16.23

Execution Plan
----------------------------------------------------------
Plan hash value: 1313880268

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|   192M|  7311   (1)| 00:01:28 |
|   1 |  TABLE ACCESS FULL| N2   |   100K|   192M|  7311   (1)| 00:01:28 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
40017  consistent gets
0  physical reads
0  redo size
3350410  bytes sent via SQL*Net to client
73818  bytes received via SQL*Net from client
6668  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
100000  rows processed

SQL> select /*+parallel(n2,4)*/* from n2;

100000 rows selected.

Elapsed: 00:00:21.84

Execution Plan
----------------------------------------------------------
Plan hash value: 3894062962

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   100K|   192M|  2026   (0)| 00:00:25 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |   100K|   192M|  2026   (0)| 00:00:25 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |   100K|   192M|  2026   (0)| 00:00:25 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| N2       |   100K|   192M|  2026   (0)| 00:00:25 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
20  recursive calls
0  db block gets
33548  consistent gets
33337  physical reads
0  redo size
3203330  bytes sent via SQL*Net to client
73818  bytes received via SQL*Net from client
6668  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
100000  rows processed

普通查询产生了40017个一致读,这个倒正常,但是并行读的话不是会bypass buffer cache的吗,怎么还会产生33548个一致读呢,按理说consistent gets应该很小才对。

用tom的runstats脚本跑了下,问题的根源基本浮出水面。。。

SQL> exec runStats_pkg.rs_stop;
Run1 ran in 7911 hsecs
Run2 ran in 7559 hsecs
run 1 ran in 104.66% of the time

Name                                  Run1        Run2        Diff
…

LATCH.In memory undo latch              32         330         298
STAT...Elapsed Time                  7,940       7,588        -352
STAT...CPU used when call star          64         459         395
STAT...CPU used by this sessio          64         460         396
STAT...physical read total mul           0       2,134       2,134
STAT...physical read IO reques           0       2,135       2,135
STAT...physical read total IO            0       2,135       2,135
LATCH.simulator hash latch           2,503           0      -2,503
STAT...consistent gets              40,023      33,555      -6,468
STAT...session logical reads        40,061      33,583      -6,478
STAT...no work - consistent re      40,004      33,337      -6,667
STAT...table scan blocks gotte      40,004      33,337      -6,667
STAT...table scan rows gotten      120,001     100,000     -20,001
STAT...DB time                          64      24,395      24,331
STAT...physical reads                    0      33,337      33,337
STAT...physical reads direct             0      33,337      33,337
STAT...consistent gets direct            0      33,337      33,337
STAT...consistent gets from ca      40,023         218     -39,805
LATCH.cache buffers chains          80,340         696     -79,644
STAT...PX local messages sent            0     107,181     107,181
STAT...PX local messages recv'           0     107,181     107,181
STAT...bytes sent via SQL*Net    3,351,051   3,228,971    -122,080
STAT...session uga memory max      123,512           0    -123,512
STAT...session pga memory max      131,072           0    -131,072
LATCH.process queue reference            0   1,182,634   1,182,634
STAT...physical read bytes               0 273,096,704 273,096,704
STAT...physical read total byt           0 273,096,704 273,096,704

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
107,667   1,208,796   1,101,129      8.91%

PL/SQL procedure successfully completed.

关注下这几个统计信息的区别:

STAT...physical reads                    0      33,337      33,337
STAT...physical reads direct             0      33,337      33,337
STAT...consistent gets direct            0      33,337      33,337
STAT...consistent gets from ca      40,023         218     -39,805

基本可以确定是consistent gets direct的问题了。查了下reference,发现除了consistent gets,db block gets跟physical reads都有direct,from cache的区分。

Name

Description

consistent gets Number of times a consistent read was requested for a block.
consistent gets direct Number of times a consistent read was requested for a block bypassing the buffer cache (for example, direct load operation). This is a subset of “consistent gets” statistics value.
consistent gets from cache Number of times a consistent read was requested for a block from buffer cache. This is a subset of “consistent gets” statistics value.
db block gets Number of times a CURRENT block was requested
db block gets direct Number of times a CURRENT block was requested bypassing the buffer cache (for example, a direct load operation). This is a subset of “db block gets” statistics value.
db block gets from cache Number of times a CURRENT block was requested from the buffer cache. This is a subset of “db block gets” statistics value.
physical reads Total number of data blocks read from disk. This value can be greater than the value of “physical reads direct” plus “physical reads cache” as reads into process private buffers also included in this statistic.
physical reads cache Total number of data blocks read from disk into the buffer cache. This is a subset of “physical reads” statistic.
physical reads direct Number of reads directly from disk, bypassing the buffer cache. For example, in high bandwidth, data-intensive operations such as parallel query, reads of disk blocks bypass the buffer cache to maximize transfer rates and to prevent the premature aging of shared data blocks resident in the buffer cache.

看到consistent gets,consistent gets direct,consistent gets from cache的解释,问题的根源也就基本上理清了,原来我一直误解了db block gets跟consistent gets,以为他们就是属于LIO的gets from buffer cache,现在看来这个想法欠妥,至少从autotrace中计算得出的LIO是不精确的,准确一点的LIO公式我觉得应该更正为:

LIO=db block gets from cache+consistent gets from cache.

Categories: fundamental Tags:
  1. No comments yet.
  1. No trackbacks yet.