TABLE ACCESS FULLしているSQLを探す
Oracleでパフォーマンス低下したり処理遅延が発生するようになった際の原因調査手段として、
V$SESSION_LONGOPSから手掛かりを探せる可能性がある。
V$SESSION_LONGOPS
V$SESSION_LONGOPSは、実行に6秒(絶対時間)より長くかかる様々な操作の状態を示します。現在これらの操作には、多くのバックアップおよびリカバリ機能、統計収集、問合せ実行、およびOracleリリースごとに追加される多くの操作が含まれます。
ここでは、TABLE ACCESS FULLしているSQLを探すケースを記載する。
■対象テーブルとSQL_IDの抽出
select count(sid) as cnt, sql_id, target, sql_plan_options from v$session_longops t where t.sql_plan_options = 'FULL' group by sql_id ,target,sql_plan_options order by cnt desc;
実行結果
CNT SQL_ID TARGET SQL_PLAN_OPTIONS
170 c1y7mg1xfgznb USER.TABLE_NAME1 FULL
20 bd22x5v9dx79k USER.TABLE_NAME1 FULL
対象テーブルが特定できた。SQL_IDから、対象のSQL文をv$sqltext から抽出する。
■SQL文の抽出
select sql_text from v$sqltext where sql_id = 'c1y7mg1xfgznb' order by piece;
対象のSQLも特定できた。実行計画を確認すれば、TABLE ACCESS FULLしていることがわかるはず。
今回は最も件数の多いSQLを確認しているが、処理の長いSQLを探すためにelapsed_secondsで絞込むのもありかと思われる。
あとはヒント文で解決するなり、インデックスを見直すなり、状況に合わせて対処していく。