Things you should know about Oracle Truncate 2019/7/23＠JPOUG Kazuhiro Takahashi

Introduction • Database team lead in a system integration company in Japan • Design, migration, tuning, trouble shooting, consulting with dev team • Oracle to Exadata • Struggling to improve DBA’s position and quality of life • Seminars, blogs, Oracle community… 2 https://tech-oracle.blog.so-net.ne.jp/ https://community.oracle.com/people/Kazuhiro

Agenda 1. What is Truncate? 2. Things you should know about Truncate A) Shared cursors are invalidated and hard parsed B) Table level locked C) Mini-check point D) REUSE STORAGE option may be slower E) Global index maintenance required 12c New feature -Deferred global index maintenance -DDL deferred invalidation 3. Summary 3 NEW

１．What is Truncate? • DDL to remove all rows from a table (partition), no rollback allowed e.g) TRUNCATE TABLE scott.emp • Fast and Efficient (small logs) than DELETE, by lowering HWM (High Water Mark) • Allocated extents are preserved with REUSE STORAGE option e.g.) TRUNCATE TABLE scott.emp REUSE STORAGE *By default (drop storage), all extents are deallocated • Index rebuild and parallelism option (partition) e.g.) ALTER TABLE scott.emp TRUNCATE PARTITION part01 update global indexes parallel 4 *From 12c, more than one partitions can be specified 4

２．Things you should know about Truncate A) Shared cursors are invalidated and hard parsed B) Table level locked C) Mini-check point D) REUSE STORAGE option may be slower E) Global index maintenance required 5

A) Shared cursors are invalidated and hard parsed • DATA_OBJECT_ID (segment version ID) is changed • Related shared cursors are invalidated (for all RAC nodes) • and hard parsed on next execution 6 SQL1 SQL2 SQLn Library Cache Table 1 2 n ・・・ Partitions (2) Shared cursors are invalidated × × × ・・・ (1)TRUNCATE (partition 1) DATA_OBJECT_IDs of the table and all partitions are changed (3) hard parsed on next execution reference TIPS: This happens not only TRUNCATE but also ALTER TABLE/INDEX as well.

B) Table level locked • Upon truncating a partition, exclusive “library cache lock” is held in the table level (Probably in order to invalidate shared cursors) • During above, hard parses on invalidated SQLs have to wait to acquire shared “library cache lock” • Concurrent partition truncate in a table will be serialized • Due to the above, truncating old partitions (even if not accessed) in a table during online time may cause severe performance problem 7 Table 1 2 n ・・・ Partitions (1)TRUNCATE (partition1) SQL1 SQL2 SQLn Library Cache × × × ・・・ (3)Hard parses of invalidated SQLs have to wait to acquire library cache lock (shared) 参照 (2) Acquire library cache lock (exclusive) in table level (4)TRUNCATE (partition2) will always be serialized

C) Mini-check point • Just before TRUNCATE, dirty blocks of the partition are written to storage by mini-check point. The performance depends on the number of dirty blocks • “enq: RO - fast object reuse” wait event 8 Buffer Cache Table 1 2 n ・・・ Partitions (1)TRUNCATE (partition1) (2)I/O wait for mini-check point 1 2 n ・・・ :Dirty blocks Storage

D) REUSE STORAGE option may be slower • Although REUSE STORAGE option preserve allocated extents, all extents should be marked as empty. The performance depends on the number of extents (blocks) 9 1 2 n ・・・ extent 1 extent 2 extent n ・・・ HWM header Storage header extent 1 extent 2 extent n ・・・ header HWM HWM with REUSE STORAGE without REUSE STORAGE (Default) (1)TRUNCATE (partition1) All extents deallocated. Only the segment header is updated All extents BMB must be marked as empty in order to reuse them

Truncate performance with REUSE STORAGE • Truncate performance depends on the sum of extent size • Performance is better without REUSE STORAGE • Irrelevant to UNIFORM 512k, 1024K, AUTO of table space (ASSM) 10 Sum of Extent size [MB] Time [sec] Truncate performance and extent size without REUSE STORAGE with REUSE STORAGE

REUSE STORAGE and extent BMB 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 ・・・ Probably, Truncate with REUSE STORAGE has to update these block status for each extent * How to dump a segment header 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(,,); oradebug setmypid oradebug tracefile_name

E) Global index maintenance required • TRUNCATE partition makes global indexes “unusable” state • UPDATE GLOBAL INDEXES option will automatically update related indexes, keeping “usable” state e.g.) ALTER TABLE scott.emp TRUNCATE PARTITION part01 UPDATE GLOBAL INDEXES PARALLEL 4 • Since full table scan will be executed (Ref1), the performance depends on the number of rows in the table. Consider using PARALLEL option to tune FTS 12 Table 1 2 n ・・・ Partitions (1)TRUNCATE (partition1) Table 1 2 n ・・・ Partitions ・・・ Local index Global index Related local index will be truncated Full table scan by automatic global index maintenace Full table scan by global index maintenence Ref1) Partition Truncate or Drop Taking a Long Time With SQL 'insert /*+ RELATIONAL . . . delete global indexes' (DocID 2177233.1) Ref2) Tips for drop partition operation (DocID 1489462.1) Inaccessible unless TRUNCATE with UPDATE GLOBAL INDEXES option Accessible (1)TRUNCATE (partition1)

[12c] Asynchronous global index maintenance • From 12cR1, global index maintenance by TRUNCATE/DROP partition will be done asynchronously (default) • Due to metadata-only index maintenance, the command is performed quickly • Although orphaned entries will be left on index leaf blocks, queries can ignore these entries and return correct results • Orphaned entries can be checked by USER_INDEXES.ORPHANED_ENTRIES • YES: included • NO: not included • Automatic scheduler jobs to maintenance orphaned entries • SYS.PMO_DEFERRED_GIDX_MAINT_JOB …2:00 A.M.(default) • DBMS_SCHEDULER.RUN_JOB …manually • DBMS_PART.CLEANUP_GIDX to cleanup global indexes e.g.) exec dbms_part.cleanup_gidx(‘SCOTT’, ‘EMP'); • For old behavior (support confirmation required) • "_fast_index_maintenance"=false 13 Ref) How to Drop/Truncate Multiple Partitions in Oracle 12C (DocID 1482264.1) 12c update

Behind the scene (just a guess) indexobj# tabpartobj# 12345 67890 ・・・ 14 Table 1 2 n ・・・ Partitions Global index Global index is not maintenance, only meta-data is updated. Queries will return correct result with the meta-data (3)Query executed, find ROWIDs in leaf blocks (1)TRUNCATE (partition1) (4)For each ROWID, filter orphaned entries (filter by TBL$OR$IDX$PART$NUM function) (2) Metadata-only index maintenance (5) Get rows in partitions via filtered ROWIDs sys.index_orphand_entry$ • “Metadata-only index maintenance” means recording OBJECT_IDs of truncated segment and related (global) indexes • Beginning 6 bytes out of 18 byte ROWID in leaf blocks means partition’s (segment’s) OBJECT_ID that the row belongs to • From above two information, Oracle will know if a ROWID is orphaned or not without actually accessing the partition • TBL$OR$IDX$PART$NUM function to filter orphaned ROWIDs (it returns 1 if not orphaned) Truncated table/partition’s OBJECT_ID Related index’s OBJECT_ID SQL (6) Automatic scheduler job to remove orphaned entries asynchronously 12c update

[12c] DDL deferred invalidation • From 12cR2, Oracle avoids (or postpones) shared cursor invalidation if possible e.g.) alter table xxx truncate partition yyy update global indexes deferred invalidation 15 12c update *Evaluation • Range-partitioned “Sales” table with local/global index (FTS, Index range scan) • Partition truncate with/without deferred invalidation option (update global indexes) • Check v$sql’s invalidation, last_load_time (last hard parse 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 -------------------------------------------------------------------- Local index Global index

[12c] DDL deferred invalidation • FTS and INDEX RANGE SCAN with a local index can avoid cursor invalidation with deferred invalidation option • INDEX RANGE SCAN with a global index had no effect, same behavior as before 16 12c update パターン 操作 deferred invalidationなし deferred invalidationあり invalidation last_load_time invalidation last_load_time Full scan select * from sales 0 19:02:34 0 19:06:20 alter table … truncate partition 1 19:02:34 0 19:06:20 select * from sales 1 19:04:43 0 19:06:20 Index range scan (local) select * from sales where prod_id>0 0 19:02:42 0 19:06:28 alter table … truncate partition 1 19:02:42 0 19:06:28 select * from sales where prod_id>0 1 19:04:50 0 19:06:28 Index range scan (global) select * from sales where amount_sold>0 0 19:02:36 0 19:06:26 alter table … truncate partition 1 19:02:36 1 19:06:26 select * from sales where amount_sold>0 1 19:04:47 1 19:07:38 Cursor invalidated Hard parse

３．Summary • TRUNCATE should not be executed easily. Avoid online time • TRUNCATE may block SQLs, even if partition is different • TRUNCATE may take time when the table is frequently updated • REUSE STORAGE option may be slower. Performance depends on segment size • Avoid global indexes. If not, consider its maintenance • Put together one TRUNCATE as possible as you can • Catch up with 12c new features 17 Table 1 2 n ・・・ Partitions (1)TRUNCATE (partition) and INSERT x n parallel Table 1 2 n ・・・ Partitions (1)TRUNCATE (table) (2)INSERT (x n parallel) TRUNCATE be serialized and SQLs are blocked with hard parse related wait event By putting TRUNCATE together, AP is more scalable AP cannot benefit from concurrent execution

18