AWR报告-调优数据库

Question

我们有一个OLTP系统，下面是我们在高峰时段每天一次三天的AWR报告。我们看到很多直接路径读取；我们希望添加更多的PGA，以减少等待事件的等待时间。下面是我们11g db中的当前设置。

Sqls order by cpu

CPU Time (s) Executions  CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text 
7,279.38 9,694 0.75 31.50 7,297.79 99.75 0.27 0dp0c8wux0jnt    select case when max(LMA.LEAS... 
6,562.18 1,395 4.70 28.40 6,582.28 99.69 0.31 3g2kw67v3v0qs    SELECT NAME, vpd_predicates.ge... 
4,229.12 836,735 0.01 18.30 4,234.99 99.86 0.00 55a17fhdqg315 DBMS_SCHEDULER  begin :con := SELECT_POLICY_FU... 
2,683.17 968 2.77 11.61 2,605.03 103.00 7.61 dfd3gcuamscs8    select sc.Doc_ID docId , sc.DO... 
2,434.46 853,025 0.00 10.54 2,442.95 99.65 0.00 4y5m5y6y6wvzs DBMS_SCHEDULER  SELECT COUNT (*) FROM CONTACT_... 


Top 5 Timed Foreground Events 08-Feb-2011
Event   Waits   Time(s) Avg wait (ms)   % DB time   Wait Class
DB CPU      23,108      94.74    
db file sequential read 1,332,461   797 1   3.27    User I/O
direct path read    430,600 523 1   2.14    User I/O
db file scattered read  245,444 224 1   0.92    User I/O
SQL*Net more data to client 2,057,853   155 0   0.64    Network

Answer 1

看起来，大约95%的等待时间都在CPU上。即使您完全消除了直接路径读取，您也会减少不到2%的总等待时间。你的等待时间减少2%似乎不太可能成为一种改善。

看看是什么消耗了所有的CPU--你有几个非常密集的CPU查询。0dp0c8wux0jnt和3g2kw67v3v0qs消耗了您系统使用的CPU的60%。您是否考虑过优化这些查询，使其使用更少的CPU?尤其是3g2kw67v3v0qs，每次执行需要花费近5秒时间。

你还能在这台机器上运行什么？SGA & PGA占机器上可用内存的6%，这意味着您可能有许多其他事情正在运行(可能有许多数据库实例)。

Answer 2

直接路径读取是并行全表扫描的典型症状--您的I/O最高SQL是什么？为此，我将查看查询计划，以确保使用了索引。你的平行度设置为什么？

只有在一个会话对给定表执行所有这些操作时，提高PGA才会有帮助，在最坏的情况下，每个会话都需要一个足够大的PGA来缓存整个表.

Answer 3

有等待事件enq: TM -争用。当取消索引外键约束列(S)时，会发生此等待事件。主表上的更新将锁定整个子表。我有一个查询，它为缺失的索引生成create语句。您可以更改命名约定以使您满意。

SELECT 'CREATE INDEX '||a.owner||'.IX_'||a.table_name||'__'||
    replace(a.column_name, 'FK_', '') ||' ON '||a.owner||'.'||a.table_name||
    ' ('||a.column_name||') LOGGING TABLESPACE '||
    replace(b.tablespace_name, '_DAT', '_IDX')||' NOPARALLEL ONLINE;
/'
FROM (
   SELECT c.owner, c.table_name, cc.column_name, cc.position column_position
   FROM   all_constraints c, all_cons_columns cc
   WHERE  c.constraint_name = cc.constraint_name and c.owner=cc.owner
   AND    c.constraint_type = 'R'
   MINUS
   SELECT i.owner, i.table_name, ic.column_name, ic.column_position
   FROM   all_indexes i, all_ind_columns ic
   WHERE  i.index_name = ic.index_name and i.owner=ic.index_owner
) a
inner join all_tables b on b.owner=a.owner and b.table_name=a.table_name
where a.owner='<owner>'
ORDER BY a.owner, a.table_name, a.column_position;