OraFAQ Forum: Performance Tuning » problem when dropping table

Home » RDBMS Server » Performance Tuning » problem when dropping table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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!

Report message to a moderator

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

Report message to a moderator

Re: problem when dropping table [message #199392 is a reply to message #199385]

Mon, 23 October 2006 23:19

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!

Report message to a moderator

Re: problem when dropping table [message #199393 is a reply to message #199392]

Tue, 24 October 2006 00:03

rleishman
Messages: 3728
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

Report message to a moderator

Re: problem when dropping table [message #199437 is a reply to message #199393]

Tue, 24 October 2006 05:07

Mahesh Rajendran
Messages: 10708
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

Re: problem when dropping table [message #200097 is a reply to message #199960]

Fri, 27 October 2006 19:02

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Fascinating. I'm stumped. /forum/fa/1600/0/

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 05:55:53 CST 2024