使用SPA（sql performance Analyzer）

来源：互联网发布：教师资格考试知乎编辑：程序博客网时间：2024/05/29 07:37

SPA比较在数据库更新前后定义在特定sql优化集中的特定sql语句的性能，重大升级，参数改变，索引或是统计信息的改变。

1.建立测试环境

create table t1 as
select * from dba_objects;
insert into t1 select * from t1;
commit;

在t1上没有任何索引，收集其统计信息：

exec dbms_stats.gather_table_stats(ownname => 'scott',tabname =>'T1',cascade=>true);

查看dba_tables表

select table_name,num_rows from dba_tables where table_name='T1';

TABLE_NAME                       NUM_ROWS
------------------------------           ----------
T1                                           145164

统计信息收集完毕。接着清空共享池，接受新的负载（生产库慎用）：

alter system flush shared_pool;

2.执行查询

select count(*) from t1 where object_id=100;
select count(*) from t1 where object_id<100;
select count(*) from t1 where object_id=1000;
select count(*) from t1 where object_id<=1000;

稍后会在object_id列上建立一个索引，并对比建立索引前后sql语句的性能。

3.创建sql优化集

exec dbms_sqltune.create_sqlset(sqlset_name=>'sql_replay_test');

4.加载sql优化集

下面的语句是从cursor_cache中寻找t1表，并将包含该表的sql语句装入sql优化集：

declare
l_cursor dbms_sqltune.sqlset_cursor;
begin
open l_cursor for
    select value(a)
      from table(dbms_sqltune.select_cursor_cache(basic_filter   => 'sql_text like ''%t1%'' and parsing_schema_name=''SCOTT''',
                                                  attribute_list => 'ALL')) a;
dbms_sqltune.load_sqlset(sqlset_name     => 'sql_replay_test',
                           populate_cursor => l_cursor);
end;

5.查询sql优化集

select sql_text
from dba_sqlset_statements
where sqlset_name='sql_replay_test';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT VALUE(A) FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(BASIC_FILTER => 'sql
select count(*) from t1 where object_id<=1000
select count(*) from t1 where object_id<100
SELECT /*+ first_rows(1) */ sql_id, force_matching_signature, sql_text, cast(NU
select count(*) from t1 where object_id=100
select count(*) from t1 where object_id=1000

6.输出sql优化集

variable v_task varchar2(64);

exec :v_task:=dbms_sqlpa.create_analysis_task(sqlset_name=>'sql_replay_test');

print :v_task

v_task
---------
TASK_281

7.在分析之前执行

在数据库改动之前执行优化集的内容，收集性能信息：

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

8.做出必要的改动

在t1表的object_id列加一个索引，来提高性能，并重新收集统计信息。

create index t1_index_id on t1(object_id);

exec dbms_stats.gather_table_stats(ownname => 'scott',tabname =>'T1',cascade=>true);

9.创建索引后执行分析任务

print :v_task

v_task
---------
TASK_281

begin
dbms_sqlpa.execute_analysis_task(task_name      => :v_task,
                                   execution_type => 'test execute',
                                   execution_name => 'after_change');
end;

10.执行分析任务比较

print :v_task

v_task
---------
TASK_281

begin
dbms_sqlpa.execute_analysis_task(task_name        => :v_task,
                                   execution_type   => 'compare performance',
                                   execution_params => dbms_advisor.argList('execution_name1',
                                                                            'before_change',
                                                                            'execution_name2',
                                                                            'after_change'));
end;

11.输出最终分析结果

select dbms_sqlpa.report_analysis_task(:v_task) from dual;

General Information
--------------------------------------------------------------------------------

Task Information:                              Workload Information:
--------------------------------------------- --------------------------------
Task Name    : TASK_281                        SQL Tuning Set Name        : sq
Task Owner   : SCOTT                           SQL Tuning Set Owner       : SC
Description :                                 Total SQL Statement Count : 6

Execution Information:
--------------------------------------------------------------------------------
Execution Name : EXEC_264               Started             : 04/20/2016 08:0
Execution Type : COMPARE PERFORMANCE    Last Updated        : 04/20/2016 08:0
Description     :                        Global Time Limit   : UNLIMITED
Scope           : COMPREHENSIVE          Per-SQL Time Limit : UNUSED
Status          : COMPLETED              Number of Errors    : 0

Analysis Information:
--------------------------------------------------------------------------------
Before Change Execution:                       After Change Execution:
--------------------------------------------- --------------------------------
Execution Name      : before_change            Execution Name      : after_cha
Execution Type      : TEST EXECUTE             Execution Type      : TEST EXEC
Scope               : COMPREHENSIVE            Scope               : COMPREHEN
Status              : COMPLETED                Status              : COMPLETED
Started             : 04/20/2016 07:53:20      Started             : 04/20/201
Last Updated        : 04/20/2016 07:53:22      Last Updated        : 04/20/201
Global Time Limit   : UNLIMITED                Global Time Limit   : UNLIMITED
Per-SQL Time Limit : UNUSED                   Per-SQL Time Limit : UNUSED
Number of Errors    : 0                        Number of Errors    : 0

---------------------------------------------
Comparison Metric: ELAPSED_TIME
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------

Report Summary
--------------------------------------------------------------------------------

Projected Workload Change Impact:
-------------------------------------------
Overall Impact      : 17.33%
Improvement Impact : 42.05%
Regression Impact   : -24.72%

SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
Overall               6                  4
Improved              4                  4
Regressed             2                  0

Top 6 SQL Sorted by Absolute Value of Change Impact on the Workload
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
|           |               | Impact on | Execution | Metric | Metric | Impact
| object_id | sql_id        | Workload | Frequency | Before | After | on SQL
--------------------------------------------------------------------------------
|        16 | 04uqmkx8ra2p6 |   -15.84% |         1 | 18185 | 27729 | -52.48%
|        18 | 7p08u8zjtkayn |    11.76% |         1 |   7118 |     33 | 99.54%
|        17 | 2575930rsg6qb |     11.2% |         1 |   6893 |    149 | 97.84%
|        20 | 8gj6r733n51jn |     9.71% |         1 |   5875 |     27 | 99.54%
|        21 | csrkbp9cvp4g4 |     9.38% |         1 |   5683 |     31 | 99.45%
|        19 | 84fx5r1d8n00p |    -8.88% |         1 | 16484 | 21831 | -32.44%
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

v_task
---------
TASK_281

这里的总体影响是17.33%，提高了。

1 0