Oracle 11g 中SQL性能优化新特性之SQL性能分析器（SQLPA）

 

Oracle11g中，真实应用测试选项（the Real Application Testing Option）提供了一个有用的特点，叫SQL性能分析器（SQL Performance Analyzer,SQLPA，SPA）。系统包DBMS_SQLPA让你可以注册和比较SQL调优集（STS）中的SQL语句执行的统计信息。通过SQL性能分析器，可比较数据库变化前后SQL语句的执行情况。下面将通过简单例子说明该Oracle 11g 新特点。

1.  创建样例表和数据

为了稍后的说明，首先，需创建一张表和往表中加一些数据，脚本如下：

登录后复制

create table test
  (idnumber not null);
 
begin
for i in 1..10000 loop
insert into test(id)
  values (i);
end loop;
commit;
end;
/

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.

2.  运行SQL语句并获取执行计划

登录SQL*Plus并运行SQL语句：

登录后复制

set serveroutput off
col id format 99999
select a.id, b.id
  from testa, test b
 wherea.id=b.id
   andb.id=500;
 
select * from table(
  dbms_xplan.display_cursor);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

其执行计划如下，记住其SQL_ID并将会添加到后面部分SQL调优集中的查询语句中：

登录后复制

-------------------------------------
SQL_ID 683kdkrs2dmrk, child number 0
-------------------------------------
select a.id, b.id   from test a, test b  where a.id=b.id
and b.id=500
 
Plan hash value: 2625395012
----------------------------------------------------------------
| Id  |Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 |SELECT STATEMENT   |      |      |       |    15 (100)|
|*  1|  HASH JOIN         |     |     1 |    26 |   15   (7)|
|*  2|   TABLE ACCESS FULL| TEST |     1 |   13 |     7   (0)|
|*  3|   TABLE ACCESS FULL| TEST |     1 |   13 |     7   (0)|
----------------------------------------------------------------
 
Predicate Information (identified byoperation id):
---------------------------------------------------
1 -access("A"."ID"="B"."ID")
2 - filter("A"."ID"=500)
3 - filter("B"."ID"=500)
 
Note
-----
- dynamic sampling used for this statement

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.

3. 捕获SQL调优集查询

一旦SQL语句位于共享池中，就可以创建一个新的SQL调优集，并向其中增加SQL语句：

登录后复制

1)   创建一个STS:
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
   sqlset_name => 'test_sts',
   description => 'STS for SPA demo');
END;
/
2)   通过查询游标缓冲向STS中增加查询：
DECLARE
 l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPENl_cursor FOR
     SELECT VALUE(p)
       FROM   TABLE (
          DBMS_SQLTUNE.select_cursor_cache(
            'sql_id = ''&sql_id''', -- basic_filter
            NULL, -- object_filter
            NULL, -- ranking_measure1
            NULL, -- ranking_measure2
            NULL, -- ranking_measure3
            NULL, -- result_percentage
            1)    -- result_limit
       ) p;
 DBMS_SQLTUNE.load_sqlset (
     sqlset_name    => 'test_sts',
     populate_cursor => l_cursor);
END;
/
accept sql_id prompt "Enter value forsql_id: "
683kdkrs2dmrk
PL/SQL procedure successfully completed.
3)   可查询STS内容以确定该SQL已被正确注册：
col sql format a50
set lines 120
SELECT sql_id,
      substr(sql_text, 1, 50) sql
  FROMTABLE(
     DBMS_SQLTUNE.select_sqlset (
       'test_sts'));
SQL_ID       SQL
------------- ---------------------
683kdkrs2dmrk select a.id, b.id
                from test a, test b
where a.i

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.
• 39.
• 40.
• 41.
• 42.
• 43.
• 44.

3.  改变数据库前，生成和存储该SQL语句执行统计信息

该步骤可能会花费一些时间，因为要运行STS查询和存储其执行统计信息。按顺序执行如下步骤：

1)  创建一个参考STS的SQLPA分析任务

登录后复制

var v_out char(50)
begin
 :v_out:=dbms_sqlpa.create_analysis_task(
              sqlset_name => 'test_sts',
             task_name   => 'test_spa_task');
end;
/
print v_out
 
V_OUT
-------------
test_spa_task
2)  检查该任务是否已创建：
col TASK_NAME format a14
col ADVISOR_NAME format a24
select TASK_NAME,
      ADVISOR_NAME,
      created
  fromDBA_ADVISOR_TASKS
 wheretask_name='test_spa_task';
 
TASK_NAME     ADVISOR_NAME             CREATED
-------------- ---------------------------------
test_spa_task SQL Performance Analyzer 15-AUG-07
3)  运行该SQLPA分析任务：
begin
 DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
       task_name      => 'test_spa_task',
      execution_type => 'TEST EXECUTE',
      execution_name => 'test_spa_task_before');
end;
/
4)  监视该任务及其状态，直到任务完成：
col TASK_NAME format a20
select execution_name,
      status,
       execution_end
  fromDBA_ADVISOR_EXECUTIONS
 wheretask_name='test_spa_task'
 orderby execution_end;
 
EXECUTION_NAME                 STATUS      EXECUTION
------------------------------ --------------------
test_spa_task_before           COMPLETED   15-AUG-07
5)  改变数据库
在表TEST上创建一个索引：
create unique index test_idx on test(id);
Index created.
6)  改变数据库后，运行该SQLPA分析任务
该脚本和前面类似。只是需改变名字以区别数据库改变前后的执行统计信息：
Ø 运行SQLPA分析任务：
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
      task_name      => 'test_spa_task',
      execution_type => 'TEST EXECUTE',
      execution_name => 'test_spa_task_after');
end;
/
Ø 监视该任务及其状态,直到其完成：
col TASK_NAME format a20
select execution_name,
      status,
      execution_end
  fromDBA_ADVISOR_EXECUTIONS
 wheretask_name='test_spa_task'
 orderby execution_end;
 
EXECUTION_NAME                 STATUS      EXECUTION
------------------------------ --------------------
test_spa_task_before           COMPLETED   15-AUG-07
test_spa_task_after            COMPLETED   15-AUG-07

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.
• 39.
• 40.
• 41.
• 42.
• 43.
• 44.
• 45.
• 46.
• 47.
• 48.
• 49.
• 50.
• 51.
• 52.
• 53.
• 54.
• 55.
• 56.
• 57.
• 58.
• 59.
• 60.
• 61.
• 62.
• 63.
• 64.
• 65.
• 66.
• 67.
• 68.
• 69.
• 70.
• 71.

7. 比较因数据库改变而发生的执行改变

需再次运行分析任务,这次分析器将比较和存储比较结果：

登录后复制

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
   task_name        => 'test_spa_task',
  execution_type   => 'COMPAREPERFORMANCE',
  execution_name   => 'test_spa_task_compare',
  execution_params => dbms_advisor.arglist(
        'comparison_metric',
        'buffer_gets'));
end;
/
PL/SQL procedure successfully completed.
一旦完成，就可如下这样用REPORT_ANALISIS_TASK打印分析报告：
variable rep CLOB;
begin
  :rep:= DBMS_SQLPA.REPORT_ANALYSIS_TASK(
             task_name=>'test_spa_task',
              type=>'HTML',
             level=>'ALL',
             section=>'ALL');
end;
/
 
SET LONG 100000
set LONGCHUNKSIZE 100000
set LINESIZE 200
set head off
set feedback off
set echo off
spool sts_changes.html
PRINT :rep
spool off
set head on

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.

建议SECTION参数置为SUMMARY或ALL，而不非DBMS_SQLPA文档中说的置为SECTION_ALL那样。

可通过SQL*Plus(文本输出)或浏览器（HTML输出）浏览生成的报告结果：

8. 另一个显示比较结果的方法：

除了打印报告，还可从下面视图中查询报告：

Ø DBA_ADVISOR_FINDINGS

Ø DBA_ADVISOR_SQLPLANS

Ø DBA_ADVISOR_SQLSTATS

9. 清除执行统计信息、分析任务及相关表

1)   重置任务结果：

登录后复制

begin
  dbms_sqlpa.reset_analysis_task(task_name=>'test_spa_task');
end;
/
col TASK_NAME format a20
select execution_name,
      status,
      execution_end
  fromDBA_ADVISOR_EXECUTIONS
 wheretask_name='test_spa_task'
 orderby execution_end;
 
no rows selected
2)   清除任务本身：
begin
 dbms_sqlpa.drop_analysis_task(task_name=>'test_spa_task');
end;
/
 
col TASK_NAME format a14
col ADVISOR_NAME format a24
select TASK_NAME,
      ADVISOR_NAME,
      created
  fromDBA_ADVISOR_TASKS
 wheretask_name='test_spa_task';
 
no rows selected

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.

3)   此外，还可以：

Ø  用过程cancel_analysis_task 取消正运行的分析任务。

Ø  用过程interrupt_analysis_task暂停运行的分析任务。

Ø  用过程resume_analysis_task恢复被暂停的分析任务。

Ø  清除相关表和索引：

drop table gark cascade constraints purge;

10. 总结

针对SQLPA做如下总结：

Ø 可轻松从AWR中捕获STS中SQL语句。

Ø 不必重建测试系统（仅INSERT/UPDATE/DELETE语句的SELECT部分被执行)。

Ø STS中SQL语句仅是真实应用负载的一个样例。

具体请参考：

Ø Oracle 11gPerformance Tuning Guide – 23.SQL Performance Analyzer

Ø Oracle 11g PL/SQLTypes and Packages Reference – DBMS_SQLPA

Oracle11g Reference

 

原创作者: lhdzbj 转载于: https://blog.51cto.com/lhdzbj/11542040