Truncate is slow on Oracle 9.2.0.6.0 (HP-UX)

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

how many extents is it having to de-allocate?

local write wait could indicate a physical storage problem. I had this problem a short time ago and found a faulty disk controller was to blame... Anyway, I found plenty on this when I googled...

https://metalink.oracle.com/metalink..._id=183745.995

I am triying to truncate plan_table which has following details...

Quote:

SEGMENT_NAME EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
--------------- ---------- -------------- ----------- ----------- -----------
PLAN_TABLE 1 65536 1 2147483645

Davey thanks for consideration. I was just serching on this forum also . I found one very old thread raised by you on the same lines. But in your case extents were more. In my case only one extent is there that to of 64k so i am going crazy.

Rgds
Parag

Sorry i am pasting o/p again.....

HTML Code:

SEGMENT_NAME EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS --------------- ---------- -------------- ----------- ----------- ----------- PLAN_TABLE 1 65536 1 2147483645