通过 SPA，您可以根据各种更改类型（如初始化参数更改、优化器统计刷新和数据库升级）播放特定的
SQL 或整个 SQL 负载，然后生成比较报告，帮助您评估它们的影响.
在 Oracle Database 11g 之前的版本中，我必须捕获所有 SQL 语句，通过跟踪运行这些语句，
然后得到执行计划 — 这是一项极其耗时又极易出错的任务。有了新版本之后，我不需要再那样做了，
我改用非常简单而有效的 SQL Performance Analyzer。
---使用场景
1.数据库升级
2.实施优化建议
3.更改方案
4.收集统计信息
5.更改数据库参数
6.更改操作系统和硬件
create tablespace test
datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\test01.DBF'
size 5000m
autoextend on
next 100m maxsize unlimited
extent management local autoallocate
segment space management auto;
create table t1
(
sid int not null ,
sname varchar2(10)
)
tablespace test;
-2.-循环导入数据
declare
maxrecords constant int:=1000000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t1 values(i,'ocpyang');
end loop;
dbms_output.put_line(' 成功录入数据！ ');
commit;
end;
/
update t1 set sname='苏州' where sid=500001;
update t1 set sname='南京' where sid=600001;
---3.收集统计信息
exec dbms_stats.gather_table_stats(USER,'T1',CASCADE=>TRUE)
alter system flush shared_pool;
---4.执行查询
select count(*) from t1 where sid<=100;
select count(*) from t1 where sid<=500;
select count(*) from t1 where sid>50000;
---5.新建STS
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'OCPYANG_STS'
);
END;
/
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'OCPYANG_STS',
sqlset_owner => 'SYS',
description => 'ocpyangtest');
END;
/
---6.加载sql优化集
set serveroutput on
DECLARE
cur01 dbms_sqltune.sqlset_cursor;
BEGIN
open cur01 for select value(a) from table(dbms_sqltune.select_cursor_cache
(
basic_filter => 'sql_text like ''%t1%'' and parsing_schema_name =''SYS''',
attribute_list => 'ALL'
)
) a;
dbms_sqltune.load_sqlset(
sqlset_name => 'OCPYANG_STS',
populate_cursor => cur01);
close cur01;
END;
/
---7.查询sql优化集
select sql_id,sql_text from dba_sqlset_statements
where sqlset_name='OCPYANG_STS' and sql_text like '% from t1%';
---8.新建SPA
var v_task varchar2(64);
begin
:v_task:=dbms_sqlpa.create_analysis_task(
sqlset_name => 'OCPYANG_STS',
task_name => 'SPA01'
);
end;
/
---9.执行SPA
begin
dbms_sqlpa.execute_analysis_task
(
task_name => 'SPA01',
execution_type => 'test execute',
execution_name => 'before_change'
);
end;
/
---10.改变
create index index_01 on t1(sid,sname)
tablespace test;
exec dbms_stats.gather_table_stats(USER,'T1',CASCADE=>TRUE)
---11.改变后执行
begin
dbms_sqlpa.execute_analysis_task
(
task_name => 'SPA01',
execution_type => 'test execute',
execution_name => 'after_change'
);
end;
/
col TASK_NAME format a30
col EXECUTION_NAME for a30
select execution_name,
status,
execution_end
from DBA_ADVISOR_EXECUTIONS
where task_name='SPA01'
order by execution_end
/
EXECUTION_NAME STATUS EXECUTION_END
------------------------------ ----------- -------------------
before_change COMPLETED 2014-05-28 15:43:58
after_change COMPLETED 2014-05-28 15:44:58
---12.执行任务比较
begin
dbms_sqlpa.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA01',
execution_type => 'compare performance',
execution_params => dbms_advisor.arglist(
'execution_name1',
'before_change',
'execution_name2',
'after_change'));
end;
/
---13.生产报告
set serveroutput on size 999999
set long 100000000
set pagesize 0
set linesize 200
set longchunksize 200
set trimspool on
spool e:\report.txt
select DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA01') from dual;
spool off;
