DBMS_SQLTUNE.REPORT_ANALYSIS_TASK()ファンクションで生成されるCLOB型のレポートをファイルに保存する簡単な方法 Tweet

Previously on Mac De Oracle
STSとの格闘というかSPAとの格闘に疲れてきたところw ですが、
SQL Tuning Set (STS)のフィルタリング
などで、SQLSETを程よい大きさに分割するところまでたどり着きましたw

今回は、いくつかある細かい分析方法については、一旦、置いといて、
分析レポートを保存する簡単な方法についての備忘録

分析などにこの記事も参考になると思いますが、紹介している記事でも分析レポートをファイルに保存する方法はSQL*Plusのコマンドを駆使して行われています。
個人的には少々やぼったい方法かなと感じている方法なんですが、以前はこんなやりかたが多かったようにも思います。

var rep CLOB
...略...
EXEC :rep := DBMS_SQLSPA.REPORT_ANALYSIS_TASK('STSNAM','TEXT',...
spo hoge_report.txt
print :rep
spo off

SQL*PlusとPL/SQLが行ったり来たりするところや、ループ制御しにくいので、この方法は避けたい。。。。

かといって、DBMS_OUTPUT.PUT_LINEで行おうとするとset linesize等の制御が面倒くさいし
結局、SQL*Plusの機能にも依存してしまうので、いまひとつ。

PL/SQLだけで行う方法として、UTL_FILEパッケージを利用してファイル出力する方法もありますが、
UTL_FILEパッケージで提供されているAPIはlow levelなものばかりなので、急ぎの時にはめんどくさくて、やだw

もっと簡単なやつないの？？？？？

あります！w

DBMS_ADVISOR.CREATE_FILE(buffer, location, filename)

なぜ、UTL_FILEパッケージに入れてくれないんだw と思いたくなる方もいるかと思いますが、 理由はよくわかりませんw
（UTL_FILEパッケージはlow levelなAPIだけだから入れたくないのかも、かも、かも。と思ってますが、DBMS_ADVISORパッケージに入れるとは。どういうつもりだ！w）

UTL_FILE.CREATE_FILE()ってあったほうが直感的に探しやすくないかw UTL_FILEみて無い！！
となると、他を探すこともあまりなくなって、結局、コード書いてる場面を多く見てる＞＜　

---

余談

DBMS_ADVISOR.CREATE_FILEとなっているがライセンス上、diagnostic/tuning packライセンスはなくてもアクセスしてできるように読み取れるのだけと、もしかすると、この影響で、DBMS_ADVISOR.CREATE_FILEの存在は知っていても使ってないのかあるのかな？？

以下のマニュアルを読む限り、Diagnostic/Tuning Packでは、DBMS_ADVISORパッケージ全体ではなく、Diagnostic/Tuning Packに関連するパラメータを与える必要のある機能についての制限であることしか記載されていない。
つまり、DBMS_ADVISOR.CREATE_FILE()に関して言えば、Diagnostic/Tuning Pack特有の機能ではないから利用可能なはず。　←間違ってたらツッコミ希望w

Oracle® Databaseライセンス情報 12cリリース1 (12.1) 2 オプションおよびパック
https://docs.oracle.com/cd/E49329_01/license.121/b71334/options.htm

---

とりあえず、SQLSETの分析レポートをDBMS_ADVISOR.CREATE_FILE()を利用して保存するやっつけスクリプトを作ってみました
これを元に育てていけるかなw

実行例）

注）STS20171102というSQLSETを事前に作成してあります。

orcl@SYS> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE	12.2.0.1.0	Production                                                            0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0


orcl@SYS> select owner,name,statement_count,created from dba_sqlset order by name;

OWNER      NAME                           STATEMENT_COUNT CREATED
---------- ------------------------------ --------------- ---------
SPAUSER    STS20171102                               1003 02-NOV-17

spa.sqlのパラメータは、SQLSETと分析レポートの出力ディレクトリオブジェクトとSQLを実行するスキーマ向けのDBリンクです。

スクリプトの概要
・初回の分析（SQLSETで元のSQLを分析）
・２回目の分析（新環境でSQLを実行して分析)
・初回と２回目の比較レポート作成／保存
・レポートを出力後、分析に利用したタスクを削除

orcl@SYS> @spa STS20171102 rep_dir link_4_target

task name:STS20171102 has been droped.
***END***

PL/SQL procedure successfully completed.

分析レポートは、ディレクトリオブジェクトに対応づけられたパスに保存されます

orcl@SYS> !ls -l report
-rw-r--r-- 1 oracle oinstall 13688 11月  3 02:44 DIFF_STS20171102171103024433.txt

orcl@SYS> !cat report/DIFF_STS20171102171103024433.txt

General Information
---------------------------------------------------------------------------------------------

 Task Information:                              Workload Information:                         
 ---------------------------------------------  --------------------------------------------- 
  Task Name    : STS20171102                     SQL Tuning Set Name        : STS20171102     
  Task Owner   : SYS                             SQL Tuning Set Owner       : SPAUSER         
  Description  :                                 Total SQL Statement Count  : 1003            

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name             : DIFF_STS20171102       Started             : 11/03/2017 02:44:26  
  Execution Type             : COMPARE PERFORMANCE    Last Updated        : 11/03/2017 02:44:29  
  Description                :                        Global Time Limit   : UNLIMITED            
  Scope                      : COMPREHENSIVE          Per-SQL Time Limit  : UNUSED               
  Status                     : COMPLETED              Number of Errors    : 0                    
  Number of Unsupported SQL  : 4                                                                 

Analysis Information:
---------------------------------------------------------------------------------------------
 Before Change Execution:                       After Change Execution:                       
 ---------------------------------------------  --------------------------------------------- 
  Execution Name      : SOURCE_STS20171102       Execution Name      : TARGET_STS20171102     
  Execution Type      : CONVERT SQLSET           Execution Type      : TEST EXECUTE REMOTE    
  Scope               : COMPREHENSIVE            Database Link       : LINK_4_TARGET          
  Status              : COMPLETED                Scope               : COMPREHENSIVE          
  Started             : 11/03/2017 02:44:04      Status              : COMPLETED              
  Last Updated        : 11/03/2017 02:44:04      Started             : 11/03/2017 02:44:04    
  Global Time Limit   : UNLIMITED                Last Updated        : 11/03/2017 02:44:18    
  Per-SQL Time Limit  : UNUSED                   Global Time Limit   : UNLIMITED              
                                                 Per-SQL Time Limit  : UNUSED                 
                                                 Number of Errors    : 0                      
                                                                                              
 ---------------------------------------------                                                
 Comparison Metric: ELAPSED_TIME                                                              
 ------------------                                                                           
 Workload Impact Threshold: 1%                                                                
 --------------------------                                                                   
 SQL Impact Threshold: 1%                                                                     
 ----------------------                                                                       

Report Summary
---------------------------------------------------------------------------------------------

Projected Workload Change Impact:
-------------------------------------------
 Overall Impact      :  0% 
 Improvement Impact  :  0% 
 Regression Impact   :  0% 

SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count 
 Overall             999                490 
 Unchanged           995                490 
 Unsupported           4                  0 

Top 100 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
|           |               | Impact on | Execution | Metric | Metric | Impact | Plan   |
| object_id | sql_id        | Workload  | Frequency | Before | After  | on SQL | Change |
-----------------------------------------------------------------------------------------
|      1477 | 7hys3h7ysgf9m |       .1% |         1 |  14630 |     33 | 99.77% | n      |
|      1811 | cw6vxf0kbz3v1 |      .03% |         1 |   3602 |     24 | 99.33% | n      |
|      1175 | 2s9mmb6g8kbqb |      .01% |         1 |   1537 |     20 |  98.7% | n      |

...中略...

|      1691 | az25yp5qunj77 |      .01% |         1 |    882 |     20 | 97.73% | n      |
|      1896 | faun6bjrjqr17 |      .01% |         1 |    881 |     19 | 97.84% | n      |
|      1956 | g7a2tmw1nrxbh |      .01% |         1 |    881 |     19 | 97.84% | n      |
-----------------------------------------------------------------------------------------
Note: time statistics are displayed in microseconds
---------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------

スクリプトは以下のとおり、無名PL/SQLブロックにしてあります。
レポートのタイプなどは固定にしてますが、それらも含めてパラメータにしてもいいかも。

ちなみに、このエントリの本題である、DBMS_ADVISOR.CREATE_FILE()は赤字部分です！

orcl@SYS> !cat spa.sql

set serveroutput on
DECLARE
	report_clob CLOB;
	cSQLSET_OWNER VARCHAR2(30) := 'SPAUSER';
	cSQLSET_NAME VARCHAR2(20) := UPPER('&1');
	cEXECUTION_NAME_PREFIX_SOURCE VARCHAR2(7) := 'SOURCE_';
	cEXECUTION_NAME_PREFIX_TARGET VARCHAR2(7) := 'TARGET_';
	cEXECUTION_NAME_PREFIX_DIFF   VARCHAR2(5) := 'DIFF_';
	cREPORT_TYPE CONSTANT VARCHAR2(4) := 'text';
	cFILE_EXTENTION CONSTANT VARCHAR2(5) := '.txt';
	cDIRECTORY_NAME CONSTANT VARCHAR2(30) := UPPER('&2');
	cDB_LINK_4_PARSING_SCHEMA CONSTANT VARCHAR2(128) := UPPER('&3');
	stmt_task VARCHAR2(64);

	PROCEDURE drop_task (iTASK_NAME VARCHAR2)
	IS
	BEGIN
        	FOR task_rec IN (
                	SELECT
                        	task_name
                	FROM
                        	dba_advisor_tasks
                	WHERE
                        	task_name = iTASK_NAME
                        	AND advisor_name = 'SQL Performance Analyzer'
        	) LOOP
                	DBMS_OUTPUT.PUT_LINE(
				'task name:'||task_rec.task_name||' has been droped.'
			);
                	DBMS_SQLPA.DROP_ANALYSIS_TASK(
            			task_name => task_rec.task_name
                	);
        	END LOOP;
	END drop_task;
BEGIN
	--Create analysis task
	stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
		sqlset_name => cSQLSET_NAME
		,basic_filter => 'parsing_schema_name in (''SCOTT'')'
		,sqlset_owner => cSQLSET_OWNER
		,task_name => cSQLSET_NAME
	);

	--SQL Analysis on Source DB
	DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
		task_name => cSQLSET_NAME
		,execution_type => 'CONVERT SQLSET'
		,execution_name => 
			cEXECUTION_NAME_PREFIX_SOURCE
			|| cSQLSET_NAME
		,execution_params => 
			DBMS_ADVISOR.ARGLIST(
				'sqlset_name', cSQLSET_NAME
				,'sqlset_owner', cSQLSET_OWNER
		)
	);

	--SQL Analysis on Target DB
	DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
		task_name => cSQLSET_NAME
		,execution_type => 'EXECUTE'
		,execution_name => 
			cEXECUTION_NAME_PREFIX_TARGET
			|| cSQLSET_NAME
		,execution_params =>
			DBMS_ADVISOR.ARGLIST(
				'DATABASE_LINK', cDB_LINK_4_PARSING_SCHEMA	
				,'EXECUTE_FULLDML', 'TRUE'
			)
	);

	--Comparison Source and Target Analysys 
	DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
		task_name => cSQLSET_NAME
		,execution_type => 'COMPARE'
		,execution_name => 
			cEXECUTION_NAME_PREFIX_DIFF
			|| cSQLSET_NAME
		,execution_params =>
			DBMS_ADVISOR.ARGLIST(
				'PLAN_LINES_COMPARISON','ALWAYS'
			)
	);

	--Make comparison report
	report_clob := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
		task_name => cSQLSET_NAME
		,execution_name => 
			cEXECUTION_NAME_PREFIX_DIFF
			|| cSQLSET_NAME
		,type => cREPORT_TYPE
		,level => 'typical'
		,section => 'summary'
	);

	--Save comparison report
	DBMS_ADVISOR.CREATE_FILE(
		buffer => report_clob
		,location => cDIRECTORY_NAME
		,filename => 
			cEXECUTION_NAME_PREFIX_DIFF
			||cSQLSET_NAME
			||TO_CHAR(systimestamp, 'RRMMDDHH24MISS')
			||cFILE_EXTENTION
	);

	--Drop Task
	drop_task(cSQLSET_NAME);
	DBMS_OUTPUT.PUT_LINE('***END***');
EXCEPTION
	WHEN OTHERS THEN
		drop_task(cSQLSET_NAME);
		RAISE;
END;
/

---

・DBMS_SQLTUNE.PACK_STGTAB_SQLSETって、例外投げんのかよw
・SQL Tuning Setのキャプチャから退避までのスクリプト（やっつけ）
・SQL Tuning Set (STS)のフィルタリング