前回のおさらい

前回、「TRUNCATEを邪魔する犯人は誰だ？(1/2)」では、 TRUNCATE を異常終了させている犯人を
たろーちゃんが突き止めたところで終わりました。

さて、犯人は一体誰だったのでしょうか？

TRUNCATEを異常終了させていた犯人

たろー「このログを見てくれ。」

私はAさんに、トレースログを見せることにしました。

 

 

 

 

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました
----- Current SQL Statement for this session (sql_id=a5vcafj4kq07d) -----
alter table "APP1"."TAB2" shrink space CHECK
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object line object
  handle number name
0x65650580 636 type body SYS.WRI$_ADV_OBJSPACE_TREND_T.SUB_EXECUTE.SHRINKABLE_SEGMENT
0x65650580 2304 type body SYS.WRI$_ADV_OBJSPACE_TREND_T.SUB_EXECUTE
0x6a33f900 739 package body SYS.PRVT_ADVISOR.COMMON_SUB_EXECUTE
0x6a33f900 3238 package body SYS.PRVT_ADVISOR.COMMON_EXECUTE_TASK
0x6a3c8638 262 package body SYS.DBMS_ADVISOR.EXECUTE_TASK
0x6a3c8638 207 package body SYS.DBMS_ADVISOR.EXECUTE_TASK

　 
Aさん「alter table “APP1”.”TAB2″ shrink space CHECKだと？？」
たろー「これが犯人だ。 Statspack レポートには、ALTER INDEX … SHRINK コマンドで発生する待機イベント『Wait for shrink lock』と、ALTER TABLE … SHRINK コマンドで発生する待機イベント『Wait for shrink lock2』が上位に来ていたからな。こいつが発行している ALTER TABLE … SHRINK SPACE CHECK が、それらの待機イベントを発生させていたんだろう。」
Aさん「発行してるのは誰なんだ？」
たろー「こいつだ。」

*** SERVICE NAME:(SYS$USERS) 2018-11-19 23:02:59.155
*** MODULE NAME:(DBMS_SCHEDULER) 2018-11-19 23:02:59.155
*** ACTION NAME:(ORA$AT_SA_OPT_SY_53) 2018-11-19 23:02:59.155

Aさん「DBMS_SCHEDULER？Oracle Database の中で、何かジョブが動いてるのか？」
たろー「そう。自動化メンテナンスタスクだ。」
Aさん「自動化メンテナンスタスク？統計情報を収集するやつだよな？」
たろー「自動化メンテナンスタスクは統計情報を収集するだけじゃないんだ。
他にも自動セグメントアドバイザや、SQLチューニングアドバイザってのが動いてる。
もっとも、この Oracle Database は Standard Edition だから、SQLチューニングアドバイザは動かない設定（無効）になっているけどな。」
Aさん「じゃあ犯人は…！」
たろー「そう。残りの自動セグメントアドバイザだ。」

*** ACTION NAME:(ORA$AT_SA_OPT_SY_53) 2018-11-19 23:02:59.155

たろー「このSAってのは、セグメントアドバイザの頭文字だ。」
Aさん「何者なんだ、そいつは？」
たろー「Aさんは『テーブルの断片化』って分かるか？」
Aさん「ああ。いわゆるフラグメンテーションだよな？
DELETEなどでレコードを削除すると、その領域が穴あきになったりする。」
たろー「そう。未使用領域があっちこちに出来てしまう現象だ。それに対し、セグメントアドバイザは『縮小』、『再編成』、『圧縮』といったアドバイスを提案する機能だ。」
Aさん「提案？」
たろー「ああ。このSQLの実行結果を見てくれ。」

SELECT tablespace_name, segment_owner, segment_name, segment_type, recommendations, c1
FROM table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'))
ORDER BY tablespace_name, segment_owner, segment_name, segment_type
;

TABLESPACE_NAME SEGMENT_OWNER SEGMENT_NAME SEGMENT_TYPE RECOMMENDATIONS                                                                                  C1

--------------- ------------- ------------ ------------ ------------------------------------------------------------------------------------------------ ---------------------------------------

USERS           APP1          TAB1         TABLE        表APP1.TAB1の行移動を有効にして縮小を実行してください。92266496バイトの節約が予測されるためです。 alter table "APP1"."TAB1" shrink space

USERS           APP1          TAB2         TABLE        表APP1.TAB2の行移動を有効にして縮小を実行してください。11526144バイトの節約が予測されるためです。 alter table "APP1"."TAB2" shrink space

　：：：

＜以下、略＞

たろー「これがセグメントアドバイザの結果だ。このように、領域に対するアドバイスをしてくれる。実行すればいいコマンドまで含めて。」
Aさん「うーん…ありがたいんだかどうか分からないな。この提案をするために、 TRUNCATE が異常終了しているのか？」
たろー「そうだ。」
Aさん「なら、ありがた迷惑だ。その自動セグメントアドバイザって停止出来るか？」
たろー「勿論出来るよ。」
Aさん「それなら、即刻停止してくれ。もう夜中に呼び出されるのは真っ平御免だ。」
たろー「わかった。」

select client_name,status from dba_autotask_client;

CLIENT_NAME STATUS
------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor              ENABLED
sql tuning advisor              DISABLED

BEGIN
     dbms_auto_task_admin.disable(
     client_name => 'auto space advisor',
     operation => NULL,
     window_name => NULL);
   END;
/

select client_name,status from dba_autotask_client;

CLIENT_NAME STATUS
------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor              DISABLED
sql tuning advisor              DISABLED

たろー「これで自動セグメントアドバイザは停止された。」

---

これ以降、この事象は再現しなくなり、Aさんが夜中に呼び出されることも無くなりました。

たろーちゃんの思い込み（先入観）

Aさん「１か月が経ったけど、あれから呼び出されることは無くなった。やっぱり自動セグメントアドバイザが犯人だったんだな。」
たろー「今回はすまなかったな。まさかアドバイザ関連の処理が業務テーブルをロックするなんて思いもしなかったからさ。てっきり他の業務アプリがテーブルをロックしているんだという思い込み（先入観）が俺にあったよ。」
Aさん「らしくないな、たろーちゃん。しっかりしてくれよ。」
たろー「ああ、気を付けるよ。…おっと、そうだ。仕掛けた罠を忘れないうちに取り外さないとな。」
Aさん「そのままにしといても、いいんじゃないか？」
たろー「いや、event設定は多少なりともパフォーマンスに影響を及ぼすんだ。元に戻しておいたほうがいい。」

SQL> alter system set events '54 trace name errorstack off';
SQL> alter system set events '54 trace name context off';

たろー「これでよしっと。」

ALTER TABLE … SHRINK SPACE CHECK

ALTER TABLE … SHRINK SPACE は、Oracle Database 10gR1 から使用可能なコマンドであり、テーブルの断片化をある程度解消することが出来ます。（行移行や行連鎖を完全に解消することは出来ません。）

マニュアルには「オンラインで実施可能である」と書かれていますが、実は注意が必要です。
確かに、ALTER TABLE … MOVE に比べるとオンラインで実行可能なのですが、ALTER TABLE … SHRINK SPACE は、

 ● データの縮小フェーズ
 ● エクステントの解放フェーズ

という２つのフェーズから成り立っています。

「データの縮小フェーズ」では行レベルのロックが取得されるだけなのですが、
「エクステントの解放フェーズ」では表全体のロックが取得されます。
その際、DMLはロックの解放を待ちますが、TRUNCATE などの DDL ではロックの解放を待たず、
即時にエラーとなります。

またDMLはロックの解放を待つとは言っても、例えばWEBシステム等の場合、
エンドユーザ様からのHTTPリクエストに対するレスポンスが長時間返せないことになります。
これでは、いくら「オンラインで実行可能」と言われても、そうおいそれとは実行出来ませんね。

更に処理時間の面では、ALTER TABLE … MOVE に比べると
ALTER TABLE … SHRINK SPACEの方が長く掛かります。
なので、表の再編成時間を短くしたい方はちゃんとしたメンテナンス時間を設けて、
ALTER TABLE … SHRINK SPACEよりもALTER TABLE … MOVEを使用したほうがよいでしょう。

 

 

 

さて。
今回登場した CHECK というオプションを御存知の方はいらっしゃるでしょうか？
このオプションはマニュアルにも記載されていません。

一体何を行うオプションなのでしょうか？

実はこのオプションは、対象の表に対してセグメントのタイプや属性(行移動の可否等)をチェックし、
セグメントの縮小が可能かどうか判断するオプションなのです。
その際、ALTER TABLE … SHRINKが可能かどうか検証しますが、
実際のセグメント縮小は実行されません。（詳細はMy Oracle Support のドキュメントID 1132163.1をご覧ください。）

---

今回の「心臓外科医の術式」いかがだったでしょうか？

Oracle Database では内部でいくつかのジョブが動いています。
そのジョブがときには業務アプリケーションの邪魔をすることがあるのです。
その原因を特定する、見事な「術式」だったのではないでしょうか？
次回も頑張りますので、応援よろしくお願い致します。