修改oracle open_cur,【学习笔记】oracle 11g新特性SQL Performance Analyzer使用案例教程...

天萃荷净

oracle研究中心学习笔记：分享一篇关于oracle 11g新特性的使用文章，详细介绍了oracle SQL Performance Analyzer优化器的使用案例教程。

通过 SPA，您可以根据各种更改类型(如初始化参数更改、优化器统计刷新和数据库升级)播放特定的SQL 或整个 SQL 负载，然后生成比较报告，帮助您评估它们的影响.

在 Oracle Database 11g 之前的版本中，我必须捕获所有 SQL 语句，通过跟踪运行这些语句，然后得到执行计划 — 这是一项极其耗时又极易出错的任务。新版本中，我们不需要再那样做了，我改用非常简单而有效的 SQL Performance Analyzer。

---使用场景

1.数据库升级

2.实施优化建议

3.更改方案

4.收集统计信息

5.更改数据库参数

6.更改操作系统和硬件

create tablespace test

datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\test01.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 cur01for 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,

task_name        IN VARCHAR2  := 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,

plan_hash_value  IN NUMBER    := NULL,

task_name        IN VARCHAR2  := NULL,

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,

task_name         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_type    IN VARCHAR2               := 'test execute',

execution_name    IN VARCHAR2               := NULL,

execution_params  IN dbms_advisor.argList   := NULL,

execution_desc    IN VARCHAR2               := NULL);

*********/

---10.改变

create index index_01 on t1(sid,sname)

tablespace test;

exec dbms_stats.gather_table_stats(USER,'T1',CASCADE=>TRUE)

---11.改变后执行

begin

dbms_sqlpa.execute_analysis_task

(

task_name => 'SPA01',

execution_type => 'test execute',

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

selectDBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA01') from dual;

spool off;

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】oracle 11g新特性SQL Performance Analyzer使用案例教程