SYS.HIST_HEAD$ defrag?

Folks,

I've noticed that access of SYS.HIST_HEAD$ takes rather long, especially during 
the drop of an option with hundreds of partitions, hundreds of columns.  The 
table referenced has statistics gathered on it using 
DBMS_STATS.GATHER_TABLE_STATS(<owner>,<tabname>,estimate=>1,granularity=>'ALL'),
 which I believe creates 1 row per column per partition, which in this 
particular table's case consumes 100,000+ entries.  A drop of this table takes 
around 1.5 hrs!  The table has around 60,000,000 rows in it.

I've found using event 10046 that nearly all elapsed time is spent removing 
rows from HIST_HEAD$, one DELETE statement per entry.

My question is, can SYS.HIST_HEAD$ be rebuilt/defragmented somehow?  I gathered 
stats on it to get some info (then deleted those stats), then created a copy of 
the table with same storage info.  The block count dropped 50%.

This is on a 9.2.0.8 database under Tru64 5.1b.  The ETL process which 
drops/recreates this table weekly is not something I can control.  So I can't 
change the object's configuration nor how stats are gathered.

I will add that I've created a BEFORE DDL trigger to check for a DROP of this 
table.  When triggered, a loop runs DBMS_STATS.DROP_COLUMN_STATISTICS per 
column in the table, which speeds things up to the point of the DROP taking at 
most .5 hrs.

I'd appreciate any helpful suggestions or experiences with any type of 
SYS-owned objects related to problems like the above.

Thanks.

Dave
 

___________________________________
David C. Herring, DBA  |   A c x i o m  Delivery Center Organization
630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax
*************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be
legally privileged.

If the reader of this message is not the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank you.
*************************************************************************
--
http://www.freelists.org/webpage/oracle-l

Other related posts: