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?
asked Aug 19, 2009 at 9:55
Add a comment
|
2 Answers
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.
answered Aug 19, 2009 at 13:17
ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells
8,8502 gold badges32 silver badges52 bronze badges
-
Can you tell me some SQL that I can run to see what is happening? Aug 19, 2009 at 13:36
-
1I don't have any to hand but there are lots of Oracle resources on the web with scripts that do this type of thing. Take a look at the queries described in orafaq.com/node/854 or dba-oracle.com/t_locked_rows_user_locks.htm for some examples. Google is your friend here. Aug 19, 2009 at 13:42
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.
