Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 关于fts和buffer cache缓存数据的疑问
发新帖
枕霜卧雪

3

积分

0

好友

4

主题

[SQL调优] 关于fts和buffer cache缓存数据的疑问

1#
发表于 2015-3-25 16:56:45 | 查看: 3736| 回复: 5
本帖最后由 枕霜卧雪 于 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》改造版本。
2#
发表于 2015-3-25 16:59:28
针对你说的语句 给出下面的查询结果：

  2.    SELECT *
  3.     FROM (SELECT '1.v$sql'||'实例号:'||GV$SQL.inst_id source,
  4.                  SQL_ID,
  5.                  plan_hash_value,
  6.                  TO_CHAR (FIRST_LOAD_TIME) begin_time,
  7.                  '在cursor cache中' end_time,
  8.                  executions "No. of exec",
  9.                  (buffer_gets / executions) "LIO/exec",
  10.                  (cpu_time / executions / 1000000) "CPUTIM/exec",
  11.                  (elapsed_time / executions / 1000000) "ETIME/exec",
  12.                  (disk_reads / executions) "PIO/exec",
  13.                  (ROWS_PROCESSED / executions) "ROWs/exec"
  14.             FROM Gv$SQL
  15.            WHERE sql_id = '&A'
  16.           UNION ALL
  17.           SELECT '2.sqltuning set' source,
  18.                  sql_id,
  19.                  plan_hash_value,
  20.                  'JUST SQLSET NO DATE' begin_time,
  21.                  'JUST SQLSET NO DATE' end_time,
  22.                  EXECUTIONS "No. of exec",
  23.                  (buffer_gets / executions) "LIO/exec",
  24.                  (cpu_time / executions / 1000000) "CPUTIM/exec",
  25.                  (elapsed_time / executions / 1000000) "ETIME/exec",
  26.                  (disk_reads / executions) "PIO/exec",
  27.                  (ROWS_PROCESSED / executions) "ROWs/exec"
  28.             FROM dba_sqlset_statements
  29.            WHERE SQL_ID = '&A'
  30.           UNION ALL
  31.           SELECT '3.dba_advisor_sqlstats' source,
  32.                  sql_id,
  33.                  plan_hash_value,
  34.                  'JUST SQLSET NO DATE' begin_time,
  35.                  'JUST SQLSET NO DATE' end_time,
  36.                  EXECUTIONS "No. of exec",
  37.                  (buffer_gets / executions) "LIO/exec",
  38.                  (cpu_time / executions / 1000000) "CPUTIM/exec",
  39.                  (elapsed_time / executions / 1000000) "ETIME/exec",
  40.                  (disk_reads / executions) "PIO/exec",
  41.                  (ROWS_PROCESSED / executions) "ROWs/exec"
  42.             FROM dba_sqlset_statements
  43.            WHERE SQL_ID = '&A'
  44.           UNION ALL
  45.           SELECT DISTINCT
  46.                  '4.dba_hist_sqlstat' || '实例号:' || SQL.INSTANCE_NUMBER
  47.                     source,
  48.                  sql_id,
  49.                  PLAN_HASH_VALUE,
  50.                  TO_CHAR (s.BEGIN_INTERVAL_TIME ,'YYYY-MM-DD hh24:mi:ss') begin_time,
  51.                  TO_CHAR (s.END_INTERVAL_TIME,'YYYY-MM-DD hh24:mi:ss') end_time,
  52.                  SQL.executions_delta,
  53.                  SQL.buffer_gets_delta
  54.                  / DECODE (NVL (SQL.executions_delta, 0),
  55.                            0, 1,
  56.                            SQL.executions_delta)
  57.                     "LIO/exec",
  58.                  (SQL.cpu_time_delta / 1000000)
  59.                  / DECODE (NVL (SQL.executions_delta, 0),
  60.                            0, 1,
  61.                            SQL.executions_delta)
  62.                     "CPUTIM/exec",
  63.                  (SQL.elapsed_time_delta / 1000000)
  64.                  / DECODE (NVL (SQL.executions_delta, 0),
  65.                            0, 1,
  66.                            SQL.executions_delta)
  67.                     "ETIME/exec",
  68.                  SQL.DISK_READS_DELTA
  69.                  / DECODE (NVL (SQL.executions_delta, 0),
  70.                            0, 1,
  71.                            SQL.executions_delta)
  72.                     "PIO/exec",
  73.                  SQL.ROWS_PROCESSED_DELTA
  74.                  / DECODE (NVL (SQL.executions_delta, 0),
  75.                            0, 1,
  76.                            SQL.executions_delta)
  77.                     "ROWs/exec"
  78.             FROM dba_hist_sqlstat SQL, dba_hist_snapshot s
  79.            WHERE     SQL.INSTANCE_NUMBER = s.INSTANCE_NUMBER
  80.                  AND SQL.dbid = (SELECT dbid FROM v$database)
  81.                  AND s.snap_id = SQL.snap_id
  82.                  AND sql_id IN ('&A'))
  83. ORDER BY source, begin_time DESC;
复制代码

回复 只看该作者 道具 举报

3#
发表于 2015-3-25 17:06:48
本帖最后由 枕霜卧雪 于 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

回复 只看该作者 道具 举报

4#
发表于 2015-3-26 09:58:34
关于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又消失了？

回复 只看该作者 道具 举报

5#
发表于 2015-3-26 10:41:12
表中的块被cache到内存中的比例少于50%会DPR高于则不走DPR

回复 只看该作者 道具 举报

6#
发表于 2015-3-26 11:00:10
tjsxxl 发表于 2015-3-26 10:41
表中的块被cache到内存中的比例少于50%会DPR高于则不走DPR

我已经调整了第二贴的信息，请检查！
我推测算法（无实际依据）：
1.db重启后，对于执行频率低的稍大表fts语句，默认是首选走dpr。
2.如果同样的查询一直执行，到某个阈值（该阈值并不清楚）后，再次读取的这些block就会被缓存。
  前提是缓存的冷端不会频繁被扫描，被刷出。
3.被缓存后，后面的访问就会继续从缓存走，以提高效率。

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-21 02:20 , Processed in 0.051028 second(s), 20 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569