使用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优化》