前回の続きになります。
前回の検証では、insert … values … のような細かいDMLを大量に発行すると、standby query scn advance待機が発生して処理時間が大きく伸びてしまう事が判りました。
では単一のinsert … select … 文でプライマリ側とスタンバイ側に大きく性能差が出た理由は何か、検証していきます。
Active Data Guard DMLリダイレクトはExadata固有の機能ではありませんが、19cの新機能の多くがExadataでしか使えない機能なので、今回の検証はExadata環境で行っています。
Exadataで性能を大きく左右するのはSmartScan*1なので、SmartScanが効いているかどうか、確認してみます。
プライマリ側
SQL> insert into t1(col1) select col2 from t2 where col1=1;
1000 rows created.
Elapsed: 00:00:00.77
SQL> select n.name,m.value from v$mystat m ,v$statname n where n.STATISTIC#=m.STATISTIC# and
2 (n.name like 'cell physical%' or n.name like 'physical read bytes%' or
3 n.name like 'physical write bytes%' or n.name like 'cell IO%');
NAME VALUE
---------------------------------------------------------------- --------------------
cell physical IO interconnect bytes 855,160
physical read bytes 160,609,943,552
physical write bytes 0
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 160,609,845,248
cell physical IO bytes eligible for smart IOs 160,609,845,248
cell physical IO bytes saved by columnar cache 0
cell physical IO bytes saved by storage index 159,571,869,696
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO bytes processed for IM capacity 0
cell physical IO bytes processed for IM query 0
cell physical IO bytes processed for no memcompress 0
cell physical IO interconnect bytes returned by smart scan 756,856
cell physical write bytes saved by smart file initialization 0
cell IO uncompressed bytes 1,037,975,552
cell physical write IO bytes eligible for offload 0
cell physical write IO host network bytes written during offloa 0
18 rows selected.
SmartScanが実行され、更に storage index*2が効いているのが判りますね。
160GB近い物理読み込みの殆どがオフロードされて、750KB程度のみが返されています。
※上記、確認用のSQLはセッション単位の累計値なので、テストの度にconnectし直す必要があります。
では、スタンバイ側でも同じようにテストしてみます。
SQL> insert into t1(col1) select col2 from t2 where col1=1;
1000 rows created.
Elapsed: 00:11:09.31
SQL> select n.name,m.value from v$mystat m ,v$statname n where n.STATISTIC#=m.STATISTIC# and
2 (n.name like 'cell physical%' or n.name like 'physical read bytes%' or
3 n.name like 'physical write bytes%' or n.name like 'cell IO%');
NAME VALUE
---------------------------------------------------------------- --------------------
cell physical IO interconnect bytes 160,227,999,744
physical read bytes 160,227,999,744
physical write bytes 0
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 0
cell physical IO bytes eligible for smart IOs 0
cell physical IO bytes saved by columnar cache 0
cell physical IO bytes saved by storage index 0
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO bytes processed for IM capacity 0
cell physical IO bytes processed for IM query 0
cell physical IO bytes processed for no memcompress 0
cell physical IO interconnect bytes returned by smart scan 0
cell physical write bytes saved by smart file initialization 0
cell IO uncompressed bytes 0
cell physical write IO bytes eligible for offload 0
cell physical write IO host network bytes written during offloa 0
18 rows selected.
SmartScanが全く効かず、約16GBがそのまま返されています。
プライマリ側で1秒もかからないので軽いinsertのつもりでしたが、実は16GBも読んでいたのですね。
遅い筈です。
しかしながらスタンバイ側も同一サーバ上に構築しているので、SmartScanの機能そのものは使える筈です。
実際、selectだけ実行すればスタンバイ側でもSmartScanが有効になりました。
どうやらinsert … select … 文のようなDML中で実行されるselectをスタンバイ側で発行した時にはSmartScanが有効にならないようです。
念のため、プライマリースタンバイ間の転送に時間がかかっているのでは無い事を確認する為、0件インサートをテストします。
SQL> insert into t1(col1) select col2 from t2 where col1=0;
0 rows created.
Elapsed: 00:12:27.49
この時のSQLトレース抜粋です。
insert into t1(col1) select col2 from t2 where col1=0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 6 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 6 0 0
SELECT "COL1","COL2" FROM "T2" "A2" WHERE "COL1"=0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 6 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 334.34 762.52 22106795 22115392 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 334.34 762.52 22106795 22115398 0 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to dblink 1 0.00 0.00
cell single block physical read 336 0.00 0.08
cell multiblock physical read 1302110 0.01 518.84
latch free 120 0.00 0.00
latch: gc element 154 0.00 0.00
latch: gcs resource hash 6 0.00 0.00
latch: cache buffers chains 22 0.00 0.00
latch: cache buffers lru chain 7 0.00 0.00
Disk file operations I/O 1 0.00 0.00
0件インサートでも1000件の時と同じくらいの時間がかかり、insert部分ではなくselect部分で物理読み込みによる時間がかかっている事が判ります。
まとめると、
- insert selectのようなDMLの場合、select部分はスタンバイ側で実行される
- そのような時にはスタンバイ側でSmartScanは有効にならない
と、なるようです。
一応、サポートにも確認しましたが、それが「期待された動作」だそうです。
DMLが実行されるのはあくまでプライマリ側なので、プライマリの負荷をオフロードできる訳では無く、高頻度のDMLは勿論、高頻度でなくともロック競合すればプライマリの性能に影響を及ぼしうる・・・
だったらこれ、どういう時に役に立つの?と思って調べたら、英文マニュアルの「概要と管理」に以下の記載がありました。
You can run DML operations on Active Data Guard standby databases.
This enables you to run read-mostly applications, which occasionally execute DMLs, on the standby database.
ほぼselectのみで、たまにDMLが実行されるようなアプリケーションをスタンバイ側で動かす為の機能だそうです。
それであれば、ロックの競合に気を付ければプライマリの負荷をスタンバイ側にオフロードできる事になります。
そういうケースでは役に立ちそうですが、SmartScanが効かないので、Exadata環境には向かないようです。