Oracle SPA介绍

Posted on by

Sql Performance Analyzer (SPA)
这是oracle 11g的新特性 可对一部分sql结果进行性能分析 适用于数据升级 迁移做前后的比较
分析的结果会永远存在的数据库内部。和数据库重演不同的是spa只能分析sql语句且只有查询语句
步骤如下：
1.在生产库捕获具体语句
2.把捕获的语句打包传输到测试环境
3.创建SPA任务在测试环境
4.生成改变之前的执行信息
5.做改变（升级或者打补丁或者改参数或者等等。。）
6.生成改变之后的执行信息
7.对于两份信息（可生成报告文件）

CREATE USER ucjmh IDENTIFIED BY ucjmh;
GRANT DBA TO ucjmh;
conn ucjmh/ucjmh –创建一个用户
–建几张表
CREATE TABLE uc_objects AS SELECT * FROM dba_objects;
CREATE TABLE uc_tables AS SELECT * FROM dba_tables;
CREATE TABLE uc_users AS SELECT * FROM dba_users;
–做一些查询
select /*UCJMH*/ * from uc_objects;
select /*UCJMH*/ * from uc_tables;
select /*UCJMH*/ * from uc_users;
–生成一个sqlset
begin
sys.dbms_sqltune.create_sqlset(
sqlset_name => ‘uc_sts’,
sqlset_owner => ‘UCJMH’);
end;
/

PL/SQL procedure successfully completed.

–查看一下是否生成成功
SQL> select name from user_sqlset;

NAME
——————————
uc_sts

–把需要的sql放到这个sqlset里
declare
stscur dbms_sqltune.sqlset_cursor;
begin
open stscur for
select value(P) from table(
dbms_sqltune.select_cursor_cache(
‘sql_text like ”select /*UCJMH*/%”’,
null,null,null,null,null,null,’ALL’)) P;
dbms_sqltune.load_sqlset(
sqlset_name =>’uc_sts’, –这里是区分大小写的 如果你上面写的小写 不要想当然认为ORACLE会内部转换成大写
populate_cursor => stscur,
sqlset_owner =>’UCJMH’);
end;

PL/SQL procedure successfully completed

SQL> select sql_text from user_sqlset_statements where sqlset_name=’uc_sts’;

SQL_TEXT
——————————————————————————–
select /*UCJMH*/ * from uc_objects
select /*UCJMH*/ * from uc_users
select /*UCJMH*/ * from uc_tables

–或者
SQL> select sql_Text from table(dbms_sqltune.select_sqlset(‘uc_sts’));

SQL_TEXT
——————————————————————————–
select /*UCJMH*/ * from uc_objects
select /*UCJMH*/ * from uc_users
select /*UCJMH*/ * from uc_tables

创建暂存表保存STS：
exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(‘UC_STS_TAB’,’UCJMH’);

SQL> select count(*) from uc_sts_tab;

COUNT(*)
———-
0
–上面的步骤只是创建了一个用来暂存set的表 但是并没有真的已经把sql放进去
–把STS加载到暂存表里：
exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET(‘uc_sts’,’UCJMH’,’UC_STS_TAB’,’UCJMH’);

PL/SQL procedure successfully completed.

SQL> select count(*) from uc_sts_tab;

COUNT(*)
———-
6

–不是应该是3条sql吗 问什么是6行记录呢？仔细看看可以发现每个sql_id占两行 一行是sql 一行是统计信息和执行记划等信息

把暂存表导出生产环境 导入测试环境（我这里测试环境没有东西 我直接把整个用户导过去 如果你们其它表已经存在了可以只导一张暂存表）

create or replace directory dmp as ‘/home/oracle/dmp’;
EXIT
expdp ucjmh/ucjmh directory=dmp dumpfile=uc.dmp
export ORACLE_SID=emrep
sqlplus / AS SYSDBA
create or replace directory dmp as ‘/home/oracle/dmp’;
EXIT
impdp system/oracle directory=dmp dumpfile=uc.dmp

把STS从暂存表里放入数据字典里：
exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(‘uc_sts’,’UCJMH’,TRUE,’UC_STS_TAB’,’UCJMH’); –true表示如果有的话是否替换

PL/SQL procedure successfully completed.

–创建一个spa任务
SQL> var tname varchar2(20);
SQL> exec :tname:= dbms_sqlpa.create_analysis_task(sqlset_name => ‘uc_sts’, task_name => ‘MYSPA’);

PL/SQL procedure successfully completed.

–执行任务从而构建before change data
SQL> exec dbms_sqlpa.execute_analysis_task(task_name => :tname, execution_type => ‘TEST EXECUTE’, execution_name => ‘before’);

PL/SQL procedure successfully completed.

–生成before change的报表
SQL> set long 99999
SQL> SELECT dbms_sqlpa.report_analysis_task(task_name => :tname, type=>’text’, section=>’summary’) FROM dual;

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : MYSPA
Tuning Task Owner : UCJMH
Workload Type : SQL Tuning Set
Execution Count : 1
Current Execution : before
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Completion Status : COMPLETED
Started at : 11/01/2014 19:27:01

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
Completed at : 11/01/2014 19:27:01
SQL Tuning Set (STS) Name : uc_sts
SQL Tuning Set Owner : UCJMH
Number of Statements in the STS : 3
Number of SQLs Analyzed : 3
Number of SQLs in the Report : 3

——————————————————————————-
SUMMARY SECTION
——————————————————————————-
SQL Statements Ordered by Elapsed Time

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
——————————————————————————-
Parse Elapsed CPU Buffer Optimizer
object ID SQL ID Time (s) Time (s) Time (s) Gets Cost
———- ————- ——– ——– ——– ——– ———
3 5d0v1n7gt8j2y .014787 .016058 .015664 1013 284
5 g9shuu1cxbqqz .006716 .001369 .001333 76 23
4 71tf6tnk513p5 .002394 .000084 .000111 3 3

——————————————————————————-

–做你要做的变化，比如修改参数、升级硬件、升级操作系统等等。
SQL> exec dbms_stats.gather_schema_stats(USER);

PL/SQL procedure successfully completed.
构建after change data
SQL> EXEC dbms_sqlpa.execute_analysis_task(task_name => :tname, execution_type => ‘TEST EXECUTE’, execution_name => ‘after’);

PL/SQL procedure successfully completed.

生成after change的报表
SQL> SELECT dbms_sqlpa.report_analysis_task(task_name => :tname,type=>’text’, section=>’summary’) FROM dual;

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : MYSPA
Tuning Task Owner : UCJMH
Workload Type : SQL Tuning Set
Execution Count : 2
Current Execution : after
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Completion Status : COMPLETED
Started at : 11/01/2014 19:30:29

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
Completed at : 11/01/2014 19:30:29
SQL Tuning Set (STS) Name : uc_sts
SQL Tuning Set Owner : UCJMH
Number of Statements in the STS : 3
Number of SQLs Analyzed : 3
Number of SQLs in the Report : 3

——————————————————————————-
SUMMARY SECTION
——————————————————————————-
SQL Statements Ordered by Elapsed Time

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
——————————————————————————-
Parse Elapsed CPU Buffer Optimizer
object ID SQL ID Time (s) Time (s) Time (s) Gets Cost
———- ————- ——– ——– ——– ——– ———
7 5d0v1n7gt8j2y .000147 .017201 .016886 1012 284
9 g9shuu1cxbqqz .000244 .001269 .001444 75 23
8 71tf6tnk513p5 .000423 .000064 0 2 3

——————————————————————————-

–比较before和after
EXEC dbms_sqlpa.execute_analysis_task(task_name => :tname, execution_type => ‘COMPARE PERFORMANCE’);

–生成分析报表
SQL> SELECT dbms_sqlpa.report_analysis_task(task_name => :tname, type=>’text’, section=>’summary’) FROM dual;

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : MYSPA
Tuning Task Owner : UCJMH
Workload Type : SQL Tuning Set
Execution Count : 2
Current Execution : after
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Completion Status : COMPLETED
Started at : 11/01/2014 19:30:29

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
Completed at : 11/01/2014 19:30:29
SQL Tuning Set (STS) Name : uc_sts
SQL Tuning Set Owner : UCJMH
Number of Statements in the STS : 3
Number of SQLs Analyzed : 3
Number of SQLs in the Report : 3

——————————————————————————-
SUMMARY SECTION
——————————————————————————-
SQL Statements Ordered by Elapsed Time

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
——————————————————————————-
Parse Elapsed CPU Buffer Optimizer
object ID SQL ID Time (s) Time (s) Time (s) Gets Cost
———- ————- ——– ——– ——– ——– ———
7 5d0v1n7gt8j2y .000147 .017201 .016886 1012 284
9 g9shuu1cxbqqz .000244 .001269 .001444 75 23
8 71tf6tnk513p5 .000423 .000064 0 2 3

——————————————————————————-

删除任务：
select * from DBA_SQLSET_REFERENCES where SQLSET_NAME=’uc_sts’;
exec dbms_sqltune.drop_tuning_task(‘MYSPA’);

删除暂存表和STS：
drop table hr.hsj_sts_tab purge;
exec dbms_sqltune.drop_sqlset(‘uc_sts’);

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注