How To Fix The SQL Plan in Oracle Databases

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.

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 ).

Then, using the following script, let’s load the existing sql plan from cursor cache.

or

Next, let’s determine the sql_handle and plan_name from the sql baseline information.

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

One comment

  1. 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..

Leave a Reply

Your email address will not be published. Required fields are marked *