awr报告的导入导出

于 2013-12-26 16:23:03 发布
阅读量767 收藏
点赞数
文章标签： 数据库 操作系统
本文介绍Oracle数据库中AWR(Automatic Workload Repository)报告的生成方法及分析技巧，涵盖数据库整体性能评估及特定SQL语句性能诊断。通过使用Awrrpt.sql、Awrsqrpi.sql等脚本工具，可以有效定位性能瓶颈。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >


1     报告内容概要

AWR (Automatic Workload Repository) 报告是我们进行日常数据库性能评定、问题发现的重要手段，因此，当oracle遇到性能问题时，我们的一个重要思路就是导出数据库的AWR报告，通过报告分析定位问题根源。如何正确的导入导出AWR报告将是进行性能分析的重要前提工作，在本次报告中，将详细介绍生成分析报告的具体步骤。  

2    详细过程说明

 

2.1  常用脚本说明

u  Awrextr.sql 脚本：利用Awrextr.sql 脚本可以将数据库中的一系列的 AWR快照数据抽取到Data Pump导出文件中。导出 AWR快照数据后，就可以将此 dump文件传输到其他数据库上。要运行awrextr.sql脚本，必须以SYS用户身份连接到数据库中。

u  Awrload.sql 脚本：利用Awrload.sql 脚本可以在数据库中载入抽取的AWR数据。Awrload.sql脚本首先会创建一个中转schema，将Data Pump文件载入数据库中，随后会将该数据从中转的schema传入相应的AWR表中。要运行awrload.sql脚本，必须以SYS用户身份连接到数据库中。

u  Awrrpt.sql 脚本：利用Awrrpt.sql 脚本可以生成不同快照间显示数据统计的html文件或者text文本文件。

u  Awrrpti.sql脚本：同Awrrpt.sql脚本功能类似，只是可以指定数据库和实例。

u  Awrsqrpt.sql脚本：利用Awrsqrpt.sql脚本可以生成一个HTML或文本报告来显示特定SQL语句的一系列数据统计信息。通过这个报告可以检查或调试SQL语句的性能。

u  Awrsqrpi.sql脚本：同Awrsqrpt.sql脚本功能类似，但是可以指定数据库和实例。

 

注：在没有指定路径的情况下，以上脚本均在”$ORACLE_HOME/rdbms/admin”路径下。

2.2  执行步骤：

2.2.1      导出数据库的AWR报告

要求：

u  利用Awrrpt.sql 脚本来生成AWR报告

u  将报告上传至本地

操作如下：

指定报告生成路径，并连接数据库

 [oracle@ENMOEDU admin]$ cd -

/home/oracle

[oracle@ENMOEDU ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 26 13:30:27 2013

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

执行Awrrpt.sql 脚本（本次导出的AWR报告为HTML文件，Snap Id 为71-74，生成的

文件名为awrrpt_1_71_74.html）

SYS@ENMOEDU> @?/rdbms/admin/awrrpt

 

Current Instance

~~~~~~~~~~~~~~~~

 

   DB Id    DB Name      Inst Num Instance

----------- ------------ -------- ------------

   87396644 ENMOEDU             1 ENMOEDU

 

 

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:

 

Type Specified:  html

 

 

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 87396644          1 ENMOEDU      ENMOEDU      ENMOEDU

 

Using   87396644 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 without

specifying a number lists all completed snapshots.

 

 

Enter value for num_days:

 

Listing all Completed Snapshots

 

                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

ENMOEDU      ENMOEDU             43 18 Dec 2013 12:16      1

                                 44 18 Dec 2013 13:00      1

                                 45 18 Dec 2013 14:01      1

                                 46 18 Dec 2013 15:00      1

                                 47 18 Dec 2013 16:00      1

                                 48 18 Dec 2013 17:00      1

                                 49 18 Dec 2013 18:00      1

                                 50 18 Dec 2013 19:00      1

                                 51 18 Dec 2013 20:00      1

                                 52 18 Dec 2013 21:01      1

                                 53 18 Dec 2013 22:00      1

 

                                 54 19 Dec 2013 09:54      1

                                 55 19 Dec 2013 11:00      1

                                 56 19 Dec 2013 12:00      1

                                 57 19 Dec 2013 13:00      1

                                 58 19 Dec 2013 14:00      1

                                 59 19 Dec 2013 15:00      1

                                 60 19 Dec 2013 16:00      1

                                 61 19 Dec 2013 17:00      1

                                 62 19 Dec 2013 18:00      1

                                 63 19 Dec 2013 19:01      1

                                 64 19 Dec 2013 20:01      1

 

                                 65 20 Dec 2013 10:33      1

                                 66 20 Dec 2013 12:00      1

                                 67 20 Dec 2013 13:00      1

                                 68 20 Dec 2013 14:00      1

 

                                 69 25 Dec 2013 21:47      1

                                 70 25 Dec 2013 23:00      1

 

                                 71 26 Dec 2013 09:34      1

                                 72 26 Dec 2013 11:00      1

                                 73 26 Dec 2013 12:00      1

                                 74 26 Dec 2013 13:00      1

 

 

 

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 71

Begin Snapshot Id specified: 71

 

Enter value for end_snap: 74

End   Snapshot Id specified: 74

 

 

 

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_71_74.html.  To use this name,

press to continue, otherwise enter an alternative.

 

Enter value for report_name: awrrpt_1_71_74.html

 

将生成的awrrpt_1_71_74.html文件导出到本地查看

报告总结如下：

 WORKLOAD REPOSITORY report for

DB Name

DB Id

Instance

Inst num

Startup Time

Release

RAC

ENMOEDU

87396644

ENMOEDU

1

26-Dec-13 09:12

11.2.0.3.0

NO

Host Name

Platform

CPUs

Cores

Sockets

Memory (GB)

ENMOEDU

Linux IA (32-bit)

2

1

1

.99

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

71

26-Dec-13 09:34:16

33

1.6

End Snap:

74

26-Dec-13 13:00:00

29

1.5

Elapsed:

 

205.73 (mins)

 

 

DB Time:

 

0.71 (mins)

 

 

Report Summary

Cache Sizes

Begin

End

Buffer Cache:

80M

80M

Std Block Size:

8K

Shared Pool Size:

172M

172M

Log Buffer:

5,944K

Load Profile

Per Second

Per Transaction

Per Exec

Per Call

DB Time(s):

0.0

0.0

0.00

0.02

DB CPU(s):

0.0

0.0

0.00

0.01

Redo size:

803.2

9,905.0

 

 

Logical reads:

17.0

209.9

 

 

Block changes:

3.7

45.4

 

 

Physical reads:

0.2

1.8

 

 

Physical writes:

0.3

3.6

 

 

User calls:

0.2

2.2

 

 

Parses:

0.7

8.8

 

 

Hard parses:

0.0

0.3

 

 

W/A MB processed:

0.0

0.3

 

 

Logons:

0.1

0.6

 

 

Executes:

3.0

37.3

 

 

Rollbacks:

0.0

0.0

 

 

Transactions:

0.1

 

 

 

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:

100.00

Redo NoWait %:

100.00

Buffer Hit %:

99.15

In-memory Sort %:

100.00

Library Hit %:

97.49

Soft Parse %:

96.37

Execute to Parse %:

76.33

Latch Hit %:

99.98

Parse CPU to Parse Elapsd %:

13.20

% Non-Parse CPU:

89.77

Shared Pool Statistics

Begin

End

Memory Usage %:

80.01

88.71

% SQL with executions>1:

58.43

85.91

% Memory for SQL w/exec>1:

50.76

84.45

Top 5 Timed Foreground Events

Event

Waits

Time(s)

Avg wait (ms)

% DB time

Wait Class

DB CPU

 

17

 

38.99

 

db file sequential read

566

6

10

13.45

User I/O

log file sync

203

3

15

7.16

Commit

latch: shared pool

34

1

39

3.10

Concurrency

control file single write

42

1

20

2.01

System I/O

Host CPU (CPUs: 2 Cores: 1 Sockets: 1)

Load Average Begin

Load Average End

%User

%System

%WIO

%Idle

4.47

0.05

0.2

0.3

1.1

99.2

Instance CPU

%Total CPU

%Busy CPU

%DB time waiting for CPU (Resource Manager)

0.2

25.6

0.0

Memory Statistics

Begin

End

Host Mem (MB):

1,010.8

1,010.8

SGA use (MB):

292.0

292.0

PGA use (MB):

175.1

141.6

% Host Mem used for SGA+PGA:

46.22

42.90

2.2.2      导出指定的SQL语句的AWR报告

要求：

u  利用Awrsqrpi.sql 脚本来生成指定SQL语句的AWR报告

u  将报告上传至本地并对报告进行分析

操作如下：

指定报告生成路径，并连接数据库

[oracle@ENMOEDU admin]$ cd -

/home/oracle

[oracle@ENMOEDU ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 26 13:30:27 2013

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

执行Awrsqrpi.sql 脚本（本次导出的AWR报告为HTML文件，Snap Id 为71-74，SQL

Id为cm5vu20fhtnq1，生成的文件名为awrsqrpt_1_71_74.html）

 

SYS@ENMOEDU> @?/rdbms/admin/awrsqrpi

 

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:

 

Type Specified:  html

 

 

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 87396644          1 ENMOEDU      ENMOEDU      ENMOEDU

 

Enter value for dbid:  87396644

Using  87396644 for database Id

Enter value for inst_num: 1

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 without

specifying a number lists all completed snapshots.

 

 

Enter value for num_days:

 

Listing all Completed Snapshots

 

                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

ENMOEDU      ENMOEDU             43 18 Dec 2013 12:16      1

                                 44 18 Dec 2013 13:00      1

                                 45 18 Dec 2013 14:01      1

                                 46 18 Dec 2013 15:00      1

                                 47 18 Dec 2013 16:00      1

                                 48 18 Dec 2013 17:00      1

                                 49 18 Dec 2013 18:00      1

                                 50 18 Dec 2013 19:00      1

                                 51 18 Dec 2013 20:00      1

                                 52 18 Dec 2013 21:01      1

                                 53 18 Dec 2013 22:00      1

 

                                 54 19 Dec 2013 09:54      1

                                 55 19 Dec 2013 11:00      1

                                 56 19 Dec 2013 12:00      1

                                 57 19 Dec 2013 13:00      1

                                 58 19 Dec 2013 14:00      1

                                 59 19 Dec 2013 15:00      1

                                 60 19 Dec 2013 16:00      1

                                 61 19 Dec 2013 17:00      1

                                 62 19 Dec 2013 18:00      1

                                 63 19 Dec 2013 19:01      1

                                 64 19 Dec 2013 20:01      1

 

                                 65 20 Dec 2013 10:33      1

                                 66 20 Dec 2013 12:00      1

                                 67 20 Dec 2013 13:00      1

                                 68 20 Dec 2013 14:00      1

 

                                 69 25 Dec 2013 21:47      1

                                 70 25 Dec 2013 23:00      1

 

                                 71 26 Dec 2013 09:34      1

                                 72 26 Dec 2013 11:00      1

                                 73 26 Dec 2013 12:00      1

                                 74 26 Dec 2013 13:00      1

                                 75 26 Dec 2013 14:00      1

 

 

 

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 71

Begin Snapshot Id specified: 71

 

Enter value for end_snap: 74

End   Snapshot Id specified: 74

 

 

 

 

Specify the SQL Id

~~~~~~~~~~~~~~~~~~

Enter value for sql_id: cm5vu20fhtnq1

SQL ID specified:  cm5vu20fhtnq1

 

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrsqlrpt_1_71_74.html.  To use this name,

press to continue, otherwise enter an alternative.

 

Enter value for report_name:

 

将生成的awrrpt_1_71_74.html文件导出到本地查看，如下图所示：

WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name

DB Id

Instance

Inst num

Startup Time

Release

RAC

ENMOEDU

87396644

ENMOEDU

1

26-Dec-13 09:12

11.2.0.3.0

NO

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

71

26-Dec-13 09:34:16

33

1.6

End Snap:

74

26-Dec-13 13:00:00

29

1.5

Elapsed:

 

205.73 (mins)

 

 

DB Time:

 

0.71 (mins)

 

 

SQL Summary

SQL Id

Elapsed Time (ms)

Module

Action

SQL Text

cm5vu20fhtnq1

592

 

 

select /*+ connect_by_filtering */ privilege#, level from sysauth$ con...


Back to Top

SQL ID: cm5vu20fhtnq1

  • 1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range
  • select /*+ connect_by_filtering */ privilege#,level from sysauth$ conn...

#

Plan Hash Value

Total Elapsed Time(ms)

Executions

1st Capture Snap ID

Last Capture Snap ID

1

3332340200

592

8,874

72

74


Back to Top

Plan 1(PHV: 3332340200)

  • Plan Statistics
  • Execution Plan

Back to Top

Plan Statistics

  • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Stat Name

Statement Total

Per Execution

% Snap Total

Elapsed Time (ms)

592

0.07

1.39

CPU Time (ms)

538

0.06

3.24

Executions

8,874

 

 

Buffer Gets

29,580

3.33

14.08

Disk Reads

0

0.00

0.00

Parse Calls

174

0.02

1.97

Rows

12,528

1.41

 

User I/O Wait Time (ms)

0

 

 

Cluster Wait Time (ms)

0

 

 

Application Wait Time (ms)

0

 

 

Concurrency Wait Time (ms)

0

 

 

Invalidations

0

 

 

Version Count

3

 

 

Sharable Mem(KB)

66

 

 

Back to Plan 1(PHV: 3332340200) 
Back to Top

Execution Plan

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0

SELECT STATEMENT

 

 

 

8 (100)

 

1

   CONNECT BY WITH FILTERING

 

 

 

 

 

2

     INDEX RANGE SCAN

I_SYSAUTH1

2

16

2 (0)

00:00:01

3

     NESTED LOOPS

 

5

105

4 (0)

00:00:01

4

       CONNECT BY PUMP

 

 

 

 

 

5

       INDEX RANGE SCAN

I_SYSAUTH1

3

24

1 (0)

00:00:01

Back to Plan 1(PHV: 3332340200) 
Back to Top

Full SQL Text

SQL Id

SQL Text

cm5vu20fhtnq1

select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0


Back to Top

注：通过以上手动生成数据库的AWR报告和指定SQL语句的AWR报告不仅可以对一个时间段内整个系统资源使用情况作出全面分析，而且还可以对指定的SQL语句进行校验分析，从而找出提高性能的最优方案。

2.2.3      命令行查看SQL执行计划

要求：

u  编写执行脚本

u  通过执行脚本在命令行输出执行计划

操作如下：

注：编写的脚本存放在/home/oracle目录下,SQL id为6x3gu99adnjc7

[oracle@ENMOEDU ~]$ ls

awrrpt_1_69_70.html     db.rsp          install2013-10-07_17-41-09.log   rlwrap-0.37.tar.gz

awrrpt_1_71_74.html     examples        LOG_cat_owb.TXT                  sq.sql

awrsqlrpt_1_69_70.html  expdp_oltp.dmp  p10404530_112030_LINUX_6of7.zip

awrsqlrpt_1_71_74.html  expdp_oltp.log  rlwrap-0.37

[oracle@ENMOEDU ~]$ cat sq.sql

set linesize 150

set pagesize 999

select * from table(dbms_xplan.display_awr('&SQLID',null,87396644,'ADVANCED'))

[oracle@ENMOEDU ~]$ exit

exit

 

SYS@ENMOEDU> @/home/oracle/sq.sql

  2  ;

Enter value for sqlid: 6x3gu99adnjc7

old   1: select * from table(dbms_xplan.display_awr('&SQLID',null,87396644,'ADVANCED'))

new   1: select * from table(dbms_xplan.display_awr('6x3gu99adnjc7',null,87396644,'ADVANCED'))

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 6x3gu99adnjc7

--------------------

UPDATE MGMT_JOB_EXECUTION SET STEP_STATUS = :B3 WHERE STEP_STATUS = :B2

AND STEP_ID = :B1 AND START_TIME < (MGMT_JOB_ENGINE.SYSDATE_UTC() -

(30/60/24))

 

Plan hash value: 551834676

 

------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT             |                       |       |       |     1 (100)|          |

|   1 |  UPDATE                      | MGMT_JOB_EXECUTION    |       |       |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| MGMT_JOB_EXECUTION    |     1 |   109 |     1   (0)| 00:00:01 |

|   3 |    INDEX UNIQUE SCAN         | PK_MGMT_JOB_EXECUTION |     1 |       |     0   (0)|          |

------------------------------------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - UPD$1

   2 - UPD$1 / MGMT_JOB_EXECUTION@UPD$1

   3 - UPD$1 / MGMT_JOB_EXECUTION@UPD$1

 

Outline Data

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

      DB_VERSION('11.2.0.3')

      ALL_ROWS

      OUTLINE_LEAF(@"UPD$1")

      INDEX_RS_ASC(@"UPD$1" "MGMT_JOB_EXECUTION"@"UPD$1" ("MGMT_JOB_EXECUTION"."STEP_ID"))

      END_OUTLINE_DATA

  */

 

Peeked Binds (identified by position):

--------------------------------------

 

   2 - :B2 (NUMBER): 2

   3 - :B1 (NUMBER): -2

 

 

44 rows selected.

 


 

    


来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29366942/viewspace-1064684/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29366942/viewspace-1064684/

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值