Share

Tuning Query without Changing Code by Using SQL Plan Baseline

In day to day application performance monitoring we encountered with Un-optimized SQL consuming significant Database time. Only solution comes to mind is to improve the performance of the SQL by making changes in siebel configuration or Scripting(adding/removing hints, changing the order of joins, removing unnecessary function calls etc.) to get optimized SQL.



Sometimes it is impossible to modify  (vendor packages) in application like SIEBEL and you are stuck. 🙁

 

 

How about a Hack 🙂

I will describe a trick of forcing the optimizer to change the execution plan of a SQL statement without any modifications at application side.

 

Generate SQL Plan Baseline for the Original SQL

In an 11g database, by default Oracle does not collect SQL plan baselines automatically unless you set init.ora

Parameter optimizer_capture_sql_plan_baseline to TRUE.

So if the plan baseline does not exist for the original SQL statement, we need to generate it.

Steps:

1) Create a SQL tuning set. Give it a name and description that suit your situation.

2) If the SQL statement was run recently, get the starting and ending AWR snapshot numbers for the time period

When the SQL was run. Also using the SQL ID,

Get the plan hash value from DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOT views。




3) Load the tuning set with the execution plan extracted from AWR, using the AWR snapshot numbers, the SQL_ID and the plan hash value.

4) Create SQL plan baseline from the loaded SQL tuning set.

5) Check the newly created plan baseline.

 

I will be happy to respond to your queries , Please use below comment box and i will get back to you. Thanks.

2 comments on “Tuning Query without Changing Code by Using SQL Plan Baseline”

    • Suresh Singh Suresh Singh Reply

      Tuning Set created in the first step getting loaded into third step with the execution plan extracted from AWR, using the AWR snapshot numbers with sql_id and PHV.
      In 3rd and 4th step we are completing all the things including Tunning Set.
      Hope this clarifies your concern.
      Thanks.

Leave A Reply

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

error: Content is protected !!