Re: Drop empty table and lms flush message acks

mirfalk wrote:
> Hi everybody,
>
> I'm working on oracle 10g+cluster+ASM and I'm facing a strange and
> unexpected behaviour.
>
> I'm trying to drop an empty (but big), table and after one hour and an
> half the drop statement is still there waiting to be completed.
>
> There are no locks currently active on that table and the session
> executing the statement is spending most of the time waiting for "lms
> flush message acks".
>
> The table is NOLOGGING and it's size is about 20000 Mb.
>
> The devices managed by ASM are not so fast (20M/s) but this doesn't
> seems to be the problem. I've got the sensation that the two sides of
> the cluster do not agree about the storage management.
>
> Any Idea?
>
> TIA
> A.D.

The NOLOGGING clause on table creation only affects the amount of logging at initial table creation:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:366018048216

There is apparently a bug in 10.2 related to lms flush message acks: bug4160825.

The delay be due to redo generation, clearing of the data segments held by the table, moving the table to the recycle bin, synchronization between the servers, etc. Someone more familar with RAC would know of other causes.

Does it help if you first truncate the table?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Jul 31 2006 - 05:11:57 CDT