注：转自http://blog.csdn.net/wish503/article/details/52066944
------------------------------------------------------
--Step1: 创建名称为STS_NAME_TEST 的SQL_SET.
---------------------------------------------------
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => 'STS_NAME_TEST',
DESCRIPTION => 'COMPLETE APPLICATION WORKLOAD',
SQLSET_OWNER =>'TEST');
END;
/
--------------------------------------------------
--Step2: 初始加载当前数据库中的SQL.
---------------------------------------------------
DECLARE
STSCUR DBMS_SQLTUNE.SQLSET_CURSOR;
v_cnt NUMBER;
BEGIN
OPEN STSCUR FOR
SELECT VALUE (P)
FROM TABLE (
DBMS_SQLTUNE.SELECT_CURSOR_CACHE (
'(lower(sql_text) LIKE ''%table_name%''
) and PARSING_SCHEMA_NAME <> ''SYS''',
'ALL')) P;
-- POPULATE THE SQLSET
DBMS_SQLTUNE.LOAD_SQLSET (SQLSET_NAME => 'STS_NAME_TEST',
POPULATE_CURSOR => STSCUR,
commit_rows => 100,
SQLSET_OWNER => 'TEST');
CLOSE STSCUR;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
/
--------------------------------------------------
--Step3: 增量抓取数据库中的SQL, 会连续抓取三天,每小时抓取一次，在后台执行
---------------------------------------------------
--1编辑sql文件
vi getsql_TEST.sql
BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(SQLSET_NAME=>''STS_NAME_TEST'',
TIME_LIMIT=> 259200,
REPEAT_INTERVAL=>3600,
CAPTURE_OPTION=>'MERGE',
CAPTURE_MODE =>DBMS_SQLTUNE.MODE_ACCUMULATE_STATS,
BASIC_FILTER=>
'(lower(sql_text) LIKE ''%table_name%'') and PARSING_SCHEMA_NAME <> ''SYS''',
SQLSET_OWNER => 'TEST');
END;
/
--2编辑shell脚本
vi getsql_TEST.sh
#!/bin/ksh
sqlplus '/as sysdba' << EOF
@getsql_TEST.sql
exit
EOF
--3后台执行脚本
nohup sh getsql_TEST.sh &
-------------------------
BSCOW，创建中间表
-------------------------
Begin
dbms_sqltune.create_stgtab_sqlset(table_name => 'STGTAB_TEST' ,schema_name => 'TEST');
End;
/
---------------------------
BSCOW，将sqlset打包到中间表
---------------------------
Begin
dbms_sqltune.pack_stgtab_sqlset(sqlset_name =>'STS_NAME_TEST',sqlset_owner =>'TEST',
staging_table_name =>'STGTAB_TEST' ,staging_schema_owner => 'TEST' );
End;
/
-----------------
BSCOW，导出中间表
-----------------
exp STGTAB_TEST
--------------------------------------------
中间库,创建到部署cow的db_link，导入SQL，去重
--------------------------------------------
create public database link to_dbname connect to TEST identified by XXXX
using 'DBNAME';
imp STGTAB_TEST
--按照plan_hash_value;
begin
for v in (
select plan_hash_value,count(*) cnt from TEST.STGTAB_TEST group by plan_hash_value) loop
while(v.cnt>1) loop
delete from TEST.STGTAB_TEST a where a.plan_hash_value =v.plan_hash_value and rownum=1;
v.cnt:=v.cnt-1;
end loop;
commit;
end loop;
end;
/
--按照hash_value:
begin
for v in (
select hash_value,count(*) cnt from TEST.STGTAB_TEST group by hash_value) loop
while(v.cnt>1) loop
delete from TEST.STGTAB_TEST a where a.hash_value =v.hash_value and rownum=1;
v.cnt:=v.cnt-1;
end loop;
commit;
end loop;
end;
/
-------------------------------------------------
中间库，对中间表进行解包，将sql导入到目标端sqlset
-------------------------------------------------
update TEST.STGTAB_TEST set owner='TEST';
Begin
dbms_sqltune.unpack_stgtab_sqlset(sqlset_name => 'STS_NAME_TEST',sqlset_owner => 'TEST' ,replace => TRUE,staging_table_name =>'STGTAB_TEST' ,staging_schema_owner => 'TEST');
End;
/
------------------
中间库，过滤sqlset
-----------------
begin
DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name => 'STS_NAME_TEST',basic_filter => 'SQL_TEXT LIKE ''%Analyze(%''',sqlset_owner => 'TEST');
END;
/
(把sqlset中的analyze语句去掉，这种语句非业务语句）
select sql_text from dba_sqlset_statements where sqlset_name='STS_NAME_TEST';（查询sqlset中的sql语句）
--------------
BSCOW,增加授权
--------------
grant execute on SYS.DBMS_SQLPA to TEST;
------------------------
中间库，创建spa task
---------------------
使用devmgr用户创建SPA任务，后续执行SPA任务也使用test用户：
需要在中间库对test用户授权：
grant execute on SYS.dbms_sqlpa to TEST;
conn TEST/xxxxxx
declare
mytask varchar2(100);
begin
mytask := dbms_sqlpa.create_analysis_task(sqlset_name => 'STS_NAME_TEST',
task_name => 'task_test01');
END;
/
-----------
中间库，查询task是否创建成功
----------
Select * From Dba_Advisor_Tasks Where task_name='task_test01';
select TASK_ID,owner,TASK_NAME,status from dba_advisor_tasks where task_name='task_test01';
-----------------------------------------------
中间库，执行spa task,生成性能测试基线，后台执行,参数可能还需要考虑下任务里sql的执行次数以及对执行耗时长的sql的处理
-----------------------------------------------
sqlplus TEST/ptmjygb8 <<EOF
begin
dbms_sqlpa.execute_analysis_task(task_name => 'task_test01',
execution_type => 'TEST EXECUTE',
execution_name => 'exec01',
execution_params => dbms_advisor.argList('DATABASE_LINK','TO_DBNAME','EXECUTE_COUNT',3,'LOCAL_TIME_LIMIT','300'),
execution_desc => 'executeion on 11g');
end;
/
EOF
-----------------------
中间库，查询基线进度
-----------------------
SELECT owner,task_name,execution_name,a.execution_type,execution_start,execution_last_modified,execution_end,status,b.SOFAR,b.START_TIME,b.LAST_UPDATE_TIME
from dba_advisor_executions a, v$advisor_progress b where TASK_NAME='task_test01' and a.task_id=b.TASK_ID;
------------------
BSCOW,执行变更
------------------
比如对表收集统计信息，新建索引等。
--------------------------------------
中间库，第二次执行spa task，后台执行
-------------------------------------
sqlplus TEST/ptmjygb8 <<EOF
begin
dbms_sqlpa.execute_analysis_task(task_name => 'task_test01',
execution_type => 'TEST EXECUTE',
execution_name => 'exec02',
execution_params => dbms_advisor.argList(
'DATABASE_LINK','TO_RSAPPCOW','EXECUTE_COUNT',3,'LOCAL_TIME_LIMIT','300'),
execution_desc => 'executeion on 10g');
end;
/
EOF
——保留相关报错信息
create table Dba_Advisor_Objects_old as select * from Dba_Advisor_Objects;
create table Dba_Advisor_Findings_old as select * from Dba_Advisor_Findings;
--保留参数信息
create table dba_advisor_parameters_old as select * from dba_advisor_parameters;
create table Dba_Advisor_Executions_old as select * from Dba_Advisor_Executions;
--保留性能数据
create table dba_advisor_sqlstats_old as select * from dba_advisor_sqlstats;
create table dba_advisor_sqlplans_old as select * from dba_advisor_sqlplans;
---------------
中间库，分析结果
----------------
--执行计划改变的SQL
Select st.sql_id,
sst.executions,
dbms_lob.substr(st.sql_text, 3000) sql_text
From sys.wrh$_sqltext st,
dba_sqlset_statements sst,
(Select Distinct sql_id
From (Select sql_id,
operation,
options,
object_name,
object_alias,
object_type,
Id,
parent_id,
depth
From dba_advisor_sqlplans
Where execution_name = 'exec02'
And task_name = 'task_test01'
Minus
Select sql_id,
operation,
options,
object_name,
object_alias,
object_type,
Id,
parent_id,
depth
From dba_advisor_sqlplans
Where execution_name = 'exec01'
And task_name = 'task_test01')) cp
Where st.sql_id = cp.sql_id
And sst.sql_id = cp.sql_id
And st.sql_text Not Like '%Analyze(%'
And st.sql_text Not Like '%SELECT /* DS_SVC */%'
And st.sql_text Not Like '%/* OPT_DYN_SAMP */%'
And sst.sqlset_name = 'STS_NAME0314'；
--以Buffer_Gets作为比较条件,获取性能数据进行比对
Select *
From (Select b.*, dbms_lob.substr(st.sql_text, 3000) sql_text
From SYS.Wrh$_Sqltext st,
(Select TASK_NAME,
sql_id,
executions,
detal_buffer_gets,
bf_buffer_gets,
af_buffer_gets,
bf_plan_hash_value,
bf_rows_processed,
af_plan_hash_value,
af_ROWS_PROCESSED
From (Select TASK_NAME,
sql_id,
bf_executions executions,
round(af_buffer_gets / af_EXECUTIONS) -
round(bf_buffer_gets / bf_executions) detal_buffer_gets,
round(bf_buffer_gets / bf_executions) bf_buffer_gets,
round(af_buffer_gets / af_EXECUTIONS) af_buffer_gets,
bf_plan_hash_value,
bf_rows_processed / bf_executions bf_rows_processed,
af_plan_hash_value,
af_ROWS_PROCESSED / af_EXECUTIONS af_ROWS_PROCESSED
From (Select bf.TASK_NAME,
bf.sql_id,
bf.executions bf_executions,
bf.plan_hash_value bf_plan_hash_value,
bf.buffer_gets bf_buffer_gets,
bf.rows_processed bf_rows_processed,
af.plan_hash_value af_plan_hash_value,
af.BUFFER_GETS af_BUFFER_GETS,
af.EXECUTIONS af_EXECUTIONS,
af.ROWS_PROCESSED af_ROWS_PROCESSED
From dba_advisor_sqlstats af,
dba_advisor_sqlstats bf
Where af.execution_name = 'exec02'
And af.task_name = 'task_test01'
And bf.execution_name = 'exec01'
and bf.task_name = 'task_test01'
And bf.sql_id = af.sql_id))
Where detal_buffer_gets > 0) b
Where st.sql_id = b.sql_id
Order By detal_buffer_gets Desc)
Where sql_text Not Like '%Analyze(%'
And sql_text Not Like '%SELECT /* DS_SVC */%'
And sql_text Not Like '%/* OPT_DYN_SAMP */%';
--以elapsed_time作为比较条件获取性能数据进行比对
Select *
From (Select b.*, dbms_lob.substr(st.sql_text, 3000) sql_text
From SYS.Wrh$_Sqltext st,
(Select TASK_NAME,
sql_id,
executions,
detal_elapsed_time,
bf_elapsed_time,
af_elapsed_time,
bf_plan_hash_value,
bf_rows_processed,
af_plan_hash_value,
af_ROWS_PROCESSED
From (Select TASK_NAME,
sql_id,
bf_executions executions,
round(af_elapsed_time / af_EXECUTIONS) -
round(bf_elapsed_time / bf_executions) detal_elapsed_time,
round(bf_elapsed_time / bf_executions) bf_elapsed_time,
round(af_elapsed_time / af_EXECUTIONS) af_elapsed_time,
bf_plan_hash_value,
bf_rows_processed / bf_executions bf_rows_processed,
af_plan_hash_value,
af_ROWS_PROCESSED / af_EXECUTIONS af_ROWS_PROCESSED
From (Select bf.TASK_NAME,
bf.sql_id,
bf.executions bf_executions,
bf.plan_hash_value bf_plan_hash_value,
bf.elapsed_time bf_elapsed_time,
bf.rows_processed bf_rows_processed,
af.plan_hash_value af_plan_hash_value,
af.elapsed_time af_elapsed_time,
af.EXECUTIONS af_EXECUTIONS,
af.ROWS_PROCESSED af_ROWS_PROCESSED
From dba_advisor_sqlstats af,
dba_advisor_sqlstats bf
Where af.execution_name = 'exec02'
And af.task_name = 'task_test01'
And bf.execution_name = 'exec01'
and bf.task_name = 'task_test01'
And bf.sql_id = af.sql_id))
Where detal_elapsed_time > 0) b
Where st.sql_id = b.sql_id
Order By detal_elapsed_time Desc)
Where sql_text Not Like '%Analyze(%'
And sql_text Not Like '%SELECT /* DS_SVC */%'
And sql_text Not Like '%/* OPT_DYN_SAMP */%';