The purpose of this post is mainly to highlight the performance degradation due to dictionary index fragmentation. It is something that oracle not widely announce but it came from the physical structure of the database.
Oracle databases have the AGE and the age mainly came from the number of DDL operations done on the database. The DDL operations modify the dictionary and introduce fragmentation to the indexes and tables.
I have made the small test case
-- CREATE TABLE DROP TABLE list_customers / CREATE TABLE list_customers ( customer_id NUMBER(6) , cust_first_name VARCHAR2(20) , cust_last_name VARCHAR2(20) , nls_territory VARCHAR2(30) , cust_email VARCHAR2(30)) PARTITION BY LIST (nls_territory) ( PARTITION asia VALUES ('CHINA', 'THAILAND'), PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'), PARTITION west VALUES ('AMERICA'), PARTITION east VALUES ('INDIA')) / -- ADD partitions ALTER SESSION SET EVENTS '10046 trace name context forever, level 4' / ALTER TABLE list_customers ADD PARTITION south VALUES ('ANTARCTICA') / EXIT -- DROP partition ALTER SESSION SET EVENTS '10046 trace name context forever, level 4' / ALTER TABLE list_customers DROP PARTITION south / EXIT
It is oversimplified method without dependancies to the object and object statistics. But it already create two massive traces.
In summary during the INSERT command we insert to tables
OBJ$, DEFERRED_STG$ and TABPART$
During delete operation we remove rows from this tables
As you all know insert-delete tables have high level of fragmentation on the indexed columns
We run the standard report for indexes on 3 mentioned tables and it shows that estimated number of leaf blocks for some of them dramatically smaller then the actual one.
|INDEX||Estimated Size||Actual Size|
In case you would try to rebuild this indexes in usual way, you would get the oracle error
ORA-00701: object necessary for warmstarting database cannot be altered
that actually block all attempts to fix the fragmentation.
Index fragmentation primarelly affect index FULL and RANGE scans operation but not UNIQUE index scan. UNIQUE scan would be affected only when INDEX would grow for additional level.
The number in a table does not show something dramatic but it looks like we already have mensurable performance impact on common database operations, like name resolution.
In long term I think every database with high number of structure modifications has to go through process of APPLICATION DATA migration regularly once in 5-15 years.