TO ACCPET A SQL PROFILE
SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_96’,replace => TRUE);
If you want the profile to be registered with a custom name use,
SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_96’,name => ‘CUSTOM_PROF_Query1’);Leave a Comment
Sometimes it is required to flush the BAD SQL_PLAN from shared_pool so that new (or old) better execution plan can be picked by SQL_ID
1) FIND ADDRESS AND HASH_VALUE OF SQL_ID
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%'; ADDRESS HASH_VALUE ---------------- ---------- 000000085FD77CF0 808321886
5 Comments
Finding the SQL_ID for a SQL Statement is straight forward task. You can use query like below to find the SQL_ID
SQL> SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE '%&An_Identifiable_String%';
To Determine the SQL_ID of a SQL Statement in a PL/SQL Block below steps can be followed:
2 Comments