oracle生成awr报告命令,oracle数据库生成awr报告

用xshell登陆：

[oracle@node3 /]$ su - oracle

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 9 14:16:59 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

exec dbms_workload_repository.create_snapshot();

SQL> exec dbms_workload_repository.create_snapshot();    (loadrunner开始跑压力测试脚本后，通过这句话手动创建Snapshots，此时生产一个id，大致记住当前时间，在后面会用到)

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_snapshot();    (loadrunner结束跑压力测试脚本后，通过这句话再手动创建Snapshots，此时生产一个id，大致记住当前时间，在后面会用到)

(后面会取这两个id之间的值，作为html报告生成。)

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance

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

DB Id    DB Name   Inst Num Instance

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

787750339 LTDB          1 ltdb1

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

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

* 787750339        1 LTDB         ltdb1  node1

787750339      2 LTDB         ltdb2  node2

Using  787750339 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

Instance     DB Name     Snap Id    Snap Started    Level

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

ltdb1              LTDB              1412 08 Oct 2016 11:00       1

1429 09 Oct 2016 13:04       1

1430 09 Oct 2016 13:35       1

1431 09 Oct 2016 13:35       1

1432 09 Oct 2016 13:51       1

1433 09 Oct 2016 14:07       1

1434 09 Oct 2016 14:17       1

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap:(1433从上面的列表选出来的。)(基本上是取最后两个id，因为上面也是手动创建过两次Snapshots)

Enter value for end_snap: 1434(1434从上面的列表选出来的。)

End   Snapshot Id specified: 1434

Specify the Report Name

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

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

press to continue, otherwise enter an alternative.

Enter value for report_name:(默认报告的名字。路径是/home/orace/ awrrpt_1_1431_1434.html)回车后就生产报告了。

/export/home/oracle

报告里需要关注的点：主要就是sql脚本执行时间，这里需要开发调优。

简单介绍：

--如果需要的话DBA可以通过DBMS_WORKLOAD_REPOSITORY过程手动创建、删除或修改snapshots.

--提示调用DBMS_WORKLOAD_REPOSITORY包需要拥有DBA权限。

--1.手动创建Snapshots 手动创建Snapshots通过DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT过程

--例如

exec dbms_workload_repository.create_snapshot();

-- 然后可以通过 DBA_HIST_SNAPSHOT 视图查看刚刚创建的Snapshots信息。

SELECT * FROM DBA_HIST_SNAPSHOT;

-- 2手动删除Snapshots

--删除Snapshots是使用DBMS_WORKLOAD_REPOSITORY包的另一个过程DROP_SNAPSHOT_RANGE 该过程在执行时可以通过指定snap_id的范围的方式一次删除多个Snapshot

--例如

select count(0) from dba_hist_snapshot where snap_id between 6770 and 6774;

select max(snap_id) from dba_hist_snapshot;

select dbid from v$database;

exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 6770,high_snap_id => 6774,dbid => 4059638244);

--或者

begin

dbms_workload_repository.drop_snapshot_range(

low_snap_id => 6770,

high_snap_id => 6774,

dbid => 4059638244);

end;

select count(0) from dba_hist_snapshot where snap_id between 6770 and 6774;