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