awr报告中，update sys.aud$ set action#=:2语句性能差

jason.zhong755

早上从生产上拉了份awr，分析了下，发现在SQL ordered by Elapsed Time部分的一条系统的sql的效率很差，如图：

烦请ML老大帮忙分析下这个sql主要是什么原因导致效率这么差。

Maclean Liu(刘相兵

ODM Finding:

语句：

update sys.aud$
   set action#       = :2,
       returncode    = :3,
       logoff$time   = cast(SYS_EXTRACT_UTC(systimestamp) as date),
       logoff$pread  = :4,
       logoff$lread  = :5,
       logoff$lwrite = :6,
       logoff$dead   = :7,
       sessioncpu    = :8
where sessionid = :1
   and entryid = 1
   and action# = 100


action # =100 是 LOGON 操作， 这语句的作用可能是 给update sys.aud$上的记录 更新logoff$time

Maclean Liu(刘相兵

11.1.0.6.0        Linux 64-bit for AMD

Cluster Wait Time (s)        %Ela        %Total        Elapsed Time(s)        CPU Time(s)        Executions        SQL Id        SQL Module        SQL Text
27,737.12        22.82        65.73        121,570.85        20,014.13        3,484        209fr01svbb5s                   update sys.aud$ set action#=:2...

该语句 运行3484 次， 消耗了 大量的CPU 和 IO



select count(*) From sys.aud$;            检查一下 aud$有多大

确认 update sys.aud$ 是否使用了索引：


select * from table(dbms_xplan.display_awr('209fr01svbb5s'));

action plan:

运行一下SQL并贴出结果 ：

1. select * from table(dbms_xplan.display_awr('209fr01svbb5s'));
   
2. 
   
3. select count(*) From sys.aud$;     
   
4. 
   
5. 
   
6. select status,index_name from dba_indexes where table_name='AUD$' and owner='SYS';

复制代码

jason.zhong755

执行计划如下：

1. SQL> set pagesize 200
   
2. SQL> col plan_table_output for a120
   
3. SQL> select * from table(dbms_xplan.display_awr('209fr01svbb5s'));
   
4. 
   
5. PLAN_TABLE_OUTPUT
   
6. ------------------------------------------------------------------------------------------------------------------------
   
7. SQL_ID 209fr01svbb5s
   
8. --------------------
   
9. update sys.aud$ set action#=:2, returncode=:3,
   
10. logoff$time=cast(SYS_EXTRACT_UTC(systimestamp) as date),
    
11. logoff$pread=:4, logoff$lread=:5, logoff$lwrite=:6, logoff$dead=:7,
    
12. sessioncpu=:8 where sessionid=:1 and entryid=1 and action#=100
    
13. 
    
14. Plan hash value: 444042542
    
15. 
    
16. ---------------------------------------------------------------------------
    
17. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
18. ---------------------------------------------------------------------------
    
19. |   0 | UPDATE STATEMENT   |      |       |       |     2 (100)|          |
    
20. |   1 |  UPDATE            | AUD$ |       |       |            |          |
    
21. |   2 |   TABLE ACCESS FULL| AUD$ |     1 |    49 |     2   (0)| 00:00:01 |
    
22. ---------------------------------------------------------------------------
    
23. 
    
24. 
    
25. 17 rows selected.
    
26. 
    
27. SQL>

复制代码

Maclean Liu(刘相兵

TABLE ACCESS FULL| AUD$ |     1 |    49 |     2   (0)| 00:00:01 |

这个比较明显 统计信息过期了，  你可以查一下 AUD$ 上一次 分析是什么时候：

select blocks,num_rows,last_analyzed from dba_tables where table_name='AUD$';

jason.zhong755

1. SQL> select status,index_name from dba_indexes where table_name='AUD
   
2. and owner='SYS';
   
3. 
   
4. STATUS   INDEX_NAME
   
5. -------- ------------------------------
   
6. VALID    SYS_IL0000000375C00040$
   
7. VALID    SYS_IL0000000375C00041$
   
8. VALID    I_AUD1
   
9. 
   
10. SQL>
    
11. 
    
12. 
    
13. SQL> select blocks,num_rows,last_analyzed from dba_tables where table_name='AUD
    
14. ;
    
15. 
    
16.     BLOCKS   NUM_ROWS LAST_ANALYZED
    
17. ---------- ---------- -------------------
    
18.          1          5 2010-07-22 21:25:13

复制代码

Liu老大，这个是不是需要重新收集一下，不过目前不知道总量有多少，趁业务空闲是不是有必要手动收集一下。

Maclean Liu(刘相兵

统计信息 过期导致的 执行计划使用全表扫描， 建议在空闲时间手动收集 SYS.AUD$的统计信息。

不了峰

相当强悍，丝丝入扣 ：)

Ling.QIu

是否能说系统表的效率差往往都是统计信息 过期导致的?