Oracle SQL Plan Management, Literal Values, and Cursor Sharing

Recently, at the 12th Annual Hotsos Symposium in Irving, TX, there were some questions regarding the use of statements with literals and the use of SQL Plan Baselines (part of SQL Plan Management – a new feature introduced in Oracle 11g).

In this post we develop examples to show that the Oracle database is working just as it was designed to in this area.  This post is not considering Adaptive Cursor Sharing.  That topic is covered in a blog posting by Dominic Brooks

Various plan control techniques (such as stored outlines and SQL profiles) have been introduced in past versions of Oracle Database to address performance issues (regression) due to plan changes. These techniques are reactive processes that require manual intervention.

SQL Plan Management is a new feature introduced with Oracle Database 11g that enables the system to automatically control SQL plan evolution by maintaining SQL Plan baselines. With this feature enabled, a newly generated SQL plan can integrate a SQL Plan baseline only if it has been verified that doing so will not result in performance regression. During execution of a SQL statement, only a plan that is part of the corresponding SQL Plan baseline can be used.

SQL Plan baselines can be automatically loaded, or can be seeded using SQL Tuning Sets.

The main benefit of the SQL Plan Management feature is the performance stability of the system through the avoidance of plan regressions.

A set of one or more accepted plans for a repeatable SQL statement. Each accepted plan contains a set of hints, a plan hash value, and other plan-related information. SQL plan management uses SQL plan baselines to record and evaluate the execution plans of SQL statements over time.

SQL plan baselines reproduce a specific plan.  A SQL plan baseline is a set of accepted plans. Each plan is implemented using a set of outline hints that fully specify a particular plan.

For SQL statements that are executed multiple times, the optimizer maintains a history of plans for each individual SQL statements. The optimizer recognizes a repeatable SQL statement by maintaining a statement log.   Once logged, a SQL statement is recognized as repeatable when it is parsed or executed again.   After a SQL statement is recognized as repeatable, the optimizer maintains a plan history containing relevant information (such as SQL text, outline, bind variables, and compilation environment) that is used by the optimizer to reproduce a each of the execution plan.

A plan history contains different plans generated by the optimizer for a SQL statement over time. However, only some of the plans in the plan history may be accepted for use. For example, a new plan generated by the optimizer is not normally used until it has been verified not to cause a performance regression. Plan verification is an automated task  as part of Automatic SQL Tuning, or can be done manually.

It appears that many persons read this to mean that automatic capture can occur if the statement is run twice.  Technically, that qualifies as a repeatable statement per the definition.

However, a quick trick to try is to run this command:

SQL> alter system set optimizer_capture_sql_plan_baselines = true;

Upon issuing this command, one ran 6 different SQL statements 5 times each.  Technically, they are repeatable.  However, when setting the parameter back to false, and then checking the SQL Plan Baselines created, not one of the 6 statements I ran had a SQL Plan Baseline created for it.  More than 200+ baselines were created (and accepted) for the Recursive SQL that was generated in that short period of time.  Not one corresponded to any of the user SQL as hoped.  Speculation leads me tosay that the database engine does not consider twice as repeatable.  This post was not intended to report some magical “cutoff” number, i.e., how many times must it be run to be repeatable.  The data suggests that twice is not enough.

Instead, one finds that by modifying the parameter, OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true at the session level, and executing the SQL hundreds or even thousands of times, the SQL Optimizer picks up on this and created the SQL Plan Baselines for the user SQL as expected.  (Note: Using Automatic Capture)

What happens when the SQL statement is not really repeatable.  If one runs a statement that uses a literal in the predicate, then to the optimizer it is a different statement each time.  SQL Plan Baselines will not be created during Automatic Capture. The statements are not being repeated.

Oracle has a feature using the parameter CURSOR_SHARING designed to eliminate the need for hard parsing the statements that differ only by literal values in the predicate.  When the parameter is set to the value of FORCE (default – EXACT), then the literal is replaced by a bind variable, enabling the use of the soft parse rather than a hard parse.

This can be seen first by generating a file that runs the target SQL statement with a literal value in the where clause.  The code generates the statements with valid primary key values to be used in the file.  Six thousand SQL statements were generated.  (Maybe a little overkill…)  The file was edited to add the alter session … statements.  Then executed.

SQL> create or replace procedure gen_sql_symposium14
  2  (p_no_lines  number )
  3  is
  4
  5  v_fhandle utl_file.file_type;
  6
  7  v_code  NUMBER;
  8  v_errm  VARCHAR2(64);
  9
 10  cursor sql_gen_sym
 11  is
 12  select order_no from ord order by order_no;
 13
 14  v_no_lines number := p_no_lines;
 15  v_counter number := 0;
 16
 17  begin
 18     v_fhandle := utl_file.fopen('OP_DIR','sym_gen.sql', 'w', 512);
 19                     for r_gen_sql in sql_gen_sym
 20                     loop
 21     if (v_counter <= v_no_lines) then
 22     utl_file.put_line(v_fhandle, 'select cust_no, order_date from 
           ord where order_no = '||to_char(r_gen_sql.order_no)||';');
 23     v_counter := v_counter + 1;
 24     end if;
 25             end loop;
 26  --utl_file.put_line(v_fhandle, '/');
 27  utl_file.fflush(v_fhandle);
 28  utl_file.fclose(v_fhandle);
 29  exception
 30  when others then
 31       v_code := SQLCODE;
 32       v_errm := SUBSTR(SQLERRM, 1, 64);
 33       DBMS_OUTPUT.PUT_LINE
 34        ('Error code : ' || v_code || ': ' || v_errm);
 35  end;
 36  /
SQL>
SQL> exec gen_sql_symposium14(6000)
SQL> set term off
SQL> @sym_gen
SQL> set term on
SQL>
edit sym_gen.sql

alter session set cursor_sharing=force;
alter session set optimizer_capture_sql_plan_baselines = true;

select cust_no, order_date from ord where order_no = 2473;
select cust_no, order_date from ord where order_no = 2474;
select cust_no, order_date from ord where order_no = 2475;
...
select cust_no, order_date from ord where order_no = 8472;
select cust_no, order_date from ord where order_no = 8473;

alter session set cursor_sharing=exact;
alter session set optimizer_capture_sql_plan_baselines = false;

At the end of the execution, one SQL Plan Baseline was created.  Note the use of the Bind Variable.

 SQL_HANDLE          SQL_TEXT                   PLAN_NAME                      ENA ACC CRE
-------------------- -------------------------- ------------------------------ --- --- -----------------------------
SQL_a2eef1ae5b84a3d1 select cust_no, order_date SQL_PLAN_a5vrjptds98yjafd3f74b YES YES 10-MAR-2014 17:09:44
                     from ord where order_no 
                     = :"SYS_B_0"

The Execution Plan:

--------------------------------------------------------------------------------
SQL handle: SQL_a2eef1ae5b84a3d1
SQL text: select cust_no, order_date from ord where order_no = :"SYS_B_0"
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_a5vrjptds98yjafd3f74b         Plan id: 2949904203
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 2364090746

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |     1 |    18 |     2   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| ORD             |     1 |    18 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX UNIQUE SCAN                | ORD_ORDER_NO_PK |     1 |       |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORDER_NO"=TO_NUMBER(:SYS_B_0))

Note the Plan hash value: 2364090746

Now query the V$SQL view:

SQL> l
  1  select sql_id
  2    ,      child_number
  3    ,      is_bind_aware
  4    ,      is_bind_sensitive
  5    ,      is_shareable
  6    ,      to_char(exact_matching_signature) sig
  7    ,      executions
  8    ,      plan_hash_value
  9    from   v$sql
 10*   where  sql_id = '&1'

SQL> @q_vsql2 'select cust_no, order_date from ord where order_no ='

SQL_ID           CHILD_NUMBER I I I SIG                                           EXECUTIONS PLAN_HASH_VALUE
------------- --------------- - - - ---------------------------------------- --------------- ---------------
5x85dq4jfsc1r               0 N N Y 3622053216508695589                                    1      2364090746
5x85dq4jfsc1r               1 N N Y 3622053216508695589                                    1      2364090746
5x85dq4jfsc1r               2 N N Y 3622053216508695589                                    1      2364090746
5x85dq4jfsc1r               3 N N Y 3622053216508695589                                    1      2364090746
5gccyzyf1pkyc               0 N N Y 3622053216508695589                                    1      2364090746
9a23hu491qcqx               0 N N Y 11740587009763484625                                   2      2364090746
9a23hu491qcqx               1 N N Y 11740587009763484625                               12000      2364090746
9a23hu491qcqx               2 N N Y 11740587009763484625                                6001      2364090746
9a23hu491qcqx               3 N N Y 11740587009763484625                                 428      2364090746
9a23hu491qcqx               4 N N Y 11740587009763484625                                6001      2364090746
3pk9n4j2krmax               0 N N Y 3622053216508695589                                    1      2364090746
3pk9n4j2krmax               1 N N Y 3622053216508695589                                    1      2364090746
3pk9n4j2krmax               2 N N Y 3622053216508695589                                    1      2364090746
3pk9n4j2krmax               3 N N Y 3622053216508695589                                    1      2364090746
3pk9n4j2krmax               4 N N Y 3622053216508695589                                    1      2364090746
3pk9n4j2krmax               5 N N Y 3622053216508695589                                    1      2364090746
3pk9n4j2krmax               6 N N Y 3622053216508695589                                    1      2364090746
3pk9n4j2krmax               7 N N Y 3622053216508695589                                    1      2364090746
3pk9n4j2krmax               8 N N Y 3622053216508695589                                    1      2364090746
3pk9n4j2krmax               9 N N Y 3622053216508695589                                    1      2364090746
4c57ggac4vt8s               0 N N Y 3622053216508695589                                   86      2364090746
SQL>

Now we see that for every occurrence of the text string representing the query, it uses the same SQL PLAN HASH VALUE 2364090746.  This is the SQL PLAN HASH VALUE associated with the SQL PLAN BASELINE created using a bind variable.

Conclusions:

There are a couple of issues that will muck things up for persons testing SQL PLAN BASELINES and CURSOR_SHARING in different ways.

First, just doing an Explain Plan for the individual statements does not show the use of a bind variable.  The database is doing this at execution, not in generating a plan.  The plans will generally show the literal in the predicate.  V$SQL shows the PLAN HASH VALUE being used.

Second, what really is the concept of a repeatable SQL statement?  The data seems to suggest that the statements need to be run more than the recursive SQL seen executing in the database.  Speculation – Absolutely.  Just following where the data leads me.

SQL PLAN BASELINES do work with CURSOR_SHARING=FORCE.  Perhaps it is not working in a manner many would like it to work, or how they think it should work.  The data, however, tells us that it works in the manner that Oracle tells us it should work.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>