问个关于v$sql 里的disk_read 的概念问题
大家好，问个问题，有个概念不是很清楚 ，就是v$sql 里的disk_read 是指的什么数据库是测试库，版本10.2.0.1 oel 5.4 32位
下面是我的测试结果：
select segment_name,file_id,BLOCK_ID,blocks from dba_extents where segment_name='TEST'
SQL> /
SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ----------
TEST 5 73 8
TEST 5 1609 8
这test表是16个block
select disk_reads,sql_id,sql_text from v$sql where sql_text like 'select * from hebin.test%';
DISK_READS SQL_ID
---------- ---------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
23 f75jwdabk8b8z
select * from hebin.test
DISK_READS值是23
而我是做了10046的
STAT #9 id=1 cnt=3963 pid=0 pos=1 obj=9881 op='TABLE ACCESS FULL TEST (cr=280 pr=14 pw=0 time=4122 us)'
显示我读了14个物理块
就不知道这23是怎么来的 而且有时候这个值回事121 或者70多 老变化
SQL> select * from hebin.test;
3963 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 13 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
951 recursive calls
142 db block gets
616 consistent gets
23 physical reads
0 redo size
57930 bytes sent via SQL*Net to client
3288 bytes received via SQL*Net from client
266 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
3963 rows processed
第二个问题：
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 14 0.00 0.00 0 5 25 0
Execute 14 0.02 0.02 8 270 2 2
Fetch 280 0.03 0.02 16 298 0 4006
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 308 0.06 0.05 24 573 27 4008
这是tkprof之后的
为什么执行时候读了8个块 fetch执行了16个块？
这两个加一起 就是 disk_read 值？
谢谢～
DISK_READS NUMBER Number of disk reads for this child cursor iloracle 发表于 2014-2-27 15:08 static/image/common/back.gif
DISK_READS NUMBER Number of disk reads for this child cursor
我知道这个手册上的解释，我是问 怎么算出来的 这23是从哪来的 ？ 1、给出你的完整测试用例
2、你的测试用例在迷惑你
3、
create table mac1 tablespace users as select * from dba_objects where rownum<=1000;
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set events '10046 trace name context forever,level 12';
set autotrace on;
select /* FINDME */ * from mac1;
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
71 recursive calls
0 db block gets
148 consistent gets
21 physical reads
0 redo size
52744 bytes sent via SQL*Net to client
1250 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> SQL> SQL>
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/s01/diag/rdbms/asmdb1/ASMDB1/trace/ASMDB1_ora_32532.trc
4wu85d4578n5v
select /* FINDME */ * from mac1
SQL> select disk_reads from v$SQL where sql_id='4wu85d4578n5v';
DISK_READS
----------
21
$ tkprof /s01/diag/rdbms/asmdb1/ASMDB1/trace/ASMDB1_ora_32532.trc 32532.tkf
TKPROF: Release 11.2.0.3.0 - Development on Fri Feb 28 00:32:36 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
select /* FINDME */ *
from
mac1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 2 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 82 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 2 84 0 1000
重新测试了一遍，附件里有新的测试说明和trace文件 请大师帮忙看看
Maclean Liu(刘相兵 发表于 2014-2-28 13:42 static/image/common/back.gif
1、给出你的完整测试用例
2、你的测试用例在迷惑你
发了个第二次的 完整测试用例 请帮忙看看 谢谢～
我不清楚你的测试是否刻意忽略 自动采样的影响，还是你没有意识到这个问题，实验必须足够精确。
这里为了体现自动采样的影响 采取了大表+optimizer_dynamic_sampling=10
Release 11.2.0.3.0
alter session set optimizer_dynamic_sampling=10;
drop table mac1;
create table mac1 tablespace users as select * from dba_objects ;
alter table mac1 nologging;
insert /*+ append */ into mac1 select * from mac1;
commit;
insert /*+ append */ into mac1 select * from mac1;
commit;
insert /*+ append */ into mac1 select * from mac1;
commit;
insert /*+ append */ into mac1 select * from mac1;
commit;
set arraysize 5000;
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set events '10046 trace name context forever,level 12';
oradebug setmypid
oradebug tracefile_name
set autotrace traceonly;
select /* FINDME1 */ * from mac1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2139598695
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1137K| 224M| 4461 (1)| 00:00:54 |
| 1 | TABLE ACCESS FULL| MAC1 | 1137K| 224M| 4461 (1)| 00:00:54 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=10)
Statistics
----------------------------------------------------------
71 recursive calls
2 db block gets
32678 consistent gets
16198 physical reads
168 redo size
116305305 bytes sent via SQL*Net to client
3021 bytes received via SQL*Net from client
229 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1137216 rows processed
select disk_reads ,sql_id,sql_text from v$SQL where sql_text like '%FINDME%';
16198 bxx69d960vs2n
select /* FINDME1 */ * from mac1
/s01/diag/rdbms/asmdb1/ASMDB1/trace/ASMDB1_ora_1760.trc
select /* FINDME1 */ *
from
mac1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 4 6 2 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 229 0.83 1.53 0 16424 0 1137216
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 231 0.83 1.53 4 16430 2 1137216
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1137216 1137216 1137216 TABLE ACCESS FULL MAC1 (cr=16424 pr=0 pw=0 time=253938 us cost=4460 size=235403712 card=1137216)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 229 0.00 0.00
SQL*Net message from client 229 0.01 3.01
SQL*Net more data to client 5412 0.00 0.73
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)
FROM
(SELECT /*+ NO_PARALLEL("MAC1") FULL("MAC1") NO_PARALLEL_INDEX("MAC1") */ 1
AS C1, 1 AS C2 FROM "SYS"."MAC1" "MAC1") SAMPLESUB
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.50 0.51 16188 32399 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.50 0.51 16188 32399 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=16200 pr=8094 pw=0 time=256684 us)
1137216 1137216 1137216 TABLE ACCESS FULL MAC1 (cr=16200 pr=8094 pw=0 time=276240 us cost=4446 size=0 card=1335877)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 143 0.00 0.08
从上面可以看到
autotrace 和 disk_reads 其实都里包含了 自动统计信息采样的物理读，其实还包括其他一些递归调用的物理读。
10046里体现的是该查询当时真正的统计值
Maclean Liu(刘相兵 发表于 2014-2-28 16:21 static/image/common/back.gif
我不清楚你的测试是否刻意忽略 自动采样的影响，还是你没有意识到这个问题，实验必须足够精确。
懂了 谢谢老大
页:
[1]