请教：v$sqlarea中的问题

rchsh

to biti
你的列子怎么才能说明consistent gets
是读buffer的次数而不是字节数呢？

biti_rainy

541 consistent gets


能是字节数嘛？　当然这个问题是造就核对过了的


关于block的数量和读的次数，你自己可以测试

Yong Huang

You're showing your test again. I don't know why you think the fact that consistent gets equals buffer_gets proves that "the number of buffer gets is the number of times of buffer reading", instead of number of buffers read. Your conclusion may well be correct. But the test doesn't disprove that consistent gets or buffer_gets is the number of blocks read. If you can prove reading buffers can only be done one buffer at a time, then there's no ambiguity.

In fact, if you find the number of blocks in your table (user_tables.blocks after you analyze it), it should be close to your consistent gets. That seems to support the idea that consistent gets is at least number of blocks. Whether it's also number of accesses, I don't know.

BTW, consistent gets or buffer_gets will definitely not be number of bytes read.

Yong Huang

biti_rainy

buffer_gets = consistent gets + db block gets

我想就这个问题仔细描述我以前的看法
consistent gets 包含了这样一个过程：

1： query  mode 下访问block一次 计数 加 1 （同一个查询中可能访问多次）
2： 包括返回给前台数据的次数 (也就是你所说的 one  shot ),在我的理解中，当执行查询的时候并不是找到一条记录就理解返回给前台，而是找到一批  后返回给前台，这个一批放在哪里？ 在 PGA 中（是 数据还是  指针？）。找到一批数据后 再返回给前台，这样一次 也计入  consistent gets .  

关于 db block gets
current mode 下的读次数，在我的理解中，current  mode 应该是类似 pinned 的意思
除了FTS的数据字典获取外，主要在DML中，假如一个块中删除10条数据，则至少有10次访问该块而不是 访问一次(这样应该是 为了并发访问?)

rchsh

BITI
我做了一个测试：
create table test (id number(10));

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> set autotrace on;
SQL> select * from test;   

        ID
----------
         1


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




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

因为只有一条数据，查询时肯定是读了一次block，所以consistent gets=1，但是db block gets=4是什么意思1+4=5个逻辑读，这是怎么算的，不懂，请指教
SQL> insert into test select * from test;

1 row created.


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




Statistics
----------------------------------------------------------
          0  recursive calls
          5  db block gets
          2  consistent gets
          0  physical reads
        284  redo size
        647  bytes sent via SQL*Net to client
        464  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit;
插入一条记录，为什么db block gets变成5了，这时插入时访问快2次，一次是读，一次是写，这我理解，但是为什么db block gets变成5了
Commit complete.

SQL> select * from test;

        ID
----------
         1
         1


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




Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        310  bytes sent via SQL*Net to client
        358  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
这个时候consistent gets为什么变成2了，2条记录应该在一个块中，按理说取出这两条记录只读一次就行了，怎么访问了两次
SQL> set arraysize 1000
SQL> /

        ID
----------
         1
         1


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




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

biti_rainy

最初由 biti_rainy 发布
[B]buffer_gets = consistent gets + db block gets

关于 db block gets
current mode 下的读次数，在我的理解中，current  mode 应该是类似 pinned 的意思
除了FTS的数据字典获取外，主要在DML中，假如一个块中删除10条数据，则至少有10次访问该块而不是 访问一次(这样应该是 为了并发访问?) [/B]

注意红色这句话

对于 数据字典信息的获取，也计入  db  block  gets

rchsh

最初由 biti_rainy 发布
[B]

注意红色这句话

对于 数据字典信息的获取，也计入  db  block  gets [/B]

“假如一个块中删除10条数据，则至少有10次访问该块而不是 访问一次(这样应该是 为了并发访问?) ”

如果是这样的话consistent gets应该至少是数据表中数据行的行数？？？？

rchsh

SQL> select * from test;

        ID
----------
         1
         1
         1
         1
         3
         3

6 rows selected.


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




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

疑惑
现在数据库表中有6条记录
为什么consistent gets和db block gets没有发生变化
应该是6才行？？？？

biti_rainy

发生的时候 计入   db  block  gets ,  not  consistent gets  

为什么要用 select ?

请注意 ：  FTS and  DML  ,not  select

Yong Huang

最初由 rchsh 发布
[B]BITI
我做了一个测试：
create table test (id number(10));

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> set autotrace on;
SQL> select * from test;   

        ID
----------
         1


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




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

因为只有一条数据，查询时肯定是读了一次block，所以consistent gets=1，但是db block gets=4是什么意思1+4=5个逻辑读，这是怎么算的，不懂，请指教
[/B]

Oracle 9i eliminates unnecessary current mode gets. But prior to 9i, there's always (or may be most of the time I don't know) 4 db block gets for a very simple select. Jonathan Lewis pointed this out some time ago. chao_ping was aware of it. But I don't remember what they said.

If you have time, you may do this test. Configure a very small buffer cache. Startup the database. Record all buffers' touch count (x$bh.tch) in a temporary table. Do your simple select. Look at x$bh again to see what blocks are touched (i.e. tch has incremented). dbarfil and dbablk columns point you to the file and block number. x$bh.state tells you a touch of a buffer is done in consistent or current mode. 3 is consistent. 1 and 2 together are for current mode (Ref: www.ixora.com.au/q+a/0103/22151356.htm) BTW, tim is the time the buffer touch happened (Ref: Metalink note:276392.996)

Yong Huang