SPA（SQL Performance Analyze）を使用した統計情報の収集

(株)エクセンコンサルティング本部/DBコンサルティングチーム

---

1. SPAとは？

SQL Performance Analyze（SPA）は、Real Application Testing（RAT）サービスのオプションの1つです。

この文書はSPAを使用し、より安定して統計情報を生成するプロセスについて説明します。

2.統計情報とSQLチューニング

チューニングは、LEADING(A) USE_NL(B) INDEX(A A(COL1)) のような簡単なヒントでほとんど解決可能です。

これが可能な理由は、Leadingのようなヒントでクエリのキー条件が何であるかを指定してくれれば、残りのプランはOracle OptimizerがQuery Transformationなどの最適化アルゴリズムを適用して最適なパフォーマンスを出すことができるようにプランを作成するからです。

チューナーA君がサイトにチューニングをサポートします。

サイトに到着したAは持っていたスクリプトで上手にTuning対象を抽出します。

収集基準は、Executionが1000回以上であり、Buffer Getsが10000 Block以上のクエリ、およそ200個のクエリが抽出されました。

A君は一瞬慌てましたが、すぐに平常心を取り戻して、着ていたワイシャツの袖ボタンを外して二度折り上げてから、していた時計を外して机の上に置きます。

時間は9時30分を過ぎている。 「うーん…今日は何個できかな……」

A君は笑顔を見せては中指でメガネを一気に上げて、すぐに炎チューニングを始めます。

そして.. Excelに1つ2つ完了したクエリが増えるたびに… 喜びを感じじます….

この記事を見てあなたはどうしますか？

A 軍の判断が間違っているのでしょうか？

間違った判断ではない最善の判断だ・・・ 11g前までは・・・

11g New FeatureであるPending StatisticsをSPAとともに使用する場合、A君の200個のチューニング対象クエリは10個内外に減らすこともできます。

 Oracle Optimizerは思ったよりスマートです。

3. SPAが利用可能かどうかを確認する

RAT OptionがFALSEの場合は使用できません。

SELECT * FROM V$OPTION

WHERE PARAMETER = ‘Real Application Testing’

PARAMETER                                 VALUE

————————————– ——–

Real Application Testing                 TRUE

4. 使用例

簡単なケースを扱います。

自分のものとして吸収し修正した上で、実務に適用してほしいと思います。

シナリオの概要

0. テストスキーマの生成とクエリの実行

1. SQL_SET 作成
2. ANALYSIS_TASK #1 作成
3. SCHEMA or TABLE Pending Statistcs 設定4. 統計情報の収集
4. Session Use Pending Statistics 設定

• ANALYSIS_TASK #2 作成
• ANALYSIS_TASK #1, #2 比較8. レポート

9. 比較結果検索スクリプト

0. テストスキーマの生成とクエリの実行

DROP TABLE EXEM_T1 PURGE;

CREATE TABLE EXEM_T1 AS

SELECT LEVEL C1, ‘A’ C2 FROM DUAL

CONNECT BY LEVEL <= 100000;

INSERT INTO EXEM_T1 VALUES(1, ‘B’);

CREATE INDEX I1_EXEM_T1 ON EXEM_T1(C2) TABLESPACE TSD_QM;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>’MAXGAUGE’

, TABNAME =>’EXEM_T1′

, NO_INVALIDATE =>FALSE

, METHOD_OPT =>’FOR ALL COLUMNS SIZE 3′

, ESTIMATE_PERCENT =>10);

— SQLの実行（A、Bの2つがあり、分布がよくない – > FTS）

SELECT COUNT(C1)

FROM EXEM_T1 WHERE C2 = :B1;

–COUNT(C1)

–100000

１．SQL_SETの生成

SELECT ID,

NAME, OWNER,

DESCRIPTION, CREATED, LAST_MODIFIED, STATEMENT_COUNT FROM DBA_SQLSET

where name = ‘OKR_TEST’;

–ID NAME          OWNER   DESCRIPTION CREATED       LAST_MODIFIED     STATEMENT_COUNT

–24 OKR_TEST MAXGAUGE                    2013-06-27 午後 1:37:31 2013-06-27 午後

1:37:43 4

— SQLSET と ANALYSIS_TASK リンクを確認するSELECT DESCRIPTION

FROM DBA_SQLSET_REFERENCES WHERE SQLSET_NAME = ‘OKR_TEST’;

–DESCRIPTION

–created by: SQL Performance Analyzer – task: OKR_SPA

— TASKが存在する場合、SETを削除する前にTASKを最初に削除する必要があり、次にSETを削除できます。

— ANALYSIS_TASK DROP

EXEC DBMS_SQLPA.DROP_ANALYSIS_TASK(‘OKR_SPA’);

— TUNING SET DROP

EXEC DBMS_SQLTUNE.DROP_SQLSET(‘OKR_TEST’);

2.ANALYSIS_TASK #1の生成

ANALYSIS_TASKは4つの方法で生成可能です。

• Sql text format

EXEC :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(

sql_text => ‘select quantity_sold from sales s, times t where s.time_id = t.time_id and s.time_id = TO_DATE(”24-NOV-00”)’);

• Sql id format (cursor cache)

EXEC :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sql_id => ‘ay1m3ssvtrh24’);

(3)  Workload repository format

exec :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( begin_snap => 1,

end_snap => 2,

sql_id => ‘ay1m3ssvtrh24’);

(4)  Sql tuning set format (first we need to load an STS, then analyze it)

EXEC :sts_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( — sqlset_name => ‘my_workload’,

— order_by => ‘BUFFER_GETS’, — description => ‘process workload ordered by buffer gets’);

–以下は、Sql tuning set formatを使用した例です。

— SQLSETの生成

EXEC DBMS_SQLTUNE.CREATE_SQLSET(‘OKR_TEST’);

–内容を埋める

–COMMAND TYPE

–2 –> insert

–3 –> select (for update)

–6 –> update

–7 –> delete

–189 –> merge

DECLARE

cur DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN

OPEN cur FOR SELECT VALUE(P)

FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE(

‘parsing_schema_name in (”MAXGAUGE”) and upper(sql_text) like ”%EXEM_T1%” AND command_type IN (2,3,6,7,189)’, NULL, NULL, NULL, NULL, 1, NULL, ‘ALL’)) P;

DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => ‘OKR_TEST’

,populate_cursor => cur); END;

—-update and delete SQL statements from an STS based on a search condition

–BEGIN

— DBMS_SQLTUNE.DELETE_SQLSET(

— sqlset_name => ‘my_sql_tuning_set’,

— basic_filter => ‘executions < 50’);

–END;

–/

—

— タスク生成の確認

select count(1) from DBA_SQLSET_STATEMENTS where sqlset_name = ‘OKR_TEST’

–count(1)

–4

— 作成 DECLARE

TNAME VARCHAR2(30); BEGIN

TNAME := DBMS_SQLPA.CREATE_ANALYSIS_TASK( SQLSET_NAME => ‘OKR_TEST’

, BASIC_FILTER => NULL

, ORDER_BY => ‘EXECUTIONS’

, TOP_SQL => 100

, TASK_NAME => ‘OKR_SPA’

, DESCRIPTION => NULL

, SQLSET_OWNER => NULL

); END;

— EXECUTE_ANALYSIS_TASK

— 最速の方法は execution_type => ‘CONVERT SQLSET’

— ANALYSIS_TASK 作成

begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(

task_name => ‘OKR_SPA’, execution_type => ‘EXECUTE’, execution_name => ‘OKR_BEFORE’); end;

— 生成結果の照会

select execution_name

,status

,execution_end

,execution_end

from USER_ADVISOR_EXECUTIONS

where task_name = ‘OKR_SPA’;

–EXECUTION_NAME STATUS   EXECUTION_END

— OKR_BEFORE COMPLETED 2013-07-02 13:04:03

3.SCHEMA or TABLE Pending Statistcsの設定

— 照会結果がTRUEの場合、Pendingがかかっていない状態です。

SELECT DBMS_STATS.GET_PREFS(‘PUBLISH’, ‘maxgauge’, ‘EXeM_T1’) PUBLISH FROM DUAL;

–PUBLISH

–TRUE

— FALSEに変更 – >運用間で統計情報生成による計画変更を防ぐための措置

Exec dbms_stats.set_schema_prefs(‘maxgauge’, pname => ‘PUBLISH’, pvalue => ‘FALSE’);

4. 統計情報の収集

— ほとんどのデータを消去し、Histogramも生成するため、インデックスを利用する

delete exem_t1 where c2 = ‘A’

— 統計情報の再生成

exec dbms_stats.gather_table_stats(ownname =>’MAXGAUGE’

, tabname =>’EXEM_T1′

, no_invalidate =>FALSE

, method_opt =>’for all columns size 3′

, estimate_percent=>10);

— Pending 統計の照会

SELECT * FROM DBA_TAB_PENDING_STATS WHERE TABLE_NAME = ‘EXEM_T1’

— しかし、統計をPendingしたので、まだFTSプラン

select count(c1)

from exem_t1 where c2 = :b1;

–Pending 状態で収集した最新の統計情報を使用してSPAを実行する

5. Session Use Pending Statisticsの設定

alter session set optimizer_use_pending_statistics = TRUE;

— しかし、統計をPendingしたので、まだFTSプラン

select count(c1)

from exem_t1 where c2 = :b1;

6. ANALYSIS_TASK #2の生成

— OKR_AFTER名でTASKを作成する. begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(

task_name => ‘OKR_SPA’, execution_type => ‘EXECUTE’, execution_name => ‘OKR_AFTER’);

新しいセクション2ページ5

execution_name => ‘OKR_AFTER’); end;

7. ANALYSIS_TASK＃1、＃2の比較

begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(

task_name => ‘OKR_SPA’,

execution_type => ‘COMPARE PERFORMANCE’, execution_name => ‘OKR_COMP’, execution_params => dbms_advisor.arglist( ‘execution_name1’,

‘OKR_BEFORE’,

‘execution_name2’, ‘OKR_AFTER’

)

);

end;

8. レポート

set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off spool report.html

SELECT dbms_sqlpa.report_analysis_task(‘OKR_SPA’, ‘HTML’, ‘ALL’,’ALL’) FROM dual; spool off

–Click here to see the file report.html in html formart

;

9. 比較結果検索スクリプト

with TMP as (

select /*+ materialize */

b.sql_id

,b.plan_hash_value b_ph

,a.plan_hash_value a_ph

,case when (b.buffer_gets-a.buffer_gets) >= 0 then ‘改善’

when (b.buffer_gets-a.buffer_gets) < 0 then ‘低下’

end “性能”

,b.optimizer_cost b_cost

,a.optimizer_cost a_cost

,b.executions b_exec

,a.executions a_exec

,b.rows_processed b_rows

,a.rows_processed a_rows

,b.elapsed_time B_ET

,a.elapsed_time A_ET

,b.buffer_gets B_BG

,a.buffer_gets A_BG

,b.disk_reads b_dr

,a.disk_reads a_dr

,round(100- (decode(a.buffer_gets,0,1,a.buffer_gets)/decode(b.buffer_gets,0,1,b.buffer_gets))*100,

1. bg_rate

,round(100- (decode(a.elapsed_time,0,1,a.elapsed_time)/decode(b.elapsed_time,0,1,b.elapsed_time))* 100,1) et_rate

from dba_advisor_sqlstats b

,dba_advisor_sqlstats a where 1=1

and b.execution_name = :before and a.execution_name = :after and a.sql_id = b.sql_id

and a.plan_hash_value <> b.plan_hash_value

)

select /*+ leading(a) use_nl(r) */ hash_value hv

,a.*

,parsing_schema_name pars_nm

,sql_profile Pf

,module

,executions R_EXEC

,round(BUFFER_GETS/decode(executions,0,1,executions),0) R_BG

,round(DISK_READS/decode(executions,0,1,executions),1) R_DR

,round(ROWS_PROCESSED/decode(executions,0,1,executions),0) R_ROWS

,round(ELAPSED_TIME/decode(executions,0,1,executions)/1000000,1) R_ET

,round(CPU_TIME/decode(executions,0,1,executions)/1000000,1) R_CT

,last_active_time last_t

,first_load_time first_t

,sql_fulltext text from tmp a

,v$sqlarea r

where a.sql_id = r.sql_id

5. 終わりに

本文書の内容はプログラミングでいうところの「Hello World」です。

ぜひ内容を熟知してスクリプトやエクセルマクロなどで自動化して業務に適用していただきたい。

理論は絶対経験に勝つことはできないのです。

---