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 RACによるメンテナンスのためのドレインとアプリケーション・コンティニュイティの仕組み (2023/11/01)

イメージ
Oracle RACによるメンテナンスのためのドレインとアプリケーション・コンティニュイティの仕組み (2023/11/01) https://database-heartbeat.com/2023/11/01/draining-ac-rac/ はじめに Oracle RACは、Oracle Databaseのスケーラビリティと高可用性を提供します。1つのサーバー(RACノード)に障害が発生した場合、またはメンテナンスのためにオフラインになった場合でも、追加のノードを介してデータベースにアクセスできます。ただし、メンテナンスの開始時に、データの読取りまたは変更に関係なく、一部の作業を実行しているクライアント・セッションはどうなりますか。この作業は中断され、ドレインを実装してアプリケーション・コンティニュイティまたは透過的アプリケーション・コンティニュイティを有効にしないかぎり、エンドユーザーまたはアプリケーションによって再度実行する必要があります。 環境 2ノードのOracle RACを例に挙げて、高速アプリケーション通知(FAN)、ドレインおよびアプリケーション・コンティニュイティによってメンテナンス・イベントがエンドユーザーに透過的になる方法を理解しましょう。アプリケーションは、30個のセッションを保持するように構成された接続プールを使用しています。 通常の操作 通常の操作中は、両方のRACノードが起動し、アプリケーションを実行しています。ロード・バランシング戦略によっては、セッションの数が両方のノード間で異なるか、均等に分散される場合があります。次の図をよりよく視覚化するために、ノード2のノード1および20のセッションに10のセッションがあるとします。 セッションは、アイドル状態(接続されているが何もしていない)またはアクティブ状態(リクエストの途中)にできます。リクエストは、データの読取り(SELECT)または操作(INSERT、DELETE、UPDATE)が可能です。 ドレインによるサービスの停止 ある時点で、システムのメンテナンスが必要になります。2ノードRACがあるため、メンテナンスはローリング方式で、1つのノードを順番に(ほとんどの場合)実行できます。メンテナンスは、たとえば、オペレーティング・システム、Grid Infrastructureまたはデ
続きを読む

Oracle Cloud Infrastructure Secure Desktopsを発表: デスクトップ仮想化のためのOracleのクラウドネイティブ・サービス (2023/06/28)

イメージ
Oracle Cloud Infrastructure Secure Desktopsを発表: デスクトップ仮想化のためのOracleのクラウドネイティブ・サービス (2023/06/28) https://blogs.oracle.com/cloud-infrastructure/post/secure-desktops-cloud-native-virtualization 投稿者： Joost Pronk Van Hoogeveen Jan Hendrik Mangold | Principal Product Manager Oracleは、Oracle Cloud Infrastructure(OCI)Secure Desktopsという新しいクラウド・ネイティブ・サービスをリリースしています。このエキサイティングな開発により、顧客はOCIテナンシから簡単にすばやく仮想デスクトップ・インフラストラクチャ(VDI)をデプロイできます。 OCIの使用状況は大幅に拡大しています。組織は、OCI内でデータを安全に保つことの価値を実現しています。ゼロトラストなセキュリティ優先アーキテクチャを実装することで、OCI Compute、Storage、およびNetworkingサービスは、グローバル・アクセスのためのエンタープライズ・アプリケーションを導入するための信頼できるプラットフォームを提供します。 データ・セキュリティとVDIがどのように役立つか 業界のアナリストによると 、ラップトップは53秒ごとに盗まれています。その事実と現実を結び付けると、会社のデータは、公共の場所を経由したり、従業員の自宅に座ったりするラップトップやデスクトップへと移行します。ローカル・ディスクの暗号化、データ・アクセス権限の強制、VPNアクセスの保護によってデータを保護するためにテクノロジが使用されていますが、いずれも許容可能なセキュリティ・レベルが適用されるため、リスクは依然として高くなる可能性があります。盗難または準拠していないいくつかのマシンでは、企業データ、さらには顧客データのデータ侵害が発生する可能性があります。 VDIテクノロジは長年にわたって存在してきましたが、従業員はローカル・マシンを使用して、会社のデータ・センター内の仮想マシンで実行されているデスクトップ・アプリケー
続きを読む

Oracle Cloudのデータベースをオブジェクト・ストレージにバックアップする3つの方法 (2021/12/13)

イメージ
Oracle Cloudのデータベースをオブジェクト・ストレージにバックアップする3つの方法 (2021/12/13) https://database-heartbeat.com/2021/12/13/three-ways-backup-cloud/ はじめに Oracle Cloudでは、ボタンをクリックするだけで自動バックアップを有効にするオプションが用意されています。バックアップの要件が異なる場合でも、手動でバックアップ設定を行い、コマンドライン・ツールやRMANを直接使用してバックアップを作成する柔軟性があります。これは、VM DB Systems、Exadata Cloud Service、およびExadata Cloud at Customerに適用されます。 このブログ記事では、Oracle Cloud Object Storageをバックアップ先として使用する場合の、さまざまなバックアップ・オプションの主な違いと、それに伴うコストについて紹介します。Object Storageは、内蔵されたトリプル・ミラーリングと99.999999%(イレブンナイン)の耐久性を提供します。 ハイブリッド・クラウドとマルチ・クラウド Oracle Cloud Object StorageへのOracle Databaseのバックアップは、Oracle Cloud上のOracle Databaseに限らず、ハイブリッドクラウド上のオンプレミスのOracle Databaseや、マルチクラウド環境のOracle以外のクラウド上で稼働しているOracle Databaseに対しても、RMANを介して実装することができます。 プライベートおよび専用のネットワーク接続は、FastConnect、Azure Interconnect、またはサードパーティのネットワーク接続プロバイダを介して提供できます。マルチクラウド環境では、クラウドプロバイダー、リージョン、転送するデータ量に応じて、追加のアウトバウンドトラフィックコストが発生する可能性があります。オラクルをはじめとする多くのクラウドプロバイダーは、すでに Bandwidth Alliance に参加しており、ネットワークエグレス料金を削減または排除しています。 環境について     Oracle Cloud VM DB Sy
続きを読む