Dropping partition update global indexes performace issue
Hi Everybody,
I have one performace issue with parition drop which is consuming lot of CPU and taking too much time. I am working on 11.2.0.2.0. I have huge table in database which is range partitioned based on daily data. I am purging this table and everytime keep 10 days on data into it. For purging, we have daily job which runs everyday in morning and drop one parition everyday. We have on average 60 million rows in each partition. I am executing below statement in job which drop parition everyday:
ALTER TABLE TABLE_NAME DROP PARTITION PARTITION_NAME UPDATE GLOBAL INDEXES;
This job is taking almost 6 hours to drop one partition everyday.
Can you guys please advice on increasing performance of this? Please provide me links where i can get more information on tuning this. I wil really appreciate all your comments and responses. Thanks in advance.
Regards
Dev