AWR EXTRACT&AWR Baseline
1.AWR EXTRACT(生产数据库)
--查询是否创建导出目录,如没有可自行创建一个
create or replace directory IMP_PATH as '/u01/replay';
select DIRECTORY_NAME, DIRECTORY_PATH from
dba_directories;
--查询现有快照点
select min(SNAP_ID) from
sys.wrh$_active_session_history;
select * from dba_hist_active_sess_history ;
--导出快照点11100至11106的性能监视
exec DBMS_SWRF_INTERNAL.AWR_EXTRACT(DMPFILE
=>'awr_data_cqabdt0',DMPDIR =>'IMP_PATH',BID =>11100,EID =>11106 );
or
@$ORACLE_HOME/rdbms/admin/awrextr.sql
2.AWR 导入(测试数据库)
select DIRECTORY_NAME, DIRECTORY_PATH from
dba_directories;
exec
DBMS_SWRF_INTERNAL.AWR_LOAD(SCHNAME =>'TEST',dmpfile
=>'awr_data_cqabdt0',dmpdir =>'IMP_PATH');
exec
DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'TEST');
3.AWR Baseline(测试数据库)
set pagesize 0
set linesize 32767
select * from
table(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(1273272014,1,11100,11102,1273272014,1,11103,11104));
或者
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
其他参考
####
#
#/bin/sh
result=`sqlplus -s system@xitest01 << EOF
set pagesize 0
set linesize 32767
select *
from
table(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(1273272014,1,11100,11102,1273272014,1,11103,11104));
EOF`
echo
"ok1"
echo
"$result" > awr_baseline_html.html
#通过sh脚本导出html静态网页
#
#
#
###############
## BEGIN
## DBMS_SWRF_INTERNAL.AWR_EXTRACT
(DMPFILE => 'awr_data.dmp',
## DMPDIR
=> 'DB_DIR', -- Database directory
## BID
=> 4283, -- Begin snapshot
## EID
=> 4315 -- End snapshot
## );
## END;
## /
##
## BEGIN
## DBMS_SWRF_INTERNAL.AWR_LOAD
(SCHEMA => 'MYSCHEMA',
## DMPFILE
=> 'awr_data.dmp',
## DMPDIR
=> 'DB_DIR', -- Database directory );
## END;
## /
##
## BEGIN
## DBMS_SWRF_INTERNAL.MOVE_TO_AWR
(SCHEMA => 'MYSCHEMA');
## END;
## /
###############