One Easy Step To Get Started With SQL Plan Baselines
If you wonder how SQL Plan Baselines work, then this post is for you!
If you want to get started with SQL Plan Baselines, but don’t know how, this post is for you!
If you don’t understand how the optimizer picks the execution plans when SQL Plan Baselines are present, then this post is for you!
If you do know all of the above, and feel confident about SQL Plan Baselines, then just pass on this post to someone who would benefit from it!
Last week, I introduced to you, all the building blocks of the SQL Plan Management.
You learned about execution plans, SQL Plan History, SQL Plan Baselines and much more. If you need a refresher, just click here!
Why should you spend time on developing some knowledge around SQL Plan Baselines? Why not spend the time learning something else, or reading a book, playing a game?
Let me ask you this question:
Do you know when the execution plan of a SQL Statement will change for the worse?
I imagine you answered “No”, and neither do I!
As a DBA you need to be ready for such events, plan changes, performance problems. You never know when you might need SQL Plan Baselines!
Let me show you how to get started, and how the logic of the optimizer works when selecting the execution plans.
1. How To Turn On Capture Of SQL Plan Baselines?
2. How Does The Database Determine If A SQL Statement Is Repeatable?
3. How Does The Optimizer Select The Plan From SQL Plan Baseline?
1. How To Turn On Capture Of SQL Plan Baselines?
If you have doubts about being able to use or not the components of SQL Plan Management, because of licensing, rest assured you are allowed to use them.
Using SQL Plan Management (SPM) and SQL Plan Baselines does not require additional licensing costs. These are part of the Oracle Enterprise Edition. You can check out Maria Colgan’s post on licensing for these components here.
By default SQL Plan Baselines or SQL Plan History is not captured. Just check SQLLOG$ table, most likely there are no rows returned.
In order to start capturing baselines and plans, you need to modify the following parameter. By default, the value is set to FALSE.
alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
The parameter is dynamic, so you can do it on the fly, and you can turn it on at the session level as well.
When you turn this parameter on, 2 things are accomplished:
– SQL Plan History is turned on
– SQL Plan Baseline is turned on
The database will start capturing plan history and baselines for repeatable SQL Statements.
2. How Does The Database Determine If A SQL Statement Is Repeatable?
After you turn on the capture (Step #1 from above), the SQL Statement log will store the signature of SQL statements.
SQL signature is a numeric hash value generated during the parse phase of a SQL Statement.
These signatures are stored in SQLLOG$.
During the automatic capture, the database will match the SQL Statement’s signature with the values from SQLLOG$, to confirm if the statement has been executed before. If the statement is not found (meaning it is the first execution), then the signature is added to the log.
If the statement is found (an identical signature has been found in SQLLOG$), then it has been confirmed it is a repeatable SQL statement.
Findings from the field:
Applies to version 12.1.0.1: When you turn on the capture (alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE), if you run other sql statements in the same session that turned the capture on, these sql statements are not added to sqllog$. In order to be added, run the sql statements in another session.
Below is an example of how this works:
SQL> select version from v$instance;
VERSION
-----------------
12.1.0.1.0
SQL> show parameter optimizer_capture_sql_plan_baselines
NAME TYPE VALUE
------------------------------------ ----------- ---------
optimizer_capture_sql_plan_baselines boolean FALSE
SQL> select count(*) from sqllog$;
COUNT(*)
----------
0 -->> no entries in SQL Plan History
SQL> select count(*) from dba_sql_plan_baselines;
COUNT(*)
----------
0 -->> no entries in SQL Plan Baseline
SQL> alter session set optimizer_capture_sql_plan_baselines=TRUE;
SQL> show parameter optimizer_capture_sql_plan_baselines
NAME TYPE VALUE
------------------------------------ ----------- --------
optimizer_capture_sql_plan_baselines boolean TRUE
-->> Connect to another session. I found if I use the same session, sqllog$ doesn't get populated.
-->> First run of this SQL Statement. Should be captured in SQLLOG$, and no SQL Plan Baseline
SQL> select empno, ename, sal from scott.emp where deptno=10;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
SQL> select * from sqllog$;
SIGNATURE BATCH#
---------------------------------- ----------
3812309255651905568 1
SQL> select signature, sql_text from dba_sql_plan_baselines;
no rows selected
-->> confirms that no SQL Plan Baseline was created.
--> Execute the same statement again:
SQL> select empno, ename, sal from scott.emp where deptno=10;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
SQL> select * from sqllog$;
SIGNATURE BATCH#
---------------------------------- ----------
3812309255651905568 1
SQL> select signature, sql_text from dba_sql_plan_baselines;
SIGNATURE SQL_TEXT
---------------------------------- ------------------------------------------------------------
3812309255651905568 select empno, ename, sal from scott.emp where deptno=10
-->> confirms SQL Plan Baseline has been created.
To confirm the signature:
SQL> select sql_id, sql_text , exact_matching_signature from v$sql where exact_matching_signature=3812309255651905568;
SQL_ID SQL_TEXT EXACT_MATCHING_SIGNATURE
------------- ------------------------------------------------------------ ------------------------
axrp4uruswzap select empno, ename, sal from scott.emp where deptno=10 3812309255651905568
3. How Does The Optimizer Select The Plan From SQL Plan Baseline?
After the capture has been turned on, will the optimizer use the SQL Plan Baselines?
The answer is YES, if the following parameter is set to TRUE, which by default is.
optimizer_use_sql_plan_baselines = TRUE
What happens behind the scene, how will the optimizer choose the baseline to use?
1. During the hard parse of a statement, the optimizer will generate a “best-cost” execution plan.
2. Optimizer will try to find a matching plan in the SQL plan baselines.
- If there is no plan at all in the SQL Plan Baseline, then the “best-cost” plan is used to execute the statement.
- If the “best-cost” plan is found also in the SQL Plan Baseline, then the “best-cost” plan is used to execute the statement.
- If the “best-cost” plan is not in the baseline, and a baseline exists, then the “best-cost” plan is marked as un-accepted and is added to the plan history.
Depending on what type of plans are in the baseline, the optimizer will compare the costs of the accepted plans, and use the plan with the lowest cost.
Oracle has a great, easy to understand diagram. (Source: http://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#BEGIN)
If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!
–Diana