Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Martin.

Asked: July 15, 2016 - 1:09 pm UTC

Answered by: Connor McDonald - Last updated: March 23, 2020 - 2:51 am UTC

Category: Database - Version: 12c Enterprise Edition Release 12.1.0.2.0

Viewed 1000+ times

You Asked

Hello Tom,

We have a new application which has serious performance problems, and a reason for that might be the Oracle DB. Unfortunately, tests could not find the root cause yet.

Now I saw a very strange behavior of the DROP TABLE performance:
Most often, it is quite fast (0.1 sec), but there are also periods of several minutes, when each DROP TABLE takes 1 - 2 minutes to execute (with and w/o purge, SQL code see below).

I reported this to our DBA, but only got the answer "There is no problem visible. The DROP performance depends on many things and there are big differences between oracle versions".

Normally I would not care about DROP TABLE delays, but I am afraid that this is just an indication of a bigger problem, which also affects other SQL statements and therefore causing our slow application performance. And because it only occurs from time to time, tests couldn't detect it yet.

Do you agree with our DBA, that a DROP TABLE performance of 2 minutes can be a normal behavior Oracle 12c (at least sometimes, for a table with 1 row)?
Do you have an idea what might cause this behavior and how I can verify that (e.g. if Oracle is creating table stats in parallel)?
Is it possible, that the root cause of this problem also affects other SQL statements, i.e. not only DROP TABLE?

Any help is highly appreciated,
Thanks a lot,
Martin
________
SQL>create table T1 as select * from dual;

Abgelaufen: 00:00:00.08

SQL>drop table T1 purge;

Abgelaufen: 00:01:34.31

and we said...

Yeah, that does not seem correct.

One thing to consider in all versions of Oracle, is that there a number of database-level triggers that may fire on a DROP command (eg clean up Spatial, XML etc just in case what you dropped had a bearing on them).

To determine cause, try this:

exec dbms_monitor.session_trace_enable(waits=>true)
drop table t1;
exec dbms_monitor.session_trace_disable

and then run tkprof on the trace file. That should give an answer as to where the time is lost.

If you get stuck, get back to us and we can have a look at it too.

and you rated our response

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Thanks

July 18, 2016 - 9:24 am UTC

Reviewer: Martin from Germany

Hello Connor, thanks a lot for your quick answer. Hope I can convince our DBA to do the session trace... Best regards, Martin

Solution?

March 21, 2020 - 3:28 pm UTC

Reviewer: Praveen from USA

How did you solve this? We are seeing a similar issue.
Connor McDonald

Followup  

March 23, 2020 - 2:51 am UTC

They never came back to us.

Have you tried the trace ?

More to Explore

DBMS_MONITOR

More on PL/SQL routine DBMS_MONITOR here