文章目录
获取历史执行计划:AWR/StatsPack SQL 报告
当发生SQL性能问题后，可能要看看历史的执行状况，以确认是否有什么变化。对于这种情况，Oralcle的AWR/StatsPack SQL 报告就很有用了。
AWR SQL 报告
Oracle 10g版本推出的AWR (Automatic Workload Repository) 功能取得的信息中，也包含着SQL的执行计划信息，可以通过以下的方法进行查看相关的信息。
1.找到想要查看SQL文的SQL ID。
SQL> col sql_text for a100
SQL> set line 120 pages 1000 long 100
SQL> select sql_id,sql_text from dba_hist_sqltext
where upper(sql_text) like '%<能够识别出SQL文的字符串>%';
2.根据上面得到的SQL ID来取得相关执行计划信息。
2.1 10g R1版本
可以通过DBMS_XPLAN.DISPLAY_AWR包来表示执行计划。
SQL> select * from table(DBMS_XPLAN.DISPLAY_AWR('<SQL ID>',null,null,'ALL'));
当然也可以合并1和 2.1，像下面这样执行，来查看执行计划信息。
SQL> select tf.*
from dba_hist_sqltext ht,
table(DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null, 'ALL' )) tf
where ht.sql_text like '%<能够识别出SQL文的字符串>%';
2.2 10g R2以后版本
在10g R2的以后版本还可以通过AWR脚本awrsqrpt.sql 和awrsqrpi.sql，来取得SQL 报告。
这时候在执行时，除了SQL ID 以外，还需要指定用于特定SQL执行时间的SNAP_ID。
例：
SQL> connect /as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
※需要注意的是，通过AWR SQL 报告功能能够取得的SQL执行计划信息，必须在AWR快照取得时的共享池内存中，是AWR快照取得的对象。
收集AWR SQL报告的例子
以下是收集AWR SQL报告的一个例子：
- 收集AWR信息
--<收集AWR开始快照>
SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL');
--<执行SQL>
SQL> select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno;
--<收集AWR结束快照>
SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL');
PL/SQL procedure successfully completed.
- 通过DBMS_XPLAN.DISPLAY_AWR显示AWR中的执行计划
--<查看SQL 信息>
SQL> select sql_id,sql_text from DBA_HIST_SQLTEXT where upper(sql_text) like '%EMP E%' and command_type=3;
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
9ba377xqpau28
select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno
3.通过awrsqrpt.sql 生成AWR SQL报告，按照提示输入信息。
SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
STATSPACK SQL 报告
在导入了STATSPACK的系统中，和AWR SQL 报告相似也可以通过STATSPACK SQL 报告来查看SQL的执行计划。
具体使用方法如下：
1.前提：要想确认到SQL的执行计划，必须取得Level 6以上的STATSPACK 的快照（snapshot）。
SQL> execute statspack.snap (i_snap_level=>6, i_modify_parameter=>'true');
※关于SQL文取得的边界值默认设定可以通过以下查看：
SQL> select executions_th, parse_calls_th, disk_reads_th, buffer_gets_th
2 from stats$statspack_parameter
EXECUTIONS_TH PARSE_CALLS_TH DISK_READS_TH BUFFER_GETS_TH
------------- -------------- ------------- --------------
100 1000 1000 10000
其中，各个边界值(Threshold)的意义代表如下：
EXECUTIONS_TH ：i_executions_th( >=0 ) SQL文的执行回数
PARSE_CALLS_TH：i_parse_calls_th( >=0 )SQL文的解析回数
DISK_READS_TH ：i_disk_reads_th( >=0 ) SQL文的物理读回数
BUFFER_GETS_TH：i_buffer_gets_th( >=0 )SQL文的缓存读回数
即：SQL文的执行回数 >=100 、解析回数>=1000、物理读回数>=1000、缓存读回数>=1000的SQL文会作为STATSPACK 的快照（snapshot）的取得对象。
当然，你可以通过修改设定边界值，以便取得更多的SQL文。
例如：
---把SQL文取得的边界值改为执行回数和物理读回数大于0
SQL> execute statspack.snap (i_snap_level=>6, -
i_modify_parameter=>'true', -
i_executions_th=>0, -
i_disk_reads_th=>0);
2.通过STATSPACK 脚本sprepsql.sql和sprsqins.sql，来取得SQL 报告。
这时候在执行时，需要输入Hash Value 和用于特定SQL执行时间的SNAP_ID。其中，Hash Value 可以通过V$SQL取得。
---通过V$SQL取得hash_value
SQL> col sql_text for a100
SQL> set line 120 pages 1000 long 100
SQL>SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%<能够识别出SQL文的字符串>%';
STATSPACK SQL 报告的取得方法：
SQL> connect /as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/sprepsql.sql
或
SQL> @$ORACLE_HOME/rdbms/admin/sprsqins.sql