1 SQLチューニングの概要
SQLチューニングは、パフォーマンス標準を満たせないSQL文を診断して修正しようとするものです。
1.1 SQLチューニングについて
SQLチューニングは、SQL文のパフォーマンスを、特定の測定可能かつ達成可能な目標に合うように向上させる反復プロセスです。
SQLチューニングには、デプロイ済アプリケーションの問題の解決も暗黙的に含まれます。対照的に、アプリケーション設計では、アプリケーションをデプロイする前にセキュリティおよびパフォーマンスの目標を設定します。
関連項目:
-
SQLパフォーマンスを考慮した設計方法を学習するには、「アプリケーションの設計のガイドライン」を参照してください
1.2 SQLチューニングの目的
事前定義された測定可能な基準に沿ってSQL文を実行できない場合、そのSQL文は問題になります。
問題を特定した後、一般的なチューニング・セッションの目標は次のいずれかになります。
-
ユーザー・レスポンス時間の短縮。これは、ユーザーが文を発行してからレスポンスを受け取るまでの時間を短縮することを意味します。
-
スループットの向上。これは、文がアクセスするすべての行を処理するために必要なリソースの使用量を最小限に抑えることを意味します。
レスポンス・タイムの問題については、顧客がショッピング・カートを更新すると、3分間ハングしてしまうオンライン書店のアプリケーションを考えてみましょう。データベース・ホストのCPUをすべて消費する、データ・ウェアハウスにおける3分間のパラレル問合せ(他の問合せの実行は阻止されます)と対比してみてください。いずれのケースでも、ユーザー・レスポンス・タイムは3分間ですが、問題の原因は異なっており、したがってチューニングの目標も異なります。
1.3 SQLチューニングの前提条件
SQLパフォーマンス・チューニングには、データベースの知識の基礎が必要です。
SQLパフォーマンスをチューニングする場合、このマニュアルでは、次の表に示す知識とスキルがあることを前提としています。
表1-1 必要な知識
| 必要な知識 | 説明 | さらに学習するには |
|---|---|---|
|
データベース・アーキテクチャ |
データベース・アーキテクチャは、管理者のみが関係する領域ではありません。開発者としてOracle Databaseを使用するアプリケーションの開発にかかる時間を最小限に抑えるには、データベースのアーキテクチャおよび機能を利用する必要があります。たとえば、Oracle Databaseの並行性制御およびマルチバージョン読取りの一貫性について理解していないと、アプリケーションでデータの整合性を損なったり、アプリケーションの実行が遅くなったり、スケーラビリティが低下するおそれがあります。 |
『Oracle Database概要』で、Oracle Databaseの基本的なリレーショナル・データ構造、トランザクション管理、記憶域構造およびインスタンス・アーキテクチャについて説明しています。 |
|
SQLおよびPL/SQL |
GUIベースのツールがあるため、SQLに関する知識がなくても、アプリケーションを作成し、データベースを管理することは可能です。ただし、SQLに関する知識なしにアプリケーションまたはデータベースをチューニングすることはできません。 |
『Oracle Database概要』には、Oracle SQLおよびPL/SQLの概要が含まれています。『Oracle Database SQL言語リファレンス』、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』および『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』の実用的な知識も必要です。 |
|
SQLチューニング・ツール |
データベースにより、パフォーマンス統計が生成され、それらの統計を解釈するためのSQLチューニング・ツールが提供されます。 |
『Oracle Databaseパフォーマンス・チューニング・スタート・ガイド』で、主要なSQLチューニング・ツールの概要が示されています。 |
1.4 SQLチューニングのタスクとツール
チューニング・セッションの目標(たとえば、ユーザー・レスポンス・タイムを3分から1秒未満に短縮するなど)を設定したら、問題は、どうやってその目標を達成するかです。
1.4.1 SQLチューニング・タスク
チューニング・セッションの仕様は、事前チューニングを行うか、事後チューニングを行うかなど、多数の要因によって異なります。
事前のSQLチューニングでは通常、SQLチューニング・アドバイザを使用して、SQL文のパフォーマンスを向上させることができるかどうかを判断します。事後のSQLチューニングでは、ユーザーが経験したSQL関連の問題を解決します。
事前のチューニングを行うか、事後のチューニングを行うかに関係なく、一般的なSQLチューニング・セッションには、次のタスクのすべてまたはほとんどが含まれています。
-
高負荷のSQL文の識別
過去の実行履歴を検討し、アプリケーションのワークロードおよびシステム・リソースの大部分に関係している文を識別します。
-
パフォーマンス関連のデータの収集
オプティマイザ統計は、SQLチューニングにとって非常に重要です。これらの統計が存在しないか、正確でない場合、オプティマイザは、最適な計画を生成することができません。SQLパフォーマンスに関連する他のデータには、文がアクセスした表やビューの構造、および文で使用可能な索引の定義などがあります。
-
問題の原因の特定
通常、SQLパフォーマンスの問題の原因には、次のようなものがあります。
-
SQL文の設計が非効率的である
SQL文が不要な作業を実行するように記述されている場合、パフォーマンスの改善のためにオプティマイザができることはあまりありません。非効率的な設計パターンの例を次に示します。
-
最適ではない実行計画
問合せオプティマイザ(オプティマイザとも呼ばれます)は、どの実行計画が最も効率的かを判断する内部ソフトウェアです。オプティマイザは、データベースからデータを取り出すための手段として、アクセス・パスが最適ではない計画を選択することもあります。たとえば、選択性が低い問合せ述語に対する計画では、索引ではなく大きな表の全表スキャンを使用することが考えられます。
SQL文を最適に実行する実行計画と、最適ではないパフォーマンスの文の計画を比較できます。この比較を行うと、データ量の変化などの情報とあわせて、パフォーマンス低下の原因の識別に役立ちます。
-
SQLアクセス構造がない
SQLパフォーマンスが最適なものとならない一般的な理由は、索引やマテリアライズド・ビューなど、SQLアクセス構造がないことです。アクセス構造の最適なセットにより、SQLパフォーマンスを桁違いに向上させることができます。
-
失効したオプティマイザ統計
DBMS_STATSによって収集された統計は、自動または手動の統計メンテナンス操作が、DMLによる表データの変更に対応できていない場合に失効します。失効した表の統計は、表データを正確に反映していないため、オプティマイザによって誤った情報に基づいた決定が行われ、最適ではない実行計画が生成される可能性があります。 -
ハードウェアの問題
最適ではないパフォーマンスは、メモリー、I/OおよびCPUの問題に関係する場合があります。
-
-
問題の範囲の定義
ソリューションの範囲は、問題の範囲と一致している必要があります。データベース・レベルと文レベルで問題を検討してください。たとえば、共有プールが小さすぎると、カーソルのエージ・アウトが速くなり、その結果、ハード解析が何度も行われます。初期化パラメータを使用して共有プールのサイズを増やすと、データベース・レベルの問題は解決され、すべてのセッションのパフォーマンスが向上します。ただし、単一のSQL文で有用な索引が使用されていない場合、データベース全体に対してオプティマイザの初期化パラメータを変更すると、全体的なパフォーマンスに悪影響を及ぼすおそれがあります。単一のSQL文に問題がある場合は、適切な範囲のソリューションでその文のその問題にのみ対処します。
-
最適ではないパフォーマンスのSQL文に対する修正アクションの実装
これらのアクションは状況によって異なります。たとえば、SQL文をより効率的なものにリライトすることが考えられます。バインド変数を使用するように文をリライトすることで、不要なハード解析を回避します。また、等価結合を使用する、
WHERE句からファンクションを削除する、複雑なSQL文を複数の単純な文に分けるといったことも考えられます。場合によっては、文のリライトによってではなく、スキーマ・オブジェクトの再構築によってSQLパフォーマンスが向上することもあります。たとえば、新しいアクセス・パスの索引付けや、連結索引内の列の並替えなどです。また、表のパーティション化や導出値の導入に加えて、データベース設計の変更さえも考えられます。
-
SQLパフォーマンスの低下の回避
最適なSQLパフォーマンスを確保するには、実行計画が最適なパフォーマンスを実現し続けていることを確認し、より適切な計画が使用可能になったらそれらを採用します。オプティマイザ統計、SQLプロファイルおよびSQL計画ベースラインを使用して、これらの目標を達成できます。
関連項目:
-
共有プールについてさらに学習するには、『Oracle Database概要』を参照してください
1.4.2 SQLチューニング・ツール
SQLチューニング・ツールは、自動または手動のいずれかです。
このコンテキストでは、データベース自体が分析、アドバイスまたは修正アクションを提供できるものは自動ツールになります。手動ツールでは、これらの操作すべてをユーザーが実行する必要があります。
すべてのチューニング・ツールは、データベース・インスタンスが収集する動的パフォーマンス・ビュー、統計およびメトリックの基本ツールに依存します。SQL文のチューニングに必要なデータとメタデータは、データベース自体に含まれています。
1.4.2.1 自動SQLチューニング・ツール
Oracle Databaseには、SQLチューニングに関連するいくつかのアドバイザが用意されています。
さらに、SQL計画管理は、パフォーマンスの低下を回避し、SQLパフォーマンスの向上にも役立つメカニズムです。
すべての自動SQLチューニング・ツールで、入力としてSQLチューニング・セットを使用できます。SQLチューニング・セット(STS)は、1つ以上のSQL文がその実行統計および実行コンテキストとともに含まれているデータベース・オブジェクトです。
関連項目:
-
SQLチューニング・セットの管理についてさらに学習するには、『Oracle Databaseパフォーマンス・チューニング・スタート・ガイド』を参照してください
1.4.2.1.1 自動データベース診断モニター(ADDM)
ADDMは、Oracle Databaseに組み込まれている自己診断ソフトウェアです。
ADDMでは、パフォーマンスに関する問題の根本原因の特定、修正のための推奨事項の提示、および期待される利益の数値化を自動的に行うことができます。また、ADDMはアクションを必要としない領域を識別します。
ADDMおよび他のアドバイザでは、自動ワークロード・リポジトリ(AWR)が使用されます。これは、データベース・コンポーネントに統計の収集、保持および使用のためのサービスを提供するインフラストラクチャです。ADDMは、AWR内の統計を調べて分析し、高負荷SQLなど、パフォーマンスの問題となる可能性のある事柄を特定します。
たとえば、夜間実行されるようにADDMを構成できます。朝、担当者は、最新のADDMレポートを調べて、問題の原因と考えられることや推奨される解決策の有無を確認できます。レポートでは、たとえば、特定のSELECT文がCPUを大量に使用していることが示され、SQLチューニング・アドバイザの実行が推奨されています。
1.4.2.1.2 SQLチューニング・アドバイザ
SQLチューニング・アドバイザは、問題のあるSQL文を特定し、文のパフォーマンスの改善方法を提示する内部診断ソフトウェアです。
SQLチューニング・アドバイザが自動メンテナンス・タスクとしてデータベース・メンテナンス・ウィンドウに実行される場合、自動SQLチューニング・アドバイザと呼ばれます。
SQLチューニング・アドバイザは、入力として1つ以上のSQL文を取得し、自動チューニング・オプティマイザを起動して文に対するSQLチューニングを実行します。アドバイザは、次のタイプの分析を行います。
-
欠落している統計または失効している統計の有無をチェックする
-
SQLプロファイルの構築
SQLプロファイルは、SQL文に固有の補足情報です。SQLプロファイルには、自動SQLチューニングの際に検出された最適ではないオプティマイザの見積りに対する修正が含まれます。この情報は、カーディナリティおよび選択性に関するオプティマイザの見積りを向上させることができます。カーディナリティとは、実行計画で操作によって戻される見積りの行数または実際の行数です。これらの精度が増した見積りにより、オプティマイザはより適切な計画を選択できるようになります。
-
別のアクセス・パスによってパフォーマンスを大幅に改善できるかどうかを調査する。
-
最適ではない計画の原因となっているSQL文を特定する。
SQLチューニング・アドバイザの実行結果は、アドバイスまたは推奨事項の形式で出力され、推奨事項ごとに論理的根拠および予想される利点が示されます。推奨事項は、オブジェクトの統計、新しい索引の作成、SQL文の再構成またはSQLプロファイルの作成に関するものです。ユーザーは、推奨事項を受け入れるかどうかを選択してSQL文のチューニングを完了できます。
1.4.2.1.3 SQLアクセス・アドバイザ
SQLアクセス・アドバイザは、どのマテリアライズド・ビュー、索引およびマテリアライズド・ビュー・ログを作成、削除または保持するのが適切かを提示する内部診断ソフトウェアです。
SQLアクセス・アドバイザは、入力として実際のワークロードを使用します。または、スキーマから仮想ワークロードを導出することもできます。SQLアクセス・アドバイザでは、領域使用量と問合せパフォーマンスの兼合いが考慮され、新規および既存のマテリアライズド・ビューおよび索引の最もコスト効率の高い構成が推奨されます。アドバイザはまた、パーティション化に関する推奨事項も提示します。
関連項目:
-
自動索引付けについてさらに学習するには、『Oracle Database管理者ガイド』を参照
-
各種エディションおよびサービスで自動索引付けがサポートされているかどうかの詳細は、『Oracle Databaseライセンス情報ユーザー・マニュアル』を参照
1.4.2.1.4 自動索引付け
Oracle Databaseでは、自動的に索引を作成および管理することで、アプリケーション・ワークロードを常時監視できます。
ノート:
各種エディションおよびサービスでサポートされる機能の詳細は、『Oracle Databaseライセンス情報ユーザー・マニュアル』を参照してください。
手動で索引を作成するには、データ・モデル、アプリケーションおよびデータの配布に関する詳しい知識が必要になります。DBAは作成する索引を選択しますが、多くの場合、その選択内容を改めようとはしません。その結果、改善の機会が失われ、不要な索引がパフォーマンスに負担をかけることになります。自動索引管理がこの問題を解決します。
1.4.2.1.4.1 自動索引作成の動作
自動索引作成プロセスは、バックグラウンドで15分ごとに実行され、次の操作を実行します。
-
自動索引候補は、SQL文での表の列の使用方法に基づいて識別されます。
- DML実行中の索引メンテナンスのパフォーマンス・オーバーヘッドが問合せパフォーマンスの向上というメリットを上回る場合、DMLアクティビティが重い表は除外されます。
- 自動索引は単一列にも複数列にもなります。
- オプティマイザ統計が失効または欠落している表は、統計が収集され最新になるまで、自動索引で考慮されません。
- 索引候補は、最初の作成時には非表示かつ使用不可になります。この段階では、それらはメタデータのみであり、アプリケーション・ワークロードに表示されません。次のものが索引候補になる可能性があります:
- 表の列(仮想列を含む)
- パーティション化された表およびパーティション化されていない表
- 選択した式(JSON式など)
- 単一列または複数列。
-
ワークロードSQL文のサンプルがテスト解析されて、どの索引が有益かがオプティマイザによって判別されます。オプティマイザによって有益とみなされた索引は、作成され、有効になり、SQL文でのパフォーマンス上の効果を測定できるようになります。この検証ステップにおいて、すべての候補索引は非表示のままです。
オプティマイザによって有益と判断されなかった索引は、非表示で使用不可のままになります。
-
ワークロードSQL文のサンプルがテスト実行されて、パフォーマンスを向上させる索引が特定されます。
-
SQLパフォーマンスの向上のために見つかった有効な候補索引は、アプリケーション・ワークロードに表示されて使用可能になります。SQLパフォーマンスの改善につながらない候補索引は非表示に戻り、しばらくすると使用できなくなります。
検証段階で、索引が有益であることはわかったものの個々のSQL文でパフォーマンスの低下が発生した場合、索引が表示されたときに、低下を防ぐためにSQL計画ベースラインが作成されます。
-
使用されていない自動索引は、構成可能な保存期間をすぎると自動索引付けプロセスによって削除されます。
ノート:
デフォルトでは、未使用の自動索引は373日後に削除されます。データベース内の未使用の自動索引の保存期間は、
DBMS_AUTO_INDEX.CONFIGUREプロシージャを使用して構成できます。
1.4.2.1.4.2 自動索引付けの有効化と無効化
DBMS_AUTO_INDEXパッケージは、自動索引付けを構成、削除、監視およびレポートするためのオプションを提供します。
- 自動索引付けを有効にします。
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT') - 使用されていない自動索引を保持する期間(日数)など、その他の設定を構成します。
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO','180') - 自動索引を削除します。最初の例の一重引用符と二重引用符の使用に注意してください。
スキーマが所有する単一の索引を削除して、再作成を許可します。
EXEC DBMS_AUTO_INDEX.DROP_AUTO_INDEXES('SH','"SYS_AI_c0cmdvbzgyq94"',TRUE)スキーマが所有するすべての索引を削除して、再作成を許可します。EXEC DBMS_AUTO_INDEX.DROP_AUTO_INDEXES('SH',NULL,TRUE)スキーマが所有するすべての索引を削除して、再作成を禁止します。その後、再作成ステータスを変更して元のように許可します。EXEC DBMS_AUTO_INDEX.DROP_AUTO_INDEXES('HR',NULL) EXEC DBMS_AUTO_INDEX.DROP_AUTO_INDEXES('HR',NULL,TRUE) - 自動索引の構成設定についてレポートします。
SELECT parameter_name, parameter_value FROM dba_auto_index_config;
追加の制御
セッションでOPTIMIZER_SESSION_TYPE初期化パラメータをADHOCに設定することで、このセッションでのSQL文に対する自動索引付けを一時停止できます。自動索引付けプロセスでは索引候補が識別されず、索引は作成も検証もされません。
ALTER SESSION SET optimizer_session_type = 'ADHOC';関連項目:
-
自動索引付けについてさらに学習するには、『Oracle Database管理者ガイド』を参照
-
DBMS_AUTO_INDEXパッケージで使用可能なプロシージャおよびファンクションについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスのDBMS_AUTO_INDEXを参照 -
OPTIMIZER_SESSION_TYPEについてさらに学習するには、『Oracle Databaseリファレンス』を参照。
1.4.2.1.4.2.1 DMLのオーバーヘッドへの対処
INSERT、UPDATE、DELETEなどのDML操作のオーバーヘッドが増加します。たとえば、問合せパフォーマンスの向上が、表に対するDMLの書込みアクティビティが重いせいで消失する場合があります。自動索引では、新しい索引が有益かどうかを判別するときに、このオーバーヘッドが考慮されます。この機能は、CONFIGUREパラメータAUTO_INDEX_INCLUDE_DML_COST (デフォルトではON)によって制御します。この機能は、次のように無効化できます:EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_INCLUDE_DML_COST','ON')SQLを使用して、このパラメータの現在の設定を表示できます:
SELECT parameter_name,parameter_value FROM DBA_AUTO_INDEX_CONFIG WHERE parameter_name = 'AUTO_INDEX_INCLUDE_DML_COST';1.4.2.1.5 SQL計画管理
SQL計画管理は、データベースで既知の計画または確認済の計画のみが使用されるようにオプティマイザで実行計画を自動的に管理する予防的なメカニズムです。
このメカニズムにより、SQL計画ベースラインを構築できます。これには、各SQL文の承認済計画が1つ以上含まれます。SQL計画管理では、ベースラインを使用することで、環境の変化による計画の不具合を回避しつつ、オプティマイザがより適切な計画を検出して使用できるようにします。
関連項目:
-
Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス
DBMS_SPMパッケージについて学習するには、こちらを参照してください
1.4.2.1.5.1 自動SQL計画管理の仕組み
自動SPM (SQL計画管理)を使用すると、計画のパフォーマンス低下が自動的に検出され修復されます。
自動SPMは、DBMS_SPM.CONFIGUREを使用して有効にします。自動SPM展開アドバイザ自体は常にバックグラウンドで実行されますが、SQL実行計画のパフォーマンス評価はフォアグラウンドまたはバックグラウンドで実行するように構成できます。
DBMS_SPM.CONFIGUREがONに設定されている場合:
こうすると、バックグラウンド検証モードを使用するように自動SPMが構成されます:
EXEC DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'ON');この設定では、高頻度自動SPM展開アドバイザによって次の処理が実行されます。
- 自動ワークロード・リポジトリ(AWR)および自動SQLチューニング・セット(ASTS)を調べて、システム・リソースの消費が多いSQL文を特定します。
- ASTSにある代替SQL実行計画を見つけます。
- 代替計画をテスト実行し、それらのパフォーマンスを比較します。
- どの計画のパフォーマンスが最も高いかを評価し、それらを実行するSQL計画ベースラインを作成します。
DBMS_SPM.CONFIGUREがAUTOに設定されている場合:
これにより、リアルタイム・モードを使用するように自動SPMが構成されます。
EXEC DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'AUTO');高頻度自動SPM展開アドバイザは引き続きバックグラウンドで動作します。ただし、SQL実行計画のパフォーマンスはフォアグラウンドで即座に評価されます:
- SQL文が実行されると、実行計画が新規の場合は、実行後にSQL文のパフォーマンスが、ASTSに保存されている既知の計画のパフォーマンスと比較されて、より適切な代替SQL実行計画がすでに存在するかどうかが確認されます。
- 以前の計画のパフォーマンスが新しい計画よりも高い場合は、より最適な以前のその計画を実行するように、SQL計画ベースラインが作成されます。
1.4.2.1.6 SQLパフォーマンス・アナライザ
SQLパフォーマンス・アナライザは、SQL文ごとのパフォーマンスの相違を識別することで、SQLワークロードに対する変更の影響を判断します。
データベースのアップグレードや索引の追加などのシステム変更によって、実行計画が変更され、SQLパフォーマンスに影響を与えることがあります。SQLパフォーマンス・アナライザを使用することで、システム変更がSQLパフォーマンスに与える影響を正確に予測できるようになります。この情報を使用して、SQLパフォーマンスの低下時にデータベースのチューニングを行うことや、SQLパフォーマンスが向上することによって得られる利益を検証および評価することが可能です。
関連項目:
1.4.2.1.7 SQLトランスパイラ
SQLトランスパイラは自動的に実行され、可能な場合は、SQL内のPL/SQLファンクションを、ユーザーの介入なしでSQL式に変換(トランスパイル)します。
Oracle SQL文内の式は、PL/SQLファンクションをコールできます。ただし、PL/SQLランタイムを起動する必要があるため、これらのコールではオーバーヘッドが発生します。SQLコンパイラは、SQL文からコールされるPL/SQLファンクションを、意味的に同等のSQL式に変換しようとします。PL/SQLファンクションをSQLにトランスパイルすると、新規および既存のプログラムおよびファンクションのパフォーマンスが向上します。トランスパイルされたPL/SQLファンクションが起動されると、SQL内でトランスパイルされたコードを実行する1行当たりのコストは、元のPL/SQLコードを実行するためにSQLランタイムからPL/SQLランタイムに切り替えるよりも大幅に低くなります。
トランスパイラがPL/SQLファンクションをSQLに変換できない場合、ファンクションの実行はPL/SQLランタイムにフォール・バックします。すべてのPL/SQL構造がトランスパイラでサポートされているわけではありません。
この操作全体がユーザーに対して透過的です。
次の例は、PL/SQLファンクションGET_MONTH_ABBREVIATIONの呼出しを含むSELECT文を示しています。このファンクションは、パラメータdate_valueから3文字の月の略称を抽出します。プランの下部にあるPredicate Information (述語情報)セクションでは、GET_MONTH_ABBREVIATIONがTO_CHAR(INTERNAL_FUNCTION("HIRE_DATE"),'MON','NLS_DATE_LANGUAGE=English')='MAY')に置換されていることがわかります。これは、トランスパイルが発生したことを示しています。
create function get_month_abbreviation (
date_value date
) return varchar2 is
begin
return to_char ( date_value, 'MON', 'NLS_DATE_LANGUAGE=English' );
end;
/
alter session set sql_transpiler = ON;
select employee_id, first_name, last_name
from hr.employees
where get_month_abbreviation ( hire_date ) = 'MAY';
EMPLOYEE_ID FIRST_NAME LAST_NAME
------------------------------------
104 Bruce Ernst
115 Alexander Khoo
122 Payam Kaufling
174 Ellen Abel
178 Kimberely Grant
197 Kevin Feeney
select *
from dbms_xplan.display_cursor ( format => 'BASIC +PREDICATE' );
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| *1 | TABLE ACCESS FULL | EMPLOYEES |
---------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR(INTERNAL_FUNCTION("HIRE_DATE"),'MON','NLS_DATE_LANGUAGE=English')='MAY')
トランスパイルが発生すると、述語セクション内のファンクション名がSQL式に置き換えられます。前述の例ではこれが発生しています。
ノート:
トランスパイルが発生しなかった場合、この例での述語セクションは次のようになります:
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("GET_MONTH_ABBREVIATION"("HIRE_DATE")='MAY')1.4.2.1.7.1 SQLトランスパイラの有効化または無効化
SQLトランスパイラはデフォルトで無効になっています。ALTER SYSTEMコマンドで有効にできます。
パラメータ値はSQL_TRANSPILERを使用して変更できます。
SQL_TRANSPILER = [ON | OFF] パラメータは、システム・レベルまたはセッション・レベルで変更できます。
1.4.2.1.7.2 トランスパイルのためのPL/SQLコンストラクトの適格性
すべてのPL/SQL構造がSQLにトランスパイルできるわけではありません。
トランスパイルに適格なPL/SQL構造
SQLトランスパイラでは、次のPL/SQL言語要素がサポートされています。
- 基本SQLスカラー型: CHARACTER、DATETIMEおよびNUMBER
- 文字列型(CHAR、VARCHAR、VARCHAR2、NCHARなど)
- 数値型(NUMBER、BINARY DOUBLEなど)
- 日付型(DATE、INTERVALおよびTIMESTAMP)
- ローカル変数(宣言時のオプションの初期化あり)および定数
- オプションの(単純な)デフォルト値を持つパラメータ
- 変数代入文
- 同等のSQL式に変換できる式
- RETURN文
- BOOLEAN型の式およびローカル変数
現時点でトランスパイルに適格でないPL/SQL構造
- 埋込みSQL文。トランスパイルされたファンクションには、カーソル宣言、明示カーソル、参照カーソルまたは即時実行文を含めることはできません
- PL/SQLパッケージ内で定義されたファンクション。
- publicとprivateの両方のパッケージ変数。
- PL/SQL固有のスカラー型: PLS_INTEGER
- PL/SQL集計タイプ: レコード、コレクションおよび表
- Oracleオブジェクト(ADT/UDT)、XMLおよびJSON
- 非推奨のデータ型: LONG
- %TYPE属性および%ROWTYPE属性
- パッケージ状態(定数と変数の両方)
- ローカルで定義されたPL/SQLタイプ
- ローカルで定義された(ネストされた)ファンクション
- 他のPL/SQLファンクション(スキーマ・レベルとパッケージ・レベルの両方)へのコール。これにより、再帰的ファンクション・コールのサポートも除外されます
- LOOP、GOTOおよびRAISEなどの制御フロー文
- ネストされたDECLARE-BEGIN-EXCEPTIONブロック
- CASE制御フロー文(SQLとPL/SQLの両方でサポートされているSQL CASE式とは異なることに注意してください)
- COMMIT、ROLLBACK、LOCK-TABLE、PRAGMA AUTONOMOUS TRANSACTION、SELECT-FOR-UPDATEなどのトランザクション処理
1.4.2.2 手動SQLチューニング・ツール
状況によっては、自動ツールに加えて手動ツールの実行が必要になる場合もあります。または、自動ツールを使用できないことも考えられます。
1.4.2.2.1 実行計画
実行計画は、手動SQLチューニングの主要な診断ツールです。たとえば、計画を表示して、オプティマイザが想定どおりの計画を選択しているかどうかを確認したり、表での索引作成の効果を特定したりすることができます。
複数の方法で実行計画を表示できます。最もよく使用されるツールは次のとおりです。
-
DBMS_XPLANDBMS_XPLANパッケージ・メソッドを使用して、EXPLAIN PLANコマンドおよびV$SQL_PLANの問合せによって生成された実行計画を表示できます。 -
EXPLAIN PLANこのSQL文では、実際にSQL文を実行せずに、オプティマイザがSQL文の実行に使用する実行計画を表示できます。『Oracle Database SQL言語リファレンス』を参照してください。
-
V$SQL_PLANおよび関連ビューこれらのビューには、まだ共有プール内に存在する実行済SQL文に関する情報、およびそれらの実行計画が含まれています。『Oracle Databaseリファレンス』を参照してください。
-
AUTOTRACESQL*Plusの
AUTOTRACEコマンドは、実行計画と、問合せのパフォーマンスに関する統計を生成します。このコマンドにより、ディスク読取りやメモリー読取りなどの統計が提供されます。SQL*Plusユーザーズ・ガイドおよびリファレンスを参照してください。
1.4.2.2.2 リアルタイムなSQL監視およびリアルタイムなデータベース操作
OracleデータベースのリアルタイムSQL監視機能では、SQL文の実行中にそのパフォーマンスを監視できます。デフォルトでは、文がパラレルで実行される場合、または1回の実行で5秒以上のCPUまたはI/O時間を消費している場合に、SQL監視が自動的に開始されます。
データベース操作は、バッチ・ジョブまたはデータ抽出、変換およびロード(ETL)処理など、エンド・ユーザーまたはアプリケーション・コードで定義された一連のデータベース・タスクです。データベース操作の定義、監視およびレポート生成が可能です。リアルタイムなデータベース操作では、複合操作を自動的に監視できます。データベースは、パラレルな問合せ、DML文およびDDL文を、実行の開始と同時に自動的に監視します。
Oracle Enterprise Manager Cloud Control (Cloud Control)には、使いやすいSQL監視ページが用意されています。または、V$SQL_MONITORおよびV$SQL_PLAN_MONITORビューを使用して、SQL関連の統計を監視することができます。これらのビューを次のビューとともに使用して、監視対象の実行に関する詳細情報を取得できます。
-
V$ACTIVE_SESSION_HISTORY -
V$SESSION -
V$SESSION_LONGOPS -
V$SQL -
V$SQL_PLAN
関連項目:
-
V$ビューについて学習するには、Oracle Databaseリファレンスを参照してください
1.4.2.2.3 アプリケーションのトレース
SQLトレース・ファイルには、解析件数、物理および論理読取り、ライブラリ・キャッシュでのミスなど、個々のSQL文に関するパフォーマンス情報が記録されています。
トレース・ファイルは、SQLパフォーマンスの問題の診断に役立つ場合があります。DBMS_MONITORまたはDBMS_SESSIONパッケージを使用して、特定のセッションのSQLトレースを有効または無効にできます。Oracle Databaseでは、トレース・メカニズムが有効化されたときに各サーバー・プロセスのトレース・ファイルを生成することによって、トレースを実装します。
Oracle Databaseでは、トレース・ファイルの分析のために次のコマンドライン・ツールが用意されています。
-
TKPROFこのユーティリティは、SQLトレース機能によって生成されたトレース・ファイルを入力として受け入れ、フォーマットされた出力ファイルを生成します。
-
trcsessこのユーティリティでは、セッションID、クライアントID、サービスIDなどの基準に基づいて、複数のトレース・ファイルからのトレース出力が統合されます。
trcsessによりトレース情報が単一の出力ファイルにマージされた後、TKPROFを使用して出力ファイルをフォーマットできます。trcsessは、パフォーマンスまたはデバッグの目的で特定のセッションのトレースを統合する際に役立ちます。
エンドツーエンド・アプリケーションのトレースは、複数層環境のパフォーマンス上の問題の診断プロセスを単純化します。これらの環境では、エンド・クライアントからのリクエストは中間層により様々なデータベース・セッションにルーティングされるため、異なるデータベース・セッション間でクライアントを追跡するのは困難です。エンドツーエンド・アプリケーションのトレースでは、クライアントIDを使用して、データベースへのすべての層を通じて特定のエンドクライアントを一意にトレースします。
関連項目:
DBMS_MONITORおよびDBMS_SESSIONについてさらに学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
1.4.2.2.4 オプティマイザ・ヒント
ヒントは、SQL文のコメントを介してオプティマイザに渡される命令です。
ヒントを使用すると、通常はオプティマイザによって自動的に行われる決定をユーザーが行うことが可能になります。テスト環境または開発環境で、ヒントを使用して特定のアクセス・パスのパフォーマンスをテストできます。たとえば、ある問合せに対しては、特定の索引を選択する方がよい場合もあります。この場合は、次の例に示すように、ヒントを使用して、オプティマイザが適切な実行計画を使用するように指示できます。
SELECT /*+ INDEX (employees emp_department_ix) */
employee_id, department_id
FROM employees
WHERE department_id > 50;データベースでは、ヒントが使用されないことがあります。これは、ヒントに入力ミスや無効な引数があったり、ヒントが競合していたり、変換によってヒントが無効になることがあるためです。Oracle Database 19c以降では、計画の生成時に使用されたヒントまたは使用されなかったヒントに関するレポートを生成できます。
関連項目:
-
ヒントについてさらに学習するには、『Oracle Database SQL言語リファレンス』を参照してください
1.4.3 SQLチューニング・ツールへのユーザー・インタフェース
Cloud Controlは、データベース環境を集中管理するためのシステム管理ツールです。Cloud Controlは、ほとんどのチューニング・ツールにアクセスできます。
グラフィカル・コンソール、Oracle Management Server、Oracle Intelligent Agent、共通サービスおよび管理ツールを組み合せることで、Cloud Controlは、包括的なシステム管理プラットフォームを提供します。
コマンドライン・インタフェースを使用すると、すべてのSQLチューニング・ツールにアクセスできます。たとえば、DBMS_SQLTUNEパッケージは、SQLチューニング・アドバイザ用のコマンドライン・インタフェースです。
データベース管理およびチューニングのための最適なインタフェースとしてCloud Controlを使用することをお薦めします。コマンドライン・インタフェースのほうが特定の概念やタスクをよりわかりやすく説明できる場合、このマニュアルではコマンドラインの例を使用しています。ただし、その場合、チューニング・タスクには、タスクに関連付けられている主要なCloud Controlページへの参照が含まれます。
1.5 自動エラー軽減について
SQLコンパイル中にORA-00600エラーで失敗したSQL文については、データベースによって自動エラー軽減が試みられます。
ORA-00600は重大なエラーです。プロセスにおいて、低レベルの予期しない状態が発生したことを示します。解析フェーズでこのエラーでSQL文が失敗すると、自動エラー軽減によってSQL文がトラップされ、条件の解決が試行されます。解決策が見つかった場合は、データベースによってSQLパッチが生成されてSQL実行計画が調整されます。このパッチによって解析を正常に完了できるようになると、ORA-00600エラーは発生しなくなり、アプリケーションで例外は発生しなくなります。
自動エラー軽減の仕組み
これらの一連の例では、自動エラー軽減によってORA-00600エラーが透過的に修正される仕組みを説明します。
- 次のエラー条件を考えてみます。問合せが失敗し、致命的な例外が発生しました。
SQL> SELECT count(*) 2 FROM emp1 e1 3 WHERE ename = (select max(ename) from emp2 e2 where e2.empno = e1.empno) 4 AND empno = (select max(empno) from emp2 e2 where e2.empno = e1.empno) 5 AND job = (select max(job) from emp2 e2 where e2.empno = e1.empno); ERROR at line 3: ORA-00600: internal error code, arguments: [kkqctcqincf0], [0], [], [], [], [], [], [], [], [], [], [] - その後、セッションで自動エラー軽減がオンになります。
SQL> alter session set sql_error_mitigation = 'on'; Session altered. - 自動エラー軽減が有効で、エラーが正常に解決された場合、アプリケーションに例外が返されないため、ステップ1のORA-00600メッセージは表示されません。問合せは正常に実行されました。
SQL> SELECT count(*) 2 FROM emp1 e1 3 WHERE ename = (select max(ename) from emp2 e2 where e2.empno = e1.empno) 4 AND empno = (select max(empno) from emp2 e2 where e2.empno = e1.empno) 5 AND job = (select max(job) from emp2 e2 where e2.empno = e1.empno); COUNT(*) ---------- 999 - この問合せの実行計画を見ると、問合せを修復するためのSQLパッチが作成されたことが、下部にあるNoteセクションに表示されています。
SQL> SELECT * FROM table(dbms_xplan.display_cursor(sql_id=>'5426r24y45gz0',cursor_child_no=>1,format=>'basic +note')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ SELECT count(*) FROM emp1 e1 where ename = (select max(ename) FROM emp2 e2 WHERE e2.empno = e1.empno) AND empno = (select max(empno) FROM emp2 e2 WHERE e2.empno = e1.empno) AND job = (select max(job) FROM emp2 e2 WHERE e2.empno = e1.empno); Plan hash value: 1226419153 ------------------------------------------- | Id | Operation | Name | ------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | HASH JOIN | | | 3 | HASH JOIN | | | 4 | HASH JOIN | | | 5 | TABLE ACCESS FULL | EMP1 | | 6 | VIEW | VW_SQ_3 | | 7 | SORT GROUP BY | | | 8 | TABLE ACCESS FULL| EMP2 | | 9 | VIEW | VW_SQ_2 | | 10 | SORT GROUP BY | | | 11 | TABLE ACCESS FULL | EMP2 | | 12 | VIEW | VW_SQ_1 | | 13 | SORT GROUP BY | | | 14 | TABLE ACCESS FULL | EMP2 | ------------------------------------------- Note ----- - cpu costing is off (consider enabling it) - SQL patch "SYS_SQLPTCH_AUTO_dq7z4ydz3b2ug" used for this statement
ヒント:
DBA_SQL_PATCHESおよびDBA_SQL_ERROR_MITIGATIONSを問い合せると、パッチが適用されたSQLの問題の発生元およびタイプの詳細を取得できます。SQL> SELECT name,signature,origin FROM dba_sql_patches
2 /
NAME SIGNATURE ORIGIN
–---------------------------------------------------------------------------
SYS_SQLPTCH_AUTO_dq7z4ydz3b2ug 15789590553029872463 AUTO-FOREGROUND-REPAIR
SQL> SELECT m.sql_id, m.signature, m.problem_key, m.problem_type 2
FROM dba_sql_error_mitigations m;
SQL_ID SIGNATURE PROBLEM_KEY PROBLEM_TYPE
------------------------------------------------------------------------------------
5426r24y45gz0 15789590553029872463 ORA 600 [kkqctcqincf0] COMPILATION ERROR関連項目:
自動エラー軽減によるORA-00600の修復はアプリケーションに対して透過的ですが、プロセスの詳細情報を得て調べることができるビューがあります。
データベース・エラー・メッセージ・リファレンスに、Oracleのプログラム例外の内部エラー番号であるORA-00600についての説明があります。
Oracle Databaseリファレンス・マニュアルには、自動エラー軽減に関連する3つのビューが説明されています。- SQL_ERROR_MITIGATIONには、
SQL_ERROR_MITIGATION初期化パラメータのプロパティが記述されています。 - DBA_SQL_ERROR_MITIGATIONSには、自動エラー軽減によって実行された処理が示されています。SQL IDに基づいて、成功した各エラー軽減が記述されています。
MITIGATION_DETAILS列には、自動エラー軽減によって作成されたSQLパッチに関する情報が示されています。 - DBA_SQL_PATCHESには、生成されたSQLパッチの詳細(自動エラー軽減によって作成されたパッチおよびその他の情報)が示されています。自動エラー軽減によって作成されたパッチの
ORIGIN列値は、AUTO-FOREGROUND-REPAIRです。
アプリケーション・パッケージおよびタイプ・リファレンスに、SQL_ERROR_MITIGATION初期化パラメータについての説明があります。