使用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	   DEFAULT zx@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/awrsqrpt Current Instance ~~~~~~~~~~~~~~~~    DB Id    DB Name	 Inst Num Instance ----------- ------------ -------- ------------  1444351641 ORCL	1 orcl Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: text Type Specified: 	 text Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    DB Id     Inst Num DB Name	   Instance	Host ------------ -------- ------------ ------------ ------------ * 1444351641	    1 ORCL	   orcl 	rhel6 Using 1444351641 for database Id Using	       1 for instance number Specify 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 <return> without specifying a number lists all completed snapshots. Enter value for num_days: 1 Listing the last day's Completed Snapshots	Snap Instance     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	   1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 832 Begin Snapshot Id specified: 832 Enter value for end_snap: 833 End   Snapshot Id specified: 833 Specify the SQL Id ~~~~~~~~~~~~~~~~~~ Enter value for sql_id: 2dymmcx3kf7h2 SQL ID specified:  2dymmcx3kf7h2 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrsqlrpt_1_832_833.txt.  To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name:  Using the report name awrsqlrpt_1_832_833.txt WORKLOAD REPOSITORY SQL Report Snapshot Period Summary DB 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	       1 Module: SQL*Plus select 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 100 Stat Name	 Statement   Per Execution % Snap ---------------------------------------- ---------- -------------- ------- Elapsed Time (ms)	  1	       0.8     0.0 CPU Time (ms)	  0	       0.0     0.0 Executions	  1	       N/A     N/A Buffer Gets	  3	       3.0     0.0 Disk Reads	  0	       0.0     0.0 Parse Calls	  1	       1.0     0.1 Rows	  1	       1.0     N/A User I/O Wait Time (ms) 	  0	       N/A     N/A Cluster Wait Time (ms)	  0	       N/A     N/A Application Wait Time (ms)	  0	       N/A     N/A Concurrency Wait Time (ms)	  0	       N/A     N/A Invalidations	  0	       N/A     N/A Version Count	  1	       N/A     N/A Sharable 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 Text SQL ID	     SQL Text ------------ ----------------------------------------------------------------- 2dymmcx3kf7h select object_name from t where object_id=123 Report 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优化》