从DBA_HIST_SQLSTAT视图中查找过去时段最占用资源的会话

超过阀值的SQL语句会在AWR中保存一段时间（默认是7天），可以通过DBA_HIST_SQLSTAT视图查询。

点击(此处)折叠或打开

1. select snap_id, disk_reads_delta reads_delta,
   
2. executions_delta exec_delta, disk_reads_delta /decode
   
3. (executions_delta, 0, 1,executions_delta) rds_exec_ratio,
   
4. sql_id
   
5. from dba_hist_sqlstat
   
6. where disk_reads_delta > 100000
   
7. order by disk_reads_delta desc;

SNAP_ID	READS_DELTA	EXEC_DELTA	RDS_EXEC_RATIO	SQL_ID
39	511106	1	511106	8h1qaqha580hh
29	216898	3	72299.33	d5bcqvumxr4y4

再根据SQL_ID，在DBA_HIST_SQLTEXT中查看相关SQL语句

点击(此处)折叠或打开

1. select command_type,sql_text
   
2. from dba_hist_sqltext
   
3. where sql_id='d5bcqvumxr4y4';

COMMAND_TYPE	SQL_TEXT
3	select count(id) from bom

注：COMMAND_TYPE=3表示这是SELECT命令，完整的COMMAND_TYPE可以SELECT * FROM AUDIT_ACTIONS;

---

同样可以查看之前命令的执行计划

点击(此处)折叠或打开

1. select *
   
2. from table(DBMS_XPLAN.DISPLAY_AWR('8h1qaqha580hh'));

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22621861/viewspace-1281602/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-1281602/