请教：v$sqlarea中的问题

aleckqian

从ASKTOM上看到的文章，不知道是否与讨论主题有关。
Tom:
create table test( a int);
begin
for i in 1..10000 loop
insert into test values (i);
end loop;
end;

set autotrace on
select count(0) from test;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST'

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


select * from test where a=10;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        360  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


select * from test;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
        686  consistent gets
          0  physical reads
          0  redo size
     185864  bytes sent via SQL*Net to client
      74351  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10001  rows processed



question:
1.
when query count(0), what exactly did oracle do? since we don't have index, did
oracle went to every block and query the number?
if so, why consistent gets is much lower that select * from test?
both are full scan table.

2.
why select * from test have much higher consistent gets than
select * from test where a=10?
since there are no index, oracle need to go to every block to get the value, it
should be same consistent gets, although the first one return more rows, but it
does't matter.

Regards,



--------------------------------------------------------------------------------
and we said...

q1) select count(0) is just like

select count(*)
  from ( select 0 from t )
/

yes, oracle went to each block to find the rows to give you a zero

q2) its a side effect of your arraysize.  You must have an 8k blocksize cause I
reproduced this exactly.

We were expecting about 20 consistent gets right?  Well, the default array size
in sqlplus is 15 rows / fetch.  10000/15 = 666.66666.  Well, 666+20 = 686 --
whoah there -- 686 is our consistent gets!

Thats what happened.  When you fetched 15 rows, Oracle paused, gave you the
data.  When you went back to get the next 15, it got the buffer again to resume
your query.

Watch what happens with different array sizes, starting with 15:


ops$tkyte@8i> select * from test;

10000 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
        686  consistent gets
          0  physical reads
          0  redo size
     108813  bytes sent via SQL*Net to client
      46265  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

ops$tkyte@8i> set arraysize 1000
ops$tkyte@8i> select * from test;

10000 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
      86266  bytes sent via SQL*Net to client
        942  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

ops$tkyte@8i> set arraysize 5000
ops$tkyte@8i> select * from test;

10000 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
     149793  bytes sent via SQL*Net to client
        382  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed



Other interesting thing to note is that as the array size gets too large -- the
amount of data transferred goes up.  there is a diminishing marginal return on
the array size so don't go OVERBOARD.

followup to comment one

Set the arraysize to some constant as it will not matter for single row fetches
whether it is too big but having it too small for lots of rows (NOT just full
scans -- lots of rows) does impact you.

biti_rainy

关于 sum(tch)   ,consistent getes ,还有 v$sysstat 中各种关于consistent gets 的变化
也看了 ixora 的一些东西

但是，偶还是没有找出问题所在

因为  sum(tch)  的增长 可能很小，consistent gets 的变化却很大！
但是我仅仅是做了个FTS ,也没有道理说 touch one times of a  block 产生 多个 consistent gets

在v$sysstat 中相关的数据 看起来也没有合理的解释

所以偶正迷惑着呢

9i 中  对于数据字典的获取不计入  db  block gets (current  mode ) ，这个可能是跟 9i 中 的算法的改进有关,chao_ping 也仅仅是做了个猜测：

Oracle 8i 的时候，为了读取DataBuffer，需要去那个hash bucket之类的东西里面读取，之所以只有对FULL Scan的时候才产生db_block_gets, ，由于full scan的时候，Oracle会把由于full scan读取的Block放在LRU的最先淘汰的一段，这样，还需要修改管理data buffer所在的shared pool里面的一些内存区域，所以，在Oralce8i 里面，会有db_block_gets, 而读取那个东西，则需要用Exclusive 模式，这个就产生了current mode的logical io.

在9i 里面，由于做了改进，就不需要了。
猜测而已，没有见过正式文档。
  

btw :偶对 hash bucket 一直不清晰，可否推荐比较系统一点的资料 ?

apaches

学了不少东西，感谢斑竹和各位达人的帮助。

husthxd

关于arraysize会影响统计信息中的consistent gets的问题tom有一个很好的解析.
http://asktom.oracle.com/pls/ask ... LAYID:6749454952894

草中宝

根据biti_rainy和我自己的测试，发现v$sqlarea中的disk_reads和buffer_gets确实是会累计的。buffer_gets与consistent gets也有某种关系。
但是我不能证明disk_reads就是各次物理读的次数的累计，或者是各次物理读的块数的累计，又或者是各次物理读的字节数的累计。同样也不能证明buffer_gets就是各次逻辑读的次数的累计，或者是各次逻辑读的块数的累计，又或者是各次逻辑读的字节数的累计。

根据ORACLE文档的解释
DISK_READS    The sum of the number of disk reads over all child cursors
BUFFER_GETS The sum of buffer gets over all child cursors

DISK_READS和BUFFER_GETS也没有明确说是次数的累计，或者是块数的累计，又或者是字节数的累计。

对于fable_cao朋友说的"consistent gets 是从buffer cache读取buffer的old image的次数，就是说该buffer是从回滚段中读取的，因为在buffer cache中保留着block的几个版本。"
感觉也不尽对，一个数据库，只有你一个人在操作，执行两次
select count(*) from t;
数据并不存在什么old image，所以我觉得Yong Huang版主说的"Consistent gets are not necessarily reads of old images. If there's no old image, it reads the current image."还是有道理的。

另外我有个疑问，对于小表，连续的两次
select count(*) from t;
第一次操作也许需要物理读，第二次操作也许不需要物理读了，而是纯粹的逻辑读了，但是第二次操作为什么会是从consistent gets中获取数据，而不是从db block gets中获取数据呢。照我的理解，consistent gets发生于回滚段，db block gets发生于db cache。

还要请各位DX的指教。

Yong Huang

最初由 草中宝 发布
[B]
...Yong Huang版主说的"Consistent gets are not necessarily reads of old images. If there's no old image, it reads the current image."还是有道理的。

另外我有个疑问，对于小表，连续的两次
select count(*) from t;
第一次操作也许需要物理读，第二次操作也许不需要物理读了，而是纯粹的逻辑读了，但是第二次操作为什么会是从consistent gets中获取数据，而不是从db block gets中获取数据呢。照我的理解，consistent gets发生于回滚段，db block gets发生于db cache。
[/B]

You said you agree that "Consistent gets are not necessarily reads of old images." Then why do you say consistents gets happens in the rollback segment? Consistent gets are simply data buffer reads. The word "consistent" means if this buffer happens to have an old image (kept in a rollback segment), that old buffer will be read instead. If not, as in your simple case of select count(*) from t, then the buffers of T are read once more.

Both consistent gets and db block gets are read of buffer cache. Db block gets are done on segment headers (header blocks of T in your case) and data dictionary in SYSTEM tablespace. No check is done on their old images.

Regarding the unit of disk reads, I wrote a short note some time ago at
http://rootshell.be/~yong321/computer/diskreadunit.txt
I think the unit is number of blocks, not number of times of disk read, contrary to documentation's explanation. But I don't know the unit for buffer access.

Yong Huang

草中宝

原来是这样，谢谢Yong Huang

我原来是对
If not, as in your simple case of select count(*) from t, then the buffers of T are read once more.
这句没有很好的理解。

cc59

Yong Huang版主：
能否给大家分析一下analyze与dbms_stats的区别呢？

Yong Huang

最初由 cc59 发布
[B]Yong Huang版主：
能否给大家分析一下analyze与dbms_stats的区别呢？ [/B]

I can't say better than Metalink Note:237293.1 and other notes referenced in there. One small lesson I learned not mentioned in those notes is that if you run dbms_stats.gather_table_stats on a partitioned table, and later you run analyze on that partitioned table, some stats are not updated. I just did a test in Oracle 9.2. After a batch insert and ANALYZE (DBMS_STATS was run earlier), user_tables.num_rows is smaller than the sum of rows in all my 3 partitions:

SQL> select num_rows from user_tables where table_name = 'PART';

  NUM_ROWS
----------
       195 <-- old value here. Should be 123+131+32=295

SQL> select num_rows from user_tab_partitions where table_name = 'PART';

  NUM_ROWS
----------
       132
       131
        32

So don't mix dbms_stats and analyze. If you want to revert to the old analyze, delete statistics and start over.

Yong Huang

Yong Huang

Correction/addition. The problem is really the mix of analyze and dbms_stats. Somebody can spend time listing all the missing statistics under all conditions when you mix the two stats collection methods. It may not be worth the effort though. Analyze delete after dbms_stats.delete_table_stats doesn't delete all stats. So the list should also include anomalies of NOT deleting stats under XYZ conditions.

Yong Huang