Understanding SQL Plan Baselines in Oracle Database

 

Understanding SQL Plan Baselines in Oracle Database

SQL Plan Baseline is the feature in Oracle started from Database 11g that helps to prevent repeatedly used SQL statements from regressing because a newly generated execution plan is less effective than what was originally in the library cache. Read on to learn more…

What is one of the most challenging aspects of performance tuning in an Oracle database? 

There are probably a fair number of Oracle DBAs that will tell you dealing with the

performance of SQL in their databases is one of the biggest challenges they encounter.

SQL statement tuning is often painstaking and can be a very time consuming

activity to say the least. 

Then, once properly tuned, making sure the most resource intensive statements stay that way is yet another hurdle that is faced by DBAs.

The cost based optimizer will generate new execution plans for statements that already

have a plan cached in the library cache because of a variety of changes in the

database. Sometimes, these new plans perform worse than the plan(s) cached in memory. 

The list of actions or changes in the database that cause execution plans to be regenerated include upgrades or patches to the optimizer, update optimizer statistics, DDL changes that affect the objects being accessed in the statement, system setting changes and the creation of SQL profiles. All of

these are actions commonly performed in regular database maintenance operations.

Oracle has introduced several major features over several releases that are, or have been, designed to help the DBA manage and control the execution plans that the optimizer generates for repetitive statements run in our databases. 

These features include stored outlines, SQL Profiles and most recently with Oracle 11g, SQL Plan Baselines.

The drawback to stored outlines and SQL profiles is that they are reactive in nature and may

require maintenance and updating. SQL Plan Baselines on the other hand are much more automated, and more proactive in their behavior. 

New plans can only be added to the SQL Plan Baseline if they will not result in a slower performance,

and only plans that are actually in the SQL Plan Baseline are used by optimizer to generate the plans to process statements.

SQL Plan Baselines are a new feature in Oracle 11g that helps to prevent repeatedly used SQL statements from regressing because a newly generated execution plan is less effective than what was originally in the library cache. 

It should be noted however, that SQL Plan Baselines do not help if the action taken in the database is so significant that the execution plan absolutely must change, for example if a previously used index is dropped.

Defining SQL Plan Baselines

Defined, a SQL Plan Baseline is a set of one or more "accepted" plans that contain hints, the plan hash value and other plan related data. In addition to the actual baseline, Oracle also maintains a SQL Plan History. 

This history is a list of all execution plans generated for a statement, including those that have and have not been moved into the SQL Plan Baseline. Acceptable execution plans are moved from the SQL Plan History into the SQL Plan Baseline, which is referred to as evolving the plan. The baseline and history are maintained in the SQL Management Base (SMB), which is kept in tables in the SYSAUX

tablespace. 

Included in the SMB is also any related SQL profiles for the statements.

The diagram that follows shows the relationship between the SMB, Plan History and Plan Baseline. 

A statement has had three different execution plans generated. 

Plan 1 and Plan 2 have performed well, and have been accepted into the baseline.

Plan 3 was also generated, however it did not perform as well as the other two, and therefore, while it is part of the history, it is not a baseline plan

.

The relationship between the SMB, Plan History and Plan Baseline

Before a plan in the SQL Plan Baseline can be used or selected by the optimizer, the SQL Baseline must be initialized with at least one accepted plan for the repeatable statements being run. The two activities that populate the SQL Plan Baselines are capturing and evolving. 

Capturing is the initial load of plans into the baseline, evolving is the evaluation of new plans in the SQL History to ensure they will not cause the statement to regress and then adding them to the SQL Baseline.

Capturing SQL Plan Baselines

During the capture phase, Oracle maintains a log of the SQL ID for statements executed against the database. If a statement is parsed or executed after it was initially logged, it is considered a repeatable statement. For each of these statements, the SQL History is created and new plans (if generated) are added to it. There are two ways to initiate the capture, automatically and manually.

To do an automatic load, change the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE rather than it’s default of FALSE. This is a dynamic parameter, so the capture process can be quickly started and then stopped if necessary. During automatic capture, the first plan generated for any statement is flagged as accepted and placed into both the SQL History and the SQL Baseline. 

Any subsequent execution plans are placed only in the history.

Manual loading of the initial SQL Baseline is accomplished by using the DBMS_SPM
package supplied with Oracle. There are two functions that load SQL plans.
These are LOAD_PLANS_FROM_SQLSET and LOAD_PLANS_FROM CURSOR_CACHE. Any plans that are manually loaded are placed into the SQL Plan Baseline.

DBMS_SPM also has a procedure that will migrate stored outlines that may already exist for any of the high-load statements into the SQL Plan Baseline. This function is called MIGRATE_STORED_OUTLINE and was introduced as a part of Oracle Database 11g R2. 

This means, the work done previously to create stored outlines can now
be used to initially seed the SQL Plan Baselines, which is a nice feature.

For more details on running these procedures, refer to DBMS_SPM
Documentation.

Evolving SQL Plan Baselines

If the optimizer generates a new plan for a repeated SQL statement, it is
automatically added to the SQL Plan History. However, it is not automatically
added to the baseline. In order for a new plan to be added to the SQL Plan Baseline, it must be "evolved" or verified first. Once again, there are several
methods for evolving a plan from the history into the baseline.

The Automatic SQL Tuning job (also new in 11g) that is run as an automatic task during the maintenance window will automatically verify plans that have been added into the SQL Plan History. Verification simply means that the plans are checked to ensure that the new plan will not result in a performance degradation or regression. 

This is an out-of-the-box feature that focuses on high-load statements
because these are the statements that the Automatic SQL Tuning job selects to
analyze.

In addition, manually running the SQL Tuning Advisor may result in plans being added to the SQL Plan Baseline. 

If the SQL Tuning Advisor yields a recommendation to create and use a SQL Profile, if that profile is accepted, the corresponding plan is automatically added to the baseline.

The 
ALTER_SQL_PLAN_BASELINE function of DBMS_SPM can be used to change the status of plans in the SQL History to Accepted, which in turn moves them into the SQL Baseline and the EVOLVE_SQL_PLAN_BASELINE function of the DBMS_SPM package can be used to see which plans have been evolved.

Fixing SQL Plan Baselines

Another option that can be used with SQL Plan Baselines is to mark one (or more)
specific plan as a fixed baseline. If a fixed plan exists in a baseline, the
optimizer will give priority to that fixed plan, even if a better, lower cost
plan is available.

This would enable a DBA to essentially guarantee (or at least make most likely) a very specific plan to be used by optimizer. However, there are some factors to be
aware of, if this approach is used. First, the optimizer will not add new
plans to the history if there is a fixed baseline and the
EVOLVE_SQL_PLAN_BASELINE function will not evolve plans either. New plans in
the SQL Plan Baseline would have to be added by manually loading them from the
SQL Cache or a SQL Tuning set. Second, there is a possibility that a better
execution plan will be ignored because it has not been marked as fixed.

A plan can be marked as fixed by using the ALTER_SQL_PLAN_BASELINE function of DBMS_SPM and changing the FIXED attribute to a value of YES.

DBA_SQL_PLAN_BASELINES Dictionary View

This is the main dictionary view used to see information about the SQL Plan Baselines. 

There are several columns that are of particular interest:

Column	Value	Meaning
ORIGIN	AUTO-CAPTURE	Evolved automatically when the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES was set to TRUE
	MANUAL-LOAD	Evolved via manual actions using DBMS_SPM
	MANUAL-SQLTUNE	Evolved as the result running the SQL Tuning Advisor
	AUTO-SQLTUNE	Evolved as the result of the Automatic SQL Tuning advisor job
ENABLED	YES	The plan is enabled for use by optimizer and may be considered
	NO	The plan will not be considered by optimizer
ACCEPTED	YES	The plan has been verified and accepted as a good execution plan
	NO	The plan has not yet been verified
FIXED	YES	The plan has been marked as a fixed plan, and can be used.
	NO	The plan is not fixed, and will not be used if fixed plans exist

Selecting SQL Baseline Plans

Once SQL Plan Baselines are in place, additional steps are taken to evaluate whether or not an execution plan from the SQL Plan Baseline should be used for a repeatable statement run in the database. In order for Oracle to replace a generated SQL Plan with a plan from the SQL Plan Baseline, the
OPTIMIZER_USE_SQL_PLAN_BASELINES must be set to TRUE (which is the default value).

For each statement that is run, the following steps take place.

Is OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE?
 
	YES?  Is the plan part of the SQL History?
 
		YES?  Is the plan part of the SQL Baseline?
 
		YES?  Use the plan generated
		NO?    Replace with the best SQL Baseline Plan 
 
	NO?    Add the plan to the SQL History and use the best SQL Baseline Plan
 
NO?    Use the plan generated

Controlling SQL Baseline Space Usage

The SQL Management Base (SMB) area allocated in the SYSAUX tablespace is controlled by two settings, and an automatic purge activity takes place to remove any plans that have not been used within a designated period of time.

By default, the SMB is limited to no more than 10 percent of the SYSAUX tablespace. This can be adjusted by using the DBMS_SPM.CONFIGURE(‘SPACE_BUDGET_PERCENT’,nn); command. 

The value of nn can be from 1percent to 50percent. If the space is exceeded, warnings are generated to the alert log on a weekly basis until the space
issue is resolved by adjusting the percentage, changing the size of SYSAUX, or
plans are purged.

Plans are purged on a weekly basis. By default, any plan that has not been used in 53 weeks will be automatically purged. This is also an adjustable value and is
changed with the DBMS_SPM.CONFIGURE(‘PLAN_RETENTION_WEEKS’,nnn); procedure.
Valid values are from 5 to 523 weeks. The LAST_EXECUTED column of
DBA_SQL_PLAN_BASELINES will indicate which plans might be up for purging in the near future.

In addition, plans can be manually removed from the SMB using the
DBMS_SPM.DROP_SQL_PLAN_BASELINE function.

Wrapping Up

Additional Information on SQL Plan Baselines is available through the website listed above, including how to export/import plans from one database to another using DataPump, and more details on migrating stored outlines into SQL Plan
Baselines.

Overall, SQL Plan Baselines may prove to be a very useful tool for DBAs by providing a benefit that will actually enhance the general stability of SQL performance by avoiding the use of new execution plans that will result in a slower, regressed query result. An additional benefit may be found in reducing the time and effort that DBAs have to spend doing SQL Statement tuning.

SQL Tuning Tips and Tricks - Oracle Database

 

SQL Tuning Tips and Tricks

To increase all Database Performance, you should tune both database and SQL statements.

 There are lots of tips and tricks for SQL Tuning but they may not work under every circumstance.

 These tips and tricks are a reference while fixing performance problems in the Oracle Database.

 

1- Run TOP SQL Statements according to your Workload status.

You should know or find busy and free time of database.

Small transactions can run every time in the OLTP database, but Batch jobs or TOP SQL statements like ETL, Reporting and etc should not run every time.

If you run TOP SQL statements like ETL, Reporting and etc, then you should set available time of database for this task.

If your database load is low in the daylight, you can run it. if database is very busy, you may run it at night or weekend.

2- Make sure that CBO ( Cost based Optimizer ) statistics are correct

Statistic of Database and its objects should be up to date in Oracle database for Oracle optimizer. Because Oracle optimizer uses database statistics to generate lots of execution plans in same time and If statistics are up to date ,then Optimizer decide correct execution plans.

 If your database statistics are staled ( out of date ), then you should update it. 

3- Check and Monitor Optimizer_Mode parameters

The default value of optimizer_mode parameter is ALL_ROWS which gives better throughput, but sometimes your application or software needs FIRST_ROWS if the response time is important.

Make sure that your software or application is running very well if the optimizer_mode is FIRST_ROWS.

4- Cursor Sharing and Bind Variable

You should set CURSOR_SHARING ( default value is Exact ) parameter value according to your database and software. If related application is not used Bind Variable , response time low due to a very high number of library cache misses and lots of SQL Statements matches with the text of SQL except the literal, then you should use CURSOR_SHARING=FORCE. 

Actually Cursor_sharing= force is temporary solution, Permanent solution is to use Bind Variable.

 

Oracle doesn’t recommend CURSOR_SHARING=FORCE in the DSS(Decision support system) environment or if you are using complex queries

 

 5-Index Usage

The usage of Index is very common especially in the performance tuning of Oracle Databases. It is one of the most important objects that are indispensable and proper performance tuning of databases.

You should use an index if less than 5% of the data needs to be accessed from a data set.

If your tables are getting extremely DML, you should not use so many Index on this table. Sometimes Dropping index will provide us extra performance.

Use function-based indexes If queries contain a built-in function like to_char, decode, substr, etc. If you don’t use function-based indexes, SQL query may cause a full-table scan instead of Index scan.

 

If query contains WHERE lower(FIRST_NAME) statement, then index should be created as follows.

SELECT * FROM HR.EMPLOYEES WHERE lower(FIRST_NAME)='Donald';

CREATE INDEX EMPLOYEE_X2 ON HR.EMPLOYEES(LOWER(FIRST_NAME)) TABLESPACE HR_INDEX;

6- Materialized views usage

Materialized views is used to pre-sort sets, pre-summarize complex data warehouse information , pre-join tables and create complex objects conflicts dynamically with the demand for sub-second response time.

7- Remove Subqueries

You may remove subqueries (exists, in, not in) in the Complex queries and rewrite and use Join instead of subquery for faster performance.

8- Use Stored Procedure instead of Views

You should encapsulate the complex SQL inside a stored procedure instead of view usage. Because views are caused Unnecessary overhead and Excessive hard parsing problems.

9- Prefer uncomplicated SQL

it is not recommended to use complex SQL and subqueries. You should use WITH clause and Global temporary tables to divide and flatten-out queries.

Decomposing a query into multiple queries are better than a Complex query.

10- Use Union all instead of Union

If using union all is possible, you should prefer it instead of union, because Union are doing expensive sort to remove duplicate rows.

You should prefer uncomplicated ( Basic ) SQL instead of very complex SQL, If you cannot divide it, then You can make a very complex statement slightly less complex by using the UNION ALL operator

11- Avoid Having clause usage

Using Having clause is very expensive for the Performance tuning. You should prefer With clause instead of Having to decompose a complex query.

12- Avoid Function usage like Substr

Try to avoid using too many functions, especially don’t  use SUBSTRING, instead use LIKE clause.

Difference between substr and like is Such as “where name like ‘DEV%”;” will use an index whereas a substr such as “where substr(name,1,2) = ‘Dev’; will require a function-based index (and extended optimizer statistics) for optimal performance.

Using the substr clause is best for large production queries, when you provide a function-based index and deploy extended optimizer statistics on the target column.

 

13- Use Truncate instead of Delete

You should prefer truncate table instead of delete table if you delete too many rows on a table. Too many delete operations cause fragmentation and performance problems.

 

14- Avoid Using Triggers

You should avoid using of TRIGGERS. Just use it as a last resort. It is better to use CONSTRAINT and STORED PROCEDURE to maintain the integrity of your databases.

15- Use Bulk DML instead of Single DML

You should use Bulk Insert,Delete and Update instead of Single Insert,Delete and Update if you are executing Bulk operation.

Bulk insert,Delete and Update will give your system a huge performance boost.

16- Avoid unnecessary columns

Unnecessary columns in Select clause incur more I/O on the database and increase network traffic.

You should analyze all columns in select or DML clause and if it is unnecessary, you should remove that columns in queries. Mostly Using SELECT * is not true for database and related query performance.

17- Avoid Distinct clause

DISTINCT operator causes sorting which slows down the SQL execution time.

If it is possible, it is better that remove Distinct clause in Select.

18- Use MINUS instead of EXISTS

Minus operator is mostly running in a faster execution plan than NOT IN and NOT Exists operator.

19- Use Alias for table column

If you use more than one table in a query, you should use table aliases for column to avoid confusion.

 

 

20- Use Parallel hint for large data access

If you execute any SQL which is accessing large data sets, then you may use Parallel hint to perform it in parallel.

When you use parallel hint, you should check operating system CPU counts and cores to specify how many parallel will you use it.

If lots of people use parallel hint everytime, operating system and database can  slow down or power off because of CPU bootleneck.

21- Use COUNT(1) instead of COUNT(*)

Using COUNT(1) instead of COUNT(*) is recommended  for SQL query performance optimization

22- Use Partitioning for Large Tables

If Slow query contains large tables, then you can use table partitioning to make faster query.

23- Use CASE statements instead of DECODE

You can use CASE statements instead of DECODE for the readability and performance of the query

 

24– Use IN instead of OR

You should use IN instead of OR in the where clause. IN is used for selective predicate is in the sub query,and EXISTS is used for selective predicate is in the parent query.

 

 

 

25– Avoid Complex expressions in SQL

You should avoid using complex expression in SQL to prevent the optimizer from assigning valid cardinality or selectivity estimates.

 

--Do not use:
WHERE SUBSTR(a.id, INSTR(b.id, ',') - 1) = SUBSTR(b.id, INSTR(b.id, ',') - 1)

--Use this instead of above:
WHERE a.id = b.id



--Avoid:
WHERE id = NVL(:a1, id)

WHERE NVL(id,-1) = ( 1, etc...)

NVL (col1,-999) = ….


TO_DATE(), TO_NUMBER(), and so on

26– Prefer INNER JOIN instead of OUTER JOIN

Inner join is recommended instead of Outer Join if possible. Because Outer join limits the database optimization options

27– Avoid ORDER BY and GROUP BY If possible

it is recommended to avoid ORDER BY, GROUP BY clause , if using ORDER BY, GROUP BY clause is not mandatory for you. Because ORDER BY, GROUP BY causes sorting and result in one of the most expensive operations in SQL execution.

28– Avoid Hint usage

Hint usage is not recommended instead optimizing your code.

Hint usage is the last thing you should consider for Performance tuning or it is a temporary solution.

29– Drop Unused Index

You should drop unused index on tables, monitor all indexes and check them if they are unnecessary and unused, you should drop them.

Most common mistake is using Index, Index is not good everytime. You should monitor it, If it is good for performance, you can keep it, if not you should drop it.

30– Avoid using wildcard (%)

Using wildcard (%) at the beginning of a predicate is a known performance problem in all databases.

Wildcard (%) usage LIKE ‘%abc’ causes full table scan, so avoid Using wildcard (%).

31–  Use Hash Join for Large tables Join

You should examine execution plan carefully, If 2 large tables are joined, If they don’t use Hash Join, use Hash Join hint. If a large table is joined with a small table,  If they don’t use Nested loop Join, use Nested loop Join hint.