locking issue with TRUNCATE PARTITION

[sansho...@my-deja.com]

I have a table partitioned by date with only local indexes. I want to
allow inserts on today's partition while truncating yesterday's with
minimal delays. However, it looks like there is an exclusive lock placed
on the table, and for about 10 seconds all other sessions are blocked.
For this type of application this delay is not acceptable. It doesn't
seem like an exclusive lock is needed at the table level for ALTER TABLE
TRUNCATE PARTITION. I would think a partition-level lock would suffice.
Could someone explain why this type of locking is used? And is there a
workaround besides using DELETE FROM ..? (I want to minimize rollback
and redo). I am running Oracle 8.0.5.0.0 on Windows NT Server 4.0 SP 5.

Sent via Deja.com http://www.deja.com/
Before you buy.

[Jonathan Lewis]

There shouldn't be an exclusive table lock if all you have
is local indexes. However the entire table definition
is invalidated and has to be reloaded into the data
dictionary. Then all SQL relating to the table has
to be re-parsed. For a table with many partitions and
indexes this could take some time and appear to
have the effect you are seeing.

It shouldn't help, but you could try creating
an empty table, then exchange partition with
empty table without validation including indexes.

You aren't running parallel server are you ?

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

sansho...@my-deja.com wrote in message <8cbcud$a0p$1...@nnrp1.deja.com>...

[sansho...@my-deja.com]

>There shouldn't be an exclusive table lock if all you have
>is local indexes. However the entire table definition
>is invalidated and has to be reloaded into the data
>dictionary. Then all SQL relating to the table has
>to be re-parsed. For a table with many partitions and
>indexes this could take some time and appear to
>have the effect you are seeing.
>
>It shouldn't help, but you could try creating
>an empty table, then exchange partition with
>empty table without validation including indexes.
>
>You aren't running parallel server are you ?

Jonathan,

Thanks for the reply. No, I'm not running parallel server,
this is on plain old Enterprise Edition 8.0.5.
Do you know why the table definition would be invalidated?
I'm not dropping the partition, I'm only truncating it.
I did look at the manual today and it mentioned that this would
be a one-step partition maintenance operation and therefore
would DML lock the affected table in exclusive mode and
hold exclusive dictionary locks for the duration of the
operation. I also collected some lock data, and the sessions
are waiting on either 'library cache pin' or 'library cache lock'
(I'm assuming this is the dictionary lock). So I don't think
it is a case of just re-parsing the SQL, as these locks were
held at least 15 seconds.

I may try creating a 'dummy' global index, which according
to the manual will make the TRUNCATE a three-step operation.
The base table would then be locks in Row Exclusive (SX) mode.
Do row exclusive locks prohibit new inserts?

Thanks

[Jonathan Lewis]

The manual is clearly wrong on this count.

Insert a row into today's partition without committing,
then truncate yesterday's partition.

If there were an exclusive lock required on the
table the truncate command would fail with
error 54 - it doesn't (though I am running 8.0.4,
so your 8.0.5 _may_ be different).

Don't ask me why a truncate invalidates the
whole dictionary set for the table - perhaps
stats updates. Nevertheless when you
truncate a partition TAB$ is updated, so the
dictionary cache has to be invalidated and
any parsed SQL in the library cache invalidated.

> I also collected some lock data, and the sessions
>are waiting on either 'library cache pin' or 'library cache lock'
>(I'm assuming this is the dictionary lock).

This relates to all the relevant SQL becoming invalid,
and the dictionary cache becoming invalid as well
as the re-load, reparse.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

sansho...@my-deja.com wrote in message <8cdm87$r3l$1...@nnrp1.deja.com>...

[sansho...@my-deja.com]

It turns out just dropping the partition rather than first truncating
it does not have the same locking issues as the truncate, it completes
in about a second (compared with ~15) and does not cause the inserts
to block. I would think the DROP PARTITION would first perform a
TRUNCATE-like operation so I still don't understand the problems with
TRUNCATE.