请教：v$sqlarea中的问题

apaches · 发表于 2003-5-9 16:26

由于有条sql运行特别慢，所以在v$sqlarea跟踪。
select disk_reads,buffer_gets, sql_text from v$sqlarea
发现有一个sql的disk_reads,buffer_gets特别大.分别为14920757 15014272
请问:这两个参数的单位时bytes还是block？
如果根据这两个参数来调整db_block_buffer?

rchsh · 发表于 2003-5-9 16:35

应该是block吧

husthxd · 发表于 2003-5-9 16:40

把sql语句贴出来看看

db1 · 发表于 2003-5-9 17:15

不是BYTE也不是BLOCK，是次数。

biti_rainy · 发表于 2003-5-9 17:50

但可能是累积的

所以这个不能说明问题
得要看执行过多少次来看平均数

apaches · 发表于 2003-5-10 09:16

数据库是7.3.4的。OS:IBM AIX , S7A ,4CPU ,2G MEM.
我觉得速度慢会不会跟数据库的内存配置有关。
这个数据库的SHARED_POOL_SIZE=500M,db_block_buffers=20000,db_block_size=4k，但shared_pool的实际使用也就30来M，而db_block_buffers的hit radio只有85%。

Yong Huang · 发表于 2003-5-11 10:52

最初由 db1 发布
[B]不是BYTE也不是BLOCK，是次数。 [/B]

That's right.

BTW, v$sqlarea should not be used unless you want to look at the parent cursor only (in case there're child cursors). v$sql should be used instead, for performance reason. v$sqlarea.optimizer_mode will show 'MULTIPLE CHILDREN PRESENT' if the cursor has child cursors.

Yong Huang

biti_rainy · 发表于 2003-5-11 11:14

buffer_gets

单位应该是 blocks * 读

假如一个full  table scan 读了10个block,则 buffer_gets = 10
假如是通过index  获取数据，可能是  buffer_gets > 10 ,跟获取数据量和  block 中容纳数据条数和数据的有序程度有关

片面的说成是 blocks  或者次数  都是不确切的，应该参考  consistent gets 的意义 ?

lizhaohua1998 · 发表于 2003-5-11 12:38

两个参数的单位应该多不是bytes,block,要根据你的语句才能看的出

biti_rainy · 发表于 2003-5-11 13:17

buffer_gets =  consistent get +  db block gets  =  logic  IO

SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace on
SQL> select count(*) from t;

  COUNT(*)
----------
   25374

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1)
1 0 SORT (AGGREGATE)
2 1    INDEX (FAST FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=1
      2 Card=25376)

Statistics
----------------------------------------------------------
      242  recursive calls
      4  db block gets
      132  consistent gets   132 + 4 = 136
      80  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
      5  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> set autotrace off
SQL> select buffer_gets,disk_reads from v$sqlarea where sql_text like 'select count(*) from t%';

BUFFER_GETS DISK_READS
----------- ----------
      136       80    132 + 4 = 136

SQL> select count(*) from t;

  COUNT(*)
----------
   25374
SQL> select count(*) from t;

  COUNT(*)
----------
   25374

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1)
1 0 SORT (AGGREGATE)
2 1    INDEX (FAST FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=1
      2 Card=25376)

Statistics
----------------------------------------------------------
      0  recursive calls
      4  db block gets
      78  consistent gets   78 + 4 = 82
      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

SQL>

SQL>  select buffer_gets,disk_reads from v$sqlarea where sql_text like 'select count(*) from t%';

BUFFER_GETS DISK_READS
----------- ----------
      218       80 136 + 82 = 218

SQL> select count(*) from t;

  COUNT(*)
----------
   25374

SQL>  select buffer_gets,disk_reads from v$sqlarea where sql_text like 'select count(*) from t%';

BUFFER_GETS DISK_READS
----------- ----------
      300       80 218 + 82 = 300

SQL>

[精华] 请教：v$sqlarea中的问题

是 blocks*读

这个SQL一般每天要跑一遍，难道ORACLE都把他加起来？

我想这个问题应该明确下

我们可以看出

浏览过的版块