使用AWR生成一条sql的执行统计报告

我们都知道Oracle的AWR报告是一个很强大的功能通过分析AWR报告可以打出Oracle数据运行过程中出现的问题和可能存在的隐患。但是AWR报告中没有关于单个SQL执行计划、统计信息的详细描述，但不代表AWR不能提供这种功能。

本文介绍的是如何使用AWR报告生成一条sql的详细执行统计报告。

实验环境11.2.0.4

1、调整AWR关于SQL收集的设置，调整的目的是因为默认情况下AWR并非捕获所有的sql语句，此调整是为了让AWR可以收集实验过程中的SQL语句

zx@ORCL>select * from dba_hist_wr_control;      DBID SNAP_INTERVAL                                                            RETENTION                                                                TOPNSQL---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ------------------------------1444351641 +00000 01:00:00.0                                                         +00008 00:00:00.0                                                           DEFAULTzx@ORCL>exec dbms_workload_repository.modify_snapshot_settingS(topnsql=>'MAXIMUM');PL/SQL procedure successfully completed.zx@ORCL>select * from dba_hist_wr_control;      DBID SNAP_INTERVAL                                                            RETENTION                                                                TOPNSQL---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ------------------------------1444351641 +00000 01:00:00.0                                                         +00008 00:00:00.0                                                           MAXIMUM

2、手工创建一个AWR快照

zx@ORCL>exec dbms_workload_repository.create_snapshot;PL/SQL procedure successfully completed.

3、创建测试表并在不同情况下执行测试sql，并找到测试sql的sql_id

zx@ORCL>create table t as select * from dba_objects;Table created.zx@ORCL>create unique index idx_unique_t on t(object_id);Index created.zx@ORCL>exec dbms_stats.gather_table_stats(USER,'T',CASCADE=>TRUE);PL/SQL procedure successfully completed.zx@ORCL>select object_name from t where object_id=123;OBJECT_NAME------------------------------ECOL$zx@ORCL>select sql_id,sql_text from v$sql where sql_text='select object_name from t where object_id=123';SQL_ID                                  SQL_TEXT-------------------                     -----------------------------------------------2dymmcx3kf7h2                           select object_name from t where object_id=123

4、再次手工生成AWR快照

zx@ORCL>exec dbms_workload_repository.create_snapshot;PL/SQL procedure successfully completed.

5、使用awrsqrpt.sql脚本生成对于sql_id=2dymmcx3kf7h2的sql的详细统计信息。过程与生成awrrpt类似，不同的是需要指定要生成报告的sql_id

zx@ORCL>@?/rdbms/admin/awrsqrptCurrent Instance~~~~~~~~~~~~~~~~   DB Id    DB Name         Inst Num Instance----------- ------------ -------- ------------ 1444351641 ORCL              1 orclSpecify the Report Type~~~~~~~~~~~~~~~~~~~~~~~Would you like an HTML report, or a plain text report?Enter 'html' for an HTML report, or 'text' for plain textDefaults to 'html'Enter value for report_type: textType Specified:                                textInstances in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   DB Id     Inst Num DB Name          Instance      Host------------ -------- ------------ ------------ ------------* 1444351641           1 ORCL        orcl     rhel6Using 1444351641 for database IdUsing          1 for instance numberSpecify the number of days of snapshots to choose from~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Entering the number of days (n) will result in the most recent(n) days of snapshots being listed.  Pressing  withoutspecifying a number lists all completed snapshots.Enter value for num_days: 1Listing the last day's Completed Snapshots                                                        SnapInstance     DB Name          Snap Id    Snap Started    Level------------ ------------ --------- ------------------ -----orcl         ORCL              826 15 Feb 2017 09:25          1                                827 15 Feb 2017 10:00          1                                828 15 Feb 2017 10:00          1                                829 15 Feb 2017 10:04          1                                830 15 Feb 2017 10:09          1                                831 15 Feb 2017 11:00          1                                832 15 Feb 2017 11:02          1                                833 15 Feb 2017 11:07          1Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 832Begin Snapshot Id specified: 832Enter value for end_snap: 833End   Snapshot Id specified: 833Specify the SQL Id~~~~~~~~~~~~~~~~~~Enter value for sql_id: 2dymmcx3kf7h2SQL ID specified:  2dymmcx3kf7h2Specify the Report Name~~~~~~~~~~~~~~~~~~~~~~~The default report file name is awrsqlrpt_1_832_833.txt.  To use this name,press  to continue, otherwise enter an alternative.Enter value for report_name: Using the report name awrsqlrpt_1_832_833.txtWORKLOAD REPOSITORY SQL ReportSnapshot Period SummaryDB Name       DB Id       Instance     Inst Num Startup Time    Release    RAC------------ ----------- ------------ -------- --------------- ----------- ---ORCL          1444351641 orcl                     1 15-Feb-17 09:14 11.2.0.4.0  NO              Snap Id           Snap Time          Sessions Curs/Sess            --------- ------------------- -------- ---------Begin Snap:          832 15-Feb-17 11:02:01    27       1.3  End Snap:        833 15-Feb-17 11:07:24    29       1.5   Elapsed:                 5.38 (mins)   DB Time:            0.06 (mins)SQL Summary                                    DB/Inst: ORCL/orcl Snaps: 832-833                Elapsed   SQL Id      Time (ms)------------- ----------2dymmcx3kf7h2          1Module: SQL*Plusselect object_name from t where object_id=123          -------------------------------------------------------------SQL ID: 2dymmcx3kf7h2                     DB/Inst: ORCL/orcl Snaps: 832-833-> 1st Capture and Last Capture Snap IDs   refer to Snapshot IDs witin the snapshot range-> select object_name from t where object_id=123    Plan Hash          Total Elapsed                    1st Capture   Last Capture#   Value                     Time(ms)   Executions          Snap ID         Snap ID--- ---------------- ---------------- ------------- ------------- --------------1   3476657867                       1                 1         833      833          -------------------------------------------------------------Plan 1(PHV: 3476657867)-----------------------Plan Statistics                           DB/Inst: ORCL/orcl Snaps: 832-833-> % Total DB Time is the Elapsed Time of the SQL statement divided   into the Total Database Time multiplied by 100Stat Name                           Statement   Per Execution % Snap---------------------------------------- ---------- -------------- -------Elapsed Time (ms)                               1          0.8     0.0CPU Time (ms)                                   0          0.0     0.0Executions                                        1          N/A     N/ABuffer Gets                                          3          3.0     0.0Disk Reads                                   0          0.0     0.0Parse Calls                                          1          1.0     0.1Rows                                              1          1.0     N/AUser I/O Wait Time (ms)                          0          N/A     N/ACluster Wait Time (ms)                             0          N/A     N/AApplication Wait Time (ms)                         0          N/A     N/AConcurrency Wait Time (ms)                         0          N/A     N/AInvalidations                                     0          N/A     N/AVersion Count                                        1          N/A     N/ASharable Mem(KB)                            15               N/A     N/A          -------------------------------------------------------------Execution Plan--------------------------------------------------------------------------------------------| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time      |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |                  |        |        |      2 (100)|        ||   1 |  TABLE ACCESS BY INDEX ROWID| T        |      1 |  30 |        2   (0)| 00:00:01 ||   2 |   INDEX UNIQUE SCAN            | IDX_UNIQUE_T |       1 |     |      1   (0)| 00:00:01 |--------------------------------------------------------------------------------------------Full SQL TextSQL ID          SQL Text------------ -----------------------------------------------------------------2dymmcx3kf7h select object_name from t where object_id=123Report written to awrsqlrpt_1_832_833.txt

报告中列出了AWR记录中sql执行的统计信息和执行计划。

参考：http://blog.csdn.net/leshami/article/details/8732708

http://www.linuxidc.com/Linux/2013-01/77196.htm

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_workload_repos.htm#ARPLS69140

《基于Oracle的SQL优化》