Oracleインデックスの詳細理解(8):インデックスのモニタリング分析と最適化方法
生産環境では、次のことがわかります.
①インデックス表領域I/Oが非常に高い②「db file sequential read」待ちイベントも比較的高いという兆候は、データベースシステム全体、インデックスの読み書き操作が比較的多く、システムの主なボトルネックとなっている一般的な原因を示している.I/Oオーバーヘッドを増加④インデックス作成のポリシーミス、インデックスを移動するのは全テーブルスキャンのようで、1枚のテーブルフィールドは30個ですが、インデックスは50個もあります!?DBAとして、あなたはビジネスロジックを理解していないかもしれませんが、削除する勇気がなくて、どのように削除するべきか分かりません.どうすればいいですか?以下の2つの戦略を総合的に採用することを提案します:(1)原理に基づいてこのような状況を判断するには多くの複合インデックスが存在するに違いありません!複合インデックスの接頭辞性とオプション性の2つの原理に基づいて、この50個のインデックスの具体的なフィールドの分布状況を分析し、自分で合併、統合の判断を下す(2)Oracleインデックス監視特性を利用して、典型的なビジネスサイクルが始まる前に、実行する:ora@node1>alter index<インデックス名>monitoring usage;通常のビジネスサイクルが終了した後、次のことを行います.ora@node1>alter index<インデックス名>nomonitoring usage;次に、クエリー:ora@node1> select * from v$object_usage;このように、あなたはこの典型的な周期の中で、このインデックスがいったい役に立つことを知っています.たとえそうであっても、友情のヒントを2回、上の結論は必ずしも正しいとは限りません.①10 gは統計情報を収集する時にインデックスが監視され、これはSQL文の発生ではありません.11 gではこのようなことは起こりません②外部キーインデックスはメインテーブルのDML操作によって監視されることはありません.このインデックスが役に立たないから削除しないでください.しかし、ここでは、やはり問題があります.複雑なシステムでは、インデックスの数が膨大である可能性があります.では、私たちはどのように容疑者を選択し、監視範囲を減らすべきですか.1 library cacheデータの利用
library cacheでは、システム内のカーソルのクエリー・プランが格納されます(すべてではなく、library cacheサイズによって制限されます).
ビューv$sql_を介してplan、これらのデータをクエリーできます.これらのデータを使用すると、クエリー・プランに表示されるインデックスを除外できます.
②AWRデータ10 gを利用すると、AWRを利用してどのインデックスが使われているかを分析することができます
上記の方法を利用して、必ず使用されるindexの大部分をフィルタリングした後、総合的に応用し、不審なインデックスを選択して監視し、不要なインデックスを見つけて削除し、データベースのダイエットのためにインデックスフィールドに対してDML操作を頻繁に行い、インデックスに大量の破片をもたらし、インデックスの使用効率に大きく影響し、インデックスI/Oの増加をもたらした.では、インデックス破片の分析と整理をどのように行うのか.インデックスの断片化を監視する文は、analyze index<インデックス名>validate structure online; select name,(del_lf_rows_len/lf_rows_len)*100 from index_stats;ここで、インデックス断片化率(%)=(del_lf_rows_len/lf_rows_len)*100インデックス断片化率が20%を超える場合、Oracleではインデックス断片化が非常に深刻であると判断し、DBAはすべてのインデックス断片化率を検出するスクリプトを作成し、定期的に実行し、インデックス断片化率のモニタリングを維持することを推奨します.インデックスフラグメントの分析と整理は、DBAの日常的なメンテナンスの1つであるOracleのインデックスフラグメントの処理には、1インデックスalter index<インデックス名>rebuildの再構築、②圧縮インデックスalter index<インデックス名>coalesce;ただし、Rockyは、揮発性インデックスを毎週末または毎晩オンラインで再構築できるなど、定期的なインデックス再構築戦略を提案しています.
①インデックス表領域I/Oが非常に高い②「db file sequential read」待ちイベントも比較的高いという兆候は、データベースシステム全体、インデックスの読み書き操作が比較的多く、システムの主なボトルネックとなっている一般的な原因を示している.I/Oオーバーヘッドを増加④インデックス作成のポリシーミス、インデックスを移動するのは全テーブルスキャンのようで、1枚のテーブルフィールドは30個ですが、インデックスは50個もあります!?DBAとして、あなたはビジネスロジックを理解していないかもしれませんが、削除する勇気がなくて、どのように削除するべきか分かりません.どうすればいいですか?以下の2つの戦略を総合的に採用することを提案します:(1)原理に基づいてこのような状況を判断するには多くの複合インデックスが存在するに違いありません!複合インデックスの接頭辞性とオプション性の2つの原理に基づいて、この50個のインデックスの具体的なフィールドの分布状況を分析し、自分で合併、統合の判断を下す(2)Oracleインデックス監視特性を利用して、典型的なビジネスサイクルが始まる前に、実行する:ora@node1>alter index<インデックス名>monitoring usage;通常のビジネスサイクルが終了した後、次のことを行います.ora@node1>alter index<インデックス名>nomonitoring usage;次に、クエリー:ora@node1> select * from v$object_usage;このように、あなたはこの典型的な周期の中で、このインデックスがいったい役に立つことを知っています.たとえそうであっても、友情のヒントを2回、上の結論は必ずしも正しいとは限りません.①10 gは統計情報を収集する時にインデックスが監視され、これはSQL文の発生ではありません.11 gではこのようなことは起こりません②外部キーインデックスはメインテーブルのDML操作によって監視されることはありません.このインデックスが役に立たないから削除しないでください.しかし、ここでは、やはり問題があります.複雑なシステムでは、インデックスの数が膨大である可能性があります.では、私たちはどのように容疑者を選択し、監視範囲を減らすべきですか.1 library cacheデータの利用
library cacheでは、システム内のカーソルのクエリー・プランが格納されます(すべてではなく、library cacheサイズによって制限されます).
ビューv$sql_を介してplan、これらのデータをクエリーできます.これらのデータを使用すると、クエリー・プランに表示されるインデックスを除外できます.
select a.object_owner, a.object_name
from v$sql_plan a, v$sqlarea b
where a.sql_id = b.sql_id and a.object_type='INDEX' and
b.last_load_time > <START_AUDIT_DATE>②AWRデータ10 gを利用すると、AWRを利用してどのインデックスが使われているかを分析することができます
select b.object_owner, b.object_name
from dba_hist_snapshot a, dba_hist_sql_plan b, dba_hist_sqlstat c
where a.snap_id = c.snap_id and
b.sql_id=c.sql_id and
b.object_type = 'INDEX' and
a.startup_time > <START_AUDIT_DATE>上記の方法を利用して、必ず使用されるindexの大部分をフィルタリングした後、総合的に応用し、不審なインデックスを選択して監視し、不要なインデックスを見つけて削除し、データベースのダイエットのためにインデックスフィールドに対してDML操作を頻繁に行い、インデックスに大量の破片をもたらし、インデックスの使用効率に大きく影響し、インデックスI/Oの増加をもたらした.では、インデックス破片の分析と整理をどのように行うのか.インデックスの断片化を監視する文は、analyze index<インデックス名>validate structure online; select name,(del_lf_rows_len/lf_rows_len)*100 from index_stats;ここで、インデックス断片化率(%)=(del_lf_rows_len/lf_rows_len)*100インデックス断片化率が20%を超える場合、Oracleではインデックス断片化が非常に深刻であると判断し、DBAはすべてのインデックス断片化率を検出するスクリプトを作成し、定期的に実行し、インデックス断片化率のモニタリングを維持することを推奨します.インデックスフラグメントの分析と整理は、DBAの日常的なメンテナンスの1つであるOracleのインデックスフラグメントの処理には、1インデックスalter index<インデックス名>rebuildの再構築、②圧縮インデックスalter index<インデックス名>coalesce;ただし、Rockyは、揮発性インデックスを毎週末または毎晩オンラインで再構築できるなど、定期的なインデックス再構築戦略を提案しています.