Accept, disable and drop a SQL profile

Command

-- 1) Accept the profile recommended by the tuning task:
DECLARE
  l_name VARCHAR2(128);
BEGIN
  l_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
              task_name   => '<task_name>',
              name        => 'PROF_<sql_id>',
              description => 'STA recommendation, CHG-1234',
              force_match => TRUE,     -- also matches statements differing only in literals
              replace     => TRUE);
  DBMS_OUTPUT.PUT_LINE('Profile: ' || l_name);
END;
/

-- 2) Profiles and the cursors using them:
SELECT name, category, status, force_matching,
       TO_CHAR(created,'YYYY-MM-DD HH24:MI') AS created
FROM   dba_sql_profiles
ORDER  BY created DESC;

SELECT sql_id, child_number, plan_hash_value, sql_profile
FROM   v$sql
WHERE  sql_profile IS NOT NULL;

-- 3) Temporarily disable / drop:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(name => 'PROF_<sql_id>', attribute_name => 'STATUS', value => 'DISABLED');
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROF_<sql_id>', ignore => TRUE);

What it does

A SQL profile gives the optimizer extra cardinality/selectivity corrections (OPT_ESTIMATE and similar) so it produces a better plan without changing the SQL text; with FORCE_MATCH it also applies to statements that differ only in literals.

When to use it

When the SQL cannot be changed (packaged application), when STA recommends a validated improvement, or as a fast fix for a plan regression.

Caution

Requires the Tuning Pack license. A profile does not "lock" the plan; it corrects estimates, so the plan can still change when data distribution changes. If plan stability is mandatory, use a SQL plan baseline. When both exist, the optimizer chooses among the baseline's accepted plans. Always read the validation results (the report's 'Validation results' section) before accepting.