Hi,
Just 2-3 days back i started facing this strange problem.
If you try to truncate empty table it takes too long. Prev'ly it used to happen in fraction of seconds. I am running Oracle Oracle 9.2.0.6.0 on HP UX.

I traced the session which truncates empty table found following waits are coming..


HTML Code:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      1      0.09     508.50         32        154         18           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.09     508.53         32        154         18           0

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         5        0.00          0.00
  enqueue                                        62        2.94        180.51
  rdbms ipc reply                                81        1.96        156.03
  local write wait                              171        1.01        168.37
  log file sync                                   1        0.07          0.07
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       44.09         44.09


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       22      0.02       3.33          1          3          0           0
Execute     25      0.04       0.10          8         14          4           4
Fetch       20      0.00      40.31         22        145          0          17
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       67      0.06      43.75         31        162          4          21

Misses in library cache during parse: 12

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        30        0.02          0.14
  free buffer waits                              43        0.99         40.19

Out of which free buffer waits i can take care of but i want to know
what is "local write wait" i searched for it a lot didnt got anything..

Also on metalink i found one bug Note:287429.1
" Bug 3282805 TRUNCATE table scans entire buffer cache for small tables in 9.2.0.4"
They say 9.2.0.5 includes fix for these bugs but mine is 9.2.0.6 then why i should i get this problem.

Does any body else facing any kind of these problems.

Thanks in Advance...

Rgds
Parag