oracle awr报告生成与分析

Automatic Workload Repository(AWR)是10g引入的一个重要组件。

在里面存贮着近期一段时间内（默认是7天）数据库活动状态的详细信息。

1、生成awr报告

以oracle用户登录

sqlplus / as sysdba

@?/rdbms/admin/awrrpt.sql

2、分析报告

SQL ordered by Elapsed Time

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.

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

Elapsed Time (s)CPU Time (s)Executions Elap per Exec (s) % Total DB TimeSQL IdSQL ModuleSQL Text

Elapsed Time(S) : SQL 语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL 跑的时间，而是监控范围内SQL 执行次数的总和时间。单位时间为秒。Elapsed Time = CPU Time + Wait Time

CPU Time(s) : 为SQL 语句执行时CPU 占用时间总时长，此时间会小于等于Elapsed Time 时间。单位时间为秒。

Executions : SQL 语句在监控范围内的执行次数总计。

Elap per Exec(s): 执行一次SQL 的平均时间。单位时间为秒。

% Total DB Time : 为SQL 的Elapsed Time 时间占数据库总时间的百分比。 SQL ID: SQL 语句的ID 编号，点击之后就能导航到下边的SQL 详细列表中，点击IE 的返回可以回到当前SQL ID 的地方。

SQL Module : 显示该SQL 是用什么方式连接到数据库执行的，如果是用SQL*Plus 或者PL/SQL 链接上来的那基本上都是有人在调试程序。一般用前台应用链接过来执行的sql 该位置为空。

SQL Text: 简单的sql 提示，详细的需要点击SQL ID 。

SQL ordered by CPU Time

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.

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

CPU Time (s)Elapsed Time (s)Executions CPU per Exec (s)% Total DB TimeSQL IdSQL ModuleSQL Text

记录了执行占CPU 时间总和时间最长的TOP SQL( 注意是监控范围内该SQL 的执行占CPU 时间总和，而不是单次SQL 执行时间) 。

SQL ordered by Gets

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.

Total Buffer Gets: 964,486

Captured SQL account for 103.6% of Total

Buffer Gets Executions Gets per Exec %TotalCPU Time (s)Elapsed Time (s)SQL IdSQL ModuleSQL Text

记录了执行占总buffer gets( 逻辑IO ) 的TOP SQL( 注意是监控范围内该SQL 的执行占Gets 总和，而不是单次SQL 执行所占的Gets).

SQL ordered by Reads

Total Disk Reads: 5,606

Captured SQL account for 168.4% of Total

Physical ReadsExecutionsReads per Exec %TotalCPU Time (s)Elapsed Time (s)SQL IdSQL ModuleSQL Text

记录了执行占总磁盘物理读( 物理IO ) 的TOP SQL( 请注意是监控范围内该SQL 的执行占磁盘物理读总和，而不是单次SQL 执行所占的磁盘物理读) 。

SQL ordered by Executions

Total Executions: 20,124

Captured SQL account for 59.3% of Total

Executions Rows ProcessedRows per ExecCPU per Exec (s)Elap per Exec (s) SQL IdSQL ModuleSQL Text

记录了按照SQL 的执行次数 排序的TOP SQL 。该排序可以看出监控范围内的SQL 执行次数。

SQL ordered by Parse Calls

Total Parse Calls: 14,635

Captured SQL account for 69.0% of Total

Parse CallsExecutions % Total ParsesSQL IdSQL ModuleSQL Text

记录了SQL 的软解析次数 的TOP SQL 。

SQL ordered by Sharable Memory

Only Statements with Sharable Memory greater than 1048576 are displayed Sharable Mem (b)Executions % TotalSQL IdSQL ModuleSQL Text

记录了SQL 占用library cache 的大小 的TOP SQL 。

Sharable Mem (b) ： 占用 library cache 的大小。单位是byte 。

SQL ordered by Version Count

Only Statements with Version Count greater than 20 are displayed Version Count Executions SQL IdSQL ModuleSQL Text

记录了SQL 的打开子游标 的TOP SQL 。

Oracle性能分析入门学习中，遇到Oracle数据库的性能问题，一般首要的步骤就是导出AWR的分析报告，AWR是10g中新引入的一个工具，在这之前一般是利用statspack。要导出AWR报告，只要利用Oracle的一个脚本即可以完成，下面是我利用script录制的一段导出AWR report的过程。

首先进入$ORACLE_HOME/rdbms/admin目录，在sqlplus下运行@awrrpt脚本，按照提示一步一步就可以达成，导出的格式有两种，txt格式和html格式。

在OTN上，有一个DBA新feature系列，第六周就是专门讲的这个feature，大家可以去那里了解一下（中文的）

Script started on Sun 27 Jan 2008 08:47:15 PM PST

[ractest@sun880-1 /u01/app/oracle/orahome/rdbms/admin]$ cd $ORACLE_HOME/rdbms/admin

[ractest@sun880-1 /u01/app/oracle/orahome/rdbms/admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Jan 27 20:47:38 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> @awrrpt

Current Instance

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

DB Id DB Name Inst Num Instance

———– ———— ——– ————

2080780909 ORCLDB 1 orcldb1

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

———— ——– ———— ———— ————

* 2080780909 1 ORCLDB orcldb1 sun880-1

2080780909 4 ORCLDB orcldb4 sun880-4

2080780909 2 ORCLDB orcldb2 sun880-2

2080780909 3 ORCLDB orcldb3 sun880-3

Using 2080780909 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: 1

Listing the last day’s Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

———— ———— ——— —————— —–

orcldb1 ORCLDB 66 27 Jan 2008 00:55 1

67 27 Jan 2008 01:55 1

68 27 Jan 2008 02:55 1

69 27 Jan 2008 03:55 1

70 27 Jan 2008 04:55 1

71 27 Jan 2008 05:55 1

72 27 Jan 2008 06:55 1

73 27 Jan 2008 07:55 1

74 27 Jan 2008 08:55 1

75 27 Jan 2008 09:55 1

76 27 Jan 2008 10:55 1

77 27 Jan 2008 11:55 1

78 27 Jan 2008 12:55 1

83 27 Jan 2008 17:54 1

84 27 Jan 2008 18:55 1

85 27 Jan 2008 19:55 1

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 70

Begin Snapshot Id specified: 70

Enter value for end_snap: 71

End Snapshot Id specified: 71

Specify the Report Name

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

The default report file name is awrrpt_1_70_71.txt. To use this name, press to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_70_71.txt

WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Release RAC Host

———— ———– ———— ——– ———– — ———— ORCLDB 2080780909 orcldb1 1 10.2.0.4.0 YES sun880-1 Snap Id Snap Time Sessions Curs/Sess

——— ——————- ——– ———

Begin Snap: 70 27-Jan-08 04:55:07 136 16.2

End Snap: 71 27-Jan-08 05:55:21 141 16.5

Elapsed: 60.23 (mins)

DB Time: 5,814.07 (mins)

Cache Sizes

~~~~~~~~~~~ Begin End

//此处有删减

————————————————————-

End of Report

Report written to awrrpt_1_70_71.txt

SQL> quit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

[ractest@sun880-1 /u01/app/oracle/orahome/rdbms/admin]$ exit

script done on Sun 27 Jan 2008 08:48:16 PM PST

第二篇：The review of Charlie and chocolate factory《查理与巧克力工厂》英语影评

The review of Charlie and

chocolate factory

It is a truth universally acknowledged that children are the symbol of innocence. But, apparently, the director of the Charlie and chocolate factory doesn’t agree.

There are five children in the film who have won the golden thickets which were offered by the owner of the largest chocolate factory in the world. The first kid is a gluttonous fat boy, who is always eating chocolate every time. The second is an emulative little girl. She has won many champions and never want to lose any game. The third girl is from a very rich family, whose father is a boss of a company. The forth is a little genius who is wallowed in those violent computer games. The last one, Charlie, the leading actor in this film, is just a normal boy, who is not faster, not stronger, or not more clever than other children, and his family is very poor. These children with one of their family members were invited to have a visit to that famous and mysterious chocolate factory, and one of them will got a special present at the end of the trip.

The factory’s owner, Mr Willy Wonka, is an odd fish who can’t pronounce the word parents clearly and have a lot of strange but funny ideas. To some extent, he is a man who has the child’s heart and adult’s mind. During the trip of visit in the factory, the kids and their parents were punished in succession for their greed, gluttony, envy, willfulness, selfishness and so on. So, at the end of the trip, Charlie was the only child left still , without any punishment. Mr Willy Wonka praised him and he want to give Charlie the chocolate factory as a present ,but there still has a condition to be mentioned ,which goes that if Charlie want to take over the factory , he must leave his family and go alone.

Now , there is a problem ,if you are Charlie ,which one will you choose , to go to the factory and to be rich or refuse the present and still live with the poor but warm family ? This is what the direct want to tell us. Nowadays, more and more people prefer to pursue the money and the power rather than cherish the love and dream. The first four children stand for the people in current society, Charlie stands for the people who are warmhearted, concerned, full of dream and love. In the film, Charlie’s family is not rich or powerful or even well connected, but Charlie still choose to stay with his family members and refuse to go to the factory alone. Maybe if such situation comes to us , most of us will hesitate to choose , but Charlie really understand that the love between family is the most valuable thing in the world , so he can make the decision without hesitation.

In the film , there is a sharp contrast between Mr Willy Wonka and Charlie –Willy Wonk have the largest and the most famous chocolate factory in the world, but he is alone; however, Charlie has nothing but he has a warm family. So Charlie is the luckiest boy in the world.(from the film) Not for his lucky chance to get the golden ticket, but for his family. I still remember the screen that when Charlie got his birthday gift , a bar of chocolate which is produced by Willy Wonka’s factory, he opened the box and found no golden thicket, I thought maybe he is not delighted ,but

when I saw he is sharing the chocolate with his family , I suddenly understand that he is the most happiest one in the world. He has a warm family , he loves them ,and they love him as well. The director think this is the greatest thing in the world, and I think so.

Charlie and chocolate factory is like an allegory rather than a fairy tale. Love is the most important thing in our life, so we can’t neglect it to pursue material things such as money or power. From the film we can learn that we should regress to the life which is full of love and innocence.