Estimate of rollback completion time Tweet
さて、前回、おおよそのロールバック完了時刻を見積もる関数でも作ってみるか！ なんて言っていたが実際に作ってみた。
尚、今回は、Oracle SQL Developer 1.2 for MacOSXからWindows XP professional上のOracle10g R2 EEへ接続。
● 準備
まずは、テーブルの作成から。
SCOTT>
SCOTT> desc test
名前 NULL? 型
----------------------------------------- -------- ----------------------------
DATA VARCHAR2(4000)
SCOTT>
SCOTT>
SCOTT>
● データの登録
ロールバックに長時間を要するデータを登録する。（更新でも削除でも構わないが、この例ではINSERT文で。）
SCOTT> begin
2 for i in 1..400000 loop
3 insert into test values(lpad('x',4000,'x'));
4 end loop;
5 end;
6 /
PL/SQLプロシージャが正常に完了しました。
経過: 00:05:30.06
SCOTT>
SCOTT>
● ロールバック！
準備が整ったので、早速、ロールバック！！
SCOTT>
SCOTT> rollback;
以下、ロールバック処理中に別途起動した、SQL*plusより実行
========以下、ロールバック中、別途起動したSQL*plusから実行した内容==============
● おおよそのロールバック完了時刻を確認する。
自作関数estimateRollbackCompleteTime()を以下のようにスカラ関数として実行すれば、おおよそのロールバック完了日時を確認できる。尚、関数の引数は、ロールバック完了時刻を見積もりたいセッションのSIDとSERIAL#で事前にv$session、v$transactionを問い合わせて確認しておく。尚、今回は、dbms_lockパッケージの実行権限と、select any dictionaryシステム権限をSCOTTユーザへ付与し、estimateRollbackCompleteTime()関数を作成してある。
SCOTT> select estimateRollbackCompleteTime(146,91) as "Estimate Time",sysdate as now from dual;
Estimate Time NOW
------------------- -------------------
2007/08/02 16:29:46 2007/08/02 16:21:16
SCOTT>
● 以下、UNDOブロックのモニタリング。
ロールバックが進み、used_ublkの値が減少していくことが確認できる。ロールバックの完了時刻は、前述の自作関数で求めたおおよそのロールバック完了時間に近い。
環境などにも影響sれるのだが、予想時間を超えることもあるし、短くなる場合もあるので何度か実行してみるといいですね。または、関数を改造して、見積もりのベースとしている３０秒間の処理件数を１分間の処理件数に変更するなどして、精度を高めることは可能かもしれない。
ただ、いつ終わるか分からないロールバック処理時間のおおよその時間を知るにはこのままでも十分なのではないかと思う。
SYSTEM> r
1 select
2 s.sid,
3 s.serial#,
4 s.username,
5 t.used_ublk
6 ,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') as time
7 from
8 v$session s join v$transaction t
9 on t.addr = s.taddr
10 where
11* s.username = 'SCOTT'
SID SERIAL# USERNAME USED_UBLK TIME
---------- ---------- ---------- ---------- -------------------
146 91 SCOTT 1813 2007/08/02 16:19:13
経過: 00:00:00.01
SYSTEM> /
SID SERIAL# USERNAME USED_UBLK TIME
---------- ---------- ---------- ---------- -------------------
146 91 SCOTT 1594 2007/08/02 16:24:21
経過: 00:00:00.00
SYSTEM> /
SID SERIAL# USERNAME USED_UBLK TIME
---------- ---------- ---------- ---------- -------------------
146 91 SCOTT 882 2007/08/02 16:26:45
経過: 00:00:00.01
SYSTEM> /
SID SERIAL# USERNAME USED_UBLK TIME
---------- ---------- ---------- ---------- -------------------
146 91 SCOTT 747 2007/08/02 16:27:10
経過: 00:00:00.01
SYSTEM> /
SID SERIAL# USERNAME USED_UBLK TIME
---------- ---------- ---------- ---------- -------------------
146 91 SCOTT 668 2007/08/02 16:27:24
経過: 00:00:00.01
SYSTEM> /
SID SERIAL# USERNAME USED_UBLK TIME
---------- ---------- ---------- ---------- -------------------
146 91 SCOTT 28 2007/08/02 16:29:23
経過: 00:00:00.01
SYSTEM> /
レコードが選択されませんでした。
SYSTEM> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') as now from dual;
NOW
-------------------
2007/08/02 16:29:30
経過: 00:00:00.01
SYSTEM>
ということで、今回は、たまたま、見積もり時刻より早めに終わったようですね。ラッキー！===========ロールバック中に別途起動したSQL*Plusでのログ、ここまで==============
ロールバックが完了しました。
経過: 00:14:34.83
SCOTT>
以下、ロールバック完了時間の見積もり関数の例。
内容は単純で、２点間のused_ublk数の残量から単位時間あたりのロールバック処理時間をもとめ、その時点のused_ublk数を元にロールバックが完了するおおよその日時を算出しているだけ。FORループを利用しているのは、単に、明示カーソルのオープン／フェッチ／クローズと例外処理を書かずにすむから。特に必要性がなければ、このプログラミングスタイルが個人的には好き。（この辺りは、好みの問題だけど。）
CREATE OR REPLACE FUNCTION estimateRollbackCompleteTime
(
iSid IN NUMBER,
iSerial# IN NUMBER
)
RETURN DATE IS
C_INTERVAL CONSTANT PLS_INTEGER := 30;
vStartTimestamp TIMESTAMP;
vEndTimestamp TIMESTAMP;
vStartUsedUndoBlocks NUMBER;
vEndUsedUndoBlocks NUMBER;
vEstimatedSecs NUMBER;
vIsFound BOOLEAN;
CURSOR csr_undoblocks
(
pSid IN NUMBER,
pSerial# IN NUMBER
) IS
SELECT
s.username,
t.used_ublk,
SYSTIMESTAMP AS now
FROM
v$session s JOIN v$transaction t
ON t.addr = s.taddr
WHERE
s.sid = pSid
AND s.serial# = pSerial#;
BEGIN
vIsFound := FALSE;
FOR rUndoBlocks IN csr_undoBlocks(iSid, iSerial#) LOOP
vStartTimeStamp := rUndoBlocks.now;
vStartUsedUndoBlocks := rUndoBlocks.used_ublk;
vIsFound := TRUE;
END LOOP;
IF NOT vIsFound THEN
RETURN NULL;
ELSE
vIsFound := FALSE;
END IF;
DBMS_LOCK.SLEEP(C_INTERVAL);
FOR rUndoBlocks IN csr_undoBlocks(iSid, iSerial#) LOOP
vEndTimestamp := rUndoBlocks.now;
vEndUsedUndoBlocks := rUndoBlocks.used_ublk;
vIsFound := TRUE;
END LOOP;
IF NOT vIsFound
OR (vStartUsedUndoBlocks - vEndUsedUndoBlocks) <= 0
THEN
RETURN NULL;
ELSE
vEstimatedSecs :=
CEIL(vEndUsedUndoBlocks / (vStartUsedUndoBlocks - vEndUsedUndoBlocks)) * C_INTERVAL;
RETURN SYSDATE + NUMTODSINTERVAL(vEstimatedSecs, 'SECOND');
END IF;
END;
| 固定リンク | 0
コメント