備忘録

SELECT FOR UPDATE対象が別セッションで更新され、条件に合わなくなったらどうなるか?

1.セッションA で SELECT FOR UPDATE
  副問い合わせで最大値を求めそれを取得。値が10,20とあったら、20のレコードをロックされる。
2.セッションB で同条件で SELECT FOR UPDATE
  同じく値20のレコードを待つ。
3.セッションA で、2.の条件に合わない
  20→0と値を変更しコミット。
4.セッションB ではどうなるか?

値10のレコードが取れるか?データなしとなるか?
結果は、データなしとなる。
まあ、考えればわかる。
10を返そうとするには、副問い合わせを再実行しないといけない。
下手すれば無限ループ。

以下、試した結果。


-- ↓準備
create table T ( C number);
insert into t values (10);
insert into t values (20);
commit;
-- ↑準備

-- ↓セッションA
select * from t
where c = (select max(c) from t)
for update;
-- ↑セッションA

-- ↓セッションB
SQL> select * from t
2 where c = (select max(c) from t)
3 for update;
-- ↑セッションB

-- ↓セッションA
update t set c=0 where c = 20;
commit;
-- ↑セッションA

-- ↓セッションB
レコードが選択されませんでした。
-- ↑セッションB

データ0件なのに検索が遅い

ブログに始めてのコメントがあったので、久しぶりに再会。

開発環境で0件なのにSELECTが遅いことがあったので、結果はほぼ想像できていたが調べてみた。

  1. V$SQLから遅いSQLのSQLIDを探す

  2. SELECT a.SQL_ID, a.SQL_TEXT, a.* FROM V$SQL a WHERE a.SQL_TEXT LIKE '%なんたら%';


  3. 実行計画を見る

  4. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('見つけたSQL_ID'));


  5. 使っているオブジェクトがぶっ壊れていないか調べる

  6. SELECT a.STATUS, a.* FROM ALL_OBJECTS a WHERE a.OBJECT_NAME = 'テーブル名なりインデックス名';


  7. セグメントのサイズを調べる
  8. SELECT TRUNC(a.BYTES/1024/1024/1024, 2) "サイズ(GB)", a.* FROM DBA_SEGMENTS a WHERE a.SEGMENT_NAME = 'テーブル名なりインデックス名';


    0件なのにデカイことがここでわかる。

無駄にデカイからFULL SCAN時にそのセグメントを舐め回さないといけないから遅かった。
なぜでかいか?マニュアルのこの辺りを見ると良いかと。

  • オンラインでセグメントを縮小する方法の詳細は、『Oracle Database管理者ガイド』を参照してください。

  • TRUNCATE TABLEの構文およびセマンティクスについては、『Oracle Database SQL言語リファレンス』を参照してください。


再編成かtruncateをせよってこと。
開発環境のユーザにDROP TABLE権限がなく、TRUNCATEができなかったからセグメントが広がりっぱなしだったんでしょう。
DROP TABLE権限をつけるのは躊躇するだろうから、TRUNCATE権限っていうのがあれば良いんだけどね。

outパラメータ

select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

set serveroutput on

CREATE TABLE OUT_PARAM (
pNum NUMBER(10) default 1
,pCHR CHAR(10) default '1'
,pVCR VARCHAR2(10) default '1'
,pDT DATE default sysdate
,pTS TIMESTAMP default systimestamp
,pNV NVARCHAR2(10) default '1'
,pNC NCHAR(120) default '1'
);

create or replace PACKAGE BODY OUT_PARAM_TEST as

TYPE T_OUT_PARRAM IS RECORD (
pNum NUMBER(10) default 1
,pCHR CHAR(10) default '1'
,pVCR VARCHAR2(10) default '1'
,pDT DATE default sysdate
,pTS TIMESTAMP default systimestamp
,pNV NVARCHAR2(10) default '1'
,pNC NCHAR(120) default '1'
);

PROCEDURE HOGE(
pInt OUT PLS_INTEGER
,pNum OUT NUMBER
,pCHR OUT CHAR
,pVCR OUT VARCHAR2
,pDT OUT DATE
,pTS OUT TIMESTAMP
,pNV OUT NVARCHAR2
,pNC OUT NCHAR
,pRT OUT OUT_PARAM%ROWTYPE
,pRT2 OUT OUT_PARAM%ROWTYPE
,pRT3 OUT OUT_PARAM%ROWTYPE
) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('pInt[' || pInt || ']');
DBMS_OUTPUT.PUT_LINE('pNum[' || pNum || ']');
DBMS_OUTPUT.PUT_LINE('pCHR[' || pCHR || ']');
DBMS_OUTPUT.PUT_LINE('pVCR[' || pVCR || ']');
DBMS_OUTPUT.PUT_LINE('pDT[' || pDT || ']');
DBMS_OUTPUT.PUT_LINE('pTS[' || pTS || ']');
DBMS_OUTPUT.PUT_LINE('pNV[' || pNV || ']');
DBMS_OUTPUT.PUT_LINE('pNC[' || pNC || ']');
DBMS_OUTPUT.PUT_LINE('pRT.pNum[' || pRT.pNum || ']');
DBMS_OUTPUT.PUT_LINE('pRT2.pNum[' || pRT2.pNum || ']');
DBMS_OUTPUT.PUT_LINE('pRT3.pNum[' || pRT3.pNum || ']');
END HOGE;


PROCEDURE TEST IS
pInt PLS_INTEGER := 1;
pNum NUMBER(10) := 1;
pCHR CHAR(10) := '1';
pVCR VARCHAR2(10) := '1';
pDT DATE := SYSDATE;
pTS TIMESTAMP := SYSTIMESTAMP;
pNV NVARCHAR2(10) := '1';
pNC NCHAR(120) := '1';
pRT OUT_PARAM%ROWTYPE;
pRT2 T_OUT_PARRAM;
TYPE tArray IS TABLE OF T_OUT_PARRAM INDEX BY BINARY_INTEGER;
pTA tArray;
BEGIN
pRT.pNum := '2';
pRT2.pNum := '3';
HOGE(
pInt
,pNum
,pCHR
,pVCR
,pDT
,pTS
,pNV
,pNC
,pRT
,pRT2
,pTA(0)
);
END TEST;

END OUT_PARAM_TEST;
/

exec OUT_PARAM_TEST.test;
pInt[]
pNum[]
pCHR[]
pVCR[]
pDT[]
pTS[]
pNV[]
pNC[]
pRT.pNum[]
pRT2.pNum[]
pRT3.pNum[]

PL/SQLプロシージャが正常に完了しました。

Intelligent Data Placement

Orale11g の ASM 新機能で、Intelligent Data Placement というのがあるのを知った。
HDD はディスクの内側より外側のほうが速いから、内側をCOLD、外側をHOTと定義して使い分けるという機能。
惜しむらくべきは、データファイル単位でしか出来ないこと。
⇒いちいちテーブルスペースを分けないと使えない。
もっとインテリジェントじゃないと使えないですね。同じテーブルスペースでも勝手に使い方を学習してデータを移動とかしてくれるのならいいのに。
SSDとかHDDとかごっちゃ混ぜで適当に振り分けてくれると何も考えなくていいのに。

【検証資料】Automatic Storage Management Intelligent Data Placementによるパフォーマンス・チューニング

仮想列にINSERT時に値設定

おら! オラ! Oracle どっぷり検証生活のOracle 11g検証 隠れた新機能検証 その4から SQL を借用し仮想列を持つテーブルを作って insert してみる。

SQL> insert into VTP_ADDRESS values('1','1',null);
insert into VTP_ADDRESS values('1','1',null)
*
行1でエラーが発生しました。:
ORA-54013: 仮想列ではINSERT操作は許可されていません



だめですね。無視してくれるとうれしかった。
PL/SQL の ROWTYPE で定義されたレコードを bulk insert したかったけどできない。
実体を作ってINSERTトリガーで値を入れた方が使い勝手がいい。

SQL> ed
1 declare
2 TYPE TYPE_ADDRESS IS RECORD (
3 KEN_NAME VTP_ADDRESS.KEN_NAME%TYPE
4 ,KEN_CODE VTP_ADDRESS.KEN_CODE%TYPE
5 );
6 TYPE tArray IS TABLE OF TYPE_ADDRESS INDEX BY PLS_INTEGER;
7 list tArray;
8 begin
9 FOR i IN 1..10 LOOP
10 list(i).KEN_NAME := '北海道';
11 list(i).KEN_CODE := '01';
12 END LOOP;
13 forall i in 1 .. list.count
14 insert into VTP_ADDRESS values list(i);
15* end;
SQL> /
insert into VTP_ADDRESS values list(i);
*
行14でエラーが発生しました。:
ORA-06550: 行14、列14:
PL/SQL: ORA-00947: 値の個数が不足しています。
ORA-06550: 行14、列2:
PL/SQL: SQL Statement ignored


1 declare
2 TYPE TYPE_ADDRESS IS RECORD (
3 KEN_NAME VTP_ADDRESS.KEN_NAME%TYPE
4 ,KEN_CODE VTP_ADDRESS.KEN_CODE%TYPE
5 ,AREA_CODE VTP_ADDRESS.AREA_CODE%TYPE
6 );
7 TYPE tArray IS TABLE OF TYPE_ADDRESS INDEX BY PLS_INTEGER;
8 list tArray;
9 begin
10 FOR i IN 1..10 LOOP
11 list(i).KEN_NAME := '北海道';
12 list(i).KEN_CODE := '01';
13 END LOOP;
14 forall i in 1 .. list.count
15 insert into VTP_ADDRESS values list(i);
16* end;
17 /
declare
*
行1でエラーが発生しました。:
ORA-54013: 仮想列ではINSERT操作は許可されていません
ORA-06512: 行14



20万件入れるときの性能は仮想列で実装した時には 28.28秒、トリガーの場合は 30.75秒 で誤差の範囲?なのでトリガーがいいか。参考までに、INSERTトリガーをはずしたら 20.76秒だった。
INSERTする部分を修正できるのなら、トリガーも無しでやった方が性能的にはより良い。
前のページ 次のページ

FC2Ad