1、ASH和AWR的故事

1.1、关于ASH

用户在Oracle数据库中执行操作时必然要创建相应的连接和会话,其中,所有的当前会话信息都保存在动态性能视图v$session中,通过视图,DBA可以查看用户实际执行的操作,或者当前等待的事件。通常这部分信息是调优过程中的关键信息,不过,一旦连接断开。会话信息就会被同时从V$SESSION及其它相关视图中清除。

10g 版本中,ORACLE又新增加了一个视图V$ACTIVE_SESSION_HISTORY,代表活动会话的历史记录,即使用户操作完成后,断开了连接,其会话的情况也会被记录下来,这项特性就是ASH了。

ASH 每秒钟收集一次当前处于非空闲等待事件的、活动状态的、session的信息,并保存在V$ACTIVE_SESSION_HISTORY视图中,我们知道,动态性能视图其实上是ORACLE自行构造的一堆存在于SGA内存区的虚表,就是说,ASH的数据是保存在内存里的,实际上,ORACLE分配给ASH的空间并不是无限大(更何况ORACLE自身管理的内存空间也不是无限大),查看ASH可供使用的内存空间,可以通过如下SQL:

SQL> select pool,name,bytes/1024/1024 MB from v$sgastat where name like '%ASH%';


POOL         NAME                               MB

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

shared pool  ASH buffers                         8

shared pool  UNDO INFO HASH             .005706787

SQL>

直白的讲 ,V$ACTIVE_SESSION_HISTORY中能够记录多少会话信息, 一方面取决于该数据库的SGA 分配给ASH buffers的大小 ,另一方面取决于数据库的启动和关闭(重启数据库时将重构SGA内存区)。这两方面的因素制约了V$ACTIVE_SESSION_HISTORY中能够保存的会话信息的能力。ORACLE又提供AWR特性,ASH收集到的会话信息,是做为AWR中快照信息的一部分,被保存到了硬盘上。

1.2、关于AWR

AWR 是 Oracle 10g 版本 推出的新特性,全称叫Automatic Workload Repository-自动负载信息库(接替了之前的Statspack的功能,功能更加完善了)。

AWR负责收集、处理并维护性能统计信息,用于检查和分析性能问题,AWR生成的统计数据即可以通过V$视图和DBA_*数据字典查看,也可以通过脚本来生成相应报表。

2.生成分析报表

AWR 是通过对比两次快照(snapshot)收集到的统计信息,来生成报表数据,生成的报表包括多个部分,AWR在生成报告时,可以选择生成TXT或HTML两种格式的报告。可以生成的报告脚本如下:

  • awrrpt.sql :生成指定快照区间的统计报表;

  • awrrpti.sql :生成指定数据库实例,并且指定快照区间的统计报表;

  • awrsqlrpt.sql :生成指定快照区间,指定SQL语句(实际指定的是该语句的SQLID)的统计报表;

  • awrsqrpi.sql :生成指定数据库实例,指定快照区间的指定SQL语句的统计报表;

  • awrddrpt.sql :指定两个不同的时间周期,生成这两个周期的统计对比报表;

  • awrddrpi.sql :指定数据库实例,并指定两个的不同时间周期,生成这两个周期的统计对比报表;

注意:想要让AWR收集到准确的统计信息,从而生成可靠的性能分析报告,必须将初始化参数statistics_level的值设置为typical(默认)或all。

SQL> show parameter statistics_level;

NAME                                 TYPE        VALUE

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

statistics_level                     string      TYPICAL

SQL>

2.1生成标准统计报表

SQL> show user;

USER is "SYS"

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Current Instance

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

  DB Id    DB Name      Inst Num Instance

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

1368089174 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: html

--此处需指定生成的报表格式,有txt和html两种选择,默认情况下为html格式

Type Specified:  html


Instances in this Workload Repository schema

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

  DB Id     Inst Num DB Name      Instance     Host

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

* 1368089174        1 ORCL         orcl         myCentOS03


Using 1368089174 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: 2

此处需指定要读取多少天内的快照信息

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots

                                                      Snap

Instance     DB Name        Snap Id    Snap Started    Level

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

orcl         ORCL               204 13 Mar 2014 00:00      1

                               205 13 Mar 2014 01:00      1


                               206 13 Mar 2014 16:38      1

                               207 13 Mar 2014 18:00      1

                               208 13 Mar 2014 19:00      1

                               209 13 Mar 2014 20:00      1

                               210 13 Mar 2014 21:00      1

                               211 13 Mar 2014 22:00      1

                               212 13 Mar 2014 23:00      1

                               213 14 Mar 2014 00:00      1


                               214 14 Mar 2014 01:00      1

                               215 14 Mar 2014 02:00      1

                               216 14 Mar 2014 03:00      1

                               217 14 Mar 2014 04:00      1

                               218 14 Mar 2014 05:00      1


                               219 14 Mar 2014 05:42      1

                               220 14 Mar 2014 07:00      1


Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 217

Begin Snapshot Id specified: 217


Enter value for end_snap: 218

End   Snapshot Id specified: 218


Specify the Report Name

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

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

press <return> to continue, otherwise enter an alternative.


Enter value for report_name:


此处为要生成的报告指定一个文件名,默认会根据前面输入的snap_id生成一个文件名,比如这里生成的默认文件名为awrrpt_1_217_218.html,当然DBA可以根据实际情况对文件名进行自定义。

可能出现的问题:

declare

*

ERROR at line 1:

ORA-20200: The instance was shutdown between snapshots 206 and 215

ORA-06512: at line 42

Enter value for report_name:/u01/app/oracle/awrrpt_1_217_218.html

........

End of Report

</BODY></HTML>

Report written to /u01/app/oracle/awrrpt_1_217_218.html

最终生成统计报表,如下:

[oracle@myCentOS03 oracle]$ ls

admin                  db_1                 my_arch_backup  oradata

awrrpt_1_217_218.html  flash_recovery_area  my_cold_backup  oraInventory

[oracle@myCentOS03 oracle]$ pwd

/u01/app/oracle

[oracle@myCentOS03 oracle]$

通过浏览器打开,如下所示:

wKiom1MjDNrCSFe6AAIfP9SeW1U454.jpg

2.2生成指定实例的数据库报表

这项统计报表一般是针对多实例数据库,前面使用的脚本是生成数据库级别的统计报表,对于多实例的数据库,有时候DBA可能希望看到某个实例的表现,那么本脚本就不能用了。

SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql


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:

此处需指定生成的报表格式,有txt和html两种选择,默认情况下为html格式。

Type Specified:  html


Instances in this Workload Repository schema

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


  DB Id     Inst Num DB Name      Instance     Host

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

* 1368089174        1 ORCL         orcl         myCentOS03


Enter value for dbid: 1368089174

Using 1368089174 for database Id

Enter value for inst_num: 1

Using 1 for instance number

相比标准统计报表的生成,这里多了两个需指定的值,就是选择要生成报表的DBID以及实例的ID。

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: 2

指定要读取多少天内的快照信息。

Enter value for num_days: 2


Listing the last 2 days of Completed Snapshots

                                                       Snap

Instance     DB Name        Snap Id    Snap Started    Level

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

orcl         ORCL               204 13 Mar 2014 00:00      1

                               205 13 Mar 2014 01:00      1


                               206 13 Mar 2014 16:38      1

                               207 13 Mar 2014 18:00      1

                               208 13 Mar 2014 19:00      1

                               209 13 Mar 2014 20:00      1

                               210 13 Mar 2014 21:00      1

                               211 13 Mar 2014 22:00      1

                               212 13 Mar 2014 23:00      1

                               213 14 Mar 2014 00:00      1


                               214 14 Mar 2014 01:00      1

                               215 14 Mar 2014 02:00      1

                               216 14 Mar 2014 03:00      1

                               217 14 Mar 2014 04:00      1

                               218 14 Mar 2014 05:00      1


                               219 14 Mar 2014 05:42      1

                               220 14 Mar 2014 07:00      1


Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 214

Begin Snapshot Id specified: 214


Enter value for end_snap: 215

End   Snapshot Id specified: 215


Specify the Report Name

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

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

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: /u01/app/oracle/awrrpt_1_214_215.html

......

End of Report

</BODY></HTML>

Report written to /u01/app/oracle/awrrpt_1_214_215.html

剩下的步骤就与标准统计报表的步骤完全相同。

2.3生成指定SQL语句的统计报表

这项统计专门用来分析某条指定的SQL语句,通过awrsqrpt.sql脚本,awr能够生成指定sql(曾经执行过的SQL)的执行计划,消耗的资源等等信息,有助于DBA进行SQL调优。

具体操作如下,首先还是执行生成脚本:

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql


Current Instance

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


  DB Id    DB Name      Inst Num Instance

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

1368089174 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: html

Type Specified:  html


Instances in this Workload Repository schema

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


  DB Id     Inst Num DB Name      Instance     Host

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

* 1368089174        1 ORCL         orcl         myCentOS03


Using 1368089174 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: 2


Listing the last 2 days of Completed Snapshots


                                                       Snap

Instance     DB Name        Snap Id    Snap Started    Level

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

orcl         ORCL               204 13 Mar 2014 00:00      1

                               205 13 Mar 2014 01:00      1


                               206 13 Mar 2014 16:38      1

                               207 13 Mar 2014 18:00      1

                               208 13 Mar 2014 19:00      1

                               209 13 Mar 2014 20:00      1

                               210 13 Mar 2014 21:00      1

                               211 13 Mar 2014 22:00      1

                               212 13 Mar 2014 23:00      1

                               213 14 Mar 2014 00:00      1


                               214 14 Mar 2014 01:00      1

                               215 14 Mar 2014 02:00      1

                               216 14 Mar 2014 03:00      1

                               217 14 Mar 2014 04:00      1

                               218 14 Mar 2014 05:00      1


                               219 14 Mar 2014 05:42      1

                               220 14 Mar 2014 07:00      1


Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 214

Begin Snapshot Id specified: 214


Enter value for end_snap: 215

End   Snapshot Id specified: 215


Specify the SQL Id

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

Enter value for sql_id: 17y8m0gj8x941

指定要分析的SQL_ID

批注:

首先知道当前的会话ID,如下:

SQL> select sid from v$mystat where rownum=1;


      SID

----------

      148

SQL> select SQL_ID from v$session where sid=148;


SQL_ID

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

17y8m0gj8x941

AWR自动生成分析报表

2.4生成不同时间段时的统计对比报表

在没有awr之前,如果希望对不同时间段时,数据库的整体影响进行对比,只能依靠DBA手工查询相关视图,并通过时间条件来获取差异(还有些统计已经无法对比),而在AWR中,直接就提供了,对不同时间段时,数据库的性能统计做差异对比的功能。

执行脚本如下:

SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql


Current Instance

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


  DB Id       DB Id    DB Name      Inst Num Inst Num Instance

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

1368089174  1368089174 ORCL                1        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: html


Instances in this Workload Repository schema

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


  DB Id     Inst Num DB Name      Instance     Host

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

* 1368089174        1 ORCL         orcl         myCentOS03


Database Id and Instance Number for the First Pair of Snapshots

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

Using 1368089174 for Database Id for the first pair of snapshots

Using          1 for Instance Number for the first pair of snapshots

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: 2


Listing the last 2 days of Completed Snapshots


                                                      Snap

Instance     DB Name        Snap Id    Snap Started    Level

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

orcl         ORCL               204 13 Mar 2014 00:00      1

                               205 13 Mar 2014 01:00      1


                               206 13 Mar 2014 16:38      1

                               207 13 Mar 2014 18:00      1

                               208 13 Mar 2014 19:00      1

                               209 13 Mar 2014 20:00      1

                               210 13 Mar 2014 21:00      1

                               211 13 Mar 2014 22:00      1

                               212 13 Mar 2014 23:00      1

                               213 14 Mar 2014 00:00      1


                               214 14 Mar 2014 01:00      1

                               215 14 Mar 2014 02:00      1

                               216 14 Mar 2014 03:00      1

                               217 14 Mar 2014 04:00      1

                               218 14 Mar 2014 05:00      1


                               219 14 Mar 2014 05:42      1

                               220 14 Mar 2014 07:00      1


Specify the First Pair of Begin and End Snapshot Ids

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

Enter value for begin_snap: 214

First Begin Snapshot Id specified: 214


Enter value for end_snap: 215

First End   Snapshot Id specified: 215

Instances in this Workload Repository schema

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


  DB Id     Inst Num DB Name      Instance     Host

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

* 1368089174        1 ORCL         orcl         myCentOS03



Database Id and Instance Number for the Second Pair of Snapshots

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


Using 1368089174 for Database Id for the second pair of snapshots

Using          1 for Instance Number for the second pair of snapshots


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_days2: 2


Listing the last 2 days of Completed Snapshots


                                                       Snap

Instance     DB Name        Snap Id    Snap Started    Level

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

orcl         ORCL               204 13 Mar 2014 00:00      1

                               205 13 Mar 2014 01:00      1


                               206 13 Mar 2014 16:38      1

                               207 13 Mar 2014 18:00      1

                               208 13 Mar 2014 19:00      1

                               209 13 Mar 2014 20:00      1

                               210 13 Mar 2014 21:00      1

                               211 13 Mar 2014 22:00      1

                               212 13 Mar 2014 23:00      1

                               213 14 Mar 2014 00:00      1


                               214 14 Mar 2014 01:00      1

                               215 14 Mar 2014 02:00      1

                               216 14 Mar 2014 03:00      1

                               217 14 Mar 2014 04:00      1

                               218 14 Mar 2014 05:00      1


                               219 14 Mar 2014 05:42      1

                               220 14 Mar 2014 07:00      1


Specify the Second Pair of Begin and End Snapshot Ids

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

Enter value for begin_snap2: 216

Second Begin Snapshot Id specified: 216


Enter value for end_snap2: 217

Second End   Snapshot Id specified: 217

Specify the Report Name

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

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

press <return> to continue, otherwise enter an alternative.


Enter value for report_name: /u01/app/oracle/awrdiff_1_214_1_216.html

......

Report written to /u01/app/oracle/awrdiff_1_214_1_216.html

报表生成以后,在显示时将以并列的形式,直观的显示出两个不同时间段里,数据库各项参数的差异,摘要如图:

wKioL1MjvDyzQ_tVAAL8h2_1Nkg379.jpg

2.5生成指定SQL语句的统计报表

前例的对比是在单实例环境下进行的,如果希望对多实例的数据库做对比,那就要使用$ORACLE_HOME/rdbms/admin/awrddrpi.sql脚本。

3.查看AWR视图

不管是EM也好,或是前面演示中使用的awr*.sql脚本也好,实质都是访问ORACLE中的部分相关视图来生成统计数据,当然也可以直接查询动态性能视图(或相关数据字典)的方式来获取自己想要的那部分性能数据。ORACLE将这部分性能统计数据保存在DBA_HIST开头的数据字典中,要查询当前实例所有能够访问的DBA_HIST字典,可以通过下列语句:

SQL>  select * from dict where table_name like ¨DBA_HIST%¨;

TABLE_NAME                     COMMENTS

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

DBA_HIST_DATABASE_INSTANCE     Database Instance Information

DBA_HIST_SNAPSHOT              Snapshot Information

DBA_HIST_SNAP_ERROR            Snapshot Error Information

DBA_HIST_BASELINE              Baseline Metadata Information

DBA_HIST_WR_CONTROL            Workload Repository Control Information

DBA_HIST_DATAFILE              Names of Datafiles

DBA_HIST_FILESTATXS            Datafile Historical Statistics Information

DBA_HIST_TEMPFILE              Names of Temporary Datafiles

DBA_HIST_TEMPSTATXS            Temporary Datafile Historical Statistics Information

DBA_HIST_COMP_IOSTAT           I/O stats aggregated on component level

DBA_HIST_SQLSTAT               SQL Historical Statistics Information

DBA_HIST_SQLTEXT               SQL Text

......................