17 August, 2019

Drop SQL Profile

Step-1: Check below SQL Profile: SYS_SQLPROF_7cd65e0000

SQL> select name from dba_sql_profiles;


SQL> select NAME,CATEGORY,TYPE,STATUS,TASK_ID,TASK_EXEC_NAME from dba_sql_profiles where NAME like '%7cd65e0000%';


Step-2: Drop below SQL Profile: SYS_SQLPROF_7cd65e0000

SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_7cd65e0000');


SQL> select NAME,CATEGORY,TYPE,STATUS,TASK_ID,TASK_EXEC_NAME from dba_sql_profiles where NAME like '%7cd65e0000%';


Drop SQL Plan directives:
==========================

Step-2: Drop SQL Plan directives:

SQL> EXEC DBMS_SPD.flush_sql_plan_directive;

PL/SQL procedure successfully completed.

SQL> BEGIN
FOR rec IN (SELECT directive_id did FROM DBA_SQL_PLAN_DIRECTIVES)
LOOP
DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE (directive_id => rec.did);
END LOOP;
END;   2    3    4    5    6
  7  /

PL/SQL procedure successfully completed.

SQL>

Step-3:
Set the optimizer_adaptive_features to FALSE. They can Ref. the DOC ID 396009.1 for the recommended parameters for EBS.
--

SQL> show parameter optimizer_adaptive_features

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features          boolean     TRUE
SQL>
SQL>
SQL>
SQL> ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE SCOPE=BOTH;


No comments:

Post a Comment

OEM Agent Installation

./agentDeploy.sh AGENT_BASE_DIR=/u001/oracle/product/agent13c OMS_HOST=oem-dev.xxx.com EM_UPLOAD_PORT=1159 AGENT_REGISTRATION_PASSWORD=*****...