Oracle 19cのOracle Optimizer Statistics Advisor (2023/02/28)

Oracle 19cのOracle Optimizer Statistics Advisor (2023/02/28)

https://mikedietrichde.com/2023/02/28/oracle-optimizer-statistics-advisor-in-oracle-19c/

投稿者：Mike.Dietrich 


Christian Pfundtnerは、2017年のStatistics Advisorに関する私の古いブログ記事が、もう最新ではないかもしれないという事実を指摘してくれました。そしてさらに、彼のクライアントの中には、いまだにこのツールの問題に悩まされている人がいることを指摘されました。したがって、Oracle 19cのOracle Optimizer Statistics Advisorでトピックをリフレッシュさせてください。そして、ご指摘いただいたChristianさんに感謝します。

Photo by Tobias Keller on Unsplash



概要


まずは、ブログ記事全体を書き直すのではなく、最近の知見に焦点を当てることにします。そのため、Oracle Optimizer Statistics Advisorについて概要を知りたいという方のために、機能やツールそのものに関する前回のブログ記事も読んでおいてくださいますようお願いします。


また、このブログ記事を書いている時点の最新情報が掲載されているOracle 21cのドキュメントへのリンクもご紹介しておきます。


ルールはOracle 12.2.0.1と全く同じのようです。21cと比較しても変化は見られませんね。


set long 3000
set line 500
set pages 4000

select RULE_ID, NAME, RULE_TYPE, DESCRIPTION from V$STATS_ADVISOR_RULES order by 1;


   RULE_ID NAME 							    RULE_TYPE DESCRIPTION
---------- ---------------------------------------------------------------- --------- ---------------------------------------------------------------------------
	 0								    SYSTEM
	 1 UseAutoJob							    SYSTEM    Use Auto Job for Statistics Collection
	 2 CompleteAutoJob						    SYSTEM    Auto Statistics Gather Job should complete successfully
	 3 MaintainStatsHistory 					    SYSTEM    Maintain Statistics History
	 4 UseConcurrent						    SYSTEM    Use Concurrent preference for Statistics Collection
	 5 UseDefaultPreference 					    SYSTEM    Use Default Preference for Stats Collection
	 6 TurnOnSQLPlanDirective					    SYSTEM    SQL Plan Directives should not be disabled
	 7 AvoidSetProcedures						    OPERATION Avoid Set Statistics Procedures
	 8 UseDefaultParams						    OPERATION Use Default Parameters in Statistics Collection Procedures
	 9 UseGatherSchemaStats 					    OPERATION Use gather_schema_stats procedure
	10 AvoidInefficientStatsOprSeq					    OPERATION Avoid inefficient statistics operation sequences
	11 AvoidUnnecessaryStatsCollection				    OBJECT    Avoid unnecessary statistics collection
	12 AvoidStaleStats						    OBJECT    Avoid objects with stale or no statistics
	13 GatherStatsAfterBulkDML					    OBJECT    Do not gather statistics right before bulk DML
	14 LockVolatileTable						    OBJECT    Statistics for objects with volatile data should be locked
	15 UnlockNonVolatileTable					    OBJECT    Statistics for objects with non-volatile should not be locked
	16 MaintainStatsConsistency					    OBJECT    Statistics of dependent objects should be consistent
	17 AvoidDropRecreate						    OBJECT    Avoid drop and recreate object seqauences
	18 UseIncremental						    OBJECT    Statistics should be maintained incrementally when it is beneficial
	19 NotUseIncremental						    OBJECT    Statistics should not be maintained incrementally when it is not beneficial
	20 AvoidOutOfRange						    OBJECT    Avoid Out of Range Histogram endpoints
	21 UseAutoDegree						    OBJECT    Use Auto Degree for statistics collection
	22 UseDefaultObjectPreference					    OBJECT    Use Default Object Preference for statistics collection
	23 AvoidAnalyzeTable						    OBJECT    Avoid using analyze table commands for statistics collection

24 rows selected.


アドバイザータスクの開始


アドバイザーのタスクをキックオフする手順も同様に問題なく動作します。


SET LONG 1000000
SET LONGCHUNKSIZE 100000 
SET SERVEROUTPUT ON
SET LINE 300
SET PAGES 1000

DECLARE
  v_tname   VARCHAR2(128) := 'TEST_TASK_MIKE';
  v_ename   VARCHAR2(128) := NULL;
  v_report  CLOB := NULL;
  v_script  CLOB := NULL;
BEGIN
  v_tname  := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
  v_ename  := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
  v_report := DBMS_STATS.REPORT_ADVISOR_TASK(v_tname);
  DBMS_OUTPUT.PUT_LINE(v_report);
END;
/


私の環境では、これで推奨品のリストができました。


GENERAL INFORMATION
-------------------------------------------------------------------------------

 Task Name	 : TEST_TASK_MIKE    
 Execution Name  : EXEC_8	     
 Created	 : 02-27-23 11:59:43 
 Last Modified	 : 02-27-23 11:59:46

-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
 For execution EXEC_8 of task TEST_TASK_MIKE, the Statistics Advisor has 2	
 finding(s). The findings are related to the following rules: USECONCURRENT,    
 AVOIDSTALESTATS. Please refer to the finding section for detailed information. 
-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
 Rule Name: UseConcurrent						    
 Rule Description:  Use Concurrent preference for Statistics Collection 	
 Finding:  The CONCURRENT preference is not used.				

 Recommendation:  Set the CONCURRENT preference.

 Example:									
 dbms_stats.set_global_prefs('CONCURRENT', 'ALL');				
 Rationale:  The system's condition satisfies the use of concurrent statistics	
	     gathering. Using CONCURRENT increases the efficiency of statistics 
	     gathering. 							
----------------------------------------------------
 Rule Name:	    AvoidStaleStats						
 Rule Description:  Avoid objects with stale or no statistics		     
 Finding:  There are 22 object(s) with stale statistics.	
		
 Schema:			
 AUDSYS 			
 Objects:			
 AUD$UNIFIED	
		
 Schema:			
 DBSNMP
 Objects:			
 BSLN_BASELINES 		
 BSLN_METRIC_DEFAULTS		
 BSLN_TIMEGROUPS	
	
 Schema:			
 LBACSYS			
 Objects:			
 OLS$INSTALLATIONS		
 OLS$PROPS	
 
 Schema: 		       
 WMSYS				
 Objects:			
 WM$ENV_VARS$		
	
 Schema:			
 XDB				
 Objects:			
 X$NM69LEG270EHE2WV0L8000000001 
 X$QN69LEG270EHE2WV0L8000000001 
 XDB$ALL_MODEL		       

 Recommendation:  Regather statistics on objects with stale statistics. 	
 Example:									
 -- Gathering statistics for tables with stale or no statistics in schema, SH:	
 exec dbms_stats.gather_schema_stats('SH', options => 'GATHER AUTO')		  
 Rationale:  Stale statistics or no statistics will result in bad plans.	
-----------------------------------------------------------------------------------------------------------------------------------


私の場合、ここで心配することはありません。


 

相違点


さて、19cとそれ以前またはそれ以降のリリースの違い、つまりChristianが上に書いたメールを送ってきた理由に焦点を当てます。


タスクを無効にするコールの実行は、Oracle Database 12.2.0.1 と 21c で問題なく動作します。


exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');

PL/SQL procedure successfully completed.


しかし、Oracle Database 19cではそうはいきません。私はこれをOracle 19.18.0で具体的に実行しましたが、Christianはこれが他の19c RUでも起こることを確認しました。


exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');

BEGIN dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE'); END;
*
ERROR at line 1:
ORA-20001: Invalid input values for pname
ORA-06512: at "SYS.DBMS_STATS", line 52546
ORA-06512: at "SYS.DBMS_STATS", line 52794
ORA-06512: at line 1


さて、2017年のブログ記事を再訪すると、後から赤字で注釈が加えられています。


    更新のお知らせ


    現在、この修正はすべてのリリースでリリースされているように見えます。Unpublished Bug 26749785 - PERF_DIAG: NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK の修正を適用したら、それを無効化することができます。


exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
I double-checked with Nigel Bayliss, our Optimizer PM. And Nigel confirmed what I spotted in the bug as well. This fix is non-RAC rolling, and hence does not appear in any of the 19c Release Updates. Never.


公式ソリューション


バグ26749785の修正は19c RUのいずれにも含まれていないため、あなたの環境でタスクを無効にするためにこのコールを利用できるようにするには、あなたのRUにワンオフパッチを適用する必要があるかもしれません。


バグ26749785のワンオフパッチをダウンロードして適用するだけで、お使いの環境ではすでに利用可能になっていることがほとんどです。


Oracle Database 21c 以降では、この問題はコードで修正されています。この修正により、新しいバージョンのprvtstai.sqlが導入されます。


MOS Note: 2750857.1 - Enabling/Disabling AUTO_STATS_ADVISOR_TASK Throwing ORA-20001: Invalid input values for pname も上記の調査結果の要約と一緒にご覧ください。



Christianの解決策


Christianが彼の回避策を教えてくれたので、膨大な量のストレージを消費して困っている人のために、ここで紹介します。以下の方法は、SYSAUXで消費されたストレージを消去するものです。これが、彼の訓練の主な目的である、スペースを再び解放することでした。


まずは、消費された容量を確認することができます。


select OCCUPANT_NAME,SPACE_USAGE_KBYTES from V$SYSAUX_OCCUPANTS where OCCUPANT_NAME='SM/ADVISOR';

OCCUPANT_NAME                                                    SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
SM/ADVISOR                                                                    10816


ですから、私の場合は11MBと本当に少ないのです。これはデフォルトで割り当てられているものです。


なお、詳細な確認は


column segment_name format a30
select segment_name, bytes, tablespace_name from dba_segments where segment_name like 'WRI$_ADV_OBJECTS%'

SEGMENT_NAME                        BYTES TABLESPACE_NAME
------------------------------ ---------- ------------------------------
WRI$_ADV_OBJECTS                    65536 SYSAUX
WRI$_ADV_OBJECTS_PK                 65536 SYSAUX
WRI$_ADV_OBJECTS_IDX_01             65536 SYSAUX
WRI$_ADV_OBJECTS_IDX_02             65536 SYSAUX


そして、Christianはアドバイザーの仕事を降ろします。


exec DBMS_STATS.DROP_ADVISOR_TASK('AUTO_STATS_ADVISOR_TASK');


そして、インデックスとテーブルを素早く再編成することで、追加のストレージを解放することができます。ただ、デフォルト値の10MB強を再び割り当てることになります。


ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;


万が一、もう一度タスクを取り戻したい場合は、この通話をご利用ください。


EXEC DBMS_STATS.INIT_PACKAGE();

PL/SQL procedure successfully completed.


そして最後にチェック：


column OPERATION_NAME format a26
column CLIENT_NAME format a36
column ATTRIBUTES format a44

SELECT client_name, operation_name, attributes, status FROM dba_autotask_operation;

CLIENT_NAME                          OPERATION_NAME             ATTRIBUTES                                   STATUS
------------------------------------ -------------------------- -------------------------------------------- --------
auto optimizer stats collection      auto optimizer stats job   ON BY DEFAULT, VOLATILE, SAFE TO KILL        ENABLED
auto space advisor                   auto space advisor job     ON BY DEFAULT, VOLATILE, SAFE TO KILL        ENABLED
sql tuning advisor                   automatic sql tuning task  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SA ENABLED
                                                                FE TO KILL


容量を空けるためには、後者の手順が有効な場合がありますので、ご注意ください。しかし、一般的には、最初から単発のパッチを追加した方がよいかもしれません。


 


 

その他のリンクと情報

コメント

コメントを投稿

このブログの人気の投稿

Oracle Database 19cサポート・タイムラインの重要な更新 (2024/11/20)

イメージ
Oracle Database 19cサポート・タイムラインの重要な更新 (2024/11/20) https://mikedietrichde.com/2024/11/20/important-update-to-oracle-database-19c-support-timelines/ 長い沈黙は、私が隠れているという意味ではありません。つまり、今はとても忙しいということです。そして、より少ない旅行で、私はあなたのコメントにもブログと返信するより多くの時間を持っていることを願っています。それでも、Oracle Database 19cサポート・タイムラインの非常に重要な更新について説明します。 * 更新された内容 2024年11月19日現在、Oracle Database 19cのサポート・タイムラインが調整されています。 MOSノート: 742060.1 – 現在のデータベース・リリースのリリース・スケジュールを見ると、新しい日付が表示されます。 Premier Support、2029年12月31日まで 2030年1月1日から2032年12月31日までの拡張サポート 2033年1月1日から2034年12月31日までのアップグレード・サポート これは知ることがとても大切です。 他のリリースの更新はありますか。 MOSノートの終わりにある変更ログ: 742060.1 – 現在のデータベース・リリースのリリース・スケジュールでは、Oracle Database 21cの最新の更新も確認できます。このリリースのPremier Support期間は、2027年7月31日まで延長されました。 また、Oracle Database 23aiのパッチ適用終了日は2032年12月31日に設定されています。 Lifetime Support Policy 更新は、Oracle Technology Productsの Oracle Lifetime Support Policy (6ページ)でも確認できます。 From: Oracle Lifetime Support Policy for Oracle Technology Product s – page 6 – Nov 19, 2024 特に、次の表に示す制限事項に注意してください。 Oracle Database 1...
続きを読む

Oracle APEXのInteractive Gridで、Oracle Formsと比較して、重複行の検証を制御/通過させる方法 (2022/07/21)

イメージ
Oracle APEXのInteractive Gridで、Oracle Formsと比較して、重複行の検証を制御/通過させる方法 (2022/07/21) https://medium.com/oracledevs/how-to-control-or-pass-validation-for-duplicate-rows-in-interactive-grid-of-oracle-apex-as-compare-18f453f750bf 投稿者： Jignesh oracle Formsでは、データブロックの重複行を制御するのは非常に簡単ですが、IGの検証を制御するにはどうすればよいでしょうか。 必要な出力 ステップ1：インタラクティブグリッドのある白紙ページを作成します（私は1つの領域「IG」を作成し、インタラクティブグリッドとしてタイプを選択しました） ステップ 2: リージョンに Static ID を定義する (私の場合、リージョンは IG で、Static ID も IG です) ステップ3：複製を制御したいカラムのStatic IDを定義します ステップ4：関数とグローバル変数に移動します コード： var validity, message, ui = this.data; (function($) { function update(model) { var projKey = model.getFieldKey(“PROJECT_ID”), recObj = [], recArray = []; model.forEach(function(record, index, id) { var projid = parseInt(record[projKey], 10), // record[salKey] should be a little faster than using model.getValue in a loop meta = model.getRecordMetadata(id); if (!isNaN(projid) && !meta.deleted && !meta.agg) { recObj = {ID: id, project_id: projid}; recArray.push(recObj)...
続きを読む

Oracle GoldenGate 23aiでMicrosoft Fabricでのオープン・ミラーリングがサポートされるようになりました (2024/11/19)

イメージ
Oracle GoldenGate 23aiでMicrosoft Fabricでのオープン・ミラーリングがサポートされるようになりました (2024/11/19) https://blogs.oracle.com/dataintegration/post/how-to-replicate-to-mirrored-database-in-microsoft-fabric-using-goldengate 投稿者： Shrinidhi Kulkarni | Principal Product Manager Oracle GoldenGate 23aiでは、オープン・ミラーリングを介したMicrosoft Fabricへの統合のパブリック・プレビューがサポートされるようになりました。この強力な組み合わせにより、リアルタイムのデータ統合が可能になり、ハイブリッド環境とマルチクラウド環境のデータをMicrosoft Fabricのミラー化されたデータベースに継続的に同期できるため、データは常にAIと分析に対応できます。 Microsoft Fabric統合のOpen Mirroring は、GoldenGate for Distributed Applications and Analytics 23ai (GG for DAA) (23.6)製品を介して提供されます。 既存の Azure統合の詳細については、 ドキュメント を参照してください。 オープン・ミラーリングは、オープン・デルタ・レイク表形式に基づいて、データISVがMicrosoft Fabricのミラーリングを拡張できるように設計されています。この機能により、Oracle GoldenGate 23aiは、オープン・ミラー化パブリックAPIおよびアプローチに基づいて、Microsoft Fabricのミラー化データベースに変更データを直接書き込むことができます。Microsoft FabricでのOpen Mirroringの詳細については、「 Microsoft FabricでのOpen Mirroringの概要 」を参照してください。 このブログ投稿では、この統合の実装の側面について深く掘り下げ、GoldenGate統合の構成方法を示します。 構成ステップ: リファレンス・アーキテクチャの理解 前提条件 ...
続きを読む