Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
First check that the values of the following parameters in the database are as like below.
1 2 3 | OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE |
If 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 ).
1 2 3 4 5 6 7 8 9 10 11 | select inst_id, address, hash_value, sql_id, plan_hash_value, operation, optimizer, cost from gv$sql_plan where id = 0 and sql_id = 'XXXXXXXXXXX'; |
Then, using the following script, let’s load the existing sql plan from cursor cache.
1 2 3 4 5 6 7 | declare my_plans pls_integer; begin my_plans := dbms_spm.load_plans_from_cursor_cache(sql_id => 'XXXXXXXXXXX', plan_hash_value => 'XXXXX', fixed => 'NO'); end; |
or
1 2 3 4 5 | declare my_plans pls_integer; begin my_plans := dbms_spm.load_plans_from_cursor_cache(sql_id => 'XXXXXXXXXXX'); end; |
Next, let’s determine the sql_handle and plan_name from the sql baseline information.
1 | select * from dba_sql_plan_baselines; |
Finally, 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.
1 2 3 4 5 6 7 8 9 10 | DECLARE l_plans_altered PLS_INTEGER; BEGIN l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(sql_handle => 'SQL_aaa4f03be3519ae5', plan_name => 'SQL_PLAN_ap97h7gjp36r5e4cd1512', attribute_name => 'fixed', attribute_value => 'YES'); DBMS_OUTPUT.put_line('Plan sabitlendi : ' || l_plans_altered); END; / |
HI,
if my plan_hash_value not existed in cursor_cache, from where i can get? Is it possible to get it from AWR Snaps?
Thank you..