oracle11g查询优化器,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;

/

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

其执行计划如下，记住其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

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

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

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

建议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

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