Generate a SQL Execution statistics report using AWR

We all know that Oracle's AWR report is a powerful feature by analyzing the AWR report, you can play out problems and potential pitfalls in the operation of Oracle data. However, the AWR report does not have a detailed description of the individual SQL execution plan, statistics, but does not mean that AWR does not provide this functionality.

This article describes how to use the AWR report to generate a detailed execution statistics report for a SQL.

Experimental environment 11.2.0.4

1, adjust the AWR settings for SQL collection, the purpose of the adjustment is because AWR does not capture all SQL statements by default, this adjustment is to allow AWR to collect the SQL statements in the experimental process

[email protected]>select * from dba_hist_wr_control;       DBID SNAP_INTERVAL       RETENTION    Topnsql---------- --------------------------------------------------------------------------- ------ --------------------------------------------------------------------- ------------------------------ 1444351641 +00000 01:00:00.0       +00008 00:00:00.0    default[email protected]>exec dbms_workload_repository.modify_snapshot_settings ( Topnsql=> ' MAXIMUM ');P l/sql procedure successfully completed. [email protected]>select * from dba_hist_wr_control;       DBID SNAP_INTERVAL       RETENTION    Topnsql---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ------------- -----------------1444351641 +00000 01:00:00.0       +00008  00:00:00.0   maximum

2. Create an AWR snapshot manually

[Email protected]>exec dbms_workload_repository.create_snapshot;pl/sql procedure successfully completed.

3. Create a test table and execute the test SQL under different circumstances and find the sql_id of the test SQL

[email protected]>create table t as select * from dba_objects; Table created. [Email protected]>create unique index idx_unique_t on t (object_id); index  created. [Email protected]>exec dbms_stats.gather_table_stats (USER, ' T ', cascade=>true);P l/sql  Procedure successfully completed. [Email protected]>select object_name from t where object_id=123;object_ NAME------------------------------ecol$[email protected]>select sql_id,sql_text from v$ sql where sql_text= ' select object_name from t where object_id=123 '; Sql_idsql_text-------------------                      ----------------------------------------------- 2dymmcx3kf7h1select object_name from t where object_id=123

4. Manually generate the Awr snapshot again

[Email protected]>exec dbms_workload_repository.create_snapshot;pl/sql procedure successfully completed.

5. Use the Awrsqrpt.sql script to generate detailed statistics about the SQL for SQL_ID=2DYMMCX3KF7H1. The process is similar to the build awrrpt, where you need to specify the sql_id to generate the report

[Email protected]>@?/rdbms/admin/awrsqrptcurrent instance~~~~~~~~~~~~~~~~   db  id    db name inst num instance----------- ------------  -------- ------------ 1444351641 orcl1 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    instancehost------------ -------- ------------ ------------ ------------ * 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 <return> withoutspecifying a number lists all  Completed snapshots. Enter value for num_days: 1listing the last day ' s Completed  snapshotssnapinstance     db name    snap id     snap started    level------------ ------------ ---------  ------------------ -----orcl     orcl826 15 feb 2017 09:25    1827 15 feb 2017 10:00   1828 15 feb 2017 10:00    1829 15 feb 2017 10:04   1830 15 feb 2017  10:09   1831 15 feb 2017 11:00   1832 15 feb  2017 11:02   1833 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: 2dymmcx3kf7h1sql id  specified: &nBsp;2dymmcx3kf7h1specify 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.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:0127 1.3  end snap:   833 15-Feb-17 11:07:2429 1.5   Elapsed:   5.38  (mins)    DB Time:   0.06  (mins) sql summary    db/ inst: orcl/orclsnaps: 832-833elapsed   sql id       Time  (ms)------------- ----------2dymmcx3kf7h1        1module: sql*plusselect object_name from t where object_id=123  ------ -------------------------------------------------------sql id: 2dymmcx3kf7h1     db/inst: orcl/orclsnaps: 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 hashtotal  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/orclsnaps: 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  (|   |) |    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

The statistics and execution plan for SQL execution in the AWR record are listed in the report.

Reference: 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-based SQL optimization

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1897981

Generate a SQL Execution statistics report using AWR