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.
