Home » RDBMS Server » Performance Tuning » problem when dropping table
problem when dropping table [message #199363] Mon, 23 October 2006 16:16 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #199392 is a reply to message #199385] Mon, 23 October 2006 23:19 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
hi,
thk for the answer.
No the table is not partitioned.
The problem is not at one particulary table but on any table!
Re: problem when dropping table [message #199393 is a reply to message #199392] Tue, 24 October 2006 00:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What about just a new table with a single extent?

CREATE TABLE test AS SELECT * FROM DUAL;

DROP TABLE test;


Ross Leishman
Re: problem when dropping table [message #199437 is a reply to message #199393] Tue, 24 October 2006 05:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous messageGo to next message
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

Re: problem when dropping table [message #200097 is a reply to message #199960] Fri, 27 October 2006 19:02 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Fascinating. I'm stumped. ./fa/1600/0/
Previous Topic: Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables)
Next Topic: move all objects from tbs to new tbs /rebuild all indexes to new tbs
Goto Forum:
  


Current Time: Thu Dec 08 02:19:13 CST 2016

Total time taken to generate the page: 0.14974 seconds