关于fts和buffer cache缓存数据的疑问
本帖最后由 枕霜卧雪 于 2015-3-25 16:58 编辑环境：
OS:RHEL 5.8 X64
DB:oracle 11.2.0.3.8
buffer cache：18 GB
数据库环境：db上有vault和oem，AUD$表200w行,60多MB。
现象：
每小时从Oracle Enterprise Manager.Metric Engine发起一条基于DBA_AUDIT_TRAIL表的访问，执行计划中看是对AUD$的FTS.
3.12 凌晨对数据库进行了例行维护重启操作。
重启之后，从dba_hist_sqlstat.disk_reads_delta看该sql的资源消耗信息，显示一直有物理读（disk_reads_delta）和逻辑读（buffer_gets_delta），但是直到3.13日之后物理读就消失了。
疑问：
这种现象要怎么解释呢？
1.按照buffer cache缓存数据的原理来说：
db重启后，查询语句发起对AUD$的fts的读取。
此时buffer中是没有缓存的，AUD$的数据会缓存到buffer中的冷端。
数据库中属于半空载，没有任何业务查询，这些块应该会在缓存中，而不会再有disk read了，之后就应该是buffer get。
2.这里，忽略了11g的dpr的特性，该表应该算是大表，走dpr，那么第一条的假设就不存在了。
那么之后就应该每次访问都要是dpr进行，不会从buffer中读取。
但是，从dba_hist_sqlstat 中看，从3.13之后disk_reads_delta又消失了，这么说，就又从buffer中读取了。
这到底是什么原因导致的这种现象呢？
3.我从v$bh中看，AUD$的block基本都在buffer中的。语句就是ml的《Script:List Buffer Cache Details》改造版本。
针对你说的语句 给出下面的查询结果：
SELECT *
FROM (SELECT '1.v$sql'||'实例号:'||GV$SQL.inst_id source,
SQL_ID,
plan_hash_value,
TO_CHAR (FIRST_LOAD_TIME) begin_time,
'在cursor cache中' end_time,
executions "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM Gv$SQL
WHERE sql_id = '&A'
UNION ALL
SELECT '2.sqltuning set' source,
sql_id,
plan_hash_value,
'JUST SQLSET NO DATE' begin_time,
'JUST SQLSET NO DATE' end_time,
EXECUTIONS "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM dba_sqlset_statements
WHERE SQL_ID = '&A'
UNION ALL
SELECT '3.dba_advisor_sqlstats' source,
sql_id,
plan_hash_value,
'JUST SQLSET NO DATE' begin_time,
'JUST SQLSET NO DATE' end_time,
EXECUTIONS "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM dba_sqlset_statements
WHERE SQL_ID = '&A'
UNION ALL
SELECT DISTINCT
'4.dba_hist_sqlstat' || '实例号:' || SQL.INSTANCE_NUMBER
source,
sql_id,
PLAN_HASH_VALUE,
TO_CHAR (s.BEGIN_INTERVAL_TIME ,'YYYY-MM-DD hh24:mi:ss') begin_time,
TO_CHAR (s.END_INTERVAL_TIME,'YYYY-MM-DD hh24:mi:ss') end_time,
SQL.executions_delta,
SQL.buffer_gets_delta
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"LIO/exec",
(SQL.cpu_time_delta / 1000000)
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"CPUTIM/exec",
(SQL.elapsed_time_delta / 1000000)
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"ETIME/exec",
SQL.DISK_READS_DELTA
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"PIO/exec",
SQL.ROWS_PROCESSED_DELTA
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"ROWs/exec"
FROM dba_hist_sqlstat SQL, dba_hist_snapshot s
WHERE SQL.INSTANCE_NUMBER = s.INSTANCE_NUMBER
AND SQL.dbid = (SELECT dbid FROM v$database)
AND s.snap_id = SQL.snap_id
AND sql_id IN ('&A'))
ORDER BY source, begin_time DESC; 本帖最后由 枕霜卧雪 于 2015-3-26 10:55 编辑
补充数据：
SNAP_ID snap_time SQL_ID EXECUTIONS_DELTA DISK_READS_DELTA BUFFER_GETS_DELTA
18743 2015/3/11 22:00 d15cdr0zt3vtp 2 0 190804
18744 2015/3/11 23:00 d15cdr0zt3vtp 2 0 190814
18745 2015/3/12 0:00 d15cdr0zt3vtp 2 0 190828
18747 2015/3/12 2:00 d15cdr0zt3vtp 2 190816 191065
18749 2015/3/12 3:00 d15cdr0zt3vtp 1 95411 95416
18750 2015/3/12 3:30 d15cdr0zt3vtp 1 95411 95416
18751 2015/3/12 4:00 d15cdr0zt3vtp 1 95416 95421
期间一直有DISK_READS_
19022 2015/3/17 19:30 d15cdr0zt3vtp 1 96078 96083
19023 2015/3/17 20:00 d15cdr0zt3vtp 1 96078 96083
19024 2015/3/17 20:30 d15cdr0zt3vtp 1 96083 96088
19025 2015/3/17 21:00 d15cdr0zt3vtp 1 96083 96088
19026 2015/3/17 21:30 d15cdr0zt3vtp 1 96088 96093
19028 2015/3/17 22:30 d15cdr0zt3vtp 1 0 96111
19029 2015/3/17 23:00 d15cdr0zt3vtp 1 0 96111
19030 2015/3/17 23:30 d15cdr0zt3vtp 1 0 96113
19031 2015/3/18 0:00 d15cdr0zt3vtp 1 0 96118
19032 2015/3/18 0:30 d15cdr0zt3vtp 1 0 96118
19033 2015/3/18 1:00 d15cdr0zt3vtp 1 0 96118
期间DISK_READS_一直是0
19386 2015/3/25 9:30 d15cdr0zt3vtp 1 0 96982
19387 2015/3/25 10:00 d15cdr0zt3vtp 1 0 96987
---下面语句时在近期，就是DISK_READS_DELTA=0的时间段检查的：
SELECT /*+ ORDERED USE_HASH(o u) MERGE */
DECODE(obj#, NULL, to_char(bh.obj), u.name || '.' || o.name) name,
COUNT(*) total,
SUM(DECODE((DECODE(lru_flag, 8, 1, 0) + DECODE(SIGN(tch - 2), 1, 1, 0)),
2,
1,
1,
1,
0)) hot,
SUM(DECODE(DECODE(SIGN(lru_flag - 8), 1, 0, 0, 0, 1) +
DECODE(tch, 2, 1, 1, 1, 0, 1, 0),
2,
1,
1,
0,
0)) cold,
SUM(DECODE(BITAND(flag, POWER(2, 19)), 0, 0, 1)) fts,
SUM(tch) total_tch,
ROUND(AVG(tch), 2) avg_tch,
MAX(tch) max_tch,
MIN(tch) min_tch
FROM x$bh bh, sys.obj$ o, sys.user$ u
WHERE bh.obj <> 4294967295
AND bh.state in (1, 2, 3)
AND bh.obj = o.dataobj#(+)
AND bh.inst_id = USERENV('INSTANCE')
AND o.owner# = u.user#(+)
and O.NAME='AUD$'
AND u.name NOT like 'AURORA$%'
GROUP BY DECODE(obj#, NULL, to_char(bh.obj), u.name || '.' || o.name)
ORDER BY total desc;
NAME TOTAL HOT COLD FTS TOTAL_TCH AVG_TCH MAX_TCH MIN_TCH
------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
SYSTEM.AUD$ 92171 86704 5467 87552 6077875 65.94 255 0 关于db的小表定义：
_small_table_threshold= 23889(BLOCKS)
select BLOCKS from dba_segments where segment_name='AUD$'; --87040 blocks
说明该表在11g中已经定义为大表，每次fts就要走dpr。
如果是dpr，则统计中的disk_reads_delta是合理的。
那为什么后面disk_reads_delta又消失了？ 表中的块被cache到内存中的比例少于50%会DPR高于则不走DPR tjsxxl 发表于 2015-3-26 10:41 static/image/common/back.gif
表中的块被cache到内存中的比例少于50%会DPR高于则不走DPR
我已经调整了第二贴的信息，请检查！
我推测算法（无实际依据）：
1.db重启后，对于执行频率低的稍大表fts语句，默认是首选走dpr。
2.如果同样的查询一直执行，到某个阈值（该阈值并不清楚）后，再次读取的这些block就会被缓存。
前提是缓存的冷端不会频繁被扫描，被刷出。
3.被缓存后，后面的访问就会继续从缓存走，以提高效率。
页:
[1]