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,
SQL>create table T1 as select * from dual;
SQL>drop table T1 purge;
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:
drop table t1;
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.