Adaptive Cursors and SQL Plan Management

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. See Series TOC 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 as I can. (I also recommend a visit to Tom Kyte's asktom.oracle.com, where you can learn how singularly important it is to use bind variables to improve the performance of SQL statements as well as how to use them in several languages.) 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 you 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: Copy Copied to ClipboardError: Could not Copy select count(1) from customers where state_code = 'CT' and times_purchased > 3; select count(1) from customers where state_code = 'CT' and times_purchased > 3; When you issue this query, Oracle has to perform an activity called parsing, which will generate an execution plan for the SQL statement you just issued. After parsing the query is ready fro execution. Parsing is similar in concept to compilation of code in software; when you write something in C++, say, you 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:> Copy Copied to ClipboardError: Could not Copy select count(1) from customers where state_code = 'NY' and times_purchased > 3; 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: Copy Copied to ClipboardError: Could not Copy select count(1) from customers where state_code = and times_purchased > 3; select count(1) from customers where state_code = and times_purchased > 3; The first query would have passed NY as the value of and the second, CT. The query would not have to be parsed again. In this example, the 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; ST COUNT(1) -- ---------- NY 994901 CT 5099 select state_code, count(1) from customers group by state_code; ST COUNT(1) -- ---------- 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') 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': SQL> c/NY/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') SQL> c/NY/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) 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 you used a value such as 'CT' instead of the bind variable in the query, the optimizer would have picked the correct plan. Thus as you 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 you 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' I I SQL_ID CHILD_NUMBER - - ------------- ------------ Y Y 7cv5271zx2ttg 0 Y N 7cv5271zx2ttg 1 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' I I SQL_ID CHILD_NUMBER - - ------------- ------------ 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. A new view V$SQL_CS_HISTOGRAM shows how many times the SQL statement was executed, organized into three buckets for each child cursor as shown below: select * from v$sql_cs_histogram where sql_id = '7cv5271zx2ttg' / ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT -------- ---------- ------------- ------------ ---------- ---------- 45C8218C 2144429871 7cv5271zx2ttg 5 0 0 45C8218C 2144429871 7cv5271zx2ttg 5 1 2 45C8218C 2144429871 7cv5271zx2ttg 5 2 0 45C8218C 2144429871 7cv5271zx2ttg 4 0 8 ... and so on ... 45C8218C 2144429871 7cv5271zx2ttg 0 2 0 select * from v$sql_cs_histogram where sql_id = '7cv5271zx2ttg' / ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT -------- ---------- ------------- ------------ ---------- ---------- 45C8218C 2144429871 7cv5271zx2ttg 5 0 0 45C8218C 2144429871 7cv5271zx2ttg 5 1 2 45C8218C 2144429871 7cv5271zx2ttg 5 2 0 45C8218C 2144429871 7cv5271zx2ttg 4 0 8 ... and so on ... 45C8218C 2144429871 7cv5271zx2ttg 0 2 0 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' / ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE R LOW HIGH -------- ---------- ------------- ----------- ----------- - -------- ---------- 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 select * from v$sql_cs_selectivity where sql_id = '7cv5271zx2ttg' / ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE R LOW HIGH -------- ---------- ------------- ----------- ----------- - -------- ---------- 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, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets, cpu_time from v$sql_cs_statistics where sql_id = '7cv5271zx2ttg'; CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME ------------ ------------------- - ---------- -------------- ----------- ---------- 1 22981142 Y 1 9592 3219 0 0 22981142 Y 1 9592 3281 0 select child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets, cpu_time from v$sql_cs_statistics where sql_id = '7cv5271zx2ttg'; CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME ------------ ------------------- - ---------- -------------- ----------- ---------- 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 you 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 = 'NY' 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 you 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 you 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: From this page, click SQL Plan Control in the section Query Optimizer, which brings up the main SPM page shown below: Click the SQL Plan Baseline tab, which brings up a screen similar to as shown below: This is the main SQL Plan Baseline screen. At the top left corner, you will see the configuration parameters. It shows Capture SQL Plan Baselines as TRUE, which is what you 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 you 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: If you click the plan name, it will show you the plan details. Here is an output: 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. 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 ------------------------------------------------------------------------------- Inputs: ------- PLAN_LIST = SYS_SQL_PLAN_b5429522ee05ab0e SYS_SQL_PLAN_b5429522e53beeec TIME_LIMIT = DBMS_SPM.AUTO_LIMIT 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 ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- PLAN_LIST = SYS_SQL_PLAN_b5429522ee05ab0e SYS_SQL_PLAN_b5429522e53beeec TIME_LIMIT = DBMS_SPM.AUTO_LIMIT 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: Copy Copied to ClipboardError: Could not Copy declare ctr binary_integer; begin ctr := dbms_spm.alter_sql_plan_baseline ( sql_handle => 'SYS_SQL_e0b19f65b5429522', plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e', attribute_name => 'ACCEPTED', attribute_value => 'NO' ); end; declare ctr binary_integer; begin ctr := dbms_spm.alter_sql_plan_baseline ( sql_handle => 'SYS_SQL_e0b19f65b5429522', plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e', attribute_name => 'ACCEPTED', attribute_value => 'NO' ); end; 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: Copy Copied to ClipboardError: Could not Copy declare ctr binary_integer; begin ctr := dbms_spm.alter_sql_plan_baseline ( sql_handle => 'SYS_SQL_e0b19f65b5429522', plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e', attribute_name => 'ENABLED', attribute_value => 'NO' ); end; declare ctr binary_integer; begin ctr := dbms_spm.alter_sql_plan_baseline ( sql_handle => 'SYS_SQL_e0b19f65b5429522', plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e', attribute_name => 'ENABLED', attribute_value => 'NO' ); end; When a specific SQL statement's plan is fixed by a baseline, the explain plan shows it clearly. At the end of the plan you will see a line that confirms that the plan has been fixed by a baseline. Differences vs. Stored Outlines If you are familiar with Stored Outlines, you must be wondering how SQL Plan Management is different. They seem to be doing the same thing: forcing a specific execution plan for a query. But they have one subtle difference—namely, with the latter the baseline can be evaluated for better plan and activated in place of the original one. Outlines, on the other hand, are fixed and can't be overridden unless you disable them or replace them with a different profile. In addition, plan baselines also have a history and you can know how the plans evolved over a period of time. A related question may be: What happens if you have a stored outline on a query and the baseline finds a better plan? That will be a conflict, won't it? Well, not really. When a query is parsed with an outline, the execution plan forced by the outline will be captured as the SQL Plan Baseline for that query. If the optimizer finds a different execution plan for that statement it will also be captured and stored in the SMB but it will not be an accepted plan. You would have to execute the evolve process to prove the new execution plan is better than the existing SQL Plan Baseline (old stored outline) before it will be used. Differences vs. Stored Profiles Profiles are not "plans" but rather metadata stored as a part of the execution plans, which is based on data. So a query plan would change based on the predicate in case of profiles. With SQL Plan Baselines, however, the plan would be the same regardless of the value in the predicate. Use Cases So what are example scenarios where you can use this feature? The best example is during upgrades or other parameter changes. One of the ways you can baseline a set of queries is using a SQL Tuning Set and loading the statements from the STS to the SPM. Thus you can generate an STS from Oracle Database 10g, export it, import it into Oracle Database 11g, and run the DBMS_SPM.UNPACK_STGTAB_BASELINE package to import the execution plans as SQL Plan Baselines. Later, when the optimizer finds better plans, it will add that to the SMB, allowing you to compare. Conversion of Outlines to SQL Plan Management (Release 2 Only) In versions previous to Oracle Database 11g, when the good ol’ Cost Based Optimizer (CBO) produces bad plans, what do you do? Dig into the problem to unearth the reason, of course. When all else fails, you have a trusted weapon: stored outlines, which force a specific plan for a specific SQL statement. The moment the SQL statement is re-executed, the fixed plan takes, eliminating all risks of a bad plan. These outlines are great because they ensure the plan doesn't change, but they are also bad for the same reason: i.e., they don’t change even when the CBO could have computed a better plan based on the changed execution environment. In 11g, SQL Plan Management baselines solve that problem because they provide plan stability. The CBO is not prevented from computing a new plan; however, the new plan is not used until the DBA confirms it. Now that you see how useful baselines are, you may be tempted to change all your outlines to baselines. Fortunately, in Oracle Database 11g Release 2, you can do that in one step. Before I show you how, let me start with the creation of outlines. Let’s create an outline on SQLs in the SH schema. We have to grant the schema the necessary privilege. SQL> conn / as sysdba SQL> grant create any outline to sh; SQL> conn / as sysdba SQL> grant create any outline to sh; Now connect as SH, check the execution plan for a simple query: SQL> set lines 132 SQL> set autot traceonly explain SQL> set echo on SQL> select sum(amount_sold) 2 from sales 3 where channel_id = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 3519235612 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 489 (2)| 00:00:06 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL| | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | |* 3 | TABLE ACCESS FULL | SALES | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("CHANNEL_ID"=1) The SQL used a full table scan. There is more than one way to create an outline for the index scan. Let me show you one to create an outline named MYOUTLINE1: SQL> alter session set create_stored_outlines=myoutline1 2 / Session altered. SQL> alter session set optimizer_index_cost_adj = 1 2 / Session altered. SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 2232880448 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL | | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CHANNEL_ID"=1) At this time the outline has been created for the index scan. Now let’s test the use of outlines. First let’s see the effect without the outline: SQL> conn sh/sh Connected. SQL> set autot traceonly explain SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 3519235612 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 489 (2)| 00:00:06 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL| | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | |* 3 | TABLE ACCESS FULL | SALES | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("CHANNEL_ID"=1) The query produced full table scan, as expected. Now, let’s see the plan after the outline is in effect: SQL> alter session set use_stored_outlines = MYOUTLINE1 2 / Session altered. SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 2232880448 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL | | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CHANNEL_ID"=1) Note ----- - outline "SYS_OUTLINE_10062716393818901" used for this statement SQL> set lines 132 SQL> set autot traceonly explain SQL> set echo on SQL> select sum(amount_sold) 2 from sales 3 where channel_id = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 3519235612 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 489 (2)| 00:00:06 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL| | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | |* 3 | TABLE ACCESS FULL | SALES | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("CHANNEL_ID"=1) The SQL used a full table scan. There is more than one way to create an outline for the index scan. Let me show you one to create an outline named MYOUTLINE1: SQL> alter session set create_stored_outlines=myoutline1 2 / Session altered. SQL> alter session set optimizer_index_cost_adj = 1 2 / Session altered. SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 2232880448 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL | | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CHANNEL_ID"=1) At this time the outline has been created for the index scan. Now let’s test the use of outlines. First let’s see the effect without the outline: SQL> conn sh/sh Connected. SQL> set autot traceonly explain SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 3519235612 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 489 (2)| 00:00:06 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL| | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | |* 3 | TABLE ACCESS FULL | SALES | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("CHANNEL_ID"=1) The query produced full table scan, as expected. Now, let’s see the plan after the outline is in effect: SQL> alter session set use_stored_outlines = MYOUTLINE1 2 / Session altered. SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 2232880448 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL | | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CHANNEL_ID"=1) Note ----- - outline "SYS_OUTLINE_10062716393818901" used for this statement The last line, under Notes, says clearly that the outline has been used along with the name of the outline. The outline caused the index scan to occur. Now that we know the outline is in place, let’s convert it to a baseline, which is the objective of this section. The process is ridiculously trivial. The package DBMS_SPM now has a new function called MIGRATE_STORED_OUTLINE. It returns a report of the changes performed. SQL> conn / as sysdba Connected. SQL> DECLARE 2 l_rep clob; 3 BEGIN 4 l_rep := DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name => 'all' ); 5 END; 6 / PL/SQL procedure successfully completed. SQL> conn / as sysdba Connected. SQL> DECLARE 2 l_rep clob; 3 BEGIN 4 l_rep := DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name => 'all' ); 5 END; 6 / PL/SQL procedure successfully completed. If you check for the baselines now: SQL> select plan_name, sql_text, fixed from dba_sql_plan_baselines 2 / PLAN_NAME SQL_TEXT FIX ------------------------------ -------------------------------------------------------------------------------- --- ... SYS_OUTLINE_10062716393818901 select sum(AMOUNT_SOLD) NO from sales where CHANNEL_ID = 1 SQL> select plan_name, sql_text, fixed from dba_sql_plan_baselines 2 / PLAN_NAME SQL_TEXT FIX ------------------------------ -------------------------------------------------------------------------------- --- ... SYS_OUTLINE_10062716393818901 select sum(AMOUNT_SOLD) NO from sales where CHANNEL_ID = 1 Checking some other columns in this view (shown below), you may also notice that the baseline has been enabled and accepted so it will be used by the CBO, unless the default value of the parameter use_spm_baselines has been altered from TRUE to FALSE. The ORIGIN column shows how the baseline came into being. Most outlines are created by auto-capture but this one was migrated from outline, so the value of the ORIGIN column shows that clearly. The plan name is the same as the outline name and the MODULE is set to the category of the outline. SQL> l 1 SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE 2* FROM DBA_SQL_PLAN_BASELINES SQL> / SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX ------------------------------ ------------------------------ -------------- --- --- --- MODULE ------------------------------------------------ ... SYS_SQL_ec28978ecd0909c3 SYS_OUTLINE_10062716393818901 STORED-OUTLINE YES YES NO MYOUTLINE1 SQL> l 1 SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE 2* FROM DBA_SQL_PLAN_BASELINES SQL> / SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX ------------------------------ ------------------------------ -------------- --- --- --- MODULE ------------------------------------------------ ... SYS_SQL_ec28978ecd0909c3 SYS_OUTLINE_10062716393818901 STORED-OUTLINE YES YES NO MYOUTLINE1 Now that this baseline is in place, let’s examine its effect on the query. SQL> conn sh/sh Connected. SQL> set autot traceonly explain SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 2232880448 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL | | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CHANNEL_ID"=1) Note ----- - SQL plan baseline "SYS_OUTLINE_10062716393818901" used for this statement SQL> conn sh/sh Connected. SQL> set autot traceonly explain SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 2232880448 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL | | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CHANNEL_ID"=1) Note ----- - SQL plan baseline "SYS_OUTLINE_10062716393818901" used for this statement Note the use of baseline, shown in the last line under “Note”. The execution plan also uses index scan; exactly what we intended. We accomplished our objective with a very trivial set of commands. What about the outline we just migrated from? If you check for the outlines: SQL> SELECT name, category, used FROM user_outlines; NAME CATEGORY USED ------------------------------ ------------------------------ ------ SYS_OUTLINE_05092314510581419 SCOTT_OUTLINES USED SQL> SELECT name, category, used FROM user_outlines; NAME CATEGORY USED ------------------------------ ------------------------------ ------ SYS_OUTLINE_05092314510581419 SCOTT_OUTLINES USED The outline is still there. The migration is not strictly as such, it was more like a copy -- so the old outline is left behind. You may want to drop it, and all such migrated outlines. One simple command does that, calling the new function DROP_MIGRATED_STORED_OUTLINE in the package DBMS_SPM does it. It returns the number of outlines dropped. SQL> declare 2 l_plans number; 3 begin 4 l_plans := dbms_spm.drop_migrated_stored_outline; 5 dbms_output.put_line('Migrated stored outlines dropped: ' || l_plans); 6* end; SQL> / Migrated stored outlines dropped: 9 PL/SQL procedure successfully completed. SQL> declare 2 l_plans number; 3 begin 4 l_plans := dbms_spm.drop_migrated_stored_outline; 5 dbms_output.put_line('Migrated stored outlines dropped: ' || l_plans); 6* end; SQL> / Migrated stored outlines dropped: 9 PL/SQL procedure successfully completed. Recall that the baseline you got earlier by migrating the outline is not fixed. This is desirable because in the future there may be better plans and you don’t want to restrict the CBO to this plan alone. However, if you want precisely that – that this plan and this plan alone should be used by CBO and nothing else – you can “fix” the plan. sql> declare 2 l_plans number; 3 begin 4 l_plans := dbms_spm.alter_sql_plan_baseline( 5 sql_handle=>'SYS_SQL_ec28978ecd0909c3', 6 attribute_name=>'FIXED', 7 attribute_value=>'YES'); 8 dbms_output.put_line('plans altered: ' || l_plans); 9 end; 10 / PL/SQL procedure successfully completed. SQL> select plan_name, sql_text, fixed from dba_sql_plan_baselines 2 / PLAN_NAME SQL_TEXT FIX ------------------------------ -------------------------------------------------------------------------------- --- ... SYS_OUTLINE_10062716393818901 select sum(AMOUNT_SOLD) YES from sales where CHANNEL_ID = 1 9 rows selected. sql> declare 2 l_plans number; 3 begin 4 l_plans := dbms_spm.alter_sql_plan_baseline( 5 sql_handle=>'SYS_SQL_ec28978ecd0909c3', 6 attribute_name=>'FIXED', 7 attribute_value=>'YES'); 8 dbms_output.put_line('plans altered: ' || l_plans); 9 end; 10 / PL/SQL procedure successfully completed. SQL> select plan_name, sql_text, fixed from dba_sql_plan_baselines 2 / PLAN_NAME SQL_TEXT FIX ------------------------------ -------------------------------------------------------------------------------- --- ... SYS_OUTLINE_10062716393818901 select sum(AMOUNT_SOLD) YES from sales where CHANNEL_ID = 1 9 rows selected. Outlines gave you the ability to avoid unpleasant surprises as a result of the changes in plan. Baselines gave you that ability and potential to compute new, better plans. Instead of reinventing the wheel, you can extend the life of your investment, i.e. outlines by converting them to baselines. Conclusion Adaptive Cursors and SQL Plan Management are just two examples of how the database is now very intelligent about the kinds of requests it gets, and how to react to them. Both allow you to get the best of both worlds—with Adaptive Cursors, you can use 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. Back Series TOC