Global Index Maintenance – Pre 12c (Unwashed and Somewhat Slightly Dazed) July 26, 2013
Posted by Richard Foote in Global Indexes, Oracle Indexes, Partitioning.trackback
Before I discuss another Oracle Database 12c new feature, Asynchronous Global Index Maintenance, thought it might be worthwhile discussing how Global Indexes were handled prior to 12c.
I’ll begin by creating and populating a simple range partitioned table:
SQL> create table muse (id number, code number, name varchar2(30)) partition by range (id) (partition muse1 values less than (1000001), partition muse2 values less than (2000001), partition muse3 values less than (maxvalue)); Table created. SQL> insert into muse select rownum, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level <= 3000000; 3000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed.
I’ll now create two global indexes, one non-partitioned, the other partitioned:
SQL> create index muse_id_i on muse(id); Index created. SQL> create index muse_code_i on muse(code) global partition by range(code) (partition code_p1 values less than (50000), partition code_p2 values less than (maxvalue)); Index created. SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO' union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME NUM_ROWS BLOCKS LEAF_BLOCKS STATUS --------------- --------------- ---------- ---------- ----------- -------- MUSE_CODE_I CODE_P1 1500000 4224 4135 USABLE MUSE_CODE_I CODE_P2 1500000 4352 4177 USABLE MUSE_ID_I 3000000 9216 8633 VALID
So we currently have two happy chappy global indexes. I’m now however going to drop one of the table partitions without updating the global indexes and monitor both the db block gets and amount of redo that gets generated:
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=129; NAME VALUE ------------- ---------- db block gets 457109 redo size 234309652 SQL> alter table muse drop partition muse1; Table altered. Elapsed: 00:00:00.66 SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=129; NAME VALUE -------------- ---------- db block gets 457165 redo size 234320512
We notice the operation completed very quickly and generated minimal db block gets (just 56) and redo (just 10860 bytes). However, this of course comes at a price:
SQL> select index_name, null partition_name, num_rows, leaf_blocks, status 2 from dba_indexes i where table_name='MUSE' and partitioned = 'NO' 3 union select index_name, i.partition_name, num_rows, leaf_blocks, status 4 from dba_ind_partitions i where index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME NUM_ROWS LEAF_BLOCKS STATUS --------------- --------------- ---------- ----------- -------- MUSE_CODE_I CODE_P1 1500000 4135 UNUSABLE MUSE_CODE_I CODE_P2 1500000 4177 UNUSABLE MUSE_ID_I 3000000 8633 UNUSABLE
Both global indexes are now unusable as a result as they haven’t been maintained on the fly and so have orphaned index entries pointing to the now non-existent table partition. So it was fast but left the global indexes in an unusable state which have to now be rebuilt.
The other option would be to drop the table partition but to also update the global indexes at the same time as follows:
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=129; NAME VALUE ------------ ---------- db block gets 129615 redo size 103978912 SQL> alter table muse drop partition muse1 update global indexes; Table altered. Elapsed: 00:00:13.08 SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# =n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=129; NAME VALUE -------------- ---------- db block gets 185758 redo size 148012132
We notice this time, the operation has taken considerably longer and has generated many more db block gets (56,143 up from 56) and much more redo (44,033,220 bytes up from 10,860). So updating the global indexes on the fly comes at a cost, but at least they remain usable at the end of the operation:
SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status from dba_indexes i, dba_segments s where i.index_name = s.segment_name andtable_name='MUSE' and partitioned = 'NO' 2 union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME NUM_ROWS BLOCKS LEAF_BLOCKS STATUS --------------- --------------- ---------- ---------- ----------- -------- MUSE_CODE_I CODE_P1 1000000 4224 4135 USABLE MUSE_CODE_I CODE_P2 1000000 4352 4177 USABLE MUSE_ID_I 2000000 9216 5849 VALID
Having updated the global indexes and having effectively deleted 1/3 of the table with the lowest ID values, if we were to now try and find the current minimum ID value:
SQL> select min(id) from muse; MIN(ID) ---------- 1000001 Execution Plan ---------------------------------------------------------- Plan hash value: 2104594370 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 6 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I | 1 | 6 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2787 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
The CBO is trying to use the index via the Index Full Min/Max Scan to quickly find this minimum ID. However, it keeps hitting leaf blocks with nothing but empty/deleted entries due to dropping the table partition, until it gets through roughly 1/3 of all the index leaf blocks before finally finding the first (and so minimum) non-deleted index value. As such, at 2,787 consistent gets, it’s a relatively expensive operation.
If however, we were to insert a whole bunch of new rows into the table (note these are rows with an ID value greater than existing rows) and then re-run the same query:
SQL> insert into muse select rownum+3000000, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level <= 1000000; 1000000 rows created. SQL> commit; Commit complete. SQL> select min(id) from muse; MIN(ID) ---------- 1000001 Execution Plan ---------------------------------------------------------- Plan hash value: 2104594370 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 6 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I | 1 | 6 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 161 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
We notice that at just 161 consistent gets (reduced from 2,787), the Index Full Min/MAX Scan is much more efficient as most of the previously empty leaf blocks on the “left hand side of the index” have now been recycled due to inserting the new data into the “right hand side of the index”. As such, we now find the minimum ID value via the index much more efficiently.
So that was how things kinda worked in 11g and beforehand. However, with Oracle 12c, things have now changed as we’ll see in the next post …
[…] #DB12c by Richard Foote Global Index Maintenance – Pre 12c […]
LikeLike
Could you explain the phrase “most of the previously empty leaf blocks on the “left hand side of the index” have now been recycled due to inserting the new data into the “right hand side of the index”, how was index blocks recycle due to inserting?
LikeLike
By dropping the muse1 partition, this effectively deleted all the index entries based on the ID column with values less than 1000001. All these index entries basically existed on the LHS of the index as the index entries are ordered within the index. All these now effectively empty index leaf blocks (effectively empty as they contain now nothing but deleted entries) get placed on the segment free list and get recycled during a subsequent block split operation based on inserts in to RHS of the index.
So the empty blocks on the LHS of the index structure get reinserted into the RHS of the index.
Recycling at its best and a reason why Oracle leaf blocks are indeed “green” 🙂
LikeLike