Home / server / Questions / 55999
Accepted
Aaron Digulla
Asked: 2009-08-20 01:55:28 +0800 CST

Oracle: TRUNCATE sometimes takes ages

  • 772

Usually, truncating a table takes 5-10 seconds. But when several people work on the same DB instance (but different tables), the operation can take more than an hour. How do I debug this?

performance oracle

2 Answers

  1. Best Answer

    Locks on the table or on data dictionary resources used by the 'truncate table' command could cause this. The system could possibly be waiting around for a resource to be released before it can do the truncate.

    Check whether the session is blocked waiting for a lock to be released.

    Take a look at the queries described in orafaq.com/node/854 or dba-oracle.com/t_locked_rows_user_locks.htm to find out which locks might be blocking.

    • 4

  2. When using SAN storage, the filesystemio_options parameter setting should be reviewed. Setting the parameter to SETALL or ASYNCH alleviates log file sync waits during the truncate execution.

    On a busy system, running additional DB writers is recommended as well.

    • 0