Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> truncate partition table causes high latch free

truncate partition table causes high latch free

From: Eagle Fan <eagle.f_at_gmail.com>
Date: 11 Apr 2006 02:29:21 -0700
Message-ID: <1144747761.009644.312390@z34g2000cwc.googlegroups.com>


hi:

We have a partition table which has 60 partitions using composite range-hash partition.
Each day we need to truncate one range partition to purge the history data.
>From this, we can keep 60 days' history data.

But each day when we were doing the truncate(it's run by a cronjob), we had high active sessions waiting on library cache pin latch free.

The truncate operation will hold the exclusive pin on object in library cache and the database is pretty busy, about 1300 transactions per second. So when doing the truncate, other application session are waiting for pinning object in share mode.I think this is the reason for high latch free.

We tried to use "truncate reuse storage" and "exchange partition" to do the truncate, but both of them can't avoid this.

I know we can change application code and database structure to avoid this(for example,create 60 different ordinary no-partitioned tables), but this needs a long time to get it done.

Does someone have good idea on this?

Thanks Received on Tue Apr 11 2006 - 04:29:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US