| problem when dropping table [message #199363] |
Mon, 23 October 2006 16:16  |
aline
Messages: 92
Registered: February 2002
|
Member |
|
|
Hello,
I have a strange problem,
In a new database (9.2.0.7 on red hat 4), a drop table can take long time (in minutes!). When tracing it on v$session_wait, we can see a wait for smon process.
SELECT * FROM v$session_wait WHERE sid=41;
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- ---------- ------------ ---------- ---------------- ---------- ---------- ---------------- ------ ---------- ------ ---------- --------------- -------------------
41 464 rdbms ipc from_process 6 0000000000000006 timeout 21474780 000000000147ADDC 0 00 0 1 WAITING
reply
So when monitoring what smon is doing:
SELECT * FROM v$sqltext v WHERE v.ADDRESS=(SELECT sql_address FROM v$session WHERE sid=6)
ORDER BY piece;
UPDATE SMON_SCN_TIME SET SCN_WRP=:1, SCN_BAS=:2, TIME_MP=:3, TIME_DP=:4 WHERE TIME_MP = :5 AND THREAD = :6 AND ROWNUM <= 1
all time it is updating SMON_SCN_TIME table
I tried this to change smon
SQL> ORADEBUG WAKEUP 7;
Statement processed.
but of course, it didn't work
here a view of the v$SYSTEM_EVENT view.
SQL> SELECT * FROM V$SYSTEM_EVENT ORDER BY 4 DESC;
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO
---------------------------------------------------------------- ----------- -------------- ----------- ------------ -----------------
rdbms ipc message 895821 186845 126762506 142 1267625063386
pmon timer 125911 88210 26603975 211 266039747678
smon timer 7292 700 25343707 3476 253437065931
SQL*Net message FROM client 24019109 0 19693581 1 196935806210
log file parallel WRITE 844763 0 9245313 11 92453133546
log file switch (checkpoint incomplete) 82215 80724 7959396 97 79593956221
free buffer waits 51004 49153 4895186 96 48951856599
log buffer space 265557 1206 4449985 17 44499846088
db file scattered READ 1116468 0 2674360 2 26743602932
rdbms ipc reply 68500 12981 2611031 38 26110307545
enqueue 933707 3704 1779871 2 17798711956
any idea?
thk!
|
|
|
|
| Re: problem when dropping table [message #199385 is a reply to message #199363] |
Mon, 23 October 2006 21:08  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Is the table partitioned? Partitioned tables take longer to drop. If your table has 200 partitions, it will take about as long as dropping 200 tables.
Ross Leishman
|
|
|
|
|
|
|
|
| Re: problem when dropping table [message #199437 is a reply to message #199393] |
Tue, 24 October 2006 05:07 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
I hope you are NOT using a dictionary managed table. Else the wait may be because of the SMON cleanup.
Too many extents could be a pang ( and the test Ross has provided should throw some light on that).
Did you also try this?
sql> truncate table table_name reuse storage;
sql> drop table table_name; [Updated on: Tue, 24 October 2006 05:08] Report message to a moderator |
|
|
|
| Re: problem when dropping table [message #199960 is a reply to message #199393] |
Fri, 27 October 2006 02:49 |
aline
Messages: 92
Registered: February 2002
|
Member |
|
|
hi,
sorry for the time delay
SQL> set time on
09:40:51 SQL>
09:43:03 SQL>
09:43:03 SQL>
09:43:03 SQL>
09:43:04 SQL> CREATE TABLE test AS SELECT * FROM DUAL;
Table created
09:43:06 SQL> DROP TABLE test;
Table dropped
09:44:42 SQ
So 100 secondes to drop a table!
But now, I think the problem is more global.
During peak activity (this server is in test mode), I have this kind of wait. Else, no problem
and the trace dor this query:
********************************************************************************
DROP TABLE test
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 87.75 0 13 9 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 87.75 0 13 9 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 57
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
rdbms ipc reply 45 1.95 87.61
log file sync 1 0.31 0.31
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.02 0.02
********************************************************************************
[Updated on: Fri, 27 October 2006 02:53] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
