转自:http://www.52php.cn/article/p-oktzelyb-mv.html
通过 SPA,您可以根据各种更改类型(如初始化参数更改、优化器统计刷新和数据库升级)播放特定的 SQL 或整个 SQL 负载,然后生成比较报告,帮助您评估它们的影响.
在 Oracle Database 11g 之前的版本中,我必须捕获所有 SQL 语句,通过跟踪运行这些语句, 然后得到执行计划 — 这是一项极其耗时又极易出错的任务。新版本中,我们不需要再那样做了, 我改用非常简单而有效的 SQL Performance Analyzer。
---使用场景
1.数据库升级 2.实施优化建议 3.更改方案 4.收集统计信息 5.更改数据库参数 6.更改操作系统和硬件
create tablespace test datafile 'E:APPADMINISTRATORORADATAORCLtest01.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, 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, 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, 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_desc IN VARCHAR2 := NULL);
*********/
---10.改变
create index index_01 on t1(sid,sname) tablespace test;
exec dbms_stats.gather_table_stats(USER,CASCADE=>TRUE)
---11.改变后执行
begin dbms_sqlpa.execute_analysis_task ( task_name => 'SPA01', 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; (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|