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

我们都知道Oracle的AWR报告是一个很强大的功能通过分析AWR报告可以打出Oracle数据运行过程中出现的问题和可能存在的隐患。但是AWR报告中没有关于单个SQL执行计划、统计信息的详细描述，但不代表AWR不能提供这种功能。本文介绍的是如何使用AWR报告生成一条sql的详细执行统计报告。实验环境11.2.0.41、调整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 MAXIMUM2、手工创建一个AWR快照zx@ORCL>exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.3、创建测试表并在不同情况下执行测试sql，并找到测试sql的sql_idzx@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
------------------- -----------------------------------------------
2dymmcx3kf7h1 select object_name from t where object_id=1234、再次手工生成AWR快照zx@ORCL>exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.5、使用awrsqrpt.sql脚本生成对于sql_id=2dymmcx3kf7h1的sql的详细统计信息。过程与生成awrrpt类似，不同的是需要指定要生成报告的sql_idzx@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: 2dymmcx3kf7h1
SQL ID specified: 2dymmcx3kf7h1

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）
------------- ----------
2dymmcx3kf7h1 1
Module: SQL*Plus
select object_name from t where object_id=123

-------------------------------------------------------------

SQL ID: 2dymmcx3kf7h1 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://www.linuxidc.com/Linux/2013-03/81873.htmhttp://www.linuxidc.com/Linux/2013-01/77196.htmhttp://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_workload_repos.htm#ARPLS69140《基于Oracle的SQL优化》 PDF下载见 http://www.linuxidc.com/Linux/2017-02/140521.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址