Thanks for the question, Martin.

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

Last updated: July 18, 2022 - 3:43 am UTC

Version: 12c Enterprise Edition Release 12.1.0.2.0

Viewed 10K+ times! This question is

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 Connor 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.

Rating

(3 ratings)

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

Comments

Thanks

Martin, July 18, 2016 - 9:24 am UTC

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

Solution?

Praveen, March 21, 2020 - 3:28 pm UTC

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

March 23, 2020 - 2:51 am UTC

They never came back to us.

Have you tried the trace ?

Slow Drop Table DDL

Eric Harper, July 15, 2022 - 12:10 pm UTC

Good Morning Connor,
I have had a similar problem, we have to drop about 10K tables a month that are interim staging tables, some drop in 0.2 seconds, while others were taking in a range of 1 - 5 minutes, resulting in a multi-day drop each month. Per your recommendation, I enabled the session trace during the drop of 8 tables, generated 79 MB trace file. Our DB is 300 TB, 19.7 enterprise edition, non-RAC, the dictionary is huge, but still.

Before uploading the trace file, I found a pattern:
* all our slow table drops contained a UNIQUE index
* all our fast table drops had no indexes at all.

(1) Drop table with purge, did not help, we don't use recyclebin.
(2) Truncate table, drop table, did not help, multi-minute. So it's not data related.
(3) Drop any index(es) on the table. --- subsecond
Drop the table --- subsecond

The problem for us was our unique indexes, I'm now checking, during the generation of the drop table statement for any indexes, drop those first, then drop the table itself and my multi-day drops are down to around an hour.

If I had to guess, the presence of a unique index represents the opportunity to introduce referential integrity, this forces the drop ddl down the path of looking at these objects:

X Lock on:
Schema_Name.Table_Name_Being_Dropped

SX Lock on:
Sys.ASSEMBLY$
Sys.CACHE_STATS_1$
Sys.COL$
Sys.COM$
Sys.COMPRESSION_STAT$
Sys.DIANA_VERSION$
Sys.DIR$
Sys.EDITION$
Sys.HISTGRM$
Sys.HIST_HEAD$
Sys.ICOL$
Sys.ICOLDEP$
Sys.IDL_CHAR$
Sys.IDL_SB4$
Sys.IDL_UB1$
Sys.IDL_UB2$
Sys.ILM$
Sys.ILMOBJ$
Sys.ILMPOLICY$
Sys.JAVAOBJ$
Sys.LIBRARY$
Sys.NCOMP_DLL$
Sys.OBJAUTH$
Sys.OBJECT_USAGE
Sys.PROCEDURE$
Sys.SNAP_LOADERTIME$
Sys.SQLTXL$
Sys.SQLTXL_ERR$
Sys.SQLTXL_SQL$
Sys.SUMDELTA$
Sys.SUMPARTLOG$
Sys.TYPE_MISC$

You can see this happening with this SQL, during drop of a table with a UNIQUE index, may need a DBA to run this, if so, ask them for SELECT_CATALOG_ROLE, so you can do it yourself next time.

col oracle_username for a18 head 'Username'
col os_user_name for a12
col process for a12 head 'Process'
col session_id for 99999 head 'SID'
col locked_mode for a4 head 'Lock|Mode'
col object_name45 for a45 head 'Schema.OBJECT_NAME'
col object_type7 for a7 head 'Object|Type'

break on session_id skip 1 on inst_ID

select
a.session_id,
a.oracle_username,
initcap(b.owner) || '.' || b.object_name as object_name45,
replace(replace(replace(replace(replace
(b.object_type,
'TABLE', 'Tbl'),
'INDEX', 'Ix'),
' PARTITION','Part'),
' SUBPARTITION','SbPt'),
'MATERIALIZED VIEW', 'MV') as object_Type7,
a.process,
Case a.locked_mode
When 3 Then ' sX'
When 2 Then ' ss'
When 0 Then 'none'
When 1 Then 'null'
When 6 Then ' X'
When 5 Then ' ssX'
When 4 Then ' s'
Else to_char('.' || a.locked_mode || '?')
End as Locked_Mode
from V$Locked_object a,
dba_objects b
where A.object_id = b.object_id
order by 2, 1, 3, 4 ;

clear breaks

I do not see this overhead of checking during the drop of tables with no indexes.

Cheers,
Eric

July 18, 2022 - 3:43 am UTC

Is that a 79M raw trace or tkprof file? If the tkprof file is less than 10meg, you can email the tkprof file to asktom_us@oracle.com with subject: 9530521900346561794

and we'll take a look.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks

Solution?

Slow Drop Table DDL

More to Explore

PL/SQL demos

PL/SQL docs

DROP TABLE performance/delay

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks

Solution?

Slow Drop Table DDL

More to Explore

PL/SQL demos

PL/SQL docs