Monday, March 14, 2011

Rolling partition and global index coalesce

Resolving one of performance issues I figure out that primary key index is much bigger that it should be. Most of database activity is inserting data into partitioning table using some date field as a partitioning key. Rows can be inserted to every partition not only to most recent one. Daily job is dropping one oldest partition per day. Unfortunately primary key index is not partitioned and is maintained during partition drop as a global index. Correlation of these two things is a root cause of index size explosion.
Short test case below:
  • script test.sql has been used to check index size, number of rows in table and partition drop
  • in second session simple script inserting data into random partition was running continuously
Table definition:
  CREATE TABLE LOGS
   (    "LOG_ID" NUMBER NOT NULL ENABLE,
        "DELDATE" DATE NOT NULL ENABLE,
        "FILL" VARCHAR2(100),
         CONSTRAINT "LOGS_PK" PRIMARY KEY ("LOG_ID")
   )
  TABLESPACE "USERS"
  PARTITION BY RANGE ("DELDATE")
 (PARTITION "PARTITION1"  VALUES LESS THAN (TO_DATE(' 2011-02-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION2"  VALUES LESS THAN (TO_DATE(' 2011-03-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION3"  VALUES LESS THAN (TO_DATE(' 2011-04-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION4"  VALUES LESS THAN (TO_DATE(' 2011-05-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION5"  VALUES LESS THAN (TO_DATE(' 2011-06-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION6"  VALUES LESS THAN (TO_DATE(' 2011-07-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION7"  VALUES LESS THAN (TO_DATE(' 2011-08-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION8"  VALUES LESS THAN (TO_DATE(' 2011-09-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION9"  VALUES LESS THAN (TO_DATE(' 2011-10-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION10"  VALUES LESS THAN (TO_DATE(' 2011-11-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION11"  VALUES LESS THAN (TO_DATE(' 2011-12-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 );
Source of test.sql script 
prompt number of rows in table
 select count(*) from pioro.logs;
 
 prompt LOGS_PK index size
 select sum(bytes)/1024/1024 from dba_extents where segment_name = 'LOGS_PK';

 prompt calculating stats
 exec dbms_stats.gather_table_stats('PIORO','LOGS',cascade=>true, estimate_percent=>null);
 
 prompt rows/block without update global indexes
 select blevel, leaf_blocks, distinct_keys, NUM_ROWS, NUM_ROWS/leaf_blocks "rows/block" from dba_indexes 
where owner='PIORO' and table_name='LOGS' and leaf_blocks <> 0;

 prompt number of rows in table partition
 select count(*) from pioro.logs partition(&partname);
 set timing on 

 alter table pioro.logs drop partition &partname update global indexes;
Inserts script running in loop looks like

insert into logs select seq_log_id.nextval, to_date('2011-'|| trunc(dbms_random.value(2,9)) ||'-15','yyyy-mm-dd'),
lpad('x',70,'x') from all_source a, all_source b where rownum <= 3000000;
commit;
Here is a output from test.sql for first partition drop
SQL> @test
number of rows in table COUNT(*)                                                                                                 
----------                                                                                                          
  12000000                                                                                                          

Elapsed: 00:00:13.74
LOGS_PK index size

SUM(BYTES)/1024/1024                                                                                                
--------------------                                                                
242           

Elapsed: 00:00:00.51
calculating stats

PL/SQL procedure successfully completed.

Elapsed: 00:01:11.28
rows/block without update global indexes

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS rows/block                                                          
---------- ----------- ------------- ---------- ----------                                                          
         2       30000      12000000   12000000        400         

Elapsed: 00:00:00.03
number of rows in table partition
Enter value for partname: partition1
old   1:  select count(*) from pioro.logs partition(&partname)
new   1:  select count(*) from pioro.logs partition(partition1)

  COUNT(*)                                                                                                          
----------                                                                                                          
   3001361                                                                              
Elapsed: 00:00:00.35
Enter value for partname: partition1
old   1:  alter table pioro.logs drop partition &partname update global indexes
new   1:  alter table pioro.logs drop partition partition1 update global indexes

Table altered.

Elapsed: 00:00:55.37
As you can see before first drop average number of rows in one index leaf for primary key was 400. ( 8 kB database block ) Output from next run looks like:
SQL> @test
number of rows in table

  COUNT(*)   
----------   
  11998639   

Elapsed: 00:00:09.59
LOGS_PK index size

SUM(BYTES)/1024/1024  
--------------------  
                 296  

Elapsed: 00:00:01.12
calculating stats

PL/SQL procedure successfully completed.

Elapsed: 00:01:13.16
rows/block without update global indexes

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS rows/block    
---------- ----------- ------------- ---------- ----------    
         2       37500      11998639   11998639 319.963707    

Elapsed: 00:00:00.05
number of rows in table partition
Enter value for partname: partition2
old   1:  select count(*) from pioro.logs partition(&partname)
new   1:  select count(*) from pioro.logs partition(partition2)

  COUNT(*)                  
----------                  
   3749280                  

Elapsed: 00:00:00.43
Enter value for partname: partition2
old   1:  alter table pioro.logs drop partition &partname update global indexes
new   1:  alter table pioro.logs drop partition partition2 update global indexes

Table altered.

Elapsed: 00:01:22.85
Numbers of rows in one leaf block drop from 400 to 320 rows per block. After a few next runs it looks like:
SQL> @test
number of rows in table

  COUNT(*)                                                                                                          
----------                                                                                                          
   9748583                                                                                                          

Elapsed: 00:00:17.67
LOGS_PK index size

SUM(BYTES)/1024/1024      
--------------------      
                 414      

Elapsed: 00:00:00.85
calculating stats

PL/SQL procedure successfully completed.

Elapsed: 00:01:11.78
rows/block without update global indexes

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS rows/block        
---------- ----------- ------------- ---------- ----------        
         2       52500       9748583    9748583 185.687295        

Elapsed: 00:00:00.01
Now is really bad - only 186 rows in one leaf block and size of index segment is now 414 MB. It will have huge impact on database performance as much more index blocks have to be read during index range scans. Solution is quite simple and can be done online without huge overhead. It is a index coalesce - this command will consolidate a free space in existing index and provide more free blocks for particular index segment. See example:
SQL> alter index pioro.LOGS_PK coalesce;

Index altered.

Elapsed: 00:03:57.12
SQL> prompt number of rows in table partition
number of rows in table partition
SQL> select count(*) from pioro.logs partition(&partname);
Enter value for partname: partition4
old   1: select count(*) from pioro.logs partition(&partname)
new   1: select count(*) from pioro.logs partition(partition4)

  COUNT(*)                    
----------                    
   5248055                    

Elapsed: 00:00:10.47
SQL> alter table pioro.logs drop partition &partname update global indexes;
Enter value for partname: partition4
old   1: alter table pioro.logs drop partition &partname update global indexes
new   1: alter table pioro.logs drop partition partition4 update global indexes

Table altered.

Elapsed: 00:02:09.65
SQL> @test
number of rows in table

  COUNT(*)                          
----------                          
   7500528                          

Elapsed: 00:00:15.78
LOGS_PK index size

SUM(BYTES)/1024/1024      
--------------------      
                 414      

Elapsed: 00:00:01.47
calculating stats

PL/SQL procedure successfully completed.

Elapsed: 00:00:46.30
rows/block without update global indexes

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS rows/block      
---------- ----------- ------------- ---------- ----------      
         2       26682       7500528    7500528 281.108163      

Elapsed: 00:00:00.08
After index coalesce we can see that Oracle is able to allocate space from free block and size of index remain stable and number of rows in one leaf block is now increasing.
This post has been created based on test data and test scenario but after sucesfull implementation in life systems I will blog about real numbers from production databases.