SQL Performance (MOSC)

MOSC Banner

Huge number of SQL_PLAN_DIRECTIVES with no owner.

edited May 19, 2019 5:04AM in SQL Performance (MOSC) 3 commentsAnswered

For one of my CT there are very large number of SQL Plan Directives which are created and CT wants to drop all of them.

He is trying to follow below note.

Script to Bulk Drop SQL Plan Directives (Doc ID 2323144.1)

But he is seeing huge number of SPD with no owner.

SQL> select count(distinct directive_id) spd_count,owner from dba_sql_plan_dir_objects group by owner;

SPD_COUNT OWNER
---------- --------------------
    158044 PANMDM
116277434<<<<<<<<<<<<<<<<<<<<<<<<
   1384792 PAN_CDM_ETL
       247 SYSTEM
     90596 PAN_CDM_STG
46 FLOWS_FILES
24 OUTLN
     13250 PAN_CDM_DATA
       279 PAN_CDM_DATA_HISTORY
16 XDB
114524262 CDM_IRIS
      9214 APEX_050100
32 DBSNMP
1 ORDS_METADATA

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center