ORACLESQLPerformanceAnalyzer的使用

通过 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;
/

/*********有两个参数值得特别说明：

1）select_cursor_cache的第一个参数是basic_filter ，它可以取的值有：

sql_id varchar(13),
force_matching_signature number,
sql_text clob,
object_list sql_objects,
bind_data raw(2000),
parsing_schema_name varchar2(30),
module varchar2(48),
action varchar2(32),
elapsed_time number,
cpu_time number,
buffer_gets number,
disk_reads number,
direct_writes number,
rows_processed number,
fetches number,
executions number,
end_of_fetch_count number,
optimizer_cost number,
optimizer_env raw(1000),
priority number,
command_type number,
first_load_time varchar2(19),
stat_period number,
active_stat_period number,
other clob,
plan_hash_value number,
sql_plan sql_plan_table_type,
bind_list sql_binds

2）select_cursor_cache的最后一个参数是attribute_list

basic (default) -all attributes (such as execution statistics and binds) are returned except the plans the execution context is always part of the result.

typical - basic + sql plan (without row source statistics) and without object reference list

all - return all attributes

comma separated list of attribute names this allows to return only a subset of sql attributes: execution_statistics, bind_list, object_list, sql_plan,sql_plan_statistics: similar to sql_plan + row source statistics

*********/

---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;
/
/**********语法

syntax

sql text format. this form of the function is called to prepare the analysis of a single statement given its text.

dbms_sqlpa.create_analysis_task(
sql_text in clob,
bind_list in sql_binds := null,
parsing_schema in varchar2 := null,
task_name in varchar2 := null,
description in varchar2 := null)
return varchar2;
sql id format. this form of the function is called to prepare the analysis of a single statement from the cursor cache given its identifier.

dbms_sqlpa.create_analysis_task(
sql_id in varchar2,
plan_hash_value in number := null,
task_name in varchar2 := null,
description in varchar2 := null)
return varchar2;
workload repository format. this form of the function is called to prepare the analysis of a single statement from the workload repository given a range of snapshot identifiers.

dbms_sqlpa.create_analysis_task(
begin_snap in number,
end_snap in number,
sql_id in varchar2,
plan_hash_value in number := null,
task_name in varchar2 := null,
description in varchar2 := null)
return varchar2;

sqlset format. this form of the function is called to prepare the analysis of a sql tuning set.

dbms_sqlpa.create_analysis_task(
sqlset_name in varchar2,
basic_filter in varchar2 := null,
order_by in varchar2 := null,
top_sql in varchar2 := null,
task_name in varchar2 := null,
description in varchar2 := null
sqlset_owner in varchar2 := null)
return varchar2;

**********/

---9.执行spa

begin
dbms_sqlpa.execute_analysis_task
(
task_name => 'spa01',
execution_type => 'test execute',
execution_name => 'before_change'
);
end;
/ 

/*********语法

dbms_sqlpa.execute_analysis_task(
task_name in varchar2,
execution_type in varchar2 := 'test execute',
execution_name in varchar2 := null,
execution_params in dbms_advisor.arglist := null,
execution_desc in varchar2 := null)
return varchar2;

dbms_sqlpa.execute_analysis_task(
task_name in varchar2,
execution_type in varchar2 := 'test execute',
execution_name in varchar2 := null,
execution_params in dbms_advisor.arglist := null,
execution_desc in varchar2 := null);

*********/

---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;