11g slow drop tablespace including contents and…

Slow 11g drop tablespace including contents and datafiles.

Cause: Slow select with sql_id 7jrp85khm4tka:

select from ... INDCOMPART$, I_INDSUBPART_POBJSUBPART$, INDSUBPART$...

Solution (looks like it helped):

exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;
execute dbms_stats.gather_schema_stats('SYS');
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;

Solution description:

get rid of nested loops on tables mentioned above (INDCOMPART$,…)

Relevant Metalink notes:

Drop Tablespace Including Contents And Datafiles Takes Hours To Complete (Doc ID 438461.1)

DBMS_METADATA.GET_DDL: Query Against SYS.KU$_INDEX_VIEW Is Slow In 11.2.0.3 (Doc ID 1459841.1)

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s