手机版
你好,游客 登录 注册
背景:
阅读新闻

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

[日期:2017-02-15] 来源:Linux社区  作者:hbxztc [字体: ]

我们都知道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
-------------------                    -----------------------------------------------
2dymmcx3kf7h1              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=2dymmcx3kf7h1的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: 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.htm

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优化》 PDF下载见 http://www.linuxidc.com/Linux/2017-02/140521.htm

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-02/140660.htm

linux
相关资讯       AWR  AWR报告生成 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款