概述
我们知道,Oracle提供的脚本均位于下列目录下
$ORACLE_HOME/rdbms/admin
- 1
其中,
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
下载查看
集群中指定特定实例的SQL语句的SQL Report
针对多实例数据库,使用
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql