UNION ALLを使ったMVIEWの制約について [SQL・DDL]

1.はじめに


 最近、MVIEWでUNION ALLを使った場合の制約について調査したので、備忘までメモを残しておきたい。
 MVIEWとは実体のあるビューであり、ある断面のデータを複製してビューをあたかもローカルの表のようにして使う機能である。MVIEWは大規模なテーブルを集計する処理において、VIEW経由でアクセスされる度に計算するのではなく、あらかじめ集計結果を実体として保持しておくことにより、参照時に高価な処理を何度も行うことを回避できるメリットがある。
 ソースとなる表が更新されることにより、レプリカであるMVIEWの更新が必要となるが、高速リフレッシュという機能を使うことにより、ソース側表の更新履歴(MLOGという)だけをMVIEWに反映することがで、差分更新が可能である。
 高速リフレッシュが可能なクエリには制約がある。一般的に、結合や集計処理を含む複雑なクエリになるほど制約を意識する必要がある。また、MVIEWの定義にはDBLINKを利用できるため、(複数の)リモートDBからDBLINK経由でMVIEWを構成することができるが、これにも制約がある。実現したいビューがMVIEWで構成できるかどうかは、上記の複雑な制約の網をくぐり抜ける必要があるため、実機での確認がかかせない。
 ここでは、スキーマ分割された表(定義は同一)を別データベースにMVIEWで1つにUNION ALLでまとめて参照したい、という要件について、実機でMVIEW の実現可否や運用上の制約事項を調べてみよう。。

2.検証モデル


 検証のモデルとして、下図のような構成を考える。
  • PDBは2つ(k3,k5)でその間にリモートMVIEW用のDBLINK(k5tok3)を構成する
  • MVIEWのソースとなるPDB(k3)には3つのスキーマ(a,b,c)を作成し、それぞれemp表を作成する。データは主キーが重複しないように入れる
  • EMP表には、それぞれ高速リフレッシュ用のMLOGを作成する
  • MVIEWのターゲットであるPDB(k5)に3つのEMP表をUNION ALLしたMVIEWを作成する(MF_EMP_UNION)

20230514_MVIEWUNION.jpg

 この構成で特筆すべき点は2つある。1つはMLOGにROWIDを含めることである。ROWIDはソース表の行を特定する物理アドレスであり、これを更新ログに記録する。もう一つはMVIEWのカラム定義に、ROWIDとユニオンマーカ(ユニオンのソースとなる表を一位に特定するカラム)を含めることである。
 以下に、手元のVirtual BoxのOracle19.11の環境を用いて、このモデルを実際に作成してみよう。まず表を作成する。以下の表定義をソースのPDBの3つのスキーマで実行する。
drop table emp cascade constraints;
drop table dept cascade constraints;
create table dept(  
  deptno     number(7,0),  
  dname      varchar2(14),  
  loc        varchar2(13),  
  constraint pk_dept primary key (deptno)  
);
create table emp(  
  empno    number(7,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(7,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(7,0),  
  constraint pk_emp primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
);
create index idx1_emp on emp ( deptno );

次にデータを挿入する。a、b、cのスキーマでempnoが100ずつずれるようにして挿入する。
conn b/b@k3
@creemp
insert into dept select
rownum+10 deptno,
dbms_random.string('u',14) dname,
dbms_random.string('u',13) loc
from dual connect by level <=10;
commit;
insert into emp select
rownum+100 empno,
dbms_random.string('u',10) ename,
dbms_random.string('u',9) job,
dbms_random.value(1,100)+100 mgr,
sysdate - dbms_random.value(1,5000) hiredate,
dbms_random.value(3000,9999) sal,
dbms_random.value(3000,9999) comm,
dbms_random.value(1,10)+10 deptno
from dual connect by level <=100;
commit;
exec dbms_stats.gather_table_stats('b','dept');
exec dbms_stats.gather_table_stats('b','emp');

conn c/c@k3
@creemp
insert into dept select
rownum+20 deptno,
dbms_random.string('u',14) dname,
dbms_random.string('u',13) loc
from dual connect by level <=10;
commit;
insert into emp select
rownum+200 empno,
dbms_random.string('u',10) ename,
dbms_random.string('u',9) job,
dbms_random.value(1,100)+200 mgr,
sysdate - dbms_random.value(1,5000) hiredate,
dbms_random.value(3000,9999) sal,
dbms_random.value(3000,9999) comm,
dbms_random.value(1,10)+20 deptno
from dual connect by level <=100;
commit;
exec dbms_stats.gather_table_stats('c','dept');
exec dbms_stats.gather_table_stats('c','emp');

 挿入された行を各表毎3行ずつサンプルで確認する。
SQL> select empno, ename from a.emp where rownum <= 3
  2  union all
  3  select empno, ename from b.emp where rownum <= 3
  4  union all
  5* select empno, ename from c.emp where rownum <= 3;

   EMPNO ENAME
________ _____________
       1 WANOZRAMAP
       2 GMREPEQFIN
       3 SKWBNXLCNF
     101 GVXUEOOQJM
     102 XGLASIZISR
     103 UVODELRDCI
     201 LKCFXEGPPE
     202 JKKFUPAHXM
     203 VGMJJTAVUN

9 rows selected.

 次にソース側PDB(k3)で各EMP表に対してMLOGを作成する。MLOGはROWIDを使って作成していることに留意する。
SQL> conn a/a@k3
Connected.

SQL> create materialized view log on emp with rowid including new values;

Materialized view log EMP created.

SQL> conn b/b@k3
Connected.

SQL> create materialized view log on emp with rowid including new values;

Materialized view log EMP created.

SQL> conn c/c@k3
Connected.

SQL> create materialized view log on emp with rowid including new values;

Materialized view log EMP created.

 以上でソース側の準備が完了したため、リモート側のPDB(k5)でMVIEWを作成する。高速リフレッシュを使うため、refresh fastを指定し、各selectのカラムにrowid列とユニオンマーカとなるmarker列を含めていることに留意する。
SQL> conn f/f@k5
Connected.
SQL> create materialized view mf_emp_union
  2  build immediate refresh fast on demand as
  3  select rowid "rowid", 1 marker, empno,ename,job,mgr,hiredate,sal,comm,deptno from a.emp@k5tok3
  4  union all
  5  select rowid "rowid", 2 marker, empno,ename,job,mgr,hiredate,sal,comm,deptno from b.emp@k5tok3
  6  union all
  7  select rowid "rowid", 3 marker, empno,ename,job,mgr,hiredate,sal,comm,deptno from c.emp@k5tok3
  8* ;
alter table mf_emp_union add constraint pk_mf_emp_union primary key (empno);

Materialized view MF_EMP_UNION created.

SQL> alter table mf_emp_union add constraint pk_mf_emp_union primary key (empno);

Table MF_EMP_UNION altered.

 MVIEWが作成され、同期されていることが確認する。
SQL> select empno,ename from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);

   EMPNO ENAME
________ _____________
       1 WANOZRAMAP
       2 GMREPEQFIN
       3 SKWBNXLCNF
     101 GVXUEOOQJM
     102 XGLASIZISR
     103 UVODELRDCI
     201 LKCFXEGPPE
     202 JKKFUPAHXM
     203 VGMJJTAVUN

9 rows selected.

 以上で準備は完了である。

3.検証


 ここでは以下3つのシナリオで高速リフレッシュの動作検証を行う。
 (1)c.EMPに全件UPDATE、c.EMPに1件INSERTを行い、高速リフレッシュ
 (2)c.EMPをalter table moveし、高速リフレッシュ
 (3)c.EMPをトランケートし、高速リフレッシュ
 期待される結果は、(1)は正常に同期される。(2)(3)は高速リフレッシュがエラーとなり、復旧に完全リフレッシュが必要となるはずである。以下、それぞれについて結果を確認していこう。

(1)c.EMPに全件UPDATE、c.EMPに1件INSERTを行い、高速リフレッシュ


 c.EMPにUPDATEと1件INSERTを行う。
SQL> conn c/c@k3
Connected.
SQL> update emp set ename='ZZZ';

100 rows updated.

SQL> commit;

Commit complete.

SQL> insert into emp values(997, 'KING', 'PRESIDENT', null,  sysdate, 5000, null, 21);

1 row inserted.

SQL> commit;

Commit complete.

 ターゲットPDB(k5)にて、高速リフレッシュを実行する。高速リフレッシュ後に、MVIEWに正しくenameの更新、および1件INSERTされたことが確認できる。
SQL> conn f/f@k5
Connected.
SQL> select empno,ename from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);

   EMPNO ENAME
________ _____________
       1 WANOZRAMAP
       2 GMREPEQFIN
       3 SKWBNXLCNF
     101 GVXUEOOQJM
     102 XGLASIZISR
     103 UVODELRDCI
     201 LKCFXEGPPE
     202 JKKFUPAHXM
     203 VGMJJTAVUN

9 rows selected.

SQL> select empno,ename from mf_emp_union where empno=997;

no rows selected
SQL> EXECUTE DBMS_MVIEW.REFRESH('mf_emp_union', 'f');

PL/SQL procedure successfully completed.

SQL> select empno,ename from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);

   EMPNO ENAME
________ _____________
       1 WANOZRAMAP
       2 GMREPEQFIN
       3 SKWBNXLCNF
     101 GVXUEOOQJM
     102 XGLASIZISR
     103 UVODELRDCI
     201 ZZZ
     202 ZZZ
     203 ZZZ

9 rows selected.

SQL> select empno,ename from mf_emp_union where empno=997;

   EMPNO ENAME
________ ________
     997 KING

SQL>

(2)c.EMPをalter table moveし、高速リフレッシュ


 ソースPDB(k3)のc.EMPをalter table moveし、ROWIDを全て更新する。合わせて索引もリビルドする。これは典型的な表の断片化解消のシナリオである。
SQL> conn c/c@k3
Connected.
SQL> select empno,ename,rowid from emp where empno in (1,2,3,101,102,103,201,202,203);

   EMPNO ENAME    ROWID
________ ________ _____________________
     201 ZZZ      AAASCEAASAAAFbtAAA
     202 ZZZ      AAASCEAASAAAFbtAAB
     203 ZZZ      AAASCEAASAAAFbtAAC

SQL> alter table emp move;
select empno,ename,rowid from emp where empno in (1,2,3,101,102,103,201,202,203);

Table EMP altered.

SQL> select empno,ename,rowid from emp where empno in (1,2,3,101,102,103,201,202,203);

   EMPNO ENAME    ROWID
________ ________ _____________________
     201 ZZZ      AAASCiAASAAAFbzAAB
     202 ZZZ      AAASCiAASAAAFbzAAC
     203 ZZZ      AAASCiAASAAAFbzAAD

select index_name,status from user_indexes where index_name like '%EMP%';
SQL> select index_name,status from user_indexes where index_name like '%EMP%';

INDEX_NAME              STATUS
_______________________ ___________
SYS_C_SNAP$_23PK_EMP    VALID
I_MLOG$_EMP             VALID
IDX1_EMP                UNUSABLE
PK_EMP                  UNUSABLE

alter index pk_emp rebuild;
SQL> alter index pk_emp rebuild;

Index PK_EMP altered.

SQL> alter index idx1_emp rebuild;

Index IDX1_EMP altered.

SQL> select index_name,status from user_indexes where index_name like '%EMP%';

INDEX_NAME              STATUS
_______________________ _________
SYS_C_SNAP$_23PK_EMP    VALID
PK_EMP                  VALID
IDX1_EMP                VALID
I_MLOG$_EMP             VALID

 ターゲットPDB(k5)にて、高速リフレッシュを実行する。高速リフレッシュはORA-12034エラーとなる。完全リフレッシュにより、正しい同期状態にすることができていることがわかる。
SQL> conn f/f@k5
Connected.
SQL> select empno,ename,rowid from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);

   EMPNO ENAME         ROWID
________ _____________ _____________________
       1 WANOZRAMAP    AAASBTAAWAAAAD7AAA
       2 GMREPEQFIN    AAASBTAAWAAAAD7AAB
       3 SKWBNXLCNF    AAASBTAAWAAAAD7AAC
     101 GVXUEOOQJM    AAASBTAAWAAAAD7ABk
     102 XGLASIZISR    AAASBTAAWAAAAD7ABl
     103 UVODELRDCI    AAASBTAAWAAAAD7ABm
     201 ZZZ           AAASBTAAWAAAAD9ACA
     202 ZZZ           AAASBTAAWAAAAD9ABk
     203 ZZZ           AAASBTAAWAAAAD9AC4

9 rows selected.

SQL> EXECUTE DBMS_MVIEW.REFRESH('mf_emp_union', 'f');  --★高速リフレッシュ

Error starting at line : 1 in command -
BEGIN DBMS_MVIEW.REFRESH('mf_emp_union', 'f'); END;
Error report -
ORA-12034: materialized view log on "C"."EMP" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at line 1
12034. 0000 -  "materialized view log on \"%s\".\"%s\" younger than last refresh"
*Cause:    The materialized view log was younger than the last refresh.
*Action:   A complete refresh is required before the next fast refresh.
SQL> EXECUTE DBMS_MVIEW.REFRESH('mf_emp_union', 'c');  --★完全リフレッシュ

PL/SQL procedure successfully completed.

SQL> select empno,ename,rowid from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);

   EMPNO ENAME         ROWID
________ _____________ _____________________
       1 WANOZRAMAP    AAASBTAAWAAAAD8ABo
       2 GMREPEQFIN    AAASBTAAWAAAAD8ABp
       3 SKWBNXLCNF    AAASBTAAWAAAAD8ABq
     101 GVXUEOOQJM    AAASBTAAWAAAAD8ABm
     102 XGLASIZISR    AAASBTAAWAAAAD8ABn
     103 UVODELRDCI    AAASBTAAWAAAAD9AAA
     201 ZZZ           AAASBTAAWAAAAD7ABr
     202 ZZZ           AAASBTAAWAAAAD7ABs
     203 ZZZ           AAASBTAAWAAAAD7ABt

9 rows selected.

(3)c.EMPをトランケートし、高速リフレッシュ


 ソースPDB(k3)のc.EMPをトランケートで全件削除する。
SQL> conn c/c@k3
Connected.
SQL> truncate table emp;

Table EMP truncated.

 ターゲットPDB(k5)にて、高速リフレッシュを実行する。高速リフレッシュはORA-12034エラーとなる。完全リフレッシュにより、正しい同期状態にすることができていることがわかる。
SQL> conn f/f@k5
Connected.
SQL> select empno,ename from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);

   EMPNO ENAME

       1 WANOZRAMAP
       2 GMREPEQFIN
       3 SKWBNXLCNF
     101 GVXUEOOQJM
     102 XGLASIZISR
     103 UVODELRDCI
     201 ZZZ
     202 ZZZ
     203 ZZZ

9 rows selected.

SQL> EXECUTE DBMS_MVIEW.REFRESH('mf_emp_union', 'f');  --★高速リフレッシュ

Error starting at line : 1 in command -
BEGIN DBMS_MVIEW.REFRESH('mf_emp_union', 'f'); END;
Error report -
ORA-12034: materialized view log on "C"."EMP" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at line 1
12034. 0000 -  "materialized view log on \"%s\".\"%s\" younger than last refresh"
*Cause:    The materialized view log was younger than the last refresh.
*Action:   A complete refresh is required before the next fast refresh.
SQL> EXECUTE DBMS_MVIEW.REFRESH('mf_emp_union', 'c');  --★完全リフレッシュ

PL/SQL procedure successfully completed.

SQL> select empno,ename from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);

   EMPNO ENAME
________ _____________
       1 WANOZRAMAP
       2 GMREPEQFIN
       3 SKWBNXLCNF
     101 GVXUEOOQJM
     102 XGLASIZISR
     103 UVODELRDCI

6 rows selected.

4.まとめ


 今回の調査、検証を踏まえ、明らかになったことはは以下の通り。
  • UNION ALLで複数表をまとめたMVIEWを作成することは可能
  • 高速リフレッシュを使うには、MLOGにROWIDを含め、かつ表の定義にROWIDおよびマーカーを追加する必要がある
  • ソース表でROWIDを変更する操作(例えば、ソース表の断片化解消のためのMOVEやインポートなど)をすると、高速リフレッシュはエラーとなる(完全リフレッシュが必要)
  • トランケートをすると、高速リフレッシュはエラーとなる(完全リフレッシュが必要)

 MLOGおよびMVIEWにROWIDを含める必要があるという制約は、UNION ALLを使うMVIEWではソースの行をPKだけでは特定できないからと思われる(複数のソース表の主キーの重複が想定されるため)。
 なお、今回検証結果は示していないが、MVIEW側でPKを定義すると、重複データが発生した場合にMVIEWのリフレッシュが一意制約違反で失敗するようにすることもできる。復旧のためには、重複データを特定しソース側で削除した後、完全リフレッシュが必要であることは言うまでもない。

参考


[1]Oracle Database データ・ウェアハウス・ガイド 19c

以上

コメント(0) 

非同期グローバル索引メンテナンスについて考える [SQL・DDL]

20221224_globalindex.jpg

 この記事は、JPOUG Advent Calendar 2022 24日目の記事です。昨日は、玉城 与清哉さんの「久しぶりにGrass Fishを触ってみたんご。」でした。

1.はじめに


 2019年にJPOUGでトランケートの話をしたときに、12cの非同期グローバル索引メンテナンスについて触れた(参考[1])。具体的には、クエリがメンテナンスが終わっていないグローバル索引を使ってどのように正しい結果を返すことができるかについて考察した(参考[2])。今回は、非同期に行われるグローバル索引メンテナンスについて考えてみたい。具体的には、以下の内容について述べる。

・非同期グローバル索引メンテナンスとは
・基本動作
・メンテナンスの内部挙動
・メンテナンスの性能チューニング

2.非同期グローバル索引メンテナンスとは


 非同期グローバル索引メンテナンスとは、パーティション・メンテナンス操作(DROP PARTITIONおよびTRUNCATE PARTITION等)を行った際、メタデータのみの索引メンテナンスを実行することにより、グローバル索引のメンテナンスを先送りする機能である(参考[3])。11gまでは、DDL実行時にグローバル索引のメンテナンス処理の完了を待つ必要があったが、12cからは当該機能により瞬時にコマンドは終了し、デフォルト2amに動作するメンテナンスジョブの中で非同期にメンテナンスが行われるようになった。
 実際のグローバル索引のメンテナンスは、以下の方法で実行できる。通常何も考えなければ(1)のジョブでメンテナンスが行われるが、手動で実行したい場合は(2)の方法で同じことが行える。本稿では、これらの挙動について深堀していく。

(1)SYS.PMO_DEFERRED_GIDX_MAINT_JOBジョブ
 ・・・索引クリーンアップの自動スケジューラジョブ(毎日2:00に実行)
(2)DBMS_SCHEDULER.RUN_JOB
 ・・・上記(1)を手動で起動
(3)DBMS_PART.CLEANUP_GIDXプロシージャ
 ・・・必要な索引すべて行う、または特定の索引だけ指定してクリーンアップを行う
(4)ALTER INDEX REBUILD [PARTITION]コマンド
 ・・・索引全体をリビルドする(従来の方法)
(5)ALTER INDEX [PARTITION] COALESCE CLEANUPコマンド
 ・・・索引のorphanedエントリをクリーンアップする

3.基本動作


 簡単なモデルを作成して上記挙動を実機確認してみよう。Oracleのバージョンは19.11である。emp表のhiredateで年月の12のレンジパーティションを用意し、100万件のデータを積む。hiredateは202201~202212までの日付をランダムに振り、パーティション毎にほぼ均等の件数になるようにする。連番である主キーempnoにグローバル索引(pk_emp)を付与する。また、1~10万までのランダム値の入れたdeptnoにもグローバル索引(idx1_emp)を付与する。
drop table emp cascade constraints;

create table emp(  
  empno    number(7,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(7,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(7,0),  
  constraint pk_emp primary key (empno)
)
partition by range (hiredate) (
 partition p202201 values less than (to_date('20220201','YYYYMMDD')),
 partition p202202 values less than (to_date('20220301','YYYYMMDD')),
 partition p202203 values less than (to_date('20220401','YYYYMMDD')),
 partition p202204 values less than (to_date('20220501','YYYYMMDD')),
 partition p202205 values less than (to_date('20220601','YYYYMMDD')),
 partition p202206 values less than (to_date('20220701','YYYYMMDD')),
 partition p202207 values less than (to_date('20220801','YYYYMMDD')),
 partition p202208 values less than (to_date('20220901','YYYYMMDD')),
 partition p202209 values less than (to_date('20221001','YYYYMMDD')),
 partition p202210 values less than (to_date('20221101','YYYYMMDD')),
 partition p202211 values less than (to_date('20221201','YYYYMMDD')),
 partition p202212 values less than (to_date('20230101','YYYYMMDD')),
 partition pmax values less than (MAXVALUE)
);
create index idx1_emp on emp(deptno);

insert into emp select
rownum,
dbms_random.string('u',10) ename,
dbms_random.string('u',9) job,
dbms_random.value(1,1000000) mgr,
to_date('20230101','YYYYMMDD') - dbms_random.value(1,365) hiredate,
dbms_random.value(3000,9999) sal,
dbms_random.value(3000,9999) comm,
dbms_random.value(1,100000) deptno
from dual connect by level <=1000000;
commit;

exec dbms_stats.gather_table_stats('SCOTT','EMP');

 上記の結果、以下のようなデータが準備できる。
SQL> select count(*) from emp;

   COUNT(*)
___________
    1000000

SQL> select table_name, partition_name, num_rows from user_tab_partitions where
table_name='EMP' order by 1,2;

TABLE_NAME    PARTITION_NAME       NUM_ROWS
_____________ _________________ ___________
EMP           P202201                 85460
EMP           P202202                 77154
EMP           P202203                 84523
EMP           P202204                 82145
EMP           P202205                 84870
EMP           P202206                 82519
EMP           P202207                 85198
EMP           P202208                 85426
EMP           P202209                 82506
EMP           P202210                 85048
EMP           P202211                 82403
EMP           P202212                 82748
EMP           PMAX                        0
13 rows selected.

 この状態で、グローバル索引の状態を確認する。ORPHAND_ENTRIESがNOとなっているので、索引の状態はきれいな状態(メンテナンスは不要)である。
select index_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries 
from user_indexes i, user_segments s where i.index_name = s.segment_name
and     i.table_name='EMP';

INDEX_NAME       NUM_ROWS    BLOCKS    LEAF_BLOCKS STATUS    ORPHANED_ENTRIES   
_____________ ___________ _________ ______________ _________ ___________________
IDX1_EMP          1000000      3968           3859 VALID     NO                 
PK_EMP            1000000      2560           2375 VALID     NO                 

 ここで、P202201のパーティションにトランケートを発行する。グローバル索引の状態を確認すると、以下のようにSTATUSはVALID(利用可能)な状態だが、ORPHANED_ENTRIES列がYESとなり、これらのグローバル索引は非同期メンテナンス対象となっていることがわかる。なお、トランケートはupdate indexesをつけないと索引がUNUSABLEになってしまうので注意する。
SQL> alter table emp truncate partition p202201 update indexes;
Table EMP truncated.
INDEX_NAME       NUM_ROWS    BLOCKS    LEAF_BLOCKS STATUS    ORPHANED_ENTRIES   
_____________ ___________ _________ ______________ _________ ___________________
IDX1_EMP          1000000      3968           3859 VALID     YES                
PK_EMP            1000000      2560           2375 VALID     YES                

 この状態で、これらの索引アクセスすると、以下のように、TBL$OR$IDX$PART$NUM関数のフィルタがプレディケートに付与される。Oracleはこのフィルタにより実体のない行に対するアクセスを排除している(このフィルタについては参考[1][2]参照されたい)。
select count(*) from emp where deptno <1000                                     
Plan hash value: 2417120451                                                     
-----------------------------------------------                                 
| Id  | Operation         | Name     | E-Rows |                                 
-----------------------------------------------                                 
|   0 | SELECT STATEMENT  |          |        |                                 
|   1 |  SORT AGGREGATE   |          |      1 |                                 
|*  2 |   INDEX RANGE SCAN| IDX1_EMP |   9990 |                                 
-----------------------------------------------                                 
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
   2 - access("DEPTNO"<1000)                                                    
       filter(TBL$OR$IDX$PART$NUM(,0,8,0,"EMP".ROWID)=1)★

 ここでsysで以下のように手動でグローバル索引メンテナンスジョブを実行する。
SQL> execute dbms_scheduler.run_job('SYS.PMO_DEFERRED_GIDX_MAINT_JOB',true)
PL/SQL procedure successfully completed.

 グローバル索引の状態を確認すると、ORPHANED_ENTRIES列がNOとなっており、非同期グローバル索引のメンテナンスが完了したことがわかる。トランケートしたのに、NUM_ROWSが変わっていないのは統計情報が古いからである。統計情報を取得し直すと、トランケートの結果が反映される。
INDEX_NAME       NUM_ROWS    BLOCKS    LEAF_BLOCKS STATUS    ORPHANED_ENTRIES   
_____________ ___________ _________ ______________ _________ ___________________
IDX1_EMP          1000000      3968           3859 VALID     NO                 
PK_EMP            1000000      2560           2375 VALID     NO                 

SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');

INDEX_NAME       NUM_ROWS    BLOCKS    LEAF_BLOCKS STATUS    ORPHANED_ENTRIES   
_____________ ___________ _________ ______________ _________ ___________________
IDX1_EMP           914540      3968           2566 VALID     NO                 
PK_EMP             914540      2560           2375 VALID     NO                 

 ここで特筆すべき目は、いずれの索引もメンテナンス前後でBLOCKSは変わっていないことである。つまり、非同期グローバル索引メンテナンスに索引のサイズを減らす効果は認められない。
 また、メンテナンス後にIDX1_EMPのLEAF_BLOCKSは減っているということから、非同期グローバル索引メンテナンスの実体は、リーフブロック数を減らすことであることがうかがえる。一方、PK_EMPの方はLEAF_BLOCKS数に変化がない。恐らくこれは索引の構成に依存した仕様の挙動と思われる。1リーフブロックの格納効率がPK_EMPの方が良い(PK_EMPが421件/BLKに対してIDX1_EMPが259件/BLK)ため、同じ件数を削除したとしても、解放できるリーフブロック数が少ないためと考えられる。PK_EMPの格納効率が良いのは、格納データによる違い(1~100万の連番であるのに対し、IDX1_EMPは1~10万のランダム値)によるものだろう。トランケートするパーティション数を増やしていけば、PK_EMPもLEAF_BLOCKSが減る挙動は確認できる。
 この状態で、これらの索引アクセスすると、まだTBL$OR$IDX$PART$NUM関数のフィルタが残っていたが、共有プールをフラッシュして再度クエリを実行すると、このフィルタが無くなった。恐らくハードパースが走ったタイミングでプレディケートが見直されるのだろう。

4.メンテナンスの内部挙動


 第一の疑問は、PMO_DEFERRED_GIDX_MAINT_JOBなるものが何をしているのか、ということである。AWRを見ると、以下のように、PMO_DEFERRED_GIDX_MAINT_JOBの実行にあわせて、dbms_part.cleanup_gidx_jobが実行されていることがわかる。処理時間から、PMO_DEFERRED_GIDX_MAINT_JOBジョブの内部で呼び出されていると考えられる。
        Elapsed                  Elapsed Time
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
             0.6              1          0.64   21.7   87.9     .1 50n3y8wjbndhp
Module: sqlplus@localhost.localdomain (TNS V1-V3)
   PDB: ORCL
BEGIN dbms_scheduler.run_job('SYS.PMO_DEFERRED_GIDX_MAINT_JOB',true); END; ★

             0.6              1          0.63   21.5   87.9     .1 6frynf9v1m655
Module: DBMS_SCHEDULER
   PDB: ORCL
call dbms_part.cleanup_gidx_job ( :0,:1 ) ★

 実際に実行されている処理を確認するため、以下のように10046トレース(SQLトレース)を取得する。
alter session set events '10046 trace name context forever, level 8';
execute dbms_scheduler.run_job('SYS.PMO_DEFERRED_GIDX_MAINT_JOB',true)
alter session set events '10046 trace name context off';

 結果を確認すると、2つのグローバル索引に対し、以下のALTER INDEX COALESCE CLEANUPが発行されていることがわかる。COALESCE CLEANUPはマニュアルに記載の通り、索引のorphanedエントリをクリーンアップする処理を行うもの、とあるので、メンテナンスジョブの実体はこのコマンドと考えられる。
ALTER INDEX "SCOTT"."PK_EMP" COALESCE CLEANUP PARALLEL 1
ALTER INDEX "SCOTT"."IDX1_EMP" COALESCE CLEANUP PARALLEL 1

 実際、以下のようにP202202をトランケートした後に上記コマンドを直接実行しても、グローバル索引のORPHANED_ENTRIESがNOになることが確認できた。
SQL> alter table emp truncate partition p202202 update indexes;
Table EMP truncated.
SQL> ALTER INDEX "SCOTT"."PK_EMP" COALESCE CLEANUP PARALLEL 1;
Index "SCOTT"."PK_EMP" altered.
SQL> ALTER INDEX "SCOTT"."IDX1_EMP" COALESCE CLEANUP PARALLEL 1;
Index "SCOTT"."IDX1_EMP" altered.
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');
PL/SQL procedure successfully completed.

SQL> select index_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
  2  from user_indexes i, user_segments s where i.index_name = s.segment_name
  3* and     i.table_name='EMP';

INDEX_NAME       NUM_ROWS    BLOCKS    LEAF_BLOCKS STATUS    ORPHANED_ENTRIES   
_____________ ___________ _________ ______________ _________ ___________________
IDX1_EMP           837386      3968           2545 VALID     NO                 
PK_EMP             837386      2560           2374 VALID     NO                 

 次の疑問は、このCOALESCE CLEANUPコマンドにより何が行われるのかである。それを確認するため、dbms_space.space_usageで、グローバル索引メンテナンス前後の状態を確認してみよう。検証用テーブルの状態は初期化し、(a)truncateの実行前、(b)truncate実行後(p202201)、(c)COALESCE CLEANUP実行後の3つの状態を比較する。dbms_spaceの確認は、参考[4]のスクリプトを利用させて頂いた。Freespaceはブロックの空き状況で、左の数字がブロック数、右がバイト数(1ブロック8Kbyte)を表す。
(a)(b)
                              :          idx1_emp                   :          pk_emp
Unformatted                   :           62 /          507,904     :          110 /          901,120
Freespace 1 (  0 -  25% free) :            0 /                0     :            0 /                0
Freespace 2 ( 25 -  50% free) :           53 /          434,176★② :           15 /          122,880
Freespace 3 ( 50 -  75% free) :            0 /                0     :            0 /                0
Freespace 4 ( 75 - 100% free) :            0 /                0     :            0 /                0
Full                          :        3,783 /       30,990,336★① :        2,387 /       19,554,304☆

(c)
                              :          idx1_emp                   :          pk_emp
Unformatted                   :           62 /          507,904     :          110 /          901,120
Freespace 1 (  0 -  25% free) :            0 /                0     :            0 /                0
Freespace 2 ( 25 -  50% free) :        1,237 /       10,133,504★② :           15 /          122,880
Freespace 3 ( 50 -  75% free) :            0 /                0     :            0 /                0
Freespace 4 ( 75 - 100% free) :            0 /                0     :            0 /                0
Full                          :        2,599 /       21,291,008★① :        2,387 /       19,554,304☆

 この結果を見ると、(a)と(b)の結果が一致していることから、トランケートの実行は全くグローバル索引のブロック数に影響を与えていない。また、(c)の結果から、idx1_empグローバル索引においては、メンテナンスによりFullで格納されているブロック数が減り(★①)、その変わりFreespace2がその分増えている(★②)。トータルブロック数の変化はないことから、COALESCE CLEANUPは、トランケートにより不要となった索引のリーフブロックの状態を空き状態に変更し再利用可能とするものの、未使用ブロックを解放し表領域に返却するようなことは行わない、ということである。そして、PK_EMPの方はメンテナンス後に☆部分に全く変化がないことは、リーフブロックの状態変化がないことが原因であることがわかる。
 次に、この状態で、(d)索引のリビルドを行って、ブロックの状態の変化を確認してみよう。結果、Fullのブロック数はほぼ同じだが、Freespace2に割り当てられていたブロックが解放されている(★③)ことがわかる。つまり、索引のリビルドにより索引トータルのブロックを減らすことができている。当然、この状態では、TBL$OR$IDX$PART$NUM関数のフィルタは発生しない。
alter index idx1_emp rebuild online;
alter index pk_emp rebuild online;

(d)
                              :          idx1_emp                   :          pk_emp
Unformatted                   :            0 /                0     :            0 /                0
Freespace 1 (  0 -  25% free) :            0 /                0     :            0 /                0
Freespace 2 ( 25 -  50% free) :            1 /            8,192★③ :            1 /            8,192★③
Freespace 3 ( 50 -  75% free) :            0 /                0     :            0 /                0
Freespace 4 ( 75 - 100% free) :            0 /                0     :            0 /                0
Full                          :        2,542 /       20,824,064     :        2,424 /       19,857,408

5.メンテナンスの性能チューニング


 ここまで、非同期グローバル索引メンテナンスの基本的な動作について述べた。ここでは、非同期グローバル索引メンテナンスのメンテナンス時間について考えてみたい。
 まず、非同期グローバル索引メンテナンスの処理時間は、メンテナンス対象が大きければ大きいほど処理時間がかかる傾向がある、ということご確認したい。そのため、トランケート対象のパーティションを1,2,4,8と増やして、メンテナンス時間(coalesce cleanup)を比較してみた。結果は以下の通り、処理時間はトランケートしたパーティション数(=~レコード数)に対して線形に増加することがわかる。 
202212_globalind_1.jpg

 上記のグラフで興味深いのは、どちらも索引メンテナンス時間の傾きは同じに見えることである。あたかも、COALESCE CLEANUPの処理は(1)切片にあたる処理時間(索引によって異なる)と(2)件数に応じて増加する処理時間(索引によって変わらない)、に分割されており、件数が十分に大きいときは(2)が支配的になるように見える。つまり、索引メンテナンス時間は、この傾きと、削除件数と、索引の数をパラメータとして、おおよそ推測できるのではないか、と考えられる。
 さて、次に、パラレル度のチューニングができるかを確認する。8パーティション削除のケースで、パラレル度を1、2、4にして処理時間を確認した。手元の環境はIntel i7-10510U(1.8GHz)コア4(論理プロセッサ8)で、OracleVMで4コアまで有効にしているので、CPU_COUNT=4となっている。キャッシュの効果を配慮し、各測定前にバッファキャッシュをフラッシュする。
ALTER INDEX "SCOTT"."PK_EMP" COALESCE CLEANUP PARALLEL <パラレル度>;
ALTER INDEX "SCOTT"."IDX1_EMP" COALESCE CLEANUP PARALLEL <パラレル度>;

202212_globalind_2.jpg

 上記結果から、パラレル度を上げることによる性能向上が確認できた。ただし、それ以上パラレル度を上げても線形には性能向上する訳ではなく、その効果はグローバル索引によって異なることがわかる。
 なお、実運用上は、個別の索引にCOALESCE CLEANUPを直接実行せず、メンテナンスジョブから実行することが想定される。PMO_DEFERRED_GIDX_MAINT_JOBに対して、以下のようにデフォルトのパラレル度を設定することができる(参考[5])。10046トレースを取得すると、きちんとPARALLEL 2が付与されていることが確認できる。
exec dbms_scheduler.set_job_argument_value('SYS.PMO_DEFERRED_GIDX_MAINT_JOB', 'parallel','2')
---10046トレースに現れたDDL
ALTER INDEX "SCOTT"."PK_EMP" COALESCE CLEANUP PARALLEL 2
ALTER INDEX "SCOTT"."IDX1_EMP" COALESCE CLEANUP PARALLEL 2

6.まとめ


 本稿では、非同期グローバル索引メンテナンス、特にそのメンテナンスジョブについて述べた。主に以下の知見が得られた。

・メンテナンスジョブ(PMO_DEFERRED_GIDX_MAINT_JOB)の実体はALTER INDEX COALESCE CLEANUPである
・メンテナンスにより、リーフブロック数は減るが、グローバル索引のサイズが減ることはない(領域を減らすにはリビルドが必要)
・メンテナンスすれば、TBL$OR$IDX$PART$NUM関数のフィルタはクエリから無くなるが、直ちに無くなる訳ではない(ハードパース等の契機が必要)
・メンテナンス時間は削除するパーティションの件数およびグローバル索引数に依存し、パラレル度を設定することで処理時間のチューニングが可能

 上記から、グローバル索引の肥大化を防ぐ意味では、基本的にメンテナンスジョブの実行はこまめに実施することが望ましく、多くの場合デフォルトの日次実行のままで良いだろう。メンテナンスジョブが長時間化する場合はパラレル度を上げることを検討し、それでも肥大化傾向が続くようであれば、索引個別に定期的なリビルド運用を検討する必要があるだろう。
 一度に大量のパーティションを削除する必要がある場合は、メンテナンスジョブが長時間化するリスクがある。1度に大量のパーティションを削除するより、こまめに少しずつ削除するアプローチの方が、メンテナンスがサービス影響を与えるリスクを減らすことができるだろう。
 
 明日は最終日ですね。

◇参考


[1]:トランケートについて知っておくべきこと, JPOUG講演スライド, 2019
[2]:トランケートの非同期グローバル索引メンテナンスについて
[3]:VLDBおよびパーティショニング・ガイド 19c, 4.3.2 パーティションを削除および切り捨てる非同期グローバル索引メンテナンス
[4]:dbms_spaceの確認用スクリプト, Orache Scratchpad, Jonathan Lewis
[5]:Bug 24515918 - PMO_DEFERRED_GIDX_MAINT_JOB causes TX contention on busy tables and runs serially (ドキュメントID 24515918.8)

以上

コメント(0) 

Oracle XML DB 19cの簡易検証 [SQL・DDL]

 ゴールデンウィーク後半は良い天気に恵まれ、行楽地からのUターンラッシュが激しい様子を見ると、コロナのことなど忘れてしまいそうである。今回は、以前から気になっていたXML-Typeを使って手元の環境で簡単な検証を行ってみた。恥ずかしながら今までOracleでXMLを使ったことがないので、具体的に何ができるのか実機でさわりの動作を確認したレベルである。

1.XML DBとは


 Oracle XML DBは、XMLデータの格納、生成、アクセス、検索、検証、変換、拡張および索引付けなどの高パフォーマンスの処理に関連する一連のOracleの技術を指す。XML DBを使うために、XMLをOracleに格納するが、その際に使うカラム型をXML-Typeという。今回はXML DBのチュートリアル(参考[4]のpurchase orderのモデル)を参考に、手元の環境(19.11)で簡単なXML-Type型を持つ表を作成し、検索、索引付与をして、実行計画を確認してみた。

2.検証準備


 検証準備として、表の作成、データの準備等を行う。

(1)表の作成


 表の作成はpurchaseorder表のpo_detailカラム型にXMLTYPEを指定し、STORE ASでBINARY XMLを指定した。ここでは深く考えずにチュートリアルに従うこととする。実際に作成された型を見るとSYS.XMLTYPE STORAGE BINARYとなっている。
CREATE TABLE purchaseorder (
po_number NUMBER,
po_details XMLTYPE
)
XMLTYPE COLUMN po_details STORE AS BINARY XML ;

SQL> desc purchaseorder
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PO_NUMBER                                          NUMBER
 PO_DETAILS                                         SYS.XMLTYPE STORAGE BINARY

実はこの状態でuser_tab_colsで確認すると、内部的には隠しカラムとしてSYS_NC00003$のような名前のBLOBが作成されているため、おそらく実体としてはこのBLOBにXMLが格納され、表に出ているカラムはBLOBへのロケータなのかもしれない。
SQL> select table_name, column_name, data_type, hidden_column from user_tab_cols where table_name = 'PURCHASEORDER' order by column_id;

TABLE_NAME           COLUMN_NAME          DATA_TYPE            HID
-------------------- -------------------- -------------------- ---
PURCHASEORDER        PO_NUMBER            NUMBER               NO
PURCHASEORDER        SYS_NC00003$         BLOB                 YES
PURCHASEORDER        PO_DETAILS           XMLTYPE              NO

(2)データの準備


以下のPL/SQLを用いて初期データを作成する。po_detailsカラムはPurchaseOrderをルートとするXMLをPL/SQLで生成して格納する。データ件数は100件である。統計情報も取得しておく。なお、このPL/SQLは、参考[4]のチュートリアルの初期データ作成のサンプルを少し修正したものである。
BEGIN
    FOR i IN 1..100 LOOP
        INSERT INTO purchaseorder
        VALUES      (i,'<PurchaseOrder><Reference>ACABRIO-'
                     ||i
                     ||'PDT</Reference><Actions><Action><User>ACABRIO-'
                     ||i
                     ||
        '</User></Action></Actions><Rejection/><Requestor>A. Cabrio '
                     || i
                     ||'</Requestor><User>ACABRIO-'
                     ||i
                     ||'</User><CostCenter>A'
                     ||i
                     ||'</CostCenter><ShippingInstructions><name>A. Cabrio '
                     ||i
                     ||'</name><Address><street>'
                     ||i
                     ||' Sporting Green Centre, Science Club, building '
                     ||i
                     ||', Magdalen</street><city>SFO-'
                     ||i
                     ||
'</city><state>CA</state><zipCode>99236</zipCode><country>United States of America</country></Address><telephone>269-'
||i
||
'-4036</telephone></ShippingInstructions><SpecialInstructions>Priority Overnight</SpecialInstructions><LineItems><LineItem ItemNumber="1"><Part Description="Face to Face: First Seven Years" UnitPrice="19.95">'
||i
||'</Part><Quantity>'
||i
||
'</Quantity></LineItem><LineItem ItemNumber="2"><Part Description="Runaway" UnitPrice="27.95">'
||i
||'</Part><Quantity>'
||i
||
'</Quantity></LineItem><LineItem ItemNumber="3"><Part Description="Founding Fathers: Men Who Shaped" UnitPrice="19.95">'
||i
||'</Part><Quantity>'
||i
||'</Quantity></LineItem></LineItems></PurchaseOrder>');
END LOOP;
COMMIT;
END;
/  
-- gathering statistics
exec dbms_stats.gather_table_stats('SCOTT','PURCHASEORDER');

以上で基本的な準備は完了である。

3.XML問い合わせ動作検証


(1)検索~XMLドキュメント数確認


 XMLドキュメント数を確認するSQLはいくつかあるが、以下はその一つである。通常はselect count(*) from purchaseorderとするところだが、ここではXML-Typeに格納されたXMLのPurchaseOrderの構造の数をカウントしているようだ。実際にテストはしていないが、おそらく1カラムに複数のPurchaseOrderが含まれている場合はその分も計上するものと思われる。
SELECT Count(*)
FROM   purchaseorder p,
       XMLTABLE('for $r in /PurchaseOrder return $r' passing p.po_details) t;  

  COUNT(*)
----------
       100

 実行計画は以下の通りフルスキャンである。SQLの形から2回フルスキャンしてしまうのか?と思ったが、1回で大丈夫のようだ。良く見ると、Id:2のfilter条件は隠しカラムであるSYS_NC0003$に対して行われているので、XMLの実体はこちらに格納されているのは間違いなさそうである。何となく、BLOBカラムに格納されているXMLからSYS_MAKEXML関数でPurchaseOrderの構造を取り出し、EXISTNODE関数の結果が1となるカラムを条件としてフィルタしているように見える。
Execution Plan
----------------------------------------------------------
Plan hash value: 3002203368

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |  2002 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |               |     1 |  2002 |            |          |
|*  2 |   TABLE ACCESS FULL| PURCHASEORDER |     1 |  2002 |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(EXISTSNODE(SYS_MAKEXML(0,"P"."SYS_NC00003$" /*+ LOB_BY_VALUE
              */ ),'/PurchaseOrder')=1)

(2)検索~XMLExistsによるフィルター操作


 ReferenceがACABRIO-1PDTを満たす行のXMLをXMLExistsで抽出する。Passing句でXMLTypeカラムをバインド変数として渡すことができる。
SELECT p.po_number,p.po_details "XML"
FROM purchaseorder p
WHERE XMLExists (
 '$PO/PurchaseOrder[Reference=$REF]'
 PASSING p.po_details as "PO", 'ACABRIO-1PDT' as "REF");

 PO_NUMBER XML
---------- ------------------------------------------------------------------------------------------
         1 <PurchaseOrder>
             <Reference>ACABRIO-1PDT</Reference>
             <Actions>
               <Action>
                 <User>ACABRIO-1</User>
               </Action>
             </Actions>
             <Rejection/>
             <Requestor>A. Cabrio 1</Requestor>
             <User>ACABRIO-1</User>
             <CostCenter>A1</CostCenter>
             <ShippingInstructions>
               <name>A. Cabrio 1</name>
               <Address>
                 <street>1 Sporting Green Centre, Science Club, building 1, Magdalen</street>
                 <city>SFO-1</city>
                 <state>CA</state>
                 <zipCode>99236</zipCode>
                 <country>United States of America</country>
               </Address>
               <telephone>269-1-4036</telephone>
             </ShippingInstructions>
             <SpecialInstructions>Priority Overnight</SpecialInstructions>
             <LineItems>
               <LineItem ItemNumber="1">
                 <Part Description="Face to Face: First Seven Years" UnitPrice="19.95">1</Part>
                 <Quantity>1</Quantity>
               </LineItem>
               <LineItem ItemNumber="2">
                 <Part Description="Runaway" UnitPrice="27.95">1</Part>
                 <Quantity>1</Quantity>
               </LineItem>
               <LineItem ItemNumber="3">
                 <Part Description="Founding Fathers: Men Who Shaped" UnitPrice="19.95">1</Part>
                 <Quantity>1</Quantity>
               </LineItem>
             </LineItems>
           </PurchaseOrder>

実行計画を見ると表のフルスキャンの後にNL SEMIでXPATH EVALUATIONが走っていることがわかる。正直これが具体的にどのような動作をしているのかは想像できないが、Predicate InformationからId:5のfilterでXMLExisitsのReferenceに対するフィルタ処理をしているように見える。カラムC_01$は内部的にReferenceを意味するカラムなのだろう。Id:4のXPATH EVALUATIONは何をしているのかわからないが、もしかするとXMLの構造からこのC_01$を含む表構造を作り出す処理なのかもしれないが、想像の域を出ない。また、Id:1のfilterについては、Id:5でフィルタできればそれ以上フィルタは不要ではないかと思うのだが、これも理解できない。
Execution Plan
----------------------------------------------------------
Plan hash value: 144453041

-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |    32 | 64480 |    21   (0)| 00:00:01 |
|*  1 |  FILTER             |               |       |       |            |          |
|   2 |   TABLE ACCESS FULL | PURCHASEORDER |   100 |   196K|     5   (0)| 00:00:01 |
|   3 |   NESTED LOOPS SEMI |               |     2 |     8 |     5   (0)| 00:00:01 |
|   4 |    XPATH EVALUATION |               |       |       |            |          |
|*  5 |    XPATH EVALUATION |               |       |       |            |          |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ )
   5 - filter("P1"."C_01$"='ACABRIO-1PDT')

Note
-----
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)


(3)検索~XMLQueryによる部分抽出操作


 (2)の条件同様にReferenceがACABRIO-1PDTを満たす行のXMLから、さらにXMLQueryでShippingInstructionsのパスを抽出する。問い合わせは以下のように、得られた行のpo_detailsに対してXMLQueryを用いてShippingInstructionsを取り出している。
SELECT p.po_number, XMLQuery (
 '$PO/PurchaseOrder/ShippingInstructions'
 PASSING p.po_details as "PO"
 returning content) XML
FROM purchaseorder p
WHERE XMLExists (
 '$PO/PurchaseOrder[Reference=$REF]'
 PASSING p.po_details as "PO", 'ACABRIO-1PDT' as "REF");

 PO_NUMBER XML
---------- ------------------------------------------------------------------------------------------
         1 <ShippingInstructions>
             <name>A. Cabrio 1</name>
             <Address>
               <street>1 Sporting Green Centre, Science Club, building 1, Magdalen</street>
               <city>SFO-1</city>
               <state>CA</state>
               <zipCode>99236</zipCode>
               <country>United States of America</country>
             </Address>
             <telephone>269-1-4036</telephone>
           </ShippingInstructions>


実行計画は以下の通り、上記(2)と全く同じである。当たり前といえばそうかもしれないが、得られた結果セットに対するXMLQueryは特段実行計画に影響は与えないようである。
Execution Plan
----------------------------------------------------------
Plan hash value: 144453041

-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |    32 | 64480 |    21   (0)| 00:00:01 |
|*  1 |  FILTER             |               |       |       |            |          |
|   2 |   TABLE ACCESS FULL | PURCHASEORDER |   100 |   196K|     5   (0)| 00:00:01 |
|   3 |   NESTED LOOPS SEMI |               |     2 |     8 |     5   (0)| 00:00:01 |
|   4 |    XPATH EVALUATION |               |       |       |            |          |
|*  5 |    XPATH EVALUATION |               |       |       |            |          |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ )
   5 - filter("P1"."C_01$"='ACABRIO-1PDT')

Note
-----
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)


4.XML索引付与


これまではXMLへの問い合わせは全てフルスキャンで行っていた。今回のようにデータが100件であればいざ知らず、実際は数百万件のレコードにたいして問い合わせを行うような状況は容易に想定される。このため、索引を用いた検索ができることは非常に重要である。ここでは実際に索引を付与して、その索引がXMLの問い合わせにて使われることを確認してみよう。

全文検索用の準備


索引を作成する前に、セクショングループとストレージプリファレンスを作成する。ここはOracle Textによる全文検索機能を利用しているため、それぞれの詳細はOracle Textのマニュアルを確認する必要があるが、作成方法は参考[1]の「6.4.1 XML検索索引の作成と使用」を参考にした。特筆すべきは、セクショングループにPATH_SECTION_GROUPを指定し、XML_ENAMBEをT(True)にすることである。AUTO_SECTION_GROUPやXML_SECTION_GROUPを指定した場合、索引は作成できるが、実際に問い合わせを行うと、ORA-18177: XQuery full text expression ...のエラーが出てしまう。
-- create a section group and a preference
BEGIN
  CTX_DDL.create_section_group('XQFT', 'PATH_SECTION_GROUP');
  CTX_DDL.set_sec_grp_attr('XQFT', 'XML_ENABLE', 'T');

  CTX_DDL.create_preference('STORAGE_PREFS', 'BASIC_STORAGE');
  CTX_DDL.set_attribute('STORAGE_PREFS',
                        'D_TABLE_CLAUSE',
                        'TABLESPACE USERS
                         LOB(DOC) STORE AS SECUREFILE 
                         (TABLESPACE USERS NOCOMPRESS CACHE)');
  CTX_DDL.set_attribute('STORAGE_PREFS',
                        'I_TABLE_CLAUSE',
                        'TABLESPACE USERS
                         LOB(TOKEN_INFO) STORE AS SECUREFILE
                         (TABLESPACE USERS NOCOMPRESS CACHE)');
END;
/

次に、XML Typeカラムに対し索引を付与する。実際は日本語を扱う場合はlexerや、更新に伴い索引のメンテを行うSYNC(ON COMMIT)等の考慮が必要ではないかと思うが、ここではチュートリアルの例に従い索引を作成する。
CREATE INDEX purchaseorder_xqft_idx
ON purchaseorder (po_details)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS (
'storage STORAGE_PREFS section group XQFT'
) ;

以上でテキスト索引の付与は終わりである。

(1)テキスト索引を用いた検証


XML列から、住所の通り(street)に"Magdalen"と"80"がAND条件で含まれる列を抽出する。ftandは全文検索用の演算子でFull Text ANDの意味である。結果を見ると確かにstreetに、80 Sporting Green Centre, Science Club, building 80, Magdalenが入っていることが確認できる。
SELECT p.po_number,po_details
FROM purchaseorder p
WHERE XMLExists(
 '$P/PurchaseOrder/ShippingInstructions/Address/street[. contains text "Magdalen" ftand "80" ]'
 PASSING p.po_details as "P");

 PO_NUMBER PO_DETAILS
---------- ------------------------------------------------------------------------------------------
        80 <PurchaseOrder>
             <Reference>ACABRIO-80PDT</Reference>
             <Actions>
               <Action>
                 <User>ACABRIO-80</User>
               </Action>
             </Actions>
             <Rejection/>
             <Requestor>A. Cabrio 80</Requestor>
             <User>ACABRIO-80</User>
             <CostCenter>A80</CostCenter>
             <ShippingInstructions>
               <name>A. Cabrio 80</name>
               <Address>
                 <street>80 Sporting Green Centre, Science Club, building 80, Magdalen</street>
                 <city>SFO-80</city>
                 <state>CA</state>
                 <zipCode>99236</zipCode>
                 <country>United States of America</country>
               </Address>
               <telephone>269-80-4036</telephone>
             </ShippingInstructions>
             <SpecialInstructions>Priority Overnight</SpecialInstructions>
             <LineItems>
               <LineItem ItemNumber="1">
                 <Part Description="Face to Face: First Seven Years" UnitPrice="19.95">80</Part>
                 <Quantity>80</Quantity>
               </LineItem>
               <LineItem ItemNumber="2">
                 <Part Description="Runaway" UnitPrice="27.95">80</Part>
                 <Quantity>80</Quantity>
               </LineItem>
               <LineItem ItemNumber="3">
                 <Part Description="Founding Fathers: Men Who Shaped" UnitPrice="19.95">80</Part>
                 <Quantity>80</Quantity>
               </LineItem>
             </LineItems>
           </PurchaseOrder>


実行計画は以下の通り、テキスト索引(DOMAIN INDEX)が使われていることがわかる。Id:2のPredicate Informationを見ると、accessとなっており索引が使われていることがわかる。また、ここでCONTAINS関数によって、SYS_MAKEXMLで隠しカラムであるBLOBのSYS_NC00003$からstreetパスを取り出し、検索条件としたキーワードが含まれるかを>0で確認している様子がうかがえる。
Execution Plan
----------------------------------------------------------
Plan hash value: 4054188112

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |     1 |   482 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PURCHASEORDER          |     1 |   482 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | PURCHASEORDER_XQFT_IDX |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"(SYS_MAKEXML(0,"SYS_NC00003$"),'( ( {Magdalen} ) and ( {80} ) ) INPATH
              (/PurchaseOrder/ShippingInstructions/Address/street)')>0)

なお、上記のテキスト索引がある状態であっても、3-(2)のXMLExistsによる問い合わせはそのままではフルスキャンになってしまう。テキスト索引を使うためには、以下のようにcontainsを用いた形に修正する必要がある。
SELECT p.po_number,p.po_details "XML"
FROM purchaseorder p
WHERE XMLExists (
 '$PO/PurchaseOrder/Reference[. contains text "ACABRIO-1PDT" ]'
 PASSING p.po_details as "PO");

Execution Plan
----------------------------------------------------------
Plan hash value: 4054188112

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |     1 |   482 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PURCHASEORDER          |     1 |   482 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | PURCHASEORDER_XQFT_IDX |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"(SYS_MAKEXML(0,"SYS_NC00003$"),'{ACABRIO-1PDT} INPATH
              (/PurchaseOrder/Reference)')>0)

5.おわりに


 本稿では、簡単なXML-Typeテーブルを作成し、検索、索引付与をして、実行計画を確認した。
 XML-Type型のカラムにXMLデータを格納すると、XMLExists関数で行をフィルタリングしたり、XMLQuery関数で特定のXMLの構造(パス)を取り出せることが確認できた。また、テキスト索引を付与することで、containsを用いた全文検索を複数の文字列をANDで含むなどで抽出することができることが確認できた。
 今回はデータ件数が100件と多くないため、性能の観点では確認していない。XMLデータ作成のPL/SQLのデータ件数を変更することで、テキスト索引の性能的な効果を確認することができるだろうが、このあたりは今後の課題としたい。

参考


[1]Oracle XML DB 開発者ガイド 19c
https://docs.oracle.com/cd/F19136_01/adxdb/index.html

[2]Oracle XML DB (OTN)
https://www.oracle.com/database/technologies/appdev/xmldb.html

[3]An Introduction to Oracle XML DB in Oracle Database 19c and 21c Technical Overview
https://www.oracle.com/a/tech/docs/technical-resources/xmldb-tech-overview-2022.pdf

[4]Oracle XML DB : Storing and Processing XML Documents
https://livesql.oracle.com/apex/livesql/file/tutorial_HE5NRRMNBOHLLKRLZJU0VNRCB.html


コメント(0) 

Oracle Text 19c簡易検証 [SQL・DDL]

 ロシアのウクライナ侵攻はいまだ収束の道が見えない中、日本はゴールデンウィークである。この休みの中での個人的な宿題の1つに、Oracle Textを使ってみる、というのがあったので、手元のVirtual Box環境19c(19.11)で簡単に試してみた。

1.Oracle Textとは


 Oracle Textとは、全文検索を行うための機能である。例えば%FOO%といった条件で検索する場合、仮にそのカラムにB*Tree索引が張られていても、索引は利用できない。Oracle Textでは、全文検索用のテキスト索引を作成することができ、部分一致検索においても、この索引を利用することにより高速にクエリを返却することができる。
 Oracle Textは、Oracle Database本体のライセンスだけで利用できる機能のため、追加のオプション・ライセンスは必要ない。

2.Oracle Textの検証


 EMP表を作成し、ENAME列に対して、通常のB*Tree索引を付与する場合と、Oracle Textによる索引を付与する場合で、検索性能がどのように変わるかを確認する。

(1)環境準備


 まず環境準備をする。SCOTTユーザにOracle Textを利用するのに必要な権限CTXAPPを付与する。実行はsysdbaで実施する。
grant ctxapp to scott

次にSCOTTユーザでテスト用のEMP表を作成する。
drop table emp cascade constraints;

create table emp(  
  empno    number(7,0),  
  ename    varchar2(11),  
  job      varchar2(9),  
  mgr      number(7,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(7,0),  
  constraint pk_emp primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
);

続いて、索引を作成する。まずは通常のB*Tree索引である。ENAME列にEMP_IDX1を付与する。
create index emp_idx1 on emp(ename);

次にテキスト索引を作成する。以下のように利用するレクサをJAPANESE_VGRAM_LEXERとし、
ENAME列にEMP_IDX2を付与する。
exec ctx_ddl.drop_preference('jvl_lexer');
exec ctx_ddl.create_preference('jvl_lexer','JAPANESE_VGRAM_LEXER');
create index emp_idx2 on emp(ename) indextype is ctxsys.context parameters ('lexer jvl_lexer SYNC(ON COMMIT)');

EMP表にデータを100万件インサートし、コミットする。なお、手元の環境でインサートは1分23秒、コミットは16秒かかった。通常のコミットは瞬時に終わるため、このコミットにこれだけ時間がかかったのは、テキスト索引のメンテナンス(作成)のオーバーヘッドと思われる。
insert into emp select
rownum,
dbms_random.string('u',3)||' '||dbms_random.string('u',3)||' '||dbms_random.string('u',3) ename,
dbms_random.string('u',9) job,
dbms_random.value(1,1000000) mgr,
sysdate - dbms_random.value(1,5000) hiredate,
dbms_random.value(3000,9999) sal,
dbms_random.value(3000,9999) comm,
dbms_random.value(1,100000) deptno
from dual connect by level <=1000000;
commit;

ENAME列は、空白をはさんで以下のように3文字ずつのデータになるようにした。
SQL> select empno, ename from emp where rownum < 5;

     EMPNO ENAME
---------- -----------
       495 ROT FHC LUH
       496 XPI BUU LLM
       497 KUL LNM PQI
       498 IAO ICN UWB

以上で準備は完了である。

(2)検証(部分一致検索)


ENAME列に対する部分一致検索を実行する。まずはLIKEで%FOO%のように検索する。結果151行である。
SQL> select empno, ename from emp where ename like '%FOO%' order by ename;

     EMPNO ENAME
---------- -----------
    764193 ACD FOO IEB
    779286 ADX FOO FGT
    629631 AFG SAT FOO
...
    230238 ZTX FOO FUH
     90691 ZVA FOO PNM
    509267 ZYB FOO ALI

151 rows selected.

Elapsed: 00:00:00.15

実行計画は以下の通りB*Tree索引は使われずにフルスキャンになる。
Execution Plan
----------------------------------------------------------
Plan hash value: 150391907

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100 |  2000 |  2470   (1)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |   100 |  2000 |  2470   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |   100 |  2000 |  2469   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ENAME" IS NOT NULL AND "ENAME" LIKE '%FOO%')

次に、テキスト索引を用いるように、CONTAINS関数を用いて検索する。結果は151件と、LIKEを使った場合と一致していることがわかる。また、処理時間(Elapsed)はテキスト検索の方が確かに速い。
SQL> select empno, ename from emp where contains (ename, 'FOO') >0 order by ename;

     EMPNO ENAME
---------- -----------
    764193 ACD FOO IEB
    779286 ADX FOO FGT
    629631 AFG SAT FOO
...
    230238 ZTX FOO FUH
     90691 ZVA FOO PNM
    509267 ZYB FOO ALI

151 rows selected.

Elapsed: 00:00:00.01

実行計画は以下の通りEMP_IDX2のテキスト索引(DOMAIN INDEXと表示される)が使われていることがわかる。
Execution Plan
----------------------------------------------------------
Plan hash value: 61694877

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   147 |  4704 |    57   (2)| 00:00:01 |
|   1 |  SORT ORDER BY               |          |   147 |  4704 |    57   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP      |   147 |  4704 |    56   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | EMP_IDX2 |       |       |    25   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("ENAME",'FOO')>0)

(3)検証(前方・後方一致検索)


通常のLIKEによる検索では以下のように前方一致検ができる。
SQL> select empno, ename from emp where ename like 'FOO%';

     EMPNO ENAME
---------- -----------
    725740 FOO AFL UHM
    221838 FOO BFO VNG
    862953 FOO BSA ENH
...
50 rows selected.

Elapsed: 00:00:00.01

実行計画は以下の通り、EMP_IDX1のB*Tree索引が使われる。実行時間も高速である。
Execution Plan
----------------------------------------------------------
Plan hash value: 3621794058

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     4 |    80 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |     4 |    80 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_IDX1 |     4 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ENAME" LIKE 'FOO%')
       filter("ENAME" LIKE 'FOO%')

同様に後方一致検索も可能である。

SQL> select empno, ename from emp where ename like '%FOO';

     EMPNO ENAME
---------- -----------
     19344 VOY GXC FOO
     42350 CXU TAB FOO
     53848 IOW ZUV FOO
...
39 rows selected.

Elapsed: 00:00:00.09

実行計画は、フルスキャンになる。このため、索引検索よりは時間はかかっていることがわかる。
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  2000 |  2469   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |   100 |  2000 |  2469   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME" IS NOT NULL AND "ENAME" LIKE '%FOO')


同様のことをcontains関数を使ってやろうとしても、いずれもcontains (ename, 'FOO')>0の条件と同じ151件が返ってきてLIKEと同じ結果は得られなかった。
select empno, ename from emp where contains (ename, 'FOO%') >0 order by ename;
select empno, ename from emp where contains (ename, '%FOO') >0 order by ename;

 これは、contains関数は単語単位に文字列を分割されたテキスト索引に対して、文字列比較をするため、今回のテストデータのように、ENAME列が3文字の単語で構成される形だとFOO%や%FOOのような条件指定はFOOと同じ意味になってしまうからと思われる。逆に、LIKEと同じように前方一致や後方一致で検索したい、という要件に対しては、containsでどう実現するのかはわからなかった。
 試しに%FO%という文字列を含む条件で検索したところ、LIKEとcontainsで件数の差分が認められた。LIKEではヒットするが、containsではヒットしない条件があるようだが、今回は時間の都合でその原因の確認まではできなかった。
SQL> select count(*) from emp where ename like '%FO%';

  COUNT(*)
----------
      8753
Elapsed: 00:00:00.10
SQL> select count(*) from emp where contains (ename, '%FO%') >0;

  COUNT(*)
----------
      8588
Elapsed: 00:00:00.03

3.おわりに


 本稿では、Oracle Textによるテキスト索引を用いる例を記載した。手元の環境で100万件のEMP表に対し、ENAME列に対する検索を行い、CONTAINS関数を利用することで、テキスト索引が使われることが確認できた。今回の条件で、フルスキャンに対して、テキスト索引の性能上の効果が認められた。
 以下は、今回いくつか検証していて疑問に思ったことである。今後の備忘として残しておく。
・元表の更新(COMMIT)に伴って索引をメンテナスするオーバーヘッド
・日本語での動作確認(文字列の一部に特定の文字列が含まれるかをcontainsで検索する)
・containsでの前方一致・後方一致検索の方法
・%FO%の件数差分の原因

 なお、今回Oracle Textについて調べるにあたり、参考にした、今後の参考になりそうな資料へのリンクを記載しておく。[1]は製品マニュアル(19c)である。読みにくいが、基本は押さえておく必要がある。[2]は日本オラクルの製品紹介資料である。全体的に製品カットで機能がまとめられているので、マニュアルを読むよりざっくりと機能概要を知ることができる。特に今回は、p.22にcontainsでは英語では%を使っても良いが日本語はNG、という話しが記載されている点、参考になった。[3]はOracleAceのナイル・フィリップ氏のスイスオラクルグループ(SOUG)での講演である。英語だが、動画で動きが確認できる点、参考になると思う。

参考


[1]Oracle Textマニュアル
https://docs.oracle.com/cd/F19136_01/ccref/index.html
[2]Oracle Text 詳細解説
https://www.oracle.com/jp/a/tech/docs/technical-resources/oracletext-ver12.pdf
[3]"Oracle Text : A Powerful Tool for Oracle Developers" by Niall Mc Phillips
https://www.youtube.com/watch?v=rfMhlKuJozI

コメント(0) 

DBLINKを使ったときのLOBの制約について [SQL・DDL]

20211222_DBLINKLOB.jpg


 この記事は JPOUG Advent Calendar 2021 - Adventar 22日目の記事です。21日目は羽田 久美子さんの記事「Oracle Data Safeでの監査データの取得」でした。羽田さんといえば、先日db tech showcaseでご講演もされており、益々ご活躍の様子、嬉しく思いました。

 今年を振り返ると、春に4年弱担当していたシステムの移行が終わったこと、新しい案件で本格的にOCIを触り始めることになったことなど、いくつか環境の変化がありました。また、先月、Oracle Groudbrakers APACで19c移行の性能についてオンライン講演できる機会を頂きました。CFPへの投稿にあたりHiroshi Sekiguchiさんにご協力頂き感謝しております。この場を借りてお礼申し上げます。講演のリプレイはしばらくの間こちら(Oracle 19c移行の性能検討ポイント)に公開されていますので、ご興味のある方は御覧ください。

1.はじめに


 DBLINKとは、ユーザがリモートDBのオブジェクトにアクセスすることを可能とする、Oracleのスキーマオブジェクトである。DBLINKを使えば、極めて簡単にリモートにあるデータベースの表にアクセスすることができるだけでなく、2PCといった分散トランザクション管理まで簡単に実現できる。アプリケーションを開発する立場からは極めて便利に思える機能である。
 しかし、だからといって、アプリケーション開発する上でDBLINKを意識する必要がない、ということではない。むしろDBLINKに依存したアプリケーションであることを意識し、背後にあるアーキテクチャや制約事項を正確に理解してアプリケーションを作る必要があるだろう。
 ここでは一例として、DBLINKでリモートデータベースにあるLOB列(以下リモートLOBと記す)を利用する際の制約について考えてみたい。マニュアルによると、サポートされていない形式で使うとORA-22992が発生することがあると記載されている。
データベースSecureFilesおよびラージ・オブジェクト開発者ガイド 19c
2.7.1 LOB列のルール
...
 Oracle DatabaseによるリモートLOBに対するサポートは制限されているため、リモートLOBをサポートされていない形式で使用すると、ORA-22992エラーが発生する可能性があります。

 実際に制限されている内容は5.1 リモートLOBの操作に記載されている。ただ、これを読んだだけではよくわからないため、実際に検証してみたいと思う。

2.リモートLOBの検証


 リモートLOBの動作検証をしていく。環境は手元のOracleVM上に構成した19.11.0である。

(1)テストデータの準備


 まず、BLOBを含むテストテーブルを作成し、テストデータを入れる。BLOBは乱数で生成した80文字をRAWに変換したものを使う。
SQL> create table t1 (id number, dt blob );
Table created.
SQL> alter table t1 add constraint pk_t1 primary key ( id );
Table altered.
SQL> insert into t1 values(1,utl_raw.cast_to_raw(dbms_random.string('u',80)));1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> col dt for a40
SQL> select * from t1;
        ID DT
---------- ----------------------------------------
         1 525A44454C49524B46524E42434949594A5A4655
           48544842424A4754434B444C4449534341584459
           4D5051574953445149514C4A4F4C5A53494A584E
           5456574A41464443474D5856505651504C4B524B
SQL>

(2)リモートLOBへのビュー、シノニム、MVIEW


 このテーブルに対してビューを作成する。当たり前だが、特に問題なく作成、参照できる。
SQL> create view v_t1 as select * from t1;
View created.
SQL> select * from v_t1;
        ID DT
---------- ----------------------------------------
         1 525A44454C49524B46524E42434949594A5A4655
           48544842424A4754434B444C4449534341584459
           4D5051574953445149514C4A4F4C5A53494A584E
           5456574A41464443474D5856505651504C4B524B
SQL>

 次にDBLINKを作成する。自己参照としているが、DBLINKの挙動を確認する上では問題ないだろう。
SQL> create database link d_k5 using 'k5';
Database link created.
SQL>

 DBLINK経由でLOBにアクセスしてみる。こちらも正常にアクセスできることを確認できた。
SQL> select * from t1@d_k5;

        ID DT
---------- ----------------------------------------
         1 525A44454C49524B46524E42434949594A5A4655
           48544842424A4754434B444C4449534341584459
           4D5051574953445149514C4A4F4C5A53494A584E
           5456574A41464443474D5856505651504C4B524B
SQL>

 さて、このSQLを使って、DBLINK越しにビューを作成しようとすると、以下のようにORA-22992が発生する。LOB列を含むリモートビューは作成できない(もちろんselectでLOB列を除けば、ビューの作成は可能である)。
SQL> create view v_k5t1 as select * from t1@d_k5;
create view v_k5t1 as select * from t1@d_k5
                             *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
SQL> !oerr ora 22992
22992, 00000, "cannot use LOB locators selected from remote tables"
// *Cause:  A remote LOB column cannot be referenced.
// *Action:  Remove references to LOBs in remote tables.
SQL>

 ビューではなく、シノニムであれば問題ない。実質これはDBLINK名を直接指定しているのと同じだからだろう。
SQL> create synonym s_k5t1 for f.t1@d_k5;
Synonym created.
SQL> select * from s_k5t1;
        ID DT
---------- ----------------------------------------
         1 525A44454C49524B46524E42434949594A5A4655
           48544842424A4754434B444C4449534341584459
           4D5051574953445149514C4A4F4C5A53494A584E
           5456574A41464443474D5856505651504C4B524B
SQL>

 MVIEWはLOB列が含まれていても問題ない。LOBがローカル側に実体化されているので、通常のLOBと同じように扱うことができる。
SQL> create materialized view m_k5t1 as select * from f.t1@d_k5;
Materialized view created.
SQL>
        ID DT
---------- ----------------------------------------
         1 525A44454C49524B46524E42434949594A5A4655
           48544842424A4754434B444C4449534341584459
           4D5051574953445149514C4A4F4C5A53494A584E
           5456574A41464443474D5856505651504C4B524B
SQL>

(3)リモートLOBへのDBMS_LOB関数


 次にdbms_lobを使った動作を確認する。dbms_lob.getlengthでローカルのLOBカラム長を確認する。当然これは問題ない。
SQL> select id,dbms_lob.getlength(dt) from t1;
        ID DBMS_LOB.GETLENGTH(DT)
---------- ----------------------
         1                     80
SQL>

 同じことをDBLINK経由で取得したLOB列に対して実行すると、以下のようにORA-22992のエラーが発生する。
SQL> select id,dbms_lob.getlength(dt) from t1@d_k5;
select id,dbms_lob.getlength(dt) from t1@d_k5
                             *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
SQL>

 この場合は、以下のようにdbms_lobをリモート側で実行するよう指定することで回避できる。このように関数にDBLINKを指定することができることは知らなかった。
SQL> select id,dbms_lob.getlength@d_k5(dt) from t1@d_k5;
        ID DBMS_LOB.GETLENGTH@D_K5(DT)
---------- ---------------------------
         1                          80
SQL>

 LOB長に関していえば、SQL関数がサポートされているから、下記のように書くこともできる。dbms_lobを使わなくてよいのは楽である。
SQL> select id,length(dt) from t1@d_k5;
        ID LENGTH(DT)
---------- ----------
         1         80
SQL>

(4)リモートLOBからのCTASの動作


 DBLINK経由でCTASでテーブルを作成する。これは当然問題ない。
SQL> create table t2 as select id,dt from f.t1@d_k5;
Table created.
SQL> select * from t2;
        ID DT
---------- ----------------------------------------
         1 525A44454C49524B46524E42434949594A5A4655
           48544842424A4754434B444C4449534341584459
           4D5051574953445149514C4A4F4C5A53494A584E
           5456574A41464443474D5856505651504C4B524B
SQL>

 dbms_lob.substrでBLOBの一部を抜き出したテーブルを作成することも問題ない。オフセット1(先頭)から10バイト取得できていることが確認できる。
SQL> create table t3 as select id,dbms_lob.substr(dt,10,1) dt from f.t1;
Table created.
SQL> select * from t3;
        ID DT
---------- ----------------------------------------
         1 525A44454C49524B4652
SQL>

 しかし、これをDBLINK経由で行うとORA-22992エラーとなる。試していないが、おそらくINSERT INTO SELECTでも同じエラーになるだろう。
SQL> create table t4 as select id,dbms_lob.substr(dt,10,1) dt from f.t1@d_k5;
create table t4 as select id,dbms_lob.substr(dt,10,1) dt from f.t1@d_k5
                                             *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
SQL>

 この場合はdbms_lobをリモート側で実行するよう指定しても、以下の通りエラーは回避できない。dbms_lob.substr関数のようにLOBを返却する関数はサポートされていないためである。
SQL> create table t4 as select id,dbms_lob.substr@d_k5(dt,10,1) dt from f.t1@d_k5;
create table t4 as select id,dbms_lob.substr@d_k5(dt,10,1) dt from f.t1@d_k5
                             *
ERROR at line 1:
ORA-06553: PLS-564: lob arguments are not permitted in calls to remote server
SQL>

 dbms_lobではなく、substr関数を使えば、エラーは回避できる。ただ、この場合はBLOBがVARCHAR2に変換されてしまっている。DBLINK経由で関数がLOBを返却できないためだろう。
SQL> create table t4 as select id,substr(dt,1,10) dt from f.t1@d_k5;
Table created.
SQL> select * from t4;
        ID DT
---------- ----------------------------------------
         1 RZDELIRKFR
SQL>
SQL> desc t4
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DT                                                 VARCHAR2(40)
SQL>

3.まとめ


 DBLINK経由でリモートLOBを利用する際の制約について、マニュアルに記載されている事項のいくつかを実機にて動作確認した。その結果、VIEWの作成や一部のDBMS_LOB関数は、ORA-22992が発生するため利用できないことがわかった。
 本質的にはOracleのLOBはLOBロケータを介して実体にアクセスするため、取得されたLOBロケータを関数が処理する際、その実体がリモートにあるとエラーになるのだと思われる。スカラー型を返却するDBMS_LOB関数にDBLINK名を指定することで、関数の実行自体がリモートで実行され、このエラーが回避できる。ただ、これはdbms_lob.getlengthなどLOB値からスカラー値を返却する関数の話で、LOB値を返却するdbms_lob.substr関数ではリモートLOBがサポートされておらず、エラーは回避できない。
 表のDBLINK名はシノニムやビューで隠蔽してAPから意識させないようにすることはあるだろう。しかし、LOB列が含まれている表に対しては、このような制約があることから、AP開発上はリモートであることを意識しながら行う必要があるだろう。MVIEW等でローカルにレプリカを作成すればこの制約を受けないため、あまり気にする必要はないかもしれないが、DBLINKで直接リモートLOBを使う場合は、実際にやりたいことができるかを事前に十分検証しておく必要があるだろう。
 そして何よりも基本的な考え方として、このようなAP開発上の複雑性を回避するために、DBLINKの利用は必要最低限に留めるのが良いだろうと、改めて考えさせられた。

明日は12/23、再びHiroshi Sekiguchiさんですね。解答が楽しみです。

トランケートとORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [SQL・DDL]

台風19号が過ぎ去り被害の報道にこの台風の爪痕の大きさに驚くとともに被災された方々には1日も早い生活の復旧を願うばかりである。

ORA-54は一般的にロック中のテーブル(レコード)に対し、SELECT ... FOR UPDATE NOWAIT等でロックを獲得しようとした場合に発生するエラーである。ロック中のオブジェクトに対しロックをかけようとしているため、このオブジェクトへのロック解除後に再実行すれば解消される。この際、NOWAITではなく、SELECT ... FOR UPDATE WAITでタイムアウトを設定すると、指定された秒数までロック獲得を試み、失敗した場合にエラーとなる(この場合はORA-30006)。ロックとの競合で即時エラーを回避したい場合に有効である。

このORA-54、DML実行中のテーブルにトランケートを実行しても発生する。これはトランケートは該当オブジェクト(セグメント)に対しTMエンキューを排他モードで獲得するためである。alter table ... truncate patitionで複数のパーティションにトランケートを順に実行するとき、たまにORA-54が返って失敗するのは、多くの場合DML(オンライン処理などの業務アプリケーション)との競合が発生し、トランケートがロック獲得に失敗したためである。なお、SELECT実行中のテーブルにトランケートを実行した場合、トランケートでORA-54は発生せず処理されてしまう。SELECTは(Oracleの場合読み取り一貫性のメカニズムにより)ロックを獲得しないためである。トランケートの結果、data_object_idが変わってしまうため、SELECT側にORA-8103: object no longer existsが返る。

トランケートのORA-54を回避するには、当たり前であるがDML実行中にトランケートを実行しないことである。そもそもDML実行中にトランケートを実行しなければならないこと自体に疑問はありつつも、現実問題として、トランケート実行タイミングに影響ある業務APを完全に制御し切れないケースもあるだろう。このような場合に有効なのが、11gから導入されたDDL_LOCK_TIMEOUTパラメータである。例えばこれを以下のようにセッションレベルで設定すると、指定された秒数までDDLのロック獲得を試みることができる。多少のDMLとの競合であれば、DML実行の間にロック獲得を試みて、トランケートの成功確率を高めてくれるので便利である。なお、このデフォルトは0(秒)なので、ロック獲得に失敗したら即ORA-54が返却される。

 alter session set ddl_lock_timeout=60;

TMエンキューに限って言えば、トランケートはセグメントレベルでロックを獲得する。つまり、パーティション表において、ORA-54が発生するのは、あくまで別トランザクションがそのタイミングで該当パーティションにDML操作を行っていた場合に限られる。したがって、例えば日付レンジパーティション表で、使われなくなった過去パーティションをトランケートする場合には、ORA-54の発生確率は低いだろう。逆に、ハッシュパーティションやリストパーティションで、どのパーティションも頻繁にDML操作される状況においては、いくつかのパーティションでORA-54が発生しトランケートが失敗したりする。このような場合に、上記DDL_LOCK_TIMEOUTは非常に有効である。

ただし、パーティションの競合が無いからといってオン中のトランケートを実行しても大丈夫という訳ではない。ORA-54は避けられても、トランケートにより当該テーブルにアクセスするSQLのカーソルのinvalidation、およびそれに伴うハードパース、それがトランケートによるテーブルレベルのlibrary cache lockと競合することは避けられないためである。このあたりの挙動の詳細は記事「トランケートについて知っておくべきこと」を参照されたい。

なお、DDL_LOCK_TIMEOUTはトランケートに限らず、ALTER TABLE・INDEX等のパーティションメンテナンスのDDLにも有効である。例えば、ローカルインデックスリビルド(alter index ... rebuild partition ...)、未使用エクステントの開放(alter table ... modify partition ... deallocate unused)など、パーティションメンテナンスの実行時にORA-54が発生した場合、回避策の1つとして考慮するとよいだろう。

以上
nice!(0)  コメント(0) 

遅延カーソルインバリデーション [SQL・DDL]

以前、トランケートについて知っておくべきことで述べたように、DDLは(data_object_idの変更とともに)関連するオブジェクトの共有カーソルを即時に無効化していた。しかし、実はOracle12cR2からは、可能なものは無効化を回避し、避けられないものはローリング無効化を行うことができるようになっている。このあたりの挙動は実は設計や運用上、きわめて影響の大きい内容の割には、あまり詳しく説明したドキュメントがパブリックになっていないような気がする。津島博士によるドキュメントは参考になるが、トランケートの際の挙動が記載されていない。そこで、実際に自分で確かめてみようと考えた。

トランケートの場合、以下のようにdeferred invalidationオプションをつけることにより、共有カーソルの無効化を(可能な限り)回避することができる。

例) alter table scott.sales truncate partition sales_q1_2006 update global indexes deferred invalidation;

実際に検証してみた結果、deferred invalidationオプションにより、以下のような効果があることがわかった。

・フルテーブルスキャン、またはローカルインデックスによるレンジスキャンではカーソルの無効化を回避できる
・グローバル索引によるレンジスキャンではカーソルは即時無効化される

※詳細なログは以下参照:
https://community.oracle.com/people/Kazuhiro/blog/2019/05/31/partition-truncate-with-deferred-invalidation?customTheme=mosc

パーティション表による過去パーティションメンテナンスによりトランケートやドロップ運用を行うことは多いと思うが、このオプションによりグローバル索引を使用していなければカーソルの無効化、ひいてはハードパースを避けられるということで、こんなすばらしい機能改善はないと思う。個人的には華々しく宣伝しているオプティマイザの適応なんとか最適化とかより、ずっと広く世の中のDBAの幸せに貢献する機能であるように思う。

なお、オプションをつけない場合の挙動はCURSOR_INVALIDATION初期化パラメータに依存するが、デフォルトは12.2も19cもIMMEDIATEである。これはDEFERREDになる日が来れば、このオプションをもはや気にする必要はないだろう。(参考:Oracle Database Reference Manual 19c, CURSOR_INVALIDATION)

ただ、実際に商用で使った経験がないため、副作用についての留意は必要だと思っている。以下でコミュニティに問い合わせをしてみてはいるので、そのうちワールドワイドでなんらかの知見が得られることを期待したい。

https://community.oracle.com/thread/4281580

以上

nice!(0)  コメント(0) 

19cにアップグレードしてTO_DATEで困った話 [SQL・DDL]

2019年5月1日、日本が令和を迎えるとき、私はExadataを12.2から19cへアップグレードする作業を行っていた。プロジェクトは開発期間とはいえ運用開始に向けての試験が詰まっていおり、この10連休というまたとない長期間DBを停止されるタイミングを狙ったという訳である。個人的にDBのアップグレードという作業は(長年Oracleにかかわってきたものの)初めての経験であり、そもそも可能な限り「避けるべきもの」、という位置づけであった。しかし、今回どうしてもとなったのは、長期保守の観点でやはりターミナルである19cに上げたい、ということとなったのである。あるいみ、DBの元号変更をこのGWに経験した訳である。

一言にアップグレードといっても、実際の作業をイメージするのは難しいかもしれない。DBのアップグレードは通常DB/GIを上げることを指すが、Exadataの場合これに加え、ESS(IBSW含む)、ZFS、EMを含めて整合性の取れた形にする必要がある。また、DB/GIとESSのバージョンは依存関係があるため、ESSだけ先走ってあげてしまうと、サポートされていない組み合わせになることもある。
そのため今回は一度DBにRUを当てて、ESSを上げられる状態にし、ESSを上げ、再びDB/GIを上げてという手順を取った。当然、この前後にミドルの落とし上げやOS・DBのバックアップ等の作業を含めると、かなりの作業量となる。手順を確立する上で、アップグレードの事前検証は必須であるが、それがあったとしても、環境差異を完全に吸収することはできないため、ある程度のリスクは想定しなければならないだろう。連休中などサポートが手薄な状況で最後に頼れるのはいつも人である。

さて、全体的にアップグレードにより12.2で踏んでいたORA-600系の不具合やいくつかのExadataのクリティカルイシューが修正されたことは純粋に喜ばしいことであった。以前このBlogでも紹介したスマートスキャンの結果不正についてもこのバージョンで修正されていることを確認している。Oracleの不具合の多さ、品質に疑問を感じることも少なくないが、一方でかなりの不具合は既に修正が出ていることが多いため、パッチを当てることができさえすれば問題を事前に回避することはできると思っている。あくまでもパッチを適用できれば、の話であるが。

一方、アップグレードにより発生した新たな問題もあった。Oracleコミュニティに投稿したが、19cでTO_DATEのフォーマットのマッチングのチェック仕様の一部が変更されたのである。以下を見てほしい。

*12.2.0.1
SQL> select TO_DATE('20190520','YYMMDD') from dual;

TO_DATE('20190520
-----------------
20190520 00:00:00

*19.2.0.0
SQL> select TO_DATE('20190520','YYMMDD') from dual;
select TO_DATE('20190520','YYMMDD') from dual
*
ERROR at line 1:
ORA-01843: not a valid month

基本的に上記で指定しているYYMMDDはYYYYMMDDと記述すべきであり、19cでエラーになる・ならないに関わらずこのような曖昧な記述は避けるべきである。しかし、実はSQLリファレンスマニュアルにYYのマッチングに失敗したらYYYYを試すという挙動が記載されている。これは自分も知らなかったので正直驚いた。

---
SQL reference manual / String-to-Date Conversion Rules

The following additional formatting rules apply when converting string values to date values
...

-If a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in Table 2-20.

Table 2-20 Oracle Format Matching
original format element:'YY' --> additional format elements to try in place of the original: 'YYYY'
---

この挙動、興味深いのは19cにおいて TO_DATE('20120520','YYMMDD')はエラーにならないのである。20→YY、12→MMにマッチングさせるところで、月のチェックが通るためと思われる。このため、データの内容に応じてエラーになる・ならないが変わるのである。また、TO_DATE('2019/05/20','YY/MM/DD')は19cでもエラーは出ない。/により曖昧さが排除されている結果と思われる。同様な関数としてTO_TIMESTAMPが気になるが、こちらは12c・19cの挙動に変更はなさそうであった。どうやらコード(実際は構文を解析するパーサだとは思うが)が異なるためらしい。

なお、多くの日本で稼動しているOracleDatabaseにおいて、Table 2-20 Oracle Format Matchingに記載されているYY→YYYY以外のフォーマットマッチングについて、同様の事象が発生するのはRR→RRRRのみである。それは、上記のNLS_LANGが日本語の場合、MONとMONTHのフォーマットに違いはなく、いずれも「7月」といった文言を変換できる。また、TO_DATE('7月','MONTH')は必ずエラーとなる挙動は12c,19c変わらずである。

OracleとしてはこのTO_DATEの挙動変更はあくまで「仕様」であり、不具合ではないというスタンスらしい。このため、19cへのマイグレーションにおいてはSQLの非互換として意識しておく必要がある。しかし、この手の細かな仕様変更はリリースノートに記載されていないから厄介である。なぜこの事象に気がついたか、経験のあるDBAなら察していただけるであろう。世の中のSQLが、このようなOracleのニッチな仕様を前提として動いていないことを祈るばかりである。


※2021/2/11追記
当時、My Oracle Support Community へ投稿したときの記事がこちら(a different TO_DATE behavior in 19c)。コミュニティの有識者の中では不具合ではないか、同じ事象に遭遇した、という声があった。


以上
nice!(0)  コメント(0) 

トランケートの非同期グローバル索引メンテナンスについて [SQL・DDL]

以前、「トランケートについて知っておくべきこと」の中でグローバル索引のメンテナンス(update global indexesオプション)について書いた。当時11gR2を使っていたため、12cの新機能である「非同期グローバル索引メンテナンス(Asynchronous Global Index Maintenance)」については記載しなかった。今日はこの機能についてメモを残しておく。残念ながら実機で検証した訳ではないため、あくまで机上で調べた内容に基づき、推測した部分がある点、留意頂きたい。

11gR2までは、truncate/drop partition ... update global indexを発行した際、グローバル索引のメンテナンスが完了するまではコマンドの完了を待たなければならなかった。この処理は、グローバル索引のリーフブロックの不要となった各ROWIDエントリを削除(削除フラグを立てる)する処理を含むため、非常に時間がかかることがあった。一方、12cR1からはトランケート時のグローバル索引メンテナンスは「非同期」に実行されるのがデフォルトの挙動となった。つまり、truncate partition ... update global indexを発行した際、索引のメタデータのみメンテナンスして実際の索引メンテナンスを「先送り」にするのである。このため、トランケート処理は即時に完了し、さらに、グローバル索引はUSABLEのままなので、通常通りにクエリによる正しい問い合わせ結果を得ることができる。実際のグローバル索引のメンテナンスは、以下の方法で実行できる。

(1)SYS.PMO_DEFERRED_GIDX_MAINT_JOBジョブ
 ・・・索引クリーンアップの自動スケジューラジョブ(毎日2:00に実行)
(2)DBMS_SCHEDULER.RUN_JOB
 ・・・上記(1)を手動で起動
(3)DBMS_PART.CLEANUP_GIDXプロシージャ
 ・・・必要な索引すべて行う、または特定の索引だけ指定してクリーンアップを行う
(4)ALTER INDEX REBUILD [PARTITION]コマンド
 ・・・索引全体をリビルドする(従来の方法)
(5)ALTER INDEX [PARTITION] COALESCE CLEANUPコマンド
 ・・・索引のorphanedエントリをクリーンアップする

以上が概略である。さて、この非同期グローバル索引メンテナンス、どのような仕組みで実現されているのだろうか考えてみよう。

まず、truncate partitionした後のグローバル索引の状態であるが、これは一切メンテナンスがされない。つまり、索引のリーフブロックに記録されたROWIDは、そのまま残っている。したがって、トランケートされたパーティション内の行を参照していたROWIDは、参照先がない状態、すなわち親なし(orphaned)エントリが含まれる状態となる。グローバル索引にorphandエントリが含まれるかは、USER_INDEXES.ORPHANED_ENTRIESで確認できる(YESなら含まれる)。このような状態で一体クエリはどのように正しい問い合わせ結果を返すことができるのだろうか。

その答えを知るには、以下について理解しておく必要がある。

まず、truncate partitionした後のメタデータの更新とは、具体的にはグローバル索引のオブジェクトIDと、トランケートしたオブジェクトIDの関連を保持することである。これは、sys.index_orphand_entry$で確認することができる。indexobj#にグローバル索引のオブジェクトID、tabpartobj#にトランケート対象のオブジェクトIDが格納される。

次に、ROWIDについてである。ROWIDはレコードを一意に識別するIDであり、18バイトで構成される。先頭から、6バイトがオブジェクトID、次の3バイトがデータファイル番号、次の6バイトがブロック番号、次の3バイトが行番号という具合である。したがって、グローバル索引のリーフブロックに格納されるROWIDを見れば、それがどのパーティションに属しているかわかるのである。

そして、TBL$OR$IDX$PART$NUM関数である。この関数の仕様は非公開であるが、引数に表の名前とROWIDを与えると、そのROWIDがorphanedかどうかを判別することができる(orphandでなければ1を返す)ようだ。これは、おそらくROWIDからその参照先のパーティションのオブジェクトIDを識別し、それを上記のsys.index_orphand_entry$の情報からorphanedかどうかを判別していると思われる。

さて、上記を踏まえた上で、非同期グローバル索引メンテナンスが行われたグローバル索引を用いたレンジスキャンがどのように動くか考えてみよう。まず、クエリは検索条件からグローバル索引のブランチを通り、リーフブロックに到達し、そこから横にリーフブロックをたどる動きをする。この際、リーフブロックに格納されるROWIDを1つずつTBL$OR$IDX$PART$NUM関数を用いたfilterでorphanedエントリを取り除く(実行計画のpredicateにはfilterが現れる)。そして得られたROWIDの集合について、パーティションへアクセスしそれぞれに対応する行を取得する。実際は、USER_INDEXES.ORPHANED_ENTRIESがNOの場合は、このfilter処理は不要といったロジックは組み込まれているのだろう。

このアーキテクチャでは、orphanedエントリが多くなればなるほど、filter処理が増えることとなる。そのため、返却されるレコード数に対して、必要となる処理時間が延びていくことが想定される。また、INSERT時にリーフブロックが再利用されるのはUNIQUE制約のあるときのみであるらしい(参考1)ため、いくらパーティショントランケートしても、グローバル索引は小さくならず、むしろレコードの増加に伴って単調に増加していってしまう可能性がある。これを避けるために、上記5つのいずれかの方法でorpahnedエントリを定期的に綺麗に削除しておく必要があるだろう。ある意味、非同期による副作用といえる。

実際、Oracleのコミュニティでも以下のスレッド(2018.11)で12cのグローバル索引が肥大化する問題についての議論がされていた。SYS.PMO_DEFERRED_GIDX_MAINT_JOBによる自動メンテナンスが走っているにもかかわらず、である。

Index size of partitioned table is growing

今後、同様の事象があちこちで発生してくるのかもしれない。11gからのハード更改でこの挙動が問題となる場合は、従来の動作にするために"_fast_index_maintenance"=falseとすることも一考の価値があるかもしれない。ただし、隠しパラメータなので、利用についてはサポートに確認した方がよいだろう。

以上

◆参考文献
[1]12c Asynchronous Global Index Maintenance Part II by Richard Foote
[2]12cマニュアル Database VLDB and Partitioning Guide, Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
[3]津島博士のパフォーマンス講座 第43回 パーティションについて(3)
[4]How to Drop/Truncate Multiple Partitions in Oracle 12C (ドキュメントID 1482264.1)

nice!(1)  コメント(0) 

スマートスキャンの結果不正に遭遇 [SQL・DDL]

世の中10連休であるが、ほとんどExa19cへのアップグレードで費やしてしまった自分にとって、もはや改元とかなにか遠い世界のことのように過ぎ去ってしまった。この話は別に書くこととし、今回はExadataの結果不正に遭遇した話をしたい。

さて、最近、業務TmからIS NOT NULLを条件指定したSQLの結果にNULL列が含まれているという問い合わせがあった。SQLを見てみると、以下のようにとてもシンプルなSELECT文(もちろん実際はテーブルはemp表ではないが)

 select * from emp where job is not null

まさかと思い、自分で実行してみると確かに結果にjob列がNULLの行が含まれていた。確かに(1行だけであったが)NULLの行が入っていた。autotraceとって見ると、predicateにstorageが出ていたので、ストレージサーバへのオフロード(スマートスキャン)が走っていそうだった。SQLトレースで見たところ、確かにスマートスキャンが走っていた。そこで、試しにcell_offload_processingをfalseにして実行してみたところ、なんとNULLの行が消え、あるべき結果となった。スマートスキャンの挙動がおかしいのである。

サポートに確認したところ、ESS(Exadata Storage Server Software)の不具合であることが判明。条件はスマートスキャン、IS NOT NULL、行連鎖が発生していること。実際にこのテーブルは255カラム以上だったので、INSERT時に行連鎖が発生する。

なぜ行連鎖がこの不具合の挙動に関係するのか?本来このような行連鎖ブロックはスマートスキャンにはならず、DBサーバへブロックをそのまま返却し、DBサーバ側でfilter処理を行うべきものである。おそらく不具合はここの挙動が正常に動かなかったことによると考えられる。

ちなみにこの不具合、既にパッチ、というかESSの特定のバージョン以上で修正されているため、ESSを最新にすれば防ぐことができる。運よく、今回のGWでたまたまESSバージョンアップを予定していたので、19.2.0にしたらこの事象は発生しなくなった。

今回は開発中に検出できたのでまだ良かったが、この手の不具合は運用中に検出したら結構痛い。ストレージサーバ側のオフロード機能が充実するのは嬉しいことではあるが、不具合の確率も高まることは避けられない。不具合に遭遇して調べると、多くの場合は既知不具合でパッチも用意されていたりするのである。その意味でESS(だけではないが)のバージョンはなるべく新しくしておきたい。また、停止の影響を局所化する上でもベンダには個別パッチ提供を引き続きお願いしたいところである。

なお、スマートスキャンの結果不正については、以下のドキュメントに解析に必要なログ取得方法が記載されている。SQLをいくつかの初期化パラメータ(上記のcell_offload_processing含め)を変更しながらSQLを実行するものと、結果不正となった行のブロックダンプを取得するものである。使うことがないに越したことはないが、Exadataを扱うDBAとしては、このようなドキュメントがあることは知っておいたほうが良いかもしれない。

Exadata: How to diagnose smart scan and wrong results (ドキュメントID 1260804.1)

以上
nice!(0)  コメント(0)