Skip to content

Posts from the ‘performance tuning’ Category

Jan 15

Difference between SQL profiles and SQL plan baselines

This post explains the difference between SQL profiles and SQL plan baselines and how they interact. But first let’s briefly recap each feature.

The query optimizer normally uses information like object and system statistics, compilation environment, bind values and so on to determine the best plan for a SQL statement. In some cases, defects in either these inputs or the optimizer can lead to a sub-optimal plan.  A SQL profile contains auxiliary information that mitigates these problems.  When used together with its regular inputs, a SQL profile helps the optimizer minimize mistakes and thus more likely to select the best plan.

A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer will only select the best plan from among this set. If a different plan is found using the normal cost-based selection process, the optimizer will add it to the plan history but this plan will not be used until it is verified to perform better than the existing accepted plan and is evolved.

So, SQL profiles provide additional information to the optimizer to help select the best plan; they don’t constrain the optimizer to any specific plan, which is why they can be shared. SQL plan baselines, on the other hand, constrain the optimizer to only select from a set of accepted plans. The cost-based approach is still used to choose a plan, but only within this set of plans. SQL plan baselines are a more conservative plan selection strategy than SQL profiles. So when should you use SQL profiles versus SQL plan baselines?

You should use a SQL profiles if you just want to help the optimizer a little in its costing process without constraining it to any specific plan. This approach can be extremely useful when you want the system to adapt immediately to changes like new object statistics. You should use SQL plan baselines if you are more conservative and want to control which plans are used. If you are using SQL plan baselines and find that the optimizer sometimes does not select the best plan from the accepted list or does not find a best-cost plan to add to the plan history, then you can always use a SQL profile as well. What happens if a SQL statement has both a SQL Profile and a SQL plan Baseline?

If you recall, SQL Plan Management (SPM) has three component, plan capture, plan selection, and plan evolution. The presence of a SQL profile affects all three components of SPM and we will describe each of those interactions below.

SPM plan capture and SQL profiles

When the statement is executed it will be hard parsed and a cost based plan will be generated. That plan will be influenced by the SQL Profile. Once the cost based plan is determined it will be compared to the plans that exist in the SQL plan baseline. If the plan matches one of the accepted plans in the SQL plan baseline, we will go ahead and use it. However, if the cost based plan doesn’t match any of the accepted plans in the SQL plan baseline it will be added to the plan baseline as an unaccepted plan.

SPM plan selection and SQL profiles

When a SQL statement with a SQL plan baseline is parsed, the accepted plan with the best cost will be chosen.  This process uses the regular optimizer.  The presence of a SQL profile will affect the estimated cost of each of these plans and thus potentially the plan that is finally selected.

SPM plan evolution and SQL profiles

The third sub-component of SPM is verification or evolution of non-accepted plans. The evolution process test-executes the non-accepted plan against the best of the accepted plans.  The best accepted plan is selected based on cost.  Again, if a SQL profile exists, it will influence the estimated cost and thus the accepted plan chosen for comparison against the non-accepted plan.

Hopefully this information gives you a clear picture of how SQL profile and SQL plan baselines differ and how they interact with one another!

Sep 6

Sql Tune to improve buffer gets

Before buffer get tune. SQL plan was doing index fast  full scan. here is you can see that

buffer_get_1

buffer_get_2

After running SQL tuning adviser that suggest to implement new profile that took index rang scan and buffer get improved dramatically also CPU cost.

buffer_get_3

buffer_get_4

as you can see above buffer get reduced from 22K to 615 and CPU cost reduced from 772 to 1. huge improvement.

 

 

 

 

 

Jul 15

Run SQL Tuning Adviser Manually

Find sqlid, begin and end snapshot and put in here 

declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
begin_snap => 5175,
end_snap => 5176,
sql_id => ‘7v5ac8zqn9y65’,
scope => dbms_sqltune.scope_comprehensive,
time_limit => 300,
task_name => ‘sql_tune’,
description => ‘task description’);
dbms_output.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
end;
/

Run sql tune 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘sql_tune’);

Find commendation

SELECT
DBMS_SQLTUNE.report_tuning_task(‘sql_tune’) AS recommendations
FROM dual;

Run recommendation if any

execute dbms_sqltune.accept_sql_profile(task_name => ‘sql_tune’,task_owner => ‘SYS’, replace => TRUE);