トランケートについて知っておくべきこと

2019/7/23@JPOUG講演資料

Transcript

1. Oracleのトランケートについて 知っておくべきこと 2019/7/23＠JPOUG Kazuhiro Takahashi
2. 自己紹介 • 某SIerのDBエンジニア。PJでDBチームのリーダ • 設計、移行、パフォチュー、トラシュー、業務のお悩み相談など • Oracle→近年はExadataメイン • SI・運用におけるDBAの地位と生活の質(QoL)の向上を目指し活動中 • DB勉強会、ブログ執筆やOracleコミュニティ活動 等 2 https://tech-oracle.blog.so-net.ne.jp/ https://community.oracle.com/people/Kazuhiro
3. アジェンダ 1. トランケートとは 2. 知っておくべきこと ① 共有カーソルが無効化され、ハードパースが走る ② テーブルレベルのロックがかかる ③ ミニ･チェックポイントが走る ④ REUSE STORAGEを付けると遅くなることがある ⑤ グローバル索引のメンテナンスが必要 12c新機能について ～非同期グローバル索引メンテナンスとDDL遅延カーソル無効化 3. まとめ 3 NEW
4. １．トランケートとは • テーブル（パーティション）内のレコードを削除するDDL（＝ロールバックはできない） 例） TRUNCATE TABLE scott.emp • HWM（ハイウォーターマーク）を下げることにより、テーブル内のレコードを削除するため、DELETEよ り一般的に高速であり、ログ量も小さい • REUSE STORAGEオプションを付けると確保済みエクステントを保持する 例） TRUNCATE TABLE scott.emp REUSE STORAGE ※デフォルトはdrop storageのため、エクステントは開放される • パーティションレベルのトランケートはREUSE STORAGEオプションのほか、索引のリビルド・パラレル等 のオプションが指定可能 例） ALTER TABLE scott.emp TRUNCATE PARTITION part01 (...オプション) ※12cから複数のパーティションを指定可能 4
5. ２．知っておくべきこと ① 共有カーソルが無効化され、ハードパースが走る ② テーブルレベルのロックがかかる ③ ミニ･チェックポイントが走る ④ REUSE STORAGEを付けると遅くなることがある ⑤ グローバル索引のメンテナンスが必要 5
6. ①共有カーソルが無効化され、ハードパースが走る • DATA_OBJECT_ID（セグメントのバージョンを管理するID）が変更される • 当該オブジェクトに係るSQLの共有カーソルがINVALIDになる ※RACでは全ノードで関連するSQLがINVALIDとなる • 次回SQL実行時にハードパースが走る 6 SQL1 SQL2 SQLn ライブラリ キャッシュ テーブル 1 2 n ・・・ パーティション ②共有カーソルが INVALIDとなる × × × ・・・ ①トランケート （パーティション1） テーブルおよびパーティション のDATA_OBJECT_IDが変更 ③次回実行時に ハードパースが走る 参照 TIPS: ALTER TABLEやALTER INDEX等でも同様の挙動が発生する。どのような処理で共有カーソルが INVALIDになるかについては下記ドキュメント参照。 参考:共有カーソルがINVALIDになる処理(KROWN:101305) (ドキュメントID 1731739.1)
7. ②テーブルレベルのロックがかかる • 特定のパーティションのトランケートでも、テーブルレベルで’library cache lock’(排他モード)を獲得 する ※おそらく共有カーソルを無効化するため • ハードパースが必要なSQLは、 ’library cache lock’を共有モードで獲得する必要があるため、待 ちが発生する • 異なるパーティションのトランケート処理を並列実行したとしても、上記理由から処理はシリアライズさ れてしまう 7 テーブル 1 2 n ・・・ パーティション ①トランケート （パーティション1） SQL1 SQL2 SQLn ライブラリ キャッシュ × × × ・・・ ③次回実行時の ハードパース時に ロック待ちによる 待機が発生 参照 ②テーブルレベルで library cache lock （排他）を獲得 ④トランケート （パーティション2） はロック待ちのため シリアライズされる
8. ③ミニ･チェックポイントが走る • トランケート前に、ミニ・チェックポイントが走るため、当該テーブル（パーティション）のバッファキャッシュ 上のダーティブロックがストレージに書き込まれる。このため、バッファキャッシュ上のダーティ・ブロックが多 いと、この処理に時間がかかることがある • enq: RO - fast object reuseの待機が発生する 8 バッファ キャッシュ テーブル 1 2 n ・・・ パーティション ①トランケート （パーティション1） ②ミニ・チェック ポイントが走り I/O待ちが発生 1 2 n ・・・ :ダーティブロック ストレージ
9. ④REUSE STORAGEを付けると遅くなることがある • REUSE STORAGEはエクステントの開放をしないが、空ブロックであるというマークをする必要がある ため、エクステント数（ブロック数）に応じてトランケート時間が延びる • INSERT性能とのトレードオフも要考慮だが、REUSE STORAGEなしの方が速いことがある ※REUSE STORAGEの性能について を参照 9 1 2 n ・・・ エクステント1 エクステント2 エクステントn ・・・ HWM ヘッダ ストレージ ヘッダ エクステント1 エクステント2 エクステントn ・・・ ヘッダ HWM HWM REUSE STORAGEあり REUSE STORAGEなし （デフォルト） ①トランケート （パーティション1） エクステントを開放し、 更新はセグメント・ヘッダ部のみ エクステントを再利用するため 全てのエクステントのヘッダ部に アクセスし「空き」状態に更新する
10. 【参考】REUSE STORAGEの性能について • トランケート時間は、概ねエクステントサイズに依存して増加する • 増加の傾向として、REUSE STORAGEをつけない方が高速 • 表領域のセグメント管理方式は、 UNIFORM512K、UNIFORM1024K、AUTOいずれも大差な い 10 トランケート対象 合計エクステント サイズ[MB] 時間 [秒] トランケート処理時間とエクステントサイズの関係 REUSE STORAGE なし REUSE STORAGE あり
11. 【参考】 REUSE STORAGEのエクステントの開放処理 11 Segment Dump: segment dba = 15 : 0x04d24263 Segment Type - Pagetable Segment Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 181 #blocks: 5792 last map 0x00000000 #maps: 0 offset: 5452 Highwater:: 0x04d24264 ext#: 0 blk#: 4 ext size: 32 ・・・ Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x04d24260 Data dba: 0x04d24264 Extent 1 : L1 dba: 0x04c0a780 Data dba: 0x04c0a781 Extent 2 : L1 dba: 0x04c0a780 Data dba: 0x04c0a7a0 ・・・ Extent 178 : L1 dba: 0x04dc0320 Data dba: 0x04dc0380 Extent 179 : L1 dba: 0x04dc0320 Data dba: 0x04dc03a0 Extent 180 : L1 dba: 0x04dc0320 Data dba: 0x04dc03c0 -------------------------------------------------------- Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 1 parent dba: 0x04d24262 poffset: 0 unformatted: 12 total: 16 first useful block: 4 owning instance : 1 instance ownership changed at Last successful Search Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0 Extent Map Block Offset: 4294967295 First free datablock : 4 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Dealloc scn: 718553152.0 Flag: 0x00000021 (OBJD/-/-/-/-/HWM) Inc #: 0 Objd: 3517752 HWM Flag: HWM Set Highwater:: 0x04d24264 ext#: 0 blk#: 4 ext size: 32 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x04d24260 Length: 16 Offset: 0 0:Metadata 1:Metadata 2:Metadata 3:Metadata 4:unformatted 5:unformatted 6:unformatted 7:unformatted 8:unformatted 9:unformatted 10:unformatted 11:unformatted 12:unformatted 13:unformatted 14:unformatted 15:unformatted ・・・ Truncate REUSE STORAGEはこれらを 全てのエクステントに対して更新する必要がある ◆セグメントダンプの取得方法 select segment_name, partition_name,extents,bytes, tablespace_name, relative_fno, header_block from dba_segments where owner=‘スキーマ名’ and segment_name=‘表名’ and partition_name=‘パーティション名' order by partition_name; alter session set tracefile_identifier=‘ログファイル識別名'; exec dbms_space_admin.segment_dump('表領域名',<relative_fno >,<header_block>); oradebug setmypid oradebug tracefile_name
12. ⑤グローバル索引のメンテナンスが必要 • パーティションのトランケートでグローバル索引がアクセス不可(unusable)となる • update global indexesオプションでグローバル索引の自動更新が可能 例） ALTER TABLE scott.emp TRUNCATE PARTITION part01 UPDATE GLOBAL INDEXES PARALLEL 4 • 内部的にフルスキャンが走る（参考１）ため、テーブル全体の件数に応じて時間がかかる • このフルスキャンのパラレル度はparallel句で指定可能 12 テーブル 1 2 n ・・・ パーティション ①トランケート （パーティション1） テーブル 1 2 n ・・・ パーティション ①トランケート （パーティション1） ・・・ ローカル索引 グローバル索引 関連するローカル索引のみ トランケートされる グローバル索引の自動更新で テーブルフルスキャンが走る グローバル索引の メンテによるフル テーブルスキャン 参考１）Partition Truncate or Drop Taking a Long Time With SQL 'insert /*+ RELATIONAL . . . delete global indexes' (ドキュ メントID 2177233.1) 参考２）Tips for drop partition operation (ドキュメントID 1489462.1) UPDATE GLOBAL INDEXESで トランケートすればアクセ ス可 アクセス可
13. 【12c】非同期グローバル索引メンテナンス • 12cR1～では非同期グローバル索引メンテナンス機能により、トランケートやDROPに伴うグローバル 索引メンテナンスを非同期に行う（デフォルト） • メタデータのみの変更となるため、コマンドは高速で処理される。索引のリーフブロックには参照できない (orphaned)エントリが残るが、クエリはこれを無視することでエラーにならない仕組み • グローバル索引にorphanedエントリが含まれるかはUSER_INDEXES.ORPHANED_ENTRIES で確認できる • YES: 含まれる • NO: 含まれない • 参照できないROWIDメンテナンスは自動スケジューラ・ジョブで定期的に実行される ※SYS.PMO_DEFERRED_GIDX_MAINT_JOB ・・・2:00 A.M.起動（デフォルト） ※手動実行はDBMS_SCHEDULER.RUN_JOB • DBMS_PART.CLEANUP_GIDXでグローバル索引のクリーンナップが可能 例）exec dbms_part.cleanup_gidx(‘SCOTT’, ‘EMP'); • 従来の動作にするためには、サポートに要確認 • "_fast_index_maintenance"=false 13 参考）How to Drop/Truncate Multiple Partitions in Oracle 12C (ドキュメントID 1482264.1) 12c update
14. 仕組み（想像） tabpartobj# indexobj# 67890 12345 ・・・ 14 テーブル 1 2 n ・・・ パーティション グローバル索引 グローバル索引はメンテされず、メタデータのみ更新される クエリはメタデータの情報を利用し正しい結果を返す ③クエリ発行。ブランチ経 由でリーフに到達。リーフ を辿って検索範囲の ROWIDを取得 ①トランケート （パーティション1） ④各ROWIDからorphandを除去 (TBL$OR$IDX$PART$NUM関 数によるフィルタ) ②トランケート対象パーティションと 関連する索引の情報が記録され る(グローバル索引は変更なし） ⑤フィルタ済みROWID をもとにパーティションにア クセスし行を取得 sys.index_orphand_entry$ • truncate partitionした後のメタデータの更新とは、 グローバル索引のオブジェクトIDと、トランケート したセグメントのオブジェクトIDの関連を保持すること • ROWID (18バイト)の先頭6バイトがオブジェクトIDを示す。グローバル索引のリーフブロックに格納さ れるROWIDから、それがどのパーティション（セグメント）に属しているかわかる • 上記の情報を利用し、TBL$OR$IDX$PART$NUM関数に表の名前とROWIDを与え、ROWID がorphanedなレコードをフィルタする（orphandでなければ1を返す） トランケートされた パーティションの OBJECT_ID 関連する索引の OBJECT_ID SQL ⑥自動スケジューラジョブ 等で非同期にorphand エントリが除去される 12c update
15. 【12c】遅延カーソル無効化(DDL deferred invalidation) • 12cR2～ではDDLに対して即時に共有カーソルの無効化をせず、可能なものは無効化を回避し、 避けられないものはローリング無効化をすることができるようになった 例）alter table xxx truncate partition yyy update global indexes deferred invalidation 15 12c update ◆検証内容 • レンジパーティション＋ローカル/グローバル索引のsales表に対しSQLを実行（フルスキャン・索引レンジスキャン） • パーティショントランケートをdeferred invalidationオプションあり・なしで確認(update global indexesあり) • v$sqlのinvalidation（無効化された回数）、last_load_time（前回ハードパースの時刻）に着目 CREATE TABLE scott.sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('20060401','YYYYMMDD')) , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('20060701','YYYYMMDD')) , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('20061001','YYYYMMDD')) , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('20061201','YYYYMMDD')) ); insert into scott.sales values(1,1,to_date('20060101','YYYYMMDD'),'1',1,1,1); insert into scott.sales values(2,1,to_date('20060401','YYYYMMDD'),'1',1,1,1); insert into scott.sales values(3,1,to_date('20060701','YYYYMMDD'),'1',1,1,1); insert into scott.sales values(4,1,to_date('20061001','YYYYMMDD'),'1',1,1,1); commit; create index scott.amount_sold_ix on scott.sales(amount_sold); create index scott.prod_id_ix on scott.sales(prod_id) local; SQL> select /* trunctest01 */ * from scott.sales; -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 328 | 25256 | 2 | 1 | PARTITION RANGE ALL | | 328 | 25256 | 2 | 2 | TABLE ACCESS STORAGE FULL| SALES | 328 | 25256 | 2 -------------------------------------------------------------------- SQL> select /* trunctest01 */ * from scott.sales where prod_id>0; -------------------------------------------------------------------- | Id | Operation | Name | Ro -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | PARTITION RANGE ALL | | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | |* 3 | INDEX RANGE SCAN | PROD_ID_IX | -------------------------------------------------------------------- SQL> select /* trunctest01 */ * from scott.sales where amount_sold>0 -------------------------------------------------------------------- | Id | Operation | Name -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES |* 2 | INDEX RANGE SCAN | AMOUNT_SOLD_IX -------------------------------------------------------------------- ローカル索引 グローバル索引
16. 【12c】遅延カーソル無効化(DDL deferred invalidation) • 検証したところ、deferred invalidation付きのパーティショントランケートでは、フルスキャンおよび ローカル索引のカーソル無効化を回避できることを確認 • グローバル索引を使用している共有カーソルはdeferred invalidationでも従来通りの挙動となった 16 12c update deferred invalidationあり deferred invalidationなし 操作 パターン last_load_time invalidation last_load_time invalidation 19:06:20 0 19:02:34 0 select * from sales Full scan 19:06:20 0 19:02:34 1 alter table … truncate partition 19:06:20 0 19:04:43 1 select * from sales 19:06:28 0 19:02:42 0 select * from sales where prod_id>0 Index range scan (local) 19:06:28 0 19:02:42 1 alter table … truncate partition 19:06:28 0 19:04:50 1 select * from sales where prod_id>0 19:06:26 0 19:02:36 0 select * from sales where amount_sold>0 Index range scan (global) 19:06:26 1 19:02:36 1 alter table … truncate partition 19:07:38 1 19:04:47 1 select * from sales where amount_sold>0 ハード パース ハード パース カーソル 無効化 カーソル 無効化 ハード パース カーソル 無効化 ハード パース カーソル 無効化
17. ３．まとめ • 運用中、トランケートは安易に実行しないこと。やるならオン中は避けること • パーティションが異なっていても待機が発生することを肝に銘じること • 更新が多いテーブルのトランケートは時間がかかることを考慮すること • REUSE STORAGEは速いと思い込まないこと。サイズに応じて時間がかかる • グローバル索引は極力作らないこと（使うならメンテ方法も考えること） • スケーラブルな処理方式にするためには、なるべくまとめてトランケートすること • 12c新機能は押さえておくこと 17 テーブル 1 2 n ・・・ パーティション ①トランケート （パーティション）と INSERTをn多重で実行 テーブル 1 2 n ・・・ パーティション ①トランケート （テーブル） ②INSERT （n多重） ハードパースが多発し SQLが待機する。 トランケート処理が シリアライズ化されてしまう まとめてトランケートする ことでスケールする処理方式に 多重化のメリットを享受 できないAP処理方式