Learn Oracle

You are just 10 steps away from becoming a Oracle DBA. Materialize your dream by following the The 10 Simple Steps .

11g SqlPlanManagment

This article will make you learn Oracle Adaptive Cursors and Sql plan management.

Use bind variables that intelligently pick the right plan every time and make sure a new execution plan is perfect before it's used.

By now many of you have heard an earful about how using bind variables enhances performance; for those who haven't, let me try to explain the core concepts in as simple manner .

Assume you have a table called CUSTOMERS that has, among others, a column called STATE_CODE, which store the customer's residence state in the two-letter abbreviation of the U.S. states—CT, NY and so on. When Oracle DBA want to find out how many customers have purchased more than three times and are from the state of Connecticut ('CT'), you will most likely issue:

select count(1)
from customers
where state_code = 'CT'
and times_purchased > 3;
When Oracle DBA issue this query, Oracle has to perform an activity called parsing, which will generate an execution plan for the SQL statement Oracle DBA just issued. After parsing the query is ready fro execution. Parsing is similar in concept to compilation of code in software; when Oracle DBA write something in C++, say, Oracle DBA can't run that in the operating system—first you must compile it and make it an executable. The activity of parsing makes an executable from the SQL statement.

Now suppose another user issues a statement as shown below:

select count(1)
from customers
where state_code = 'NY'
and times_purchased > 3;  
This is almost identical to the query above, with one exception: the state_code searched is NY instead of CT. Ideally, the parsed code would be the same and the value of the literal would be supplied at runtime. But the way the queries are written, Oracle interprets them as different and has to do another parse for the second query.

Consider instead if the queries were written as:

select count(1)
from customers
where state_code = <StateCode>
and times_purchased > 3;
The first query would have passed NY as the value of <StateCode> and the second, CT. The query would not have to be parsed again.

In this example, the <StateCode> is conceptually known as a bind variable, which is a place holder for values to be passed during execution. Bind variables are represented in the form of :VariableName, as shown below:

where state_code = :state_code
If your code does not have bind variables and instead littered with references to literal values such as where state_code = 'CT', you can force all literals to be converted to bind variables by specifying an initialization parameter:
cursor_sharing = force
This parameter will cause the statement where state_code = 'CT' to be rewritten as where state_code = ":SYS_0001" where SYS_0001 is a system generated variable name. This approach will make these statements identical.

The Problem with Bind Variables

Well, if bind variables are so great, why not use them all the time? Don't we have a magic bullet—cursor_sharing—which transforms all the bad code to sharable statements? (Those already familiar with the reasons, especially the concept of bind-peeking, can skip to the section titled "Adaptive Cursors".)

Consider the case where there is an index on the column STATE_CODE. The values in the column are shown below:

select state_code, count(1)
from customers
group by state_code;
-- ----------
NY     994901
CT       5099
As you can see, the data is highly skewed; about 5% of the rows have 'CT' in them while the rest have 'NY'. It's not surprising considering the population of the states. Now, let's see what type of execution plan is generated for the query shown earlier:

SQL> set autot traceonly explain
SQL> select * from customers where state_code = 'NY' and times_purchased > 3
  2  /
Execution Plan
Plan hash value: 2008213504
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |           |   895K|    26M|  1532   (9)| 00:00:19 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |   895K|    26M|  1532   (9)| 00:00:19 |
Predicate Information (identified by operation id):
   1 - filter("TIMES_PURCHASED">3 AND "STATE_CODE"='NY')

The query used a full table scan—the appropriate action since 95% of the rows are returned with the query and an index scan would have been very expensive. Now issue the same query with 'CT':

  1* select * from customers where state_code = 'CT' and times_purchased > 3
SQL> /
Execution Plan
Plan hash value: 4876992
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT            |               |  4589 |   138K|    56   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS     |  4589 |   138K|    56   (2)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IN_CUST_STATE |  5099 |       |    12   (0)| 00:00:01 |
Predicate Information (identified by operation id):
   1 - filter("TIMES_PURCHASED">3)
   2 - access("STATE_CODE"='CT')
It used the index. Again, that was appropriate; CT accounts for only 5% of the rows and an index scan will be beneficial.

Let's see the behavior when using a bind variable. Here is the demonstrated behavior in Oracle Database 10g.

SQL> var state_code varchar2(2)
SQL> exec :state_code := 'CT'
PL/SQL procedure successfully completed.
SQL> select max(times_purchased) from customers where state_code = :state_code
  2  /
Execution Plan
Plan hash value: 296924608
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |           |     1 |     6 |  1511   (8)| 00:00:19 |
|   1 |  SORT AGGREGATE    |           |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |   500K|  2929K|  1511   (8)| 00:00:19 |
Predicate Information (identified by operation id):
   2 - filter("STATE_CODE"=:STATE_CODE)
The optimizer chose Full Table Scan on CUSTOMERS table. Shouldn't the index be used because we are searching for CT only, which accounts for a mere 5% of the total records? What made the optimizer choose full table scan over index scan?

The answer is a phenomenon called bind peeking. Earlier, when you ran that query with the bind variable value set to 'NY', the optimizer had to do a hard parse for the first time and while doing so it peeked at the bind variable to see what value had been assigned to it. The value was 'NY'. Since 'NY' accounts for about 95% of the rows, the optimizer chose full table scan (as expected). In addition, it also froze the plan for the query. Next, when we issued the same query, but for 'CT', the plan was not re-calculated and the optimizer used the same plan used earlier, even though it was not the best one for the purpose. Had Oracle DBA used a value such as 'CT' instead of the bind variable in the query, the optimizer would have picked the correct plan.

Thus as Oracle DBA can see, bind variables, even though they were good in most cases, actually failed in cases where the selectivity of the values radically affected the plans, as in this example where the selectivity of the values 'CT' and 'NY' were 5% and 95% respectively. In cases where the distribution of data is such that the selectivity is almost the same for all values, the execution plan would remain the same. Therefore smart SQL coders will choose when to break the cardinal rule of using bind variables, employing literals instead.

Adaptive Cursors

But what if Oracle DBA don't have a lot of smart coders or the time to rewrite these statements? Does Oracle provide some smart alternatives?

Yes it does. With Oracle Database 11g, cursors suddenly have a new kind of intelligence. Instead of blindly using the cached execution plan whenever the query is executed, they actually decide if a plan has to be recalculated when the bind variable value changes. If a cursor has a bind variable in it, the database observes it for a while to see what type of values are passed to the variable and if the plan needs recalculation. If the plan does need to be recalculated, the cursor is marked as "Bind-Sensitive".

The example query shown previously is a perfect candidate. The correct optimizer plan will be used based on the value of the bind variable. There is no need for you to do anything; it happens automatically.

The dictionary view V$SQL has been modified to add two more columns: IS_BIND_SENSITIVE and IS_BIND_AWARE. Let's see how they are used:

select is_bind_sensitive, is_bind_aware, sql_id, child_number
from v$sql
where sql_text = 'select count(1) from customers where state_code = :state_code and times_purchased > 3'

- - ------------- ------------
Y Y 7cv5271zx2ttg            0
Y N 7cv5271zx2ttg            1
Let's see what the columns mean. Oracle observes the cursors for a while and sees how the values differ. If the different values can potentially alter the plan, the cursor is labeled "Bind-Sensitive" and the column IS_BIND_SENSITIVE shows "Y". After a few executions, the database knows more about the cursors and the values and decides if the cursor should be made to change plans based on the values. If that is the case, the cursor is called "Bind-Aware" and the column IS_BIND_AWARE shows "Y". In summary: Bind-Sensitive cursors are potential candidates for changed plans and Bind-Aware ones are where the plans actually change.

As the adaptive cursor sharing feature uses the correct plan based on the value of the bind variable, the database must be holding that information somewhere. It exposes that information through another new view V$SQL_CS_SELECTIVITY that shows the selectivity of the different values passed to the bind variable.
select * from v$sql_cs_selectivity
where sql_id = '7cv5271zx2ttg'
-------- ---------- ------------- ----------- ----------- - -------- ----------
45C8218C 2144429871 7cv5271zx2ttg           5 =STATE_CODE 0 0.895410   1.094391
45C8218C 2144429871 7cv5271zx2ttg           4 =STATE_CODE 0 0.004589   0.005609
45C8218C 2144429871 7cv5271zx2ttg           4 =STATE_CODE 1 0.002295   0.002804
45C8218C 2144429871 7cv5271zx2ttg           3 =STATE_CODE 0 0.002295   0.002804
45C8218C 2144429871 7cv5271zx2ttg           0 =STATE_CODE 0 0.004589   0.005609
This view shows a wealth of information. The column PREDICATE shows the various predicates (the WHERE condition) users have used. The LOW and HIGH values show the range of values passed.

Finally, a third new view, V$SQL_CS_STATISTICS, shows the activities by the cursors marked either Bind-Aware or Bind-Sensitive.

select 	child_number, 
from v$sql_cs_statistics
where sql_id = '7cv5271zx2ttg';
------------ ------------------- - ---------- -------------- ----------- ----------
           1            22981142 Y          1           9592        3219          0
           0            22981142 Y          1           9592        3281          0
This view shows the statistics about the execution as recorded by the database. The column EXECUTIONS shows how many times the query was seen to be executed with different values in the bind variable. The column PEEKED (shown as "P") in the output shows if the optimizer peeked into the bind variable to arrive at a good plan.

These views show additional information that is not necessary for you to understand how this feature works. Adaptive Cursors are activated and used automatically.

SQL Plan Management

How many times you have seen this situation: A query has the best possible plan but suddenly something happens to throw the plan off? The "something" could be that someone analyzed the table again or some optimizer influencing parameters such as star_transformation is changed—the list of possibilities is endless. Out of frustration Oracle DBA may clamp down on any changes on the database, meaning no database stats collection, no parameter changes, and so on.

But that's easier said than done. What happens when the data pattern changes? Take, for instance, the example shown in the section on Adaptive Cursors. The CUSTOMERS table is now filled with customers from New York; so the STATE_CODE is mostly "NY". So when a query with a predicate as shown below is executed:

where state_code = 'CT'
the index scanning does not occur; instead the system does a full table scan. When the predicate is:
where state_code = 'CT'
...the index is used since it will return few rows. However, what happens if the pattern changes—say, suddenly there are a lot more customers from Connecticut (state_code = 'CT'); so much more so that the percentage of CT now jumps to 70%. In that case the CT queries should use full table scans. But as Oracle DBA have stopped collecting optimizer stats, the optimizer will not know about the change in pattern and will continue to derive an index scan path which is inefficient. What can Oracle DBA do?

What if Oracle used the optimal plan but reevaluated that plan when underlying factors such as stats collection or database parameters change, at which point it used the new plan if and only if the new plan is better? That would be splendid, wouldn't it? Well, it's possible in Oracle Database 11g. Let's see how.

SQL Plan Baselining

In Oracle Database 11g, when an already calculated optimizer plan needs to be updated because of changes in the underlying factors, it does not go into effect immediately. Rather Oracle evaluates the new plan and implements it in only if improves on the one already there. In addition, tools and interfaces are available to see the history of the plans calculated for each query and how they compare.

The life cycle starts with Oracle identifying a statement as one that is executed more than once, or "repeatable". Once a repeatable statement is identified, its plan is captured and stored as a SQL Plan Baseline, in the database in a logical construct known as SQL Management Base (SMB). When a new plan is calculated for this query for whatever reason, the new plan is also stored in the SMB. So the SMB stores each plan for the query, how it was generated, and so on.

The plans are not stored in SMB automatically. If that were the case, the SMB would hold plans of every type of query and become huge. Instead, you can and should control how many queries go into the SMB. There are two ways to do that: making all repeatable queries baselined in SMB automatically, or manually loading the queries that should be baselined

Let's look at the simple case first: you can make the SQL Plan Management feature capture SQL Plan Baselines for all repeatable queries automatically by setting a database parameter optimizer_capture_sql_plan_baselines, which is by default FALSE, to TRUE. Fortunately, this is a dynamic parameter.

SQL> alter system optimizer_capture_sql_plan_baselines = true;
After this statement is executed, the execution plans for all repeatable statements are stored as SQL Plan Baselines in the SMB. The SQL Plan Baselines are stored in the view called DBA_SQL_PLAN_BASELINES. You can also see it in the Enterprise Manager. To examine the baselined plans, bring up EM and click on the tab "Server" as shown in figure below:

Learn Oracle Sql Plan

From this page, click SQL Plan Control in the section Query Optimizer, which brings up the main SPM page shown below:

Learn Oracle Sql Plan

Click the SQL Plan Baseline tab, which brings up a screen similar to as shown below:

Learn Oracle Sql Plan

This is the main SQL Plan Baseline screen. At the top left corner, Oracle DBA will see the configuration parameters. It shows Capture SQL Plan Baselines as TRUE, which is what Oracle DBA enabled with the ALTER SYSTEM command. Below that is the Use SQL Plan Baselines set to TRUE (the default). It indicates that SQL Plan Baselines are to be used for a query if one is available.

Whenever a new plan is generated for the query, the old plan is retained in the history in the SMB. However, it also means that the SMB will be crowded with plan histories. A parameter controls how many weeks the plans are retained for, which is shown in the text box against Plan Retention (Weeks). In this screen it shows as set to 53 weeks. If a SQL Plan Baseline has not been used for 53 weeks it will be purged automatically.

The middle part of the screen has a search box where Oracle DBA can search for SQL statements. Enter a search string here and press Go, you will see the SQL statements and associated plans as shown in the figure above. Each baselined plan has a lot of status information associated with it. Let's see what they are:

  • Enabled - A baselined plan has to be enabled to be considered
  • Accepted - A baselined plan is considered to an be acceptable plan for a query
  • Fixed - If a plan is marked as FIXED, then the optimizer considers only that in deciding the best plan. So, if five plans are baselined for a query and three are marked "fixed", then the optimizer considers only those three in choosing the best plan.
  • Auto-Purge - If the plan should be purged automatically
The same information and more is also available in the view DBA_SQL_PLAN_BASELINES:
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 SIGNATURE                                 NOT NULL NUMBER
 SQL_HANDLE                                NOT NULL VARCHAR2(30)
 SQL_TEXT                                  NOT NULL CLOB
 PLAN_NAME                                 NOT NULL VARCHAR2(30)
 CREATOR                                            VARCHAR2(30)
 ORIGIN                                             VARCHAR2(14)
 PARSING_SCHEMA_NAME                                VARCHAR2(30)
 DESCRIPTION                                        VARCHAR2(500)
 VERSION                                            VARCHAR2(64)
 CREATED                                   NOT NULL TIMESTAMP(6)
 LAST_MODIFIED                                      TIMESTAMP(6)
 LAST_EXECUTED                                      TIMESTAMP(6)
 LAST_VERIFIED                                      TIMESTAMP(6)
 ENABLED                                            VARCHAR2(3)
 ACCEPTED                                           VARCHAR2(3)
 FIXED                                              VARCHAR2(3)
 AUTOPURGE                                          VARCHAR2(3)
 OPTIMIZER_COST                                     NUMBER
 MODULE                                             VARCHAR2(48)
 ACTION                                             VARCHAR2(32)
 EXECUTIONS                                         NUMBER
 ELAPSED_TIME                                       NUMBER
 CPU_TIME                                           NUMBER
 BUFFER_GETS                                        NUMBER
 DISK_READS                                         NUMBER
 DIRECT_WRITES                                      NUMBER
 ROWS_PROCESSED                                     NUMBER
 FETCHES                                            NUMBER
 END_OF_FETCH_COUNT                                 NUMBER
If you click the plan name, it will show you the plan details. Here is an output:

Sql Plan

In the details you can see the explain plan of the query, along with the other relevant details such as whether the plan is accepted, enabled, fixed, and so on. Another important attribute is "Origin", which shows AUTO-CAPTURE—meaning the plan was captured automatically by the system because optimizer_capture_sql_plan_baselines was set to TRUE.

Click Return to get back to the list of plans as shown in the previous figure. Now select a plan whose status is not accepted and click Evolve to see if the plan should be examined for a potentially better plan. The following screen comes up.

 Sql Plan

The important point to note in this screen is the Verify Performance radio button. If you want to examine the plans and compare its performance to that of the existing SQL Plan Baseline for the query, you should select that. Click OK. This shows the report of the comparison:
                        Evolve SQL Plan Baseline Report

  PLAN_LIST  = SYS_SQL_PLAN_b5429522ee05ab0e
  VERIFY     = YES
  COMMIT     = YES

Plan: SYS_SQL_PLAN_b5429522e53beeec
  It is already an accepted plan.

Plan: SYS_SQL_PLAN_b5429522ee05ab0e
  Plan was verified: Time used 3.9 seconds.
  Failed performance criterion: Compound improvement ratio <= 1.4.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 1              1
  Elapsed Time(ms):            3396            440              7.72
  CPU Time(ms):                1990            408              4.88
  Buffer Gets:                 7048           5140              1.37
  Disk Reads:                  4732             53             89.28
  Direct Writes:                  0              0
  Fetches:                     4732             25            189.28
  Executions:                     1              1
This is a pretty good comparison report that shows how the plans compare. If a specific plan is shown to perform better, then the optimizer will use it. If the new plan does not show an appreciable performance improvement, then it should not be accepted and be used. SQL Performance Management allows you to see first hand how the plans compare and use the ones that are truly better.

You can change the accepted status of a plan manually by executing the DBMS_SPM package:

   ctr binary_integer;
   ctr := dbms_spm.alter_sql_plan_baseline (
      sql_handle      => 'SYS_SQL_e0b19f65b5429522',
      plan_name       => 'SYS_SQL_PLAN_b5429522ee05ab0e',
      attribute_name  => 'ACCEPTED',
      attribute_value => 'NO'
You can disable a SQL Plan Baseline so that it does not get used by the optimizer. Later you can re-enable the plan so that it gets used again. To disable, use this:
   ctr binary_integer;
   ctr := dbms_spm.alter_sql_plan_baseline (
      sql_handle      => 'SYS_SQL_e0b19f65b5429522',
      plan_name       => 'SYS_SQL_PLAN_b5429522ee05ab0e',
      attribute_name  => 'ENABLED',
      attribute_value => 'NO'
with Adaptive Cursors, Oracle DBA can use learn oracle bind variables and yet not risk a sub-optimal plan and with SQL Plan Management, the execution plan is not cast in stone but rather can evolve over time while retaining the stability in the short term.

More Tutorials on Oracle dba ...

Liked it ? Want to share it ? Social Bookmarking
Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Source : Oracle Documentation | Oracle DBA

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info