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