Hello the community,
Am trying to create a Baseline for a specific statement on a 11.2.0.4 Enterprise READ ONLY WITH APPLY Physical Standby.
Am stuck with the very step of loading the plan from the cursor cache. The below SQL_ID and PHV are from the v$sql.
SQL> SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '**5ag68w2jxhyt1**',plan_hash_value => **1127995437**);
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
Plans Loaded: 0
The View DBA_SQL_PLAN_BASELINES does not therefore return any row with this SQL_ID.
SQL> SELECT b.sql_handle, b.sql_text, b.plan_name, b.enabled FROM dba_sql_plan_baselines b, v$sql s WHERE s.sql_id='5ag68w2jxhyt1' AND s.exact_matching_signature = b.signature;
no rows selected
I tried different combinations of the function's overload but it's 0 plans loaded with every try.
Please find below the parameters related to the Optimizer:
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
I am obviously missing on something here. I appreciate if you could help me with figuring out why SPM is not loading the plan as expected.
Regards.