GoldenGateでレプリカ表にNOT NULLの更新日付を入れる [GoldenGate]
１．はじめに
前回の記事で、GoldenGateを使ってレプリケーションする際に、更新日付を入れる方法について記載した。その後、このカラムをNOT NULLにしたとき、初期値を入れられないか、という話になり、動作確認をしたので、備忘までメモを残したい。
前回の方法では、元表には入っていない更新日付（作成日時、更新日時）を、レプリカ側の表にカラム追加した。初期同期ではNULLとしておき、GGで更新されたもののみ、作成日付や更新日付を入れる前提としていた。今回は、レプリカ側のこれらのカラムがNOT NULLになるため、初期同期のタイミングで両方のカラムに初期値を入れておきたい。
GGにおいて初期同期をするには、元表の断面をexpdpで抜き、レプリカ側の表に入れる。このとき、元表にないカラムは、NULLとなってしまう。このため、レプリカ側の定義にdefault句を設定し、systimestampを入れる必要がある。しかし、今回はdefault句を使いたくないとの要件があったため、expdpのremap_dataという機能を使った実機検証を行った。remap_dataは特定のカラムの値をパッケージ・ファンクションで変換することができるdatapumpの機能である。なお、この方法を使うには、元表にも（値はNULLで良いが）更新日付カラムが存在している必要がある点、留意する必要がある。
２．remap_dataについて
まず、remap_dataについておさらいしておこう。マニュアル（参考[1]）からの記載の抜粋は以下の通り。
REMAP_DATAパラメータを使用すると再マップ・ファンクションを指定できます。これにより、指定した列の元の値をソースとして再マップした値を返し、ダンプ・ファイル内の元の値をこの値に置き換えます。このオプションは、一般的に、本番システムからテスト・システムへ移動するときにデータをマスクするために使用されます。たとえば、クレジット・カード番号などの顧客の機密データの列を、REMAP_DATAファンクションで生成された番号に置き換えることができます。機密データを数値に置換することで、重要な書式設定および処理特性を、権限のない担当者に個人データを公開することなく保持できます。
REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function
今回は上記機能を利用し、エクスポート時にNULLが設定されている更新日付カラムについて、systimestampを返却する関数を指定することにより、ダンプファイルにエクスポート時の時刻を設定する。具体的には、以下のようにtimestamp型を入力引数として、systimestampを返却する関数を定義する。remap_dataに渡す関数はパッケージとする必要がある点、注意が必要である。
function current_dt(p_value timestamp) return timestamp is begin return(systimestamp); end;
３．モデル
準備として、ソース(oradb.scott)にdeptrep表を作成し、初期データを投入する。DDLは以下の通り、更新日付(cre_dt, upd_dt)をnot nullなしとし、10万件の初期データではこれらカラムはnullを設定する。
drop table deptrep cascade constraints;
create table deptrep(
deptno number(7,0),
dname varchar2(14),
loc varchar2(13),
cre_dt timestamp,
upd_dt timestamp,
constraint pk_deptrep primary key (deptno)
);
conn scott/ABcd#-123qwe@oradb
insert into deptrep select
rownum deptno,
dbms_random.string('u',14) dname,
dbms_random.string('u',13) loc,
null, null
from dual connect by level <=100000;
次にターゲット(oradbrep.scott)に以下DDL実行し、同期対象となるターゲット表を作成する。こちらの更新日付(cre_dt, upd_dt)はnot nullを付与する。
conn scott/ABcd#-123qwe@oradbrep drop table deptrep cascade constraints; create table deptrep( deptno number(7,0), dname varchar2(14), loc varchar2(13), cre_dt timestamp not null, upd_dt timestamp not null, constraint pk_deptrep primary key (deptno) );
以上で準備は完了である。
４．初期同期
初期同期を行うため、ソース(oradb.scott)のdeptrepをエクスポートし、そのダンプファイルをターゲット(oradbrep.scott)にインポートする。
まず、remap_dataを用いたエクスポートを行うため、ソース側で必要となるパッケージ・ファンクションを作成する。
create or replace package remappkg as function current_dt(p_value timestamp) return timestamp; end; / create or replace package body remappkg as function current_dt(p_value timestamp) return timestamp is begin return(systimestamp); end; end; /
expdpコマンドにremap_dataを指定し、deptrepをエクスポートする。remap_dataはカラム毎に変換する関数をパッケージ名.ファンクション名で指定する。
expdp scott/xxx@oradb tables=scott.deptrep directory=expdir dumpfile=scott_deptrep.dmp logfile=expdpscott_deptrep.log content=data_only reuse_dumpfiles=yes remap_data=scott.deptrep.cre_dt:remappkg.current_dt remap_data=scott.deptrep.upd_dt:remappkg.current_dt
上記でエクスポートされたダンプファイルを、ターゲット(oradbrep.scott)のdeptrep表にインポートする。not null制約のある更新日付には、すでにダンプファイルに値が入っているため、特別なことを行うことなくインポートが完了する。
impdp scott/xxx@oradbrep tables=scott.deptrep directory=expdir dumpfile=scott_deptrep.dmp logfile=impdpscott_deptrep.log content=data_only table_exists_action=truncate
５．Goldengateの設定
Goldengateの定義を変更する。Extractの設定については、今回作成したdeptrep表を追加する必要がある。ただ、すでに以下のような設定で抽出対象に含まれているため、変更は不要であった。
TABLE ORADB.SCOTT.*;
続いてReplicatの設定であるが、ここには前回同様に、以下のようにMAP文を指定して、更新日付を入れるように設定を行う。なお、upd_dtの更新で、INSERTの時にも更新日付を入れるようにしているのは、INSERT時にNOT NULL違反となることを避けるためである。
MAP SCOTT.DEPTREP, TARGET SCOTT.DEPTREP,
COLMAP(USEDEFAULTS,
cre_dt=@IF(@STREQ(@GETENV('GGHEADER','OPTYPE'),'INSERT'),@DATENOW(),@COLSTAT(MISSING)),
upd_dt=@IF(@VALONEOF(@GETENV('GGHEADER','OPTYPE'), 'INSERT', 'UPDATE', 'SQL COMPUPDATE', 'PK UPDATE'), @DATENOW(), @COLSTAT(MISSING)));
上記変更が完了したら、GGのプロセス（エクストラクト、レプリキャット）を停止、起動を行う。
６．動作確認
動作確認として、ソース側のdeptrepをINSERT、UPDATE（2回）、DELETEを実行し、ターゲット側のdeptrepの更新カラムを確認した。INSERT実行時に、cre_dt, upd_dt両方に更新日付が入っている。その後の更新では、upd_dtのみが更新されていることがわかる。
-- insert a record
insert into deptrep values (100001,'support','tokyo',null,null);
commit;
08:09:04 SCOTT@oradb> select * from deptrep where deptno=100001;
DEPTNO DNAME LOC CRE_DT UPD_DT
---------- -------------- ------------- ----------------------------------- -----------------------------------
100001 support tokyo
-- target
08:09:19 SCOTT@oradbrep> select * from deptrep where deptno=100001;
DEPTNO DNAME LOC CRE_DT UPD_DT
---------- -------------- ------------- ----------------------------------- -----------------------------------
100001 support tokyo 2024/04/21 08:20:04.000000 2024/04/21 08:20:04.000000
-- update a record #1
update deptrep set loc='saitama' where deptno=100001;
commit;
-- target
08:22:53 SCOTT@oradbrep> select * from deptrep where deptno=100001;
DEPTNO DNAME LOC CRE_DT UPD_DT
---------- -------------- ------------- ----------------------------------- -----------------------------------
100001 support saitama 2024/04/21 08:20:04.000000 2024/04/21 08:22:55.000000
-- update a record #2
update deptrep set loc='kanagawa' where deptno=100001;
commit;
-- target
08:23:07 SCOTT@oradbrep> select * from deptrep where deptno=100001;
DEPTNO DNAME LOC CRE_DT UPD_DT
---------- -------------- ------------- ----------------------------------- -----------------------------------
100001 support kanagawa 2024/04/21 08:20:04.000000 2024/04/21 08:23:10.000000
-- delete a record #1
delete from deptrep where deptno=100001;
commit;
-- target
08:23:30 SCOTT@oradbrep> select * from deptrep where deptno=100001;
no rows selected
７．まとめ
本稿では、GoldenGateでNOT NULLの更新日付を入れる方法について、初期同期の方法を含めて記載した。レプリカ表でdefault句を使えない場合、ソース側と初期同期を行うためには、更新日付に初期値をどう入れるかが問題となる。元表側にも更新日付カラムを付与することが条件となるが、ダンプのremap_dataを用いて初期値をソース側で設定したダンプファイルを作成できる。この方法であれば、レプリカ側は通常の方法でダンプをインポートするだけでよい。remap_dataに渡すためのファンクションを作成する必要はあるが、元表のエクスポート側でひと手間かけるだけで良い。
なお、本稿ではexpdp時にremap_dataを用いたが、remap_data自体はimpdpでも利用できる。ターゲットDB側に変換関数を作成し、null値をインポートのタイミングのsystimestampに設定することも原理的には可能だろう。この場合においても、ソースDB側には更新日付のカラムは必須である。remap_dataはあくまで変換するカラムが存在することが前提で、ダンプに存在しないカラムを追加で設定することはできない。
GoldenGateでターゲット側に更新日付を入れる必要がある場合の参考になれば幸いである。
参考
[1]Oracle Database ユーティリティ 19c, 2.4.38 REMAP_DATA
2024-04-21 21:01
GoldenGateでレプリカ表に更新日付を入れる [GoldenGate]
１．はじめに
最近の案件で、GoldenGateを使ってレプリケーションする際に、更新日付を入れられないか、という問い合わせがあった。初回INSERT時と、UPDATEされたときに、それぞれ更新日付を記録したいという要件である。実現方式を調べて、OCIで動作確認したので、備忘までメモしておく。
検証はOCIで、BaseDBと、適当なコンピュート上に構成したGoldenGate（マイクロサービス）で行った。BaseDBは1台の上に、ORADBとORADBREPという２つのPDBを構成し、ORADBのEMP表をORADBREPにレプリケーションした。その際、更新日付のない通常のレプリケーション（EMP表）と更新日付のあるレプリケーション（EMPREP表）の２つのレプリカを作成した。
２．検証モデル
ソース側となるORADBのEMP表は以下の通り。初期データとして100万件入れておく。また、レプリカの初期同期用にダンプを作成する。
EMP表 Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(7) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(7) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(7)
ターゲット側となるORADBREPのEMP表、EMPREP表は以下の通り。初期データとして100万件入れておく。EMPREP表には、CRE_DTとUPD_DTが追加されている。初期データとしてソースのダンプをインポートする。EMPREPはカラムが追加されているので、インポート時はNULLで入る。
EMP表 Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(7) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(7) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(7) EMPREP表 Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(7) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(7) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(7) CRE_DT TIMESTAMP(6)★作成日付 UPD_DT TIMESTAMP(6)★更新日付
次にGGの設定である。エクストラクトの設定は以下の通り。トレイルとして出力する対象として、ORADBのSCOTTスキーマのオブジェクトを指定した。
EXTRACT EORADB USERIDALIAS CORADB_ORADBVM1 DOMAIN ORADBVM1 EXTTRAIL AA TABLE ORADB.SCOTT.*;
レプリキャットの設定は以下の通り。非統合レプリキャットを使った。MAP文でEMP表とEMPREP表にレプリケートする設定としている。このうち、EMPは単純なMAP文（単純に全てのカラムをマッピング）しているが、EMPREPの方はCOLMAPでCRE_DTとUPD_DTカラムに更新日付を入れる処理を追加している。ここが今回のポイントである。
REPLICAT RORAREP
USERIDALIAS ORADBREP DOMAIN ORADBVM1
SOURCECATALOG ORADB
MAP SCOTT.EMP, TARGET SCOTT.EMP;
MAP SCOTT.EMP, TARGET SCOTT.EMPREP,
COLMAP(USEDEFAULTS,
cre_dt=@IF(@STREQ(@GETENV('GGHEADER','OPTYPE'),'INSERT'),@DATENOW(),@COLSTAT(MISSING)),
upd_dt=@IF(@VALONEOF(@GETENV('GGHEADER','OPTYPE'), 'UPDATE', 'SQL COMPUPDATE', 'PK UPDATE'), @DATENOW(), @COLSTAT(MISSING)));
少し補足すると、@GETENV('GGHEADER','OPTYPE')はDMLの種類を返す関数である。この返り値がINSERTなのかUPDATEなのかを、@STREQで文字列が一致するか、@VALONEOFでいくつかの選択肢のうち１つが一致すれば真を返す関数を利用し、@IFで切り替える。設定値は、現在時刻である@DATENOWを設定するか、何も設定しない@COLSTAT(MISSING)を設定する。なお、UPDATEとSQL COMPUPDATEの違いは、UPDATEが全てのカラムを含むのに対し、SQL COMPUPDATEは更新対象となるカラムしか含まない場合である（Oracleは通常更新対象となるカラムしかREDOに含まないが、SQL Serverなどでは全てのカラムをログに含めるものもある）。
３．動作確認
ここでは、ソース側のEMP表EMPNO=1000001のレコードをINSERT、UPDATE（2回）、DELETEを実行して、ターゲット側の状態を確認した。まず、INSERT後の状態である。正しくターゲットのEMP、EMPREP表にレコードが伝搬されている。また、EMPREPには、作成日を示すCRE_DTが入っており、UPD_DTはNULLのままである。
19:57:27 SCOTT@oradb> @insemp
Connected.
19:57:30 SCOTT@oradb> @ckemp
Connected.
★ソース：EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
1000001 kazuhiro dba 1 20240408 19:57:30 1000 1000 1
Connected.
★ターゲット：EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
1000001 kazuhiro dba 1 20240408 19:57:30 1000 1000 1
★ターゲット：EMPREP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CRE_DT UPD_DT
---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- -------------------- --------------------
1000001 kazuhiro dba 1 20240408 19:57:30 1000 1000 1 20240408 19:57:32
次にUPDATE後の状態である。正しくターゲットのEMP、EMPREP表にSAL,COMが+1されて伝搬されている。また、EMPREPには、更新日付を示すUPD_DTが入る。
19:57:35 SCOTT@oradb> @updemp
Connected.
19:57:39 SCOTT@oradb> @ckemp
Connected.
★ソース：EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
1000001 kazuhiro dba 1 20240408 19:57:39 1001 1001 1
Connected.
★ターゲット：EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
1000001 kazuhiro dba 1 20240408 19:57:39 1001 1001 1
★ターゲット：EMPREP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CRE_DT UPD_DT
---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- -------------------- --------------------
1000001 kazuhiro dba 1 20240408 19:57:39 1001 1001 1 20240408 19:57:32 20240408 19:57:42
もう一度UPDATEを実行した後の状態である。正しくターゲットのEMP、EMPREP表にSAL,COMが+1されて伝搬されている。EMPREPは、更新日付を示すUPD_DTが更新されている。
19:57:48 SCOTT@oradb> @updemp
Connected.
19:57:53 SCOTT@oradb> @ckemp
Connected.
★ソース：EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
1000001 kazuhiro dba 1 20240408 19:57:53 1002 1002 1
Connected.
★ターゲット：EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
1000001 kazuhiro dba 1 20240408 19:57:53 1002 1002 1
★ターゲット：EMPREP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CRE_DT UPD_DT
---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- -------------------- --------------------
1000001 kazuhiro dba 1 20240408 19:57:53 1002 1002 1 20240408 19:57:32 20240408 19:57:58
DELETEを実行した後は、正しくターゲットのEMP、EMPREP表がDELETEされることも確認した。
４．まとめ
本稿では、GoldenGateを使ってレプリカを作成する際に、更新日付を追加する方法について、実機検証した結果を記載した。日付を入れる処理ロジックは若干複雑であるが、このような柔軟な対応ができるのは、GoldenGateの良いところである。Goldengateの構成方法についてはまた別の機会に触れたいと思う。
参考
[1]Reference for Oracle GoldenGate, 2.18 @GETENV
2024-04-09 23:36
バインドミスマッチのSQLの特定方法の一考察 [オプティマイザ]
１．はじめに
以前の記事でバインドミスマッチによる性能遅延の問題について記載した。その中で、この問題を発生させないようにするためには、Java等のアプリケーションでバインド変数に適切な型を設定することが重要であることを述べた。そのためにはアプリケーションを開発する技術者にOracleのアーキテクチャを理解して設計することが求められると同時に、設計や実装段階でこの問題を検出する仕組みが必要である。
しかし、実際問題、アプリケーションの開発者はOracleのアーキテクチャに詳しい訳でもなく、試験において機能確認以上にバインド型の妥当性まで確認することはなかなか難しい。そもそも、その必要性を理解してもらうことすらままならないのが現実である。
そこで考えたのが、データベースに流れているSQLの中で、問題を発生させる可能性のあるSQLを一括して調査する方法である。これができれば、アプリケーションの開発や性能試験の中で、バインドミスマッチの問題が発生する可能性のあるSQLを（全てではないにしろ）検出し、開発者に修正を促すことができる。ここではそのようなSQLを考えてみたい。
２．バインドミスマッチのSQLの特定方法
v$sql_bind_captureには、SQL_ID毎に、バインドポジションとバインド型の情報が格納されている。バインドミスマッチが発生している場合は、同じバインドポジションに異なる複数のバインド型が割り当てられるはずである。この性質を利用して、SQL_ID、バインドポジション、データ型を重複排除し、データ型を複数持つSQL_IDを抽出する。通常はSYS等のシステム系のSQLには興味がないだろうから、v$sqlareaと外部結合してユーザ情報や実行回数、バージョンカウントもあわせて表示する。
上記のアイディアのもと、以下が実際に考えてみたSQLである。バインドミスマッチを起こしていると思われるSQLID一覧を表示するSQLである。
rem (A) sql_ids that have more than one bind types for the same column, order by executions desc
col parsing_schema_name for a20
select t1.con_id, t1.sql_id, num_position, t2.parsing_schema_name, t2.executions, t2.version_count from (
select con_id, sql_id, count(*) num_position
from (
select con_id, sql_id, position, count(*)
from (select distinct con_id, sql_id, position, datatype from v$sql_bind_capture)
group by con_id, sql_id, position having count(*) > 1
)
group by con_id, sql_id
) t1,
v$sqlarea t2
where t1.con_id=t2.con_id(+) and t1.sql_id=t2.sql_id(+) order by executions desc;
SQLだけではイメージがわかないので、実際の実行結果を見てほしい。環境はOCIのADB（ATP）で19.23である。
CON_ID SQL_ID NUM_POSITION PARSING_SCHEMA_NAME EXECUTIONS VERSION_COUNT
---------- ------------- ------------ -------------------- ---------- -------------
326 4f2g5hu1c7yu3 1 SYS 40 2
326 2szhjcqs9agym 1 SYS 29 2
326 fs7h3cb49hs7s 2 SYS 8 2
326 a8ybwb4wf635t 1 SYS 5 2
326 dqdaup2z1508s 2 SYS 5 2
326 bmrrsf60dp618 1 SYS 5 2
326 3sp9hj44p632r 5 SYS 5 2
326 ch8t72tsys7vx 1 SYS 5 2
326 fu9xz9pnu520q 1 SYS 3 2
9 rows selected.
例えば、一番上の4f2g5hu1c7yu3は、1か所のバインドポジションにおいて、バインドミスマッチが発生している。このSQLはSYSで実行されており、実行回数累計40回、バージョンカウントは2であることがわかる。1か所のバインドミスマッチであれば、子カーソルが増えてもたかが知れているし、実行回数もたいして多くないため、問題ない、ということがわかる。下から3番目の3sp9hj44p632rは、5か所のバインドポジションにおいて、バインドミスマッチが発生している。仮にこのバリエーションが増えると、最大2^5=~32個程度だろうし、問題ない。
仮に上記で問題となりそうなSQLが見つかったとき、具体的にどのようなバインドミスマッチが発生しているかが気になる。バインドポジション毎にどのような型が使われているのかを表示するSQLが以下である。
rem (B) bind type names for each sql_id and bind position col datatype_string for a40 select t1.con_id, t1.sql_id, t1.position, listagg(t2.datatype_string,',') within group (order by t2.position) datatype_string from (select con_id, sql_id, position,count(*) from (select distinct con_id, sql_id, position, datatype from v$sql_bind_capture) group by con_id, sql_id, position having count(*) > 1 ) t1, (select distinct con_id, sql_id, position, datatype, datatype_string from v$sql_bind_capture) t2 where t1.con_id=t2.con_id and t1.sql_id=t2.sql_id and t1.position=t2.position group by t1.con_id, t1.sql_id, t1.position order by 1,2,3,4 ;
こちらの実行結果は以下の通り。
CON_ID SQL_ID POSITION DATATYPE_STRING
---------- ------------- ---------- ----------------------------------------
326 2szhjcqs9agym 1 NUMBER,VARCHAR2(2000)
326 3sp9hj44p632r 1 NUMBER,VARCHAR2(2000)
326 3sp9hj44p632r 2 NUMBER,VARCHAR2(2000)
326 3sp9hj44p632r 3 NUMBER,VARCHAR2(2000)
326 3sp9hj44p632r 4 NUMBER,VARCHAR2(2000)
326 3sp9hj44p632r 5 NUMBER,VARCHAR2(2000)
326 4f2g5hu1c7yu3 1 NUMBER,VARCHAR2(2000)
326 a8ybwb4wf635t 1 NUMBER,VARCHAR2(2000)
326 bmrrsf60dp618 1 NUMBER,VARCHAR2(2000)
326 ch8t72tsys7vx 1 NUMBER,VARCHAR2(2000)
326 dqdaup2z1508s 1 NUMBER,VARCHAR2(2000)
326 dqdaup2z1508s 2 NUMBER,VARCHAR2(2000)
326 fs7h3cb49hs7s 1 NUMBER,VARCHAR2(2000)
326 fs7h3cb49hs7s 2 NUMBER,VARCHAR2(2000)
326 fu9xz9pnu520q 1 NUMBER,VARCHAR2(2000)
15 rows selected.
例えば、3sp9hj44p632rを見てみると、バインドポジションが1から5について、NUMBER型とVARCHAR2(2000)でバインドミスマッチが発生していることがわかる。他のSQLについても、この例では全てNUMBERとVARCHAR2(2000)のバインドミスマッチである。
以下の例は、BaseDBの19.20であるが、DATEとVARCHAR2(2000)でバインドミスマッチが発生している。実際のアプリケーションではTIMESTAMP型やLOB型も出てくるかもしれない。
CON_ID SQL_ID POSITION DATATYPE_STRING
---------- ------------- ---------- ----------------------------------------
...
3 531x22ur6bxc1 1 NUMBER,VARCHAR2(2000)
3 531x22ur6bxc1 2 NUMBER,VARCHAR2(2000)
3 531x22ur6bxc1 4 NUMBER,VARCHAR2(2000)
3 531x22ur6bxc1 7 DATE,VARCHAR2(2000)
3 531x22ur6bxc1 10 DATE,VARCHAR2(2000)
3 59xng0tdsqw0s 1 NUMBER,VARCHAR2(2000)
3 59xng0tdsqw0s 2 NUMBER,VARCHAR2(2000)
3 59xng0tdsqw0s 4 NUMBER,VARCHAR2(2000)
3 59xng0tdsqw0s 5 DATE,VARCHAR2(2000)
...
リスクのあるSQL_IDが特定できたら、この後の流れは通常と同じである。以下のようにv$sql_bind_captureでSQL_IDを指定してバインド型の状態を確認する。
select con_id,sql_id, child_number, position, datatype, datatype_string from v$sql_bind_capture
where sql_id='3sp9hj44p632r';
CON_ID SQL_ID CHILD_NUMBER POSITION DATATYPE DATATYPE_STRING
---------- ------------- ------------ ---------- ---------- ----------------------------------------
4 3sp9hj44p632r 3 1 2 NUMBER
4 3sp9hj44p632r 3 2 2 NUMBER
4 3sp9hj44p632r 3 3 2 NUMBER
4 3sp9hj44p632r 3 4 2 NUMBER
4 3sp9hj44p632r 3 5 2 NUMBER
4 3sp9hj44p632r 2 1 1 VARCHAR2(2000)
4 3sp9hj44p632r 2 2 1 VARCHAR2(2000)
4 3sp9hj44p632r 2 3 1 VARCHAR2(2000)
4 3sp9hj44p632r 2 4 1 VARCHAR2(2000)
4 3sp9hj44p632r 2 5 1 VARCHAR2(2000)
SQL_IDのSQLはv$sqlareaで確認できるだろう。アプリケーションのスキーマとSQLが特定できれば、開発者に修正を促すことができる。
col sql_text for a60
col parsing_schema_name for a20
select sql_id, parsing_schema_name, sql_text from v$sqlarea where sql_id='3sp9hj44p632r'
SQL_ID PARSING_SCHEMA_NAME SQL_TEXT
------------- -------------------- ------------------------------------------------------------
3sp9hj44p632r SYS select 1, max(id) from wri$_adv_objects where task_id = :1
union all select 2, max(id) from wri$_adv_recommendations w
here task_id = :1 union all select 3, max(id) from wri$_adv_
actions where task_id = :1 union all select 4, max(id) from
wri$_adv_findings where task_id = :1 union all select 5, m
ax(id) from wri$_adv_rationale where task_id = :1
３．考察
この方法で検出されたSQLについてアプリケーションの修正を促すべきSQLをどう評価したらよいだろうか。バインドミスマッチはバインドポジション数に対し2のべき乗で増加する。(A)のNUM_POSITIONが10以下であるSQLは子カーソル数は最大でもたかだか1000程度であり、Oracleのデフォルトの子カーソル数の閾値（19cで8,192）に十分収まる範囲と考えられる。(A)のNUM_POSITIONが10より大きいSQLIDについてはデータのバリエーションにより子カーソル数が1000を超えるリスクがあるため、アプリケーションを修正することが望ましいだろう。ただ実際は、業務特性として(A)のEXECUTIONSが大きくならず、並列実行されないことがわかっているのであれば、アプリケーション修正なしでも問題は顕在化しない。そう考えると、やはり一番の入り口は、バインドポジションの数である。バインドポジション数が極端に大きなSQLがないか、そこで子カーソル数が多くなっていないか、このSQLは業務的に頻繁に並列に実行される特性のものか、を確認し、全てYESならアプリケーションを修正した方が良いと私は考える。
(A)や(B)のSQLをどのタイミングで利用すべきだろうか。(A)は結合試験以降である程度実際の業務に近い状態にならないと、データバリエーションや実行回数が意味のある数字にならないだろう。それでも、リスクのあるSQLを早期に特定する目的には使える。(B)は単体試験から使えるだろう。NULL値等のバリエーションを含めた機能的な試験を行うだろうから、バインドミスマッチが発生するようなコーディングを検出できる可能性がある。
４．まとめ
本記事では、バインドミスマッチが発生しているSQLを特定する方法について述べた。この方法では、SQLの実行回数、子カーソル数とあわせて、バインドミスマッチが発生しているバインドポジション数を見て評価ができる点がポイントである。思い付きのプロトタイプレベルのSQLではあるものの、今後の開発の中で利用して改善していければと思っている。
MOSのDocID 438755.1のドキュメント[1]に、子カーソルが増加しているSQLのレポートを作成するスクリプトが公開されているので、こちらとあわせて使うとよいだろう。
参考
[1] Document 438755.1 High SQL Version Counts - Script to determine reason(s)
2024-04-01 09:41
