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

192次阅读

共计 6990 个字符，预计需要花费 18 分钟才能阅读完成。

我们都知道 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