Starting with Oracle 12c Asynchronous Global Index Maintenance for drop and Truncate operation are opimtized by making the index maintance for metadata only.
What oracle does is maintain a list of invalid data object ids and ignore those entries in the index from then on. Below is a comparision demo of 12c and 11g databases.
Table 'T' is loaded with 1,000,000 records and a has a global index 'T_IDX'
rajesh@PDB1>
rajesh@PDB1> create table t
2 partition by list(x)
3 (
4 partition p1 values (1),
5 partition p2 values (2),
6 partition p3 values (3),
7 partition p4 values (4),
8 partition p5 values (5)
9 )
10 nologging as
11 select b.*, mod(rownum,5)+1 as x,rownum as y
12 from big_table b ;
Table created.
Elapsed: 00:00:08.71
rajesh@PDB1> create index t_idx on t(owner,object_type,object_name,y) nologging;
Index created.
Elapsed: 00:00:15.17
rajesh@PDB1> column redo_size new_value r
rajesh@PDB1> column logical_io new_value l
rajesh@PDB1>
rajesh@PDB1> select nvl(redo_size,0) as redo_size,
2 nvl(logical_io,0) as logical_io
3 from (
4 select s2.value,s1.name
5 from v$sysstat s1,
6 v$mystat s2
7 where s1.statistic# = s2.statistic#
8 and s1.name in ('redo size','db block gets')
9 )
10 pivot
11 ( sum(value)
12 for name in ('redo size' as redo_size,'db block gets' as logical_io) ) ;
REDO_SIZE LOGICAL_IO
---------- ----------
739224 31356
1 row selected.
Elapsed: 00:00:00.16
rajesh@PDB1> alter table t drop partition p1 update global indexes ;
Table altered.
Elapsed: 00:00:00.21
rajesh@PDB1> select redo_size - &r as redo_diff,
2 nvl(logical_io,0) - &l as logical_io_diff
3 from (
4 select s2.value,s1.name
5 from v$sysstat s1,
6 v$mystat s2
7 where s1.statistic# = s2.statistic#
8 and s1.name in ('redo size','db block gets')
9 )
10 pivot
11 ( sum(value)
12 for name in ('redo size' as redo_size,'db block gets' as logical_io) ) ;
old 1: select redo_size - &r as redo_diff,
new 1: select redo_size - 739224 as redo_diff,
old 2: nvl(logical_io,0) - &l as logical_io_diff
new 2: nvl(logical_io,0) - 31356 as logical_io_diff
REDO_DIFF LOGICAL_IO_DIFF
---------- ---------------
10924 55
1 row selected.
Elapsed: 00:00:00.11
rajesh@PDB1>
the drop partition statement is pretty fast in 12c database and generated only 10KB of redo and modified 55 blocks. while the same operation in 11g generated about 14MB or redo and produced 29K logical IO's
rajesh@ORA11GR2> column redo_size new_value r
rajesh@ORA11GR2> column logical_io new_value l
rajesh@ORA11GR2> select nvl(redo_size,0) as redo_size,
2 nvl(logical_io,0) as logical_io
3 from (
4 select s2.value,s1.name
5 from v$sysstat s1,
6 v$mystat s2
7 where s1.statistic# = s2.statistic#
8 and s1.name in ('redo size','db block gets')
9 )
10 pivot
11 ( sum(value)
12 for name in ('redo size' as redo_size,'db block gets' as logical_io) ) ;
REDO_SIZE LOGICAL_IO
---------- ----------
640716 30701
1 row selected.
Elapsed: 00:00:00.00
rajesh@ORA11GR2> alter table t drop partition p1 update global indexes ;
Table altered.
Elapsed: 00:00:08.35
rajesh@ORA11GR2>
rajesh@ORA11GR2> select redo_size - &r as redo_diff,
2 nvl(logical_io,0) - &l as logical_io_diff
3 from (
4 select s2.value,s1.name
5 from v$sysstat s1,
6 v$mystat s2
7 where s1.statistic# = s2.statistic#
8 and s1.name in ('redo size','db block gets')
9 )
10 pivot
11 ( sum(value)
12 for name in ('redo size' as redo_size,'db block gets' as logical_io) ) ;
old 1: select redo_size - &r as redo_diff,
new 1: select redo_size - 640716 as redo_diff,
old 2: nvl(logical_io,0) - &l as logical_io_diff
new 2: nvl(logical_io,0) - 30701 as logical_io_diff
REDO_DIFF LOGICAL_IO_DIFF
---------- ---------------
14214048 29004
1 row selected.
Elapsed: 00:00:00.02
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2>
Basically, when dropping (or truncating) a table partition, Oracle in 12c now “postpones” the actual removal of the global index entries associated with the dropped/truncated partition. This can now be performed asynchronously at a time of our choosing. So it’s therefore now very quick and very cheap to update these global indexes on the fly
However, most importantly, the indexes are still usable and can be guaranteed to return the correct results, ignoring any orphaned entires as required. These can be easily ignored as they all have an object number in the index entry rowids associated with the dropped table partition object and not the table partition(s) of interest as required by the queries
rajesh@PDB1> select index_name,status,ORPHANED_ENTRIES
2 from user_indexes
3 where index_name ='T_IDX' ;
INDEX_NAME STATUS ORP
-------------------- -------- ---
T_IDX VALID YES
1 row selected.
Elapsed: 00:00:00.01
rajesh@PDB1>
So if we now select values via the indexed columns that only spans data in the dropped table partition:
rajesh@PDB1> select *
2 from t
3 where owner = 'PUBLIC'
4 and object_type ='SYNONYM'
5 and object_name = 'V$MAP_LIBRARY'
6 and y between 20 and 40
7 /
no rows selected
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1562714629
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T | 1 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM' AND "OBJECT_NAME"='V$MAP_LIBRARY' AND "Y">=20
AND "Y"<=40)
filter(TBL$OR$IDX$PART$NUM("T",0,8,0,"T".ROWID)=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1437 bytes sent via SQL*Net to client
355 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
We notice that quite correctly, no rows are now returned.
If we look at the INDEX_STATS of these indexes, we notice at one level that the orphaned index entries are counted as if they’re deleted entries:
rajesh@PDB1> analyze index t_idx validate structure;
Index analyzed.
Elapsed: 00:00:03.21
rajesh@PDB1> select name,lf_rows,del_lf_rows from index_stats;
NAME LF_ROWS DEL_LF_ROWS
-------------------------------------------------- ---------- -----------
T_IDX 1000000 200000
1 row selected.
Elapsed: 00:00:00.10
rajesh@PDB1>
We see that the index statistics is indicating that there are 200K -called deleted index entries. The validation process is ensuring that the orphaned index entries only reference partitions that indeed no longer exist and counts such entries as deleted ones.
So it currently looks we’ve got the best of both worlds here. We effectively get the same performance during the drop table partition operation as if we don’t maintain the global indexes but get the same index availability and subsequent query performance as if we do.
Well, very importantly, unlike actual deleted index entries, they are not readily removed and their space reused by subsequent DML activities within the leaf blocks. In fact, these orphaned index entries can even “get in the way” as we see here when we attempt to reinsert the same data back into table:
rajesh@PDB1> alter table t add partition p1 values (1);
Table altered.
Elapsed: 00:00:00.29
rajesh@PDB1> insert into t
2 select b.*, 1 as x,rownum as y
3 from big_table b
4 where rownum <= 1000;
1000 rows created.
Elapsed: 00:00:00.37
rajesh@PDB1> commit;
Commit complete.
Elapsed: 00:00:00.00
rajesh@PDB1>
rajesh@PDB1> analyze index t_idx validate structure;
Index analyzed.
Elapsed: 00:00:03.10
rajesh@PDB1> select name,lf_rows,del_lf_rows from index_stats;
NAME LF_ROWS DEL_LF_ROWS
-------------------------------------------------- ---------- -----------
T_IDX 1001000 200000
1 row selected.
Elapsed: 00:00:00.02
rajesh@PDB1>
We notice that unlike actual deleted index entries in which all the deleted space would have simply have been reused, we see instead that none of the space occupied by the orphaned rows has been reused.
What oracle does is maintain a list of invalid data object ids and ignore those entries in the index from then on. Below is a comparision demo of 12c and 11g databases.
Table 'T' is loaded with 1,000,000 records and a has a global index 'T_IDX'
rajesh@PDB1>
rajesh@PDB1> create table t
2 partition by list(x)
3 (
4 partition p1 values (1),
5 partition p2 values (2),
6 partition p3 values (3),
7 partition p4 values (4),
8 partition p5 values (5)
9 )
10 nologging as
11 select b.*, mod(rownum,5)+1 as x,rownum as y
12 from big_table b ;
Table created.
Elapsed: 00:00:08.71
rajesh@PDB1> create index t_idx on t(owner,object_type,object_name,y) nologging;
Index created.
Elapsed: 00:00:15.17
rajesh@PDB1> column redo_size new_value r
rajesh@PDB1> column logical_io new_value l
rajesh@PDB1>
rajesh@PDB1> select nvl(redo_size,0) as redo_size,
2 nvl(logical_io,0) as logical_io
3 from (
4 select s2.value,s1.name
5 from v$sysstat s1,
6 v$mystat s2
7 where s1.statistic# = s2.statistic#
8 and s1.name in ('redo size','db block gets')
9 )
10 pivot
11 ( sum(value)
12 for name in ('redo size' as redo_size,'db block gets' as logical_io) ) ;
REDO_SIZE LOGICAL_IO
---------- ----------
739224 31356
1 row selected.
Elapsed: 00:00:00.16
rajesh@PDB1> alter table t drop partition p1 update global indexes ;
Table altered.
Elapsed: 00:00:00.21
rajesh@PDB1> select redo_size - &r as redo_diff,
2 nvl(logical_io,0) - &l as logical_io_diff
3 from (
4 select s2.value,s1.name
5 from v$sysstat s1,
6 v$mystat s2
7 where s1.statistic# = s2.statistic#
8 and s1.name in ('redo size','db block gets')
9 )
10 pivot
11 ( sum(value)
12 for name in ('redo size' as redo_size,'db block gets' as logical_io) ) ;
old 1: select redo_size - &r as redo_diff,
new 1: select redo_size - 739224 as redo_diff,
old 2: nvl(logical_io,0) - &l as logical_io_diff
new 2: nvl(logical_io,0) - 31356 as logical_io_diff
REDO_DIFF LOGICAL_IO_DIFF
---------- ---------------
10924 55
1 row selected.
Elapsed: 00:00:00.11
rajesh@PDB1>
the drop partition statement is pretty fast in 12c database and generated only 10KB of redo and modified 55 blocks. while the same operation in 11g generated about 14MB or redo and produced 29K logical IO's
rajesh@ORA11GR2> column redo_size new_value r
rajesh@ORA11GR2> column logical_io new_value l
rajesh@ORA11GR2> select nvl(redo_size,0) as redo_size,
2 nvl(logical_io,0) as logical_io
3 from (
4 select s2.value,s1.name
5 from v$sysstat s1,
6 v$mystat s2
7 where s1.statistic# = s2.statistic#
8 and s1.name in ('redo size','db block gets')
9 )
10 pivot
11 ( sum(value)
12 for name in ('redo size' as redo_size,'db block gets' as logical_io) ) ;
REDO_SIZE LOGICAL_IO
---------- ----------
640716 30701
1 row selected.
Elapsed: 00:00:00.00
rajesh@ORA11GR2> alter table t drop partition p1 update global indexes ;
Table altered.
Elapsed: 00:00:08.35
rajesh@ORA11GR2>
rajesh@ORA11GR2> select redo_size - &r as redo_diff,
2 nvl(logical_io,0) - &l as logical_io_diff
3 from (
4 select s2.value,s1.name
5 from v$sysstat s1,
6 v$mystat s2
7 where s1.statistic# = s2.statistic#
8 and s1.name in ('redo size','db block gets')
9 )
10 pivot
11 ( sum(value)
12 for name in ('redo size' as redo_size,'db block gets' as logical_io) ) ;
old 1: select redo_size - &r as redo_diff,
new 1: select redo_size - 640716 as redo_diff,
old 2: nvl(logical_io,0) - &l as logical_io_diff
new 2: nvl(logical_io,0) - 30701 as logical_io_diff
REDO_DIFF LOGICAL_IO_DIFF
---------- ---------------
14214048 29004
1 row selected.
Elapsed: 00:00:00.02
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2>
Basically, when dropping (or truncating) a table partition, Oracle in 12c now “postpones” the actual removal of the global index entries associated with the dropped/truncated partition. This can now be performed asynchronously at a time of our choosing. So it’s therefore now very quick and very cheap to update these global indexes on the fly
However, most importantly, the indexes are still usable and can be guaranteed to return the correct results, ignoring any orphaned entires as required. These can be easily ignored as they all have an object number in the index entry rowids associated with the dropped table partition object and not the table partition(s) of interest as required by the queries
rajesh@PDB1> select index_name,status,ORPHANED_ENTRIES
2 from user_indexes
3 where index_name ='T_IDX' ;
INDEX_NAME STATUS ORP
-------------------- -------- ---
T_IDX VALID YES
1 row selected.
Elapsed: 00:00:00.01
rajesh@PDB1>
So if we now select values via the indexed columns that only spans data in the dropped table partition:
rajesh@PDB1> select *
2 from t
3 where owner = 'PUBLIC'
4 and object_type ='SYNONYM'
5 and object_name = 'V$MAP_LIBRARY'
6 and y between 20 and 40
7 /
no rows selected
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1562714629
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T | 1 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM' AND "OBJECT_NAME"='V$MAP_LIBRARY' AND "Y">=20
AND "Y"<=40)
filter(TBL$OR$IDX$PART$NUM("T",0,8,0,"T".ROWID)=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1437 bytes sent via SQL*Net to client
355 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
We notice that quite correctly, no rows are now returned.
If we look at the INDEX_STATS of these indexes, we notice at one level that the orphaned index entries are counted as if they’re deleted entries:
rajesh@PDB1> analyze index t_idx validate structure;
Index analyzed.
Elapsed: 00:00:03.21
rajesh@PDB1> select name,lf_rows,del_lf_rows from index_stats;
NAME LF_ROWS DEL_LF_ROWS
-------------------------------------------------- ---------- -----------
T_IDX 1000000 200000
1 row selected.
Elapsed: 00:00:00.10
rajesh@PDB1>
We see that the index statistics is indicating that there are 200K -called deleted index entries. The validation process is ensuring that the orphaned index entries only reference partitions that indeed no longer exist and counts such entries as deleted ones.
So it currently looks we’ve got the best of both worlds here. We effectively get the same performance during the drop table partition operation as if we don’t maintain the global indexes but get the same index availability and subsequent query performance as if we do.
Well, very importantly, unlike actual deleted index entries, they are not readily removed and their space reused by subsequent DML activities within the leaf blocks. In fact, these orphaned index entries can even “get in the way” as we see here when we attempt to reinsert the same data back into table:
rajesh@PDB1> alter table t add partition p1 values (1);
Table altered.
Elapsed: 00:00:00.29
rajesh@PDB1> insert into t
2 select b.*, 1 as x,rownum as y
3 from big_table b
4 where rownum <= 1000;
1000 rows created.
Elapsed: 00:00:00.37
rajesh@PDB1> commit;
Commit complete.
Elapsed: 00:00:00.00
rajesh@PDB1>
rajesh@PDB1> analyze index t_idx validate structure;
Index analyzed.
Elapsed: 00:00:03.10
rajesh@PDB1> select name,lf_rows,del_lf_rows from index_stats;
NAME LF_ROWS DEL_LF_ROWS
-------------------------------------------------- ---------- -----------
T_IDX 1001000 200000
1 row selected.
Elapsed: 00:00:00.02
rajesh@PDB1>
We notice that unlike actual deleted index entries in which all the deleted space would have simply have been reused, we see instead that none of the space occupied by the orphaned rows has been reused.
No comments:
Post a Comment