實戰:ORACLE SQL Performance Analyzer

通過 SPA，您能夠依據各種更改類型（如初始化參數更改、優化器統計重新整理和資料庫更新）播放特定的

SQL 或整個 SQL

負載，然後生成比較報告，幫助您評估它們的影響.

在 Oracle Database 11g 之前的版本号中，我必須捕獲全部 SQL 語句，通過跟蹤執行這些語句，

然後得到運作計劃 —

這是一項極其耗時又極易出錯的任務。新版本号中，我們不須要再那樣做了，

我改用很easy而有效的 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;

  DBMS_SQLTUNE.CREATE_SQLSET(

=> ‘OCPYANG_STS‘,

    sqlset_owner => ‘SYS‘,

    description  => ‘ocpyangtest‘);

---6.載入sql優化集

set serveroutput on

DECLARE

cur01 dbms_sqltune.sqlset_cursor;

open cur01 for 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;

/*********有兩個參數值得特别說明：

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                

buffer_gets             

disk_reads              

direct_writes           

rows_processed          

fetches                 

executions              

  end_of_fetch_count      

optimizer_cost          

optimizer_env           

RAW(1000),

priority                

command_type            

first_load_time         

VARCHAR2(19),

stat_period             

  active_stat_period      

other                   

plan_hash_value         

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(

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.

sql_id           IN

VARCHAR2,

  plan_hash_value  IN NUMBER    :=

  task_name        IN

VARCHAR2  := NULL,

  description      IN

VARCHAR2  := NULL)

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.

begin_snap       IN NUMBER,

end_snap         IN NUMBER,

SQLSET format. This form of the function is called to prepare the

analysis of a SQL tuning set.

sqlset_name       IN VARCHAR2,

basic_filter      IN VARCHAR2 :=  NULL,

order_by          IN VARCHAR2

:=  NULL,

top_sql           IN VARCHAR2

task_name         IN VARCHAR2 := 

  description       IN VARCHAR2

:=  NULL

  sqlset_owner      IN VARCHAR2

:=  NULL)

**********/

---9.運作SPA

dbms_sqlpa.execute_analysis_task

task_name =>

‘SPA01‘,

execution_type => ‘test execute‘,

execution_name =>

‘before_change‘

/*********文法

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(

task_name         IN VARCHAR2,

   execution_type    IN

VARCHAR2              

:= ‘test execute‘,

   execution_name    IN

:= NULL,

   execution_params  IN

dbms_advisor.argList   := NULL,

execution_desc    IN

:= NULL)

  RETURN VARCHAR2;

:= NULL);

---10.改變

create index index_01 on t1(sid,sname)

---11.改變後運作

‘after_change‘

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.運作任務比較

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

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

select DBMS_SQLPA.REPORT_ANALYSIS_TASK(‘SPA01‘) from dual;

spool off;