In some cases in Oracle databases, the optimizer may make the wrong decision and cause this query to run with the wrong query plan. If there is a better plan, fixing may be required for the query to work with the correct query plan. A query plan can be fixed by following the steps below. In other words, plan fix can be done.<\/p>\n
First check that the values \u200b\u200bof the following parameters in the database are as like below.<\/p>\n
OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE\n\nOPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE<\/pre>\nIf the parameters are not like this, change them. Then, check the plans of the query with the following query(you must find sql_id of query first ).<\/p>\n
select inst_id,\n address,\n hash_value,\n sql_id,\n plan_hash_value,\n operation,\n optimizer,\n cost\n from gv$sql_plan\n where id = 0\n and sql_id = 'XXXXXXXXXXX';<\/pre>\nThen, using the following script, let’s load the existing sql plan from cursor cache.<\/p>\n
declare\n my_plans pls_integer;\nbegin\n my_plans := dbms_spm.load_plans_from_cursor_cache(sql_id => 'XXXXXXXXXXX',\n plan_hash_value => 'XXXXX',\n fixed => 'NO');\nend;<\/pre>\nor<\/p>\n
declare\n my_plans pls_integer;\nbegin\n my_plans := dbms_spm.load_plans_from_cursor_cache(sql_id => 'XXXXXXXXXXX');\nend;<\/pre>\nNext, let’s determine the sql_handle and plan_name from the sql baseline information.<\/p>\n
select * from dba_sql_plan_baselines;<\/pre>\nFinally, fix the SQL Plan of the query with the following pl\/sql block with the sql_handle and plan_name information returned from the above query.<\/p>\n
DECLARE\n l_plans_altered PLS_INTEGER;\nBEGIN\n l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(sql_handle => 'SQL_aaa4f03be3519ae5',\n plan_name => 'SQL_PLAN_ap97h7gjp36r5e4cd1512',\n attribute_name => 'fixed',\n attribute_value => 'YES');\n DBMS_OUTPUT.put_line('Plan sabitlendi : ' || l_plans_altered);\nEND;\n\/<\/pre>\n\n<\/div>\n