用xshell 登陆：
[oracle@node3 /]$ su - oracle
[oracle@node3 ~]$ sqlplus system/oracle@192.168.100.10/orcl
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 <return> 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 <return> 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;
