概述
我们知道，Oracle提供的脚本均位于下列目录下
$ORACLE_HOME/rdbms/admin
其中，
awrsqrpt.sql用来分析某条指定的SQL语句，通过awrsqrpt.sql脚本，awr能够生成指定曾经执行过的SQL的执行计划，当时消耗的资源等情况。
常用的几个如下：
- awrrpt.sql ：生成指定快照区间的统计报表
- awrrpti.sql ：生成指定数据库实例，并且指定快照区间的统计报表
- awrsqlrpt.sql ：生成指定快照区间，指定SQL语句(实际指定的是该语句的SQLID)的统计报表
- awrsqrpi.sql ：生成指定数据库实例，指定快照区间的指定SQL语句的统计报表
- awrddrpt.sql ：指定两个不同的时间周期，生成这两个周期的统计对比报表
- awrddrpi.sql ：指定数据库实例，并指定两个的不同时间周期，生成这两个周期的统计对比报表
- addmrpt.sql：数据库自动诊断监视工具（Automatic Database Diagnostic Monitor :ADDM）
- addmrpti.sql：指定数据库实例，数据库自动诊断监视工具（Automatic Database Diagnostic Monitor :ADDM）
调用后台脚本awrsqrpt.sql获取
artisandb:[/oracle$]sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 17 17:51:56 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> connect artisan/artisan2017@PR_CC Connected. SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 2143881171 ARTISAN 1 artisan 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: ------ 输入report类型，不输入使用默认html格式 Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 2143881171 1 ARTISAN artisan artisandb Using 2143881171 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 ------------ ------------ --------- ------------------ ----- artisan ARTISAN 11977 17 Jun 2018 00:00 1 11978 17 Jun 2018 01:00 1 11979 17 Jun 2018 02:00 1 11980 17 Jun 2018 03:00 1 11981 17 Jun 2018 04:00 1 11982 17 Jun 2018 05:00 1 11983 17 Jun 2018 06:00 1 11984 17 Jun 2018 07:00 1 11985 17 Jun 2018 08:00 1 11986 17 Jun 2018 09:00 1 11987 17 Jun 2018 10:00 1 11988 17 Jun 2018 11:00 1 11989 17 Jun 2018 12:00 1 11990 17 Jun 2018 13:00 1 11991 17 Jun 2018 14:00 1 11992 17 Jun 2018 15:01 1 11993 17 Jun 2018 16:00 1 11994 17 Jun 2018 17:00 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 11989 ------ 输入开始的snapId Begin Snapshot Id specified: 11989 Enter value for end_snap: 11994 ------ 输入结束的snapId End Snapshot Id specified: 11994 Specify the SQL Id ~~~~~~~~~~~~~~~~~~ Enter value for sql_id: 3r0ccnd9v7q40 ------ 输入特定的sqlId SQL ID specified: 3r0ccnd9v7q40 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrsqlrpt_1_11989_11994.html. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: ------ 输入报告名称，不输入使用默认名称 Using the report name awrsqlrpt_1_11989_11994.html ........ ........ ........ Report written to awrsqlrpt_1_11989_11994.html
下载查看
集群中指定特定实例的SQL语句的SQL Report
针对多实例数据库，使用
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
