Being a DBA we must know the difference between the SQL profile and the baselines. To start with we know that optimizer uses the information like gather stats of objects environments and bind variables to choose the best plan for the SQL execution .
In some scenarios there are some defects in these inputs or the optimizer choose a not so good plan by it own.
What is SQL Profiles?
SQL profiles contains the information that solve the above problems. When we use gather stats and bind variable with the SQL profile, a SQL profile helps the optimizer to minimize the mistakes and more likely to choose the optimal plan for the SQL execution.
What is SQL Baselines?
A SQL baselines as the name suggests it creates a baseline for the accepted plans. when a query is parsed by the optimizer it will choose the best plan from the accepted baselines. If a different plan is found by the optimizer during the run of the SQL statement it will add the plan to the plan history, but it will not use the execution plan to execute the SQL statement until and unless it is verified to perform better than the existing plan.
Difference Between SQL Profiles and SQL Baselines
SQL profiles only helps the optimizer to choose the better plan providing some additional stats and information but it does not force the optimizer for any specific plan . Because of this, SQL profiles can be shared between the different environment a example for which is given below in the post.
Where as the SQL baselines constrain the optimizer to choose from the accepted set of plans. The CBO approach will be used but it will only apply to the only accepted plans from the baselines.
So what we choose SQL Profiles or the Baselines?
Well the answer to the above question is based upon the approach and what you want to achieve. You should use SQL profiles if you just want to help the optimizer in its costing process this approach can be very useful in cases where you want to system to adapt quickly to changes like new object stats.
SQL baselines should be used where you specifically want to control which plans should be used by the optimizer.
SPM creating SQL plan baselines
What is SPM and how it does affect the performance of your environment.
Suddenly the execution plan for your SQL has been changed and you are facing the slowness, there are many parameters which can affect this like stale stats of the objects or changes in the few optimizer related parameters.
All a DBA want is that plan should only change when they result in the performance gains else the optimizer should maintain the status quo. In other words, the optimizer should not pick bad plans.
SQL Plan Management
For a set of SQL statements SPM allows the database to maintain the stable performance these SQL statements which are managed by SPM are called as managed SQL statements. SPM mainly helps in maintaining the two objectives
- Prevent the managed SQL statements from performance degradation in case there are database system changes
- And if there are changes in the database it offers the managed SQL statements to accept the performance improvements gracefully.
SPM can be enabled manually, or it can be left to work automatically. By using the manual mode, we can control if we want to do it partially or wholly. SPM maintains a plan history of these managed SQL statements on disk consisting of different execution plan generated for each SQL statements and SPM helps in enabling the detection of plan changes of these SQL statements.
A higher version of oracle optimizer is called as SPM aware optimizer who access and manages this information which is stored in the repository called as SQL management base (SBM).
SPM Aware optimizer uses the information from plan history to determine whether the current execution plan generated by the cost best method is a brand new or not. The brand-new plan might have the potential to cause the performance degradation until and unless its verified. For this very reason the SPM aware optimizer does not chose the brand-new execution plan based upon the cost. It chooses the plan form a set of accepted plans which is verified to not to cause the performance degradation and these set of set of accepted set of plans is called as SQL baselines. SQL plan base lines are the subset of plan history.
The brand-new plan will be added to the plan history as a not good or non-accepted plan. Later using the SPM utility, we can verify the plan if it shows still bad performance or it provides a performance improvements then it can be added to the plan history as accepted plan.
There are multiple ways to create the SQL plan baselines. I have described tow which are widely used.
Creating SQL plan baselines from cursor cache
Lets just say we have a query with a SQL_ID = abc123def and the Plan hash value as : 123456789
The below procedure will create a SQL baseline.
|
1 2 3 4 5 6 7 8 |
begin dbms_output.put_line( dbms_spm.load_plans_from_cursor_cache ( sql_id => ‘abc123def’, plan_hash_value => 123456789) ); end; /. |
If you need to create the baselines for more than one SQL statements according to the text you can use :
|
1 2 3 |
SQL> exec :pls := dbms_spm.load_plans_from_cursor_cache( - attribute_name => 'SQL_TEXT', - attribute_value => 'select%p.prod_name%'); |
Creating SQL plan baselines using a staging table
Using this method, we can share the SQL baselines from one system to another system as this method loads the profiles in staging and that can be imported to the target environment.
On the source system where we have the SQL baselines which can be created from the above method, we need to create a staging table so the SQL baselines can be packed into it.
|
1 2 3 4 5 6 7 8 |
SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'AMIT', - > table_owner => 'VS'); PL/SQL procedure successfully completed. SQL> exec :pls := dbms_spm.pack_stgtab_baseline( - > table_name => 'AMIT', - > table_owner => 'VS', - > sql_text => 'update%p.prod_name%'); |
The table named AMIT created is a normal table which can be easily exported and imported using the Datapump utility.
On the target system now we need to unpack so that the SQL baselines can be created.
|
1 2 3 4 |
SQL> exec :pls := dbms_spm.unpack_stgtab_baseline( - > table_name => 'AMIT', - > table_owner => 'VS', - > sql_text => 'select%p.prod_name%'); |
We can use the same table to pack the different baselines and selectively unpack only a subset on the target system.
SQL baselines can be created automatically by setting the parameter optimizer_capture_sql_plan_baselines to true
