tag:blogger.com,1999:blog-30865588683526228842024-03-04T20:53:20.454-08:00Inside the Oracle Optimizer - Removing the black magicOptimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.comBlogger27125tag:blogger.com,1999:blog-3086558868352622884.post-37510074029638956442010-07-31T10:57:00.000-07:002011-09-21T16:41:17.683-07:00We have moved!You might have been wondering why things had gone so quiet on the Optimizer development team's blog <a href="http://www.optimizermagic.blogspot.com/">Optimizer Magic</a> over the last few months. Well the blog has moved to <a href="http://blogs.oracle.com/optimizer">blogs.oracle.com/optimizer</a>. All of the old articles have moved too and we plan to be a lot more active at our new home, with at least one new post every month. <br /><br /><br /><a href="https://plus.google.com/115225673827938473361" rel="author">Maria Colgan</a>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-80446332779836979672010-03-12T17:00:00.000-08:002011-08-01T19:32:58.163-07:00Explain adaptive cursor sharing behavior with cursor_sharing = similar and force.This post has moved to <br /><a href="http://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force">http://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force</a>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-31202990920251635512009-12-21T15:26:00.000-08:002011-09-21T16:45:05.991-07:00Upgrading from 9i to 11g and the implicit migration from RBOThis blog post has moved to<br /><a href="http://blogs.oracle.com/optimizer/entry/upgrading_from_9i_to_11g_and_the_implicit_migration_from_rbo">http://blogs.oracle.com/optimizer/entry/upgrading_from_9i_to_11g_and_the_implicit_migration_from_rbo</a><br /><br /><a href="https://plus.google.com/115225673827938473361" rel="author">Maria Colgan</a>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-1187045707711311152009-11-17T15:05:00.000-08:002011-09-21T16:45:31.955-07:00What should I do with old hints in my workload?This blog post has moved to<br /><a href="http://blogs.oracle.com/optimizer/entry/what_should_i_do_with_old_hints_in_my_workload">http://blogs.oracle.com/optimizer/entry/what_should_i_do_with_old_hints_in_my_workload</a><br /><br /><a href="https://plus.google.com/115225673827938473361" rel="author">Maria Colgan</a>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-64981917385809621522009-10-28T11:09:00.000-07:002011-09-21T16:45:57.889-07:00Oracle Open World 2009 SummaryWe had a great time talking to our users at Open World 2009 both at our Demogrounds booth and at our two sessions. We received a lot of interesting questions during the Optimizer Roundtable discussion, but we did not get to answer all of them due to time constraints. We plan to address the questions we received (both answered and unanswered) in future blog posts... so stay tuned. If you didn't get to attend the discussion, but have a question about the Optimizer, submit it through the email link above.<br /><br />For those of you who did not get a chance to stop by our Demogrounds booth, here's a recap of the new features that we talked about. Many of the topics have already been covered in earlier blog posts.<br /><ul><li>Enhanced bind peeking with Adaptive Cursor Sharing: Read about <a href="http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html">Adaptive Cursor Sharing</a> and a <a href="http://optimizermagic.blogspot.com/2009/04/update-on-adaptive-cursor-sharing.html">followup</a>.</li><br /><li>Faster and more accurate Statistics gathering: Read about our <a href="http://optimizermagic.blogspot.com/2008/01/improvement-of-auto-sampling-statistics.html">new statistics gathering algorithm in 11g</a> and <a href="http://optimizermagic.blogspot.com/2009/02/maintaining-statistics-on-large.html">incremental maintenance of global statistics for partitioned tables</a>.</li><br /><li>New types of Statistics that improve cardinality estimates</li><br /><li>Ability to gather but not publish Statistics </li><br /><li>Guaranteed plan stability and controlled plan evolution: Read about <a href="http://optimizermagic.blogspot.com/2009/01/plan-regressions-got-you-down-sql-plan.html">SQL Plan Management</a>.</li><br /><li>New tools to help correct regressed execution plans: Read about our <a href="http://optimizermagic.blogspot.com/2009/09/whats-changed-between-my-new-query-plan.html">Plan Diff</a> tool and <a href="http://optimizermagic.blogspot.com/2008/03/oracle-support-keeps-closing-my-tar.html">Test Case Builder</a>.</li><br /></ul>These topics are focused on well-known pain points from earlier versions of Oracle. But we also have plenty of new optimizations in Oracle 11gR1 and 11gR2. Stay tuned for details about some of our new optimizations.<br /><br /><a href="https://plus.google.com/115225673827938473361" rel="author">Maria Colgan</a>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-5922893557777214792009-10-09T12:32:00.000-07:002011-09-21T16:46:45.471-07:00Open World Recap and New White papersThe Optimizer group has two session and a demo station in the Database campground at this year's <a href="http://www.oracle.com/us/openworld/registration.htm?src=6773924&Act=51">Oracle Open World</a>. We will give a technical presentation on <a href="http://www35.cplan.com/cc221_new/session_details.jsp?isid=311421&ilocation_id=221-1&ilanguage=english">What to Expect from the Oracle Optimizer When Upgrading to Oracle Database 11g</a> and host an <a href="http://www35.cplan.com/cc221_new/session_details.jsp?isid=311728&ilocation_id=221-1&ilanguage=english">Oracle Optimizer Roundtable</a>.<br /><br />The technical session, which is on Tuesday Oct 13 at 2:30 pm, gives step by step instructions on how to use the new 11g features to ensure your upgrade goes smoothly and without any SQL plan regressions. This session is based on our latest white papers, <a href="http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf">Upgrading from Oracle Database 10g to 11g: What to expect from the Optimizer</a> and <a href="http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-upgrading-10g-to-11g-what-to-ex-133707.pdf">SQL Plan Management in Oracle Database 11g</a>. <br /><br />The roundtable, which is on Thursday Oct. 15th at 10:30 am, will give you a first hand opportunity to pose you burning Optimizer and statistics questions directly to a panel of our leading Optimizer developers. In fact if you plan to attend the roundtable and already know what questions you would like to ask, then please send them to us via <a href="mailto:optimizer_group@yahoo.com">email</a> and we will be sure to include them. Other wise, you can hand in your questions at our demo station at any stage during the week, or as you enter the actual session. Just be sure to write your questions in clear block capitals!<br /><br />We look forward to see you all at Open world.<br /><br /><a href="https://plus.google.com/115225673827938473361" rel="author">Maria Colgan</a>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-91889288689573133392009-09-01T13:21:00.000-07:002011-08-01T21:30:41.155-07:00What's Changed between my New Query Plan and the Old One?This blog post has moved here<br /><a href="http://blogs.oracle.com/optimizer/entry/whats_changed_between_my_new_query_plan_and_the_old_one">http://blogs.oracle.com/optimizer/entry/whats_changed_between_my_new_query_plan_and_the_old_one</a>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com1tag:blogger.com,1999:blog-3086558868352622884.post-66372400211716103112009-08-11T19:25:00.000-07:002009-11-17T15:11:30.096-08:00Understanding DBMS_STATS.SET_*_PREFS proceduresIn previous Database releases you had to use the <span style="font-family:courier new;">DBMS_STATS.SET_PARM</span> procedure to change the default value for the parameters used by the <span style="font-family:courier new;">DBMS_STATS.GATHER_*_STATS</span> procedures. The scope of any changes that were made was all subsequent operations. In Oracle Database 11g, the <span style="font-family:courier new;">DBMS_STATS.SET_PARM</span> procedure has been deprecated and it has been replaced with a set of procedures that allow you to set a preference for each parameter at a table, schema, database, and Global level. These new procedures are called <span style="font-family:courier new;">DBMS_STATS.SET_*_PREFS</span> and offer a much finer granularity of control.<br /><br />However there has been some confusion around which procedure you should use when and what the hierarchy is among these procedures. In this post we hope to clear up the confusion. Lets start by looking at the list of parameters you can change using the <span style="font-family:courier new;">DBMS_STAT.SET_*_PREFS</span> procedures.<br /><br /><ul><li>AUTOSTATS_TARGET (SET_GLOBAL_PREFS only)</li><li>CASCADE</li><li>DEGREE</li><li>ESTIMATE_PERCENT</li><li>METHOD_OPT</li><li>NO_INVALIDATE</li><li>GRANULARITY</li><li>PUBLISH</li><li>INCREMENTAL</li><li>STALE_PERCENT</li></ul><br />As mentioned above there are four <span style="font-family:courier new;">DBMS_STATS.SET_*_PREFS</span> procedures.<br /><br /><ol><li style="font-family: courier new;">SET_TABLE_PREFS</li><br /><li style="font-family: courier new;">SET_SCHEMA_PREFS</li><br /><li style="font-family: courier new;">SET_DATABASE_PREFS</li><br /><li style="font-family:courier new;">SET_GLOBAL_PREFS</li><br /></ol><br />The <span style="font-family:courier new;">DBMS_STATS.SET_TABLE_PREFS</span> procedure allows you to change the default values of the parameters used by the <span style="font-family:courier new;">DBMS_STATS.GATHER_*_STATS</span> procedures for the specified table only.<br /><br />The <span style="font-family:courier new;">DBMS_STATS.SET_SCHEMA_PREFS</span> procedure allows you to change the default values of the parameters used by the <span style="font-family:courier new;">DBMS_STATS.GATHER_*_STATS</span> procedures for all of the existing objects in the specified schema. This procedure actually calls <span style="font-family:courier new;">DBMS_STATS.SET_TABLE_PREFS</span> for each of the tables in the specified schema. Since it uses <span style="font-family:courier new;">DBMS_STATS.SET_TABLE_PREFS</span> calling this procedure will not affect any new objects created after it has been run. New objects will pick up the <span style="font-family:courier new;">GLOBAL_PREF</span> values for all parameters.<br /><br />The <span style="font-family:courier new;">DBMS_STATS.SET_DATABASE_PREFS</span> procedure allows you to change the default values of the parameters used by the <span style="font-family:courier new;">DBMS_STATS.GATHER_*_STATS</span> procedures for all of the user defined schemas in the database. This procedure actually calls <span style="font-family:courier new;">DBMS_STATS.SET_TABLE_PREFS</span> for each of the tables in each of the user defined schemas. Since it uses <span style="font-family:courier new;">DBMS_STATS.SET_TABLE_PREFS</span> this procedure will not affect any new objects created after it has been run. New objects will pick up the <span style="font-family:courier new;">GLOBAL_PREF</span> values for all parameters. It is also possible to include the Oracle owned schemas (sys, system, etc) by setting the <span style="font-family:courier new;">ADD_SYS</span> parameter to <span style="font-family:courier new;">TRUE</span>.<br /><br />The <span style="font-family:courier new;">DBMS_STATS.SET_GLOBAL_PREFS</span> procedure allows you to change the default values of the parameters used by the <span style="font-family:courier new;">DBMS_STATS.GATHER_*_STATS</span> procedures for any object in the database that does not have an existing table preference. All parameters default to the global setting unless there is a table preference set or the parameter is explicitly set in the <span style="font-family:courier new;">DBMS_STATS.GATHER_*_STATS</span> command. Changes made by this procedure <span style="font-weight: bold;">will</span> affect any new objects created after it has been run as new objects will pick up the <span style="font-family:courier new;">GLOBAL_PREF</span> values for all parameters.<br /><br />With <span style="font-family:courier new;">GLOBAL_PREFS</span> it is also possible to set a default value for one additional parameter, called <span style="font-family:courier new;">AUTOSTAT_TARGET</span>. This additional parameter controls what objects the automatic statistic gathering job (that runs in the nightly maintenance window) will look after. The possible values for this parameter are <span style="font-family:courier new;">ALL</span>,<span style="font-family:courier new;">ORACLE</span>, and <span style="font-family:courier new;">AUTO</span>. <span style="font-family:courier new;">ALL</span> means the automatic statistics gathering job will gather statistics on all objects in the database. <span style="font-family:courier new;">ORACLE</span> means that the automatic statistics gathering job will only gather statistics for Oracle owned schemas (sys, sytem, etc) Finally <span style="font-family:courier new;">AUTO</span> (the default) means Oracle will decide what objects to gather statistics on. Currently <span style="font-family:courier new;">AUTO</span> and <span style="font-family:courier new;">ALL</span> behave the same.<br /><br />In summary, <span style="font-family:courier new;">DBMS_STATS</span> obeys the following hierarchy for parameter values, parameters values set in the <span style="font-family:courier new;">DBMS_STAT.GATHER*_STATS</span> command over rules everything. If the parameter has not been set in the command we check for a table level preference. If there is no table preference set we use the global preference.Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-15225382145925559882009-07-23T18:18:00.001-07:002011-09-21T16:46:21.638-07:00Will the Optimizer Team be at Oracle Open World 2009?With only two and a half months to go until <a href="http://www.oracle.com/us/openworld/registration.htm?src=6773924&Act=107">Oracle Open World</a> in San Francisco, October 11-15th, we have gotten several requests asking if we plan to present any session at the conference.<br /><br />We have two session and a demo station in the Database campground at this year's show. We will give a technical presentation on <a href="http://www35.cplan.com/cc221_new/session_details.jsp?isid=311421&ilocation_id=221-1&ilanguage=english">What to Expect from the Oracle Optimizer When Upgrading to Oracle Database 11g</a> and the <a href="http://www35.cplan.com/cc221_new/session_details.jsp?isid=311728&ilocation_id=221-1&ilanguage=english">Oracle Optimizer Roundtable</a>.<br /><br />The technical session, which is on Tuesday Oct 13 at 2:30 pm, gives step by step instructions and detailed examples of how to use the new 11g features to ensure your upgrade goes smoothly and without any SQL plan regressions.<br /><br />The roundtable, which is on Thursday Oct. 15th at 10:30 am, will give you a first hand opportunity to pose you burning Optimizer and statistics questions directly to a panel of our leading Optimizer developers. In fact if you plan to attend the roundtable and already know what questions you would like to ask, then please send them to us via <a href="mailto:optimizer_group@yahoo.com">email</a> and we will be sure to include them. Other wise, you can hand in your questions at our demo station at any stage during the week, or as you enter the actual session. Just be sure to write your questions in clear block capitals!<br /><br />We look forward to seeing you all at Oracle Open World.<br /><br /><a href="https://plus.google.com/115225673827938473361" rel="author">Maria Colgan</a>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-32166192609073100002009-05-26T10:04:00.000-07:002009-11-17T15:12:39.011-08:00Why do I have hundreds of child cursors when cursor_sharing set to similar in 10g<p style="line-height: 150%;">Recently we received several questions regarding a usual situation where a SQL Statement has hundreds of child cursors. This is in fact the expected behavior when<br /></p><ol><li> CURSOR_SHARING is set to similar</li><br /><li> Bind peeking is in use</li><br /><li> And a histogram is present on the column used in the where clause predicate of query</li></ol><br /><p style="line-height: 150%;">You must now be wondering why this is the expected behavior. In order to explain, let's step back and begin by explaining what CURSOR_SHARING actually does. CURSOR_SHARING was introduced to help relieve pressure put on the shared pool, specifically the cursor cache, from applications that use literal values rather than bind variables in their SQL statements. It achieves this by replacing the literal values with system generated bind variables thus reducing the number of (parent) cursors in the cursor cache. However, there is also a caveat or additional requirement on CURSOR_SHARING, which is that the use of system generated bind should not negatively affect the performance of the application. CURSOR_SHARING has three possible values: EXACT, SIMILAR, and FORCE. The table below explains the impact of each setting with regards to the space used in the cursor cache and the query performance.<br /><br /><style type="text/css">.nobrtable br { display: none }</style><br /></p><div class="nobrtable"><br /><table border="1"><br /><tbody><tr><th>CURSOR_SHARING VALUE</th><th>SPACE USED IN SHARED POOL</th><th>QUERY PERFORMANCE</th> </tr><br /><tr><td>EXACT (No literal replacement)</td><td>Worst possible case - each stmt issued has its own parent cursor</td><td>Best possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt</td> </tr><br /><tr><td>FORCE</td><td>Best possible case as only one parent and child cursor for each distinct stmt</td><td>Potentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan</td> </tr><br /><tr><td>SIMILAR without histogram present</td><td>Best possible case as only one parent and child cursor for each distinct stmt</td><td>Potentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan</td></tr><br /><tr><td>SIMILAR with histogram present</td><td>Not quite as much space used as with EXACT but close. Instead of each stmt having its own parent cursor they will have their own child cursor (which uses less space)</td><td>Best possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt<br /></td></tr></tbody><br /></table><br /></div><br /><br /><p style="line-height: 150%;">In this case the statement with hundreds of children falls into the last category in the above table, having CURSOR_SHARING set to SIMILAR and a histogram on the columns used in the where clause predicate of the statement. The presence of the histogram tells the optimizer that there is a data skew in that column. The data skew means that there could potentially be multiple execution plans for this statement depending on the literal value used. In order to ensure we don't impact the performance of the application, we will peek at the bind variable values and create a new child cursor for each distinct value. Thus ensuring each bind variable value will get the most optimal execution plan. It's probably easier to understand this issue by looking at an example. Let's assume there is an <span style="font-family:courier new;">employees</span> table with a histogram on the <span style="font-family:courier new;">job</span> column and CURSOR_SHARING has been set to similar. The following query is issued<br /><br /><span style="font-family:courier new;">select * from employees where job = 'Clerk';</span><br /><br />The literal value 'Clerk' will be replaced by a system generated bind variable <span style="font-family:courier new;">B1</span> and a parent cursor will be created as<br /><br /><span style="font-family:courier new;">select * from employees where job = :B1;</span><br /><br /></p><p style="line-height: 150%;">The optimizer will peek the bind variable <span style="font-family:arial;">B1</span> and use the literal value 'Clerk' to determine the execution plan. 'Clerk' is a popular value in the <span style="font-family:courier new;">job</span> column and so a full table scan plan is selected and child cursor C1 is created for this plan. The next time the query is executed the where clause predicate is <span style="font-family:courier new;">job='VP'</span> so <span style="font-family:courier new;">B1</span> will be set to 'VP', this is not a very popular value in the <span style="font-family:courier new;">job</span> column so an index range scan is selected and child cursor C2 is created. The third time the query is executed the where clause predicate is <span style="font-family:courier new;">job ='Engineer'</span> so the value for <span style="font-family:courier new;">B1</span> is set to 'Engineer'. Again this is a popular value in the <span style="font-family:courier new;">job</span> column and so a full table scan plan is selected and a new child cursor C3 is created. And so on until we have seen all of the distinct values for <span style="font-family:courier new;">job</span> column. If <span style="font-family:courier new;">B1</span> is set to a previously seen value, say 'Clerk', then we would reuse child cursor C1.<br /><style type="text/css">.nobrtable br { display: none }</style><br /></p><div class="nobrtable"><br /><table border="1"><br /><tbody><tr><th>Value for B1</th><th>Plan Used</th><th>Cursor Number</th></tr><br /><tr><td>Clerk</td><td>Full Table Scan</td><td>C1</td></tr><br /><tr><td>VP</td><td>Index Range Scan</td><td>C2</td></tr><br /><tr><td>Engineer</td><td>Full Table Scan</td><td>C3</td></tr></tbody><br /></table><br /></div><br /><br /><p style="line-height: 150%;">As each of these cursors is actually a child cursor and not a new parent cursor you will still be better off than with CURSOR_SHARING set to EXACT as a child cursor takes up less space in the cursor cache. A child cursor doesn't contain all of the information stored in a parent cursor, for example, the SQL text is only stored in the parent cursor and not in each child.<br /><br /></p><p style="line-height: 150%;">Now that you know the explanation for all of the child cursors you are seeing you need to decide if it is a problem for you and if so which aspect affects you most, space used in the SHARED_POOL or query performance. If your goal is to guarantee the application performance is not affected by setting CURSOR_SHARING to SIMILAR then keep the system settings unchanged. If your goal is to reduce the space used in the shared pool then you can use one of the following solutions with different scopes:<br /></p><ol><li>Individual SQL statements - drop the histograms on the columns for each of the affected SQL statements</li><br /><li>System-wide - set CURSOR_SHARING to FORCE this will ensure only one child cursor per SQL statement</li></ol><br /><br /><p style="line-height: 150%;">Both of these solutions require testing to ensure you get the desired effect on your system. Oracle Database 11g provides a much better solution using the <a href="http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html">Adaptive Cursor Sharing</a> feature. In Oracle Database 11g, all you need to do is set CURSOR_SHARING to FORCE and keep the histograms. With Adaptive Cursor Sharing, the optimizer will create a cursor only when its plan is different from any of the plans used by other child cursors. So in the above example, you will get two child cursors (C1 and C2) instead of 3.</p>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-16302161924812591882009-04-12T18:24:00.001-07:002011-08-01T19:42:29.525-07:00Update on Adaptive Cursor SharingThis blog post has moved to<br /><a href="http://blogs.oracle.com/optimizer/entry/update_on_adaptive_cursor_sharing">http://blogs.oracle.com/optimizer/entry/update_on_adaptive_cursor_sharing</a>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-77547128790306726752009-02-11T14:07:00.000-08:002011-09-21T16:47:03.287-07:00Maintaining statistics on large partitioned tables<span>We have gotten a lot of questions recently regarding how to gather and maintain optimizer statistics on large partitioned tables. The majority of these questions can be summarized into two topics:<br /><ol><br /><li>When queries access a single partition with stale or non-existent partition level statistics I get a sub optimal plan due to “Out of Range” values</li><br /><li>Global statistics collection is extremely expensive in terms of time and system resources</li><br /></ol><p>This article will describe both of these issues and explain how you can address them both in Oracle Database 10gR2 and 11gR1.</p><p><br /><b>Out of Range</b><br />Large tables are often decomposed into smaller pieces called partitions in order to improve query performance and ease of data management. The Oracle query optimizer relies on both the statistics of the entire table (global statistics) and the statistics of the individual partitions (partition statistics) to select a good execution plan for a SQL statement. If the query needs to access only a single partition, the optimizer uses only the statistics of the accessed partition. If the query access more than one partition, it uses a combination of global and partition statistics.<br /><br />“Out of Range” means that the value supplied in a where clause predicate is outside the domain of values represented by the [minimum, maximum] column statistics. The optimizer prorates the selectivity based on the distance between the predicate value and the maximum value (assuming the value is higher than the max), that is, the farther the value is from the maximum value, the lower the selectivity will be. This situation occurs most frequently in tables that are range partitioned by a date column, a new partition is added, and then queried while rows are still being loaded in the new partition. The partition statistics will be stale very quickly due to the continuous trickle feed load even if the statistics get refreshed periodically. The maximum value known to the optimizer is not correct leading to the “Out of Range” condition. The under-estimation of selectivity often leads the query optimizer to pick a sub optimal plan. For example, the query optimizer would pick an index access path while a full scan is a better choice.<br /><br />The "Out of Range" condition can be prevented by using the new copy table statistics procedure available in Oracle Database10.2.0.4 and 11g. This procedure copies the statistics of the source [sub] partition to the destination [sub] partition. It also copies the statistics of the dependent objects: columns, local (partitioned) indexes etc. It adjusts the minimum and maximum values of the partitioning column as follows; it uses the high bound partitioning value as the maximum value of the first partitioning column (it is possible to have concatenated partition columns) and high bound partitioning value of the previous partition as the minimum value of the first partitioning column for range partitioned table. It can optionally scale some of the other statistics like the number of blocks, number of rows etc. of the destination partition.<br /><br />Assume we have a table called SALES that is ranged partitioned by quarter on the SALES_DATE column. At the end of every day data is loaded into latest partition. However, statistics are only gathered at the end of every quarter when the partition is fully loaded. Assuming global and partition level statistics (for all fully loaded partitions) are up to date, use the following steps in order to prevent getting a sub-optimal plan due to “out of range”.<br /></p><ol><br /><li>Lock the table statistics using LOCK_TABLE_STATS procedure in DBMS_STATS. This is to avoid interference from auto statistics job.<br /><br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">EXEC DBMS_STATS.LOCK_TABLE_STATS('SH','SALES');</span></span><br /></li><br /><br /><li>Before beginning the initial load into each new partition (say SALES_Q4_2000) copy the statistics from the previous partition (say SALES_Q3_2000) using COPY_TABLE_STATS. You need to specify FORCE=>TRUE to override the statistics lock.<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">EXEC DBMS_STATS.COPY_TABLE_STATS ('SH', 'SALES', 'SALES_Q3_2000', 'SALES_Q4_2000', FORCE=>TRUE);</span></span><br /></li></ol><br /><br /><b>Expensive global statistics collection</b><br /></span><p></p><p style="font-weight: bold;"><span style="font-weight: normal;">In data warehouse environment it is very common to do a bulk load directly into one or more empty partitions. This will make the partition statistics stale and may also make the global statistics stale. Re-gathering statistics for the effected partitions and for the entire table can be very time consuming. Traditionally, statistics collection is done in a two-pass approach:<br /><ul><br /><li>In the first pass we will scan the table to gather the global statistics</li><br /><li>In the second pass we will scan the partitions that have been changed to gather their partition level statistics.</li><br /></ul>The full scan of the table for global statistics collection can be very expensive depending on the size of the table. Note that the scan of the entire table is done even if we change a small subset of partitions.<br /></span></p><p></p><p style="font-weight: bold;"><span style="font-weight: normal;">In Oracle Database 11g, we avoid scanning the whole table when computing global statistics by deriving the global statistics from the partition statistics. Some of the statistics can be derived easily and accurately from partition statistics. For example, number of rows at global level is the sum of number of rows of partitions. Even global histogram can be derived from partition histograms. But the number of distinct values (NDV) of a column cannot be derived from partition level NDVs. So, Oracle maintains another structure called a synopsis for each column at the partition level. A synopsis can be considered as sample of distinct values. The NDV can be accurately derived from synopses. We can also merge multiple synopses into one. The global NDV is derived from the synopsis generated by merging all of the partition level synopses. To summarize<br /><ol><br /><li>Gather statistics and create synopses for the changed partitions only</li><li>Oracle automatically merges partition level synopses into a global synopsis</li><li>The global statistics are automatically derived from the partition level statistics and global synopses</li><br /></ol></span><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlv7IxhjndiD8SZOfGVY2wCUYvEVHI13fWKeYfsf2RQSeZ1870pIbalMZsSIJ16JDkQsU_MXiyTjbXrObFCao6GZwFAKIvD1YC5GXYVMxUDW-ZN3DQ3GL8NtWTjvWNkohdhiA2IAGQhQ0/s1600-h/incremental_stats_gathering.bmp"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 298px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlv7IxhjndiD8SZOfGVY2wCUYvEVHI13fWKeYfsf2RQSeZ1870pIbalMZsSIJ16JDkQsU_MXiyTjbXrObFCao6GZwFAKIvD1YC5GXYVMxUDW-ZN3DQ3GL8NtWTjvWNkohdhiA2IAGQhQ0/s400/incremental_stats_gathering.bmp" alt="" id="BLOGGER_PHOTO_ID_5286033685571438770" border="0" /></a><span style="font-weight: normal;"><br /></span></p><p style="font-weight: bold;"><span style="font-weight: normal;">Incremental maintenance feature is disabled by default. It can be enabled by changing the INCREMENTAL table preference to true. </span><span style="font-weight: normal;">It can also be enabled for a particular schema or at the database level. If you are interested in more details of the incremental maintenance feature, please refer to the following paper presented in <a href="http://portal.acm.org/citation.cfm?id=1376616.1376721&coll=ACM&dl=ACM&CFID=21820301&CFTOKEN=48169365">SIGMOD 2008</a></span><span style="font-weight: normal;"> and to our previous blog entry on <a href="http://optimizermagic.blogspot.com/2008/01/improvement-of-auto-sampling-statistics.html">new ndv gathering in 11g</a>.<br /></span></p><br />Assume we have table called SALES that is range partitioned by day on the SALES_DATE column. At the end of every day data is loaded into latest partition and partition statistics are gathered. Global statistics are only gathered at the end of every month because gathering them is very time and resource intensive. Use the following steps in order to maintain global statistics after every load.<br /><ol><br /><li>Turn on incremental feature for the table.<br /><br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE');</span></span><br /></li><br /><li>At the end of every load gather table statistics using GATHER_TABLE_STATS command. You don’t need to specify the partition name. Also, do not specify the granularity parameter. The command will collect statistics for partitions with stale or missing statistics and update the global statistics based on the partition level statistics and synopsis.<br /><br /><br /><span style="font-size:85%;"><span style="font-family:courier new;"> EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES');</span></span><br /></li><br /></ol><br />Note: that the incremental maintenance feature was introduced in Oracle Database 11g Release 1. However, we also provide a solution in Oracle Database10g Release 2 (10.2.0.4) that simulates the same behavior. The 10g solution is a new value, 'APPROX_GLOBAL AND PARTITION' for the GRANULARITY parameter of the GATHER_TABLE_STATS procedures. It behaves the same as the incremental maintenance feature except that we don’t update the NDV for non-partitioning columns and number of distinct keys of the index at the global level. For partitioned column we update the NDV as the sum of NDV at the partition levels. Also we set the NDV of columns of unique indexes as the number of rows of the table. In general, non-partitioning column NDV at the global level becomes stale less often. It may be possible to collect global statistics less frequently then the default (when table changes 10%) since approx_global option maintains most of the global statistics accurately.<br /><br />Let's take a look at an example to see how you would effectively use the Oracle Database 10g approach.<br /><br />After the data load is complete, gather statistics using DBMS_STATS.GATHER_TABLE_STATS for the last partition (say SALES_11FEB2009), specify granularity => 'APPROX_GLOBAL AND PARTITION'. It will collect statistics for the specified partition and derive global statistics from partition statistics (except for NDV as described before).<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">EXEC DBMS_STATS.GATHER_TABLE_STATS ('SH', 'SALES', 'SALES_11FEB2009', GRANULARITY => 'APPROX_GLOBAL AND PARTITION');</span></span><br /><br />It is necessary to install the one off patch for bug 8719831 if you are using copy_table_stats procedure or APPROX_GLOBAL option in 10.2.0.4 (patch 8877245) or in 11.1.0.7 (patch 8877251).<br /><br /><a href="https://plus.google.com/115225673827938473361" rel="author">Maria Colgan</a>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com5tag:blogger.com,1999:blog-3086558868352622884.post-14005337593754021652009-02-02T17:32:00.000-08:002009-11-17T15:13:03.994-08:00SQL Plan Management (Part 4 of 4): User Interfaces and Other FeaturesIn the first three parts of this article, we have seen how SQL plan baselines are created, used and evolved. In this final installment, we will show some user interfaces, describe the interaction of SPM with other features and answer some of your questions.<br /><br /><span style="FONT-WEIGHT: bold;">DBMS_SPM package</span><br /><br />A new package, <span style="font-family:courier new;">DBMS_SPM</span>, allows you to manage plan histories. We have already seen in previous examples how you can use it to create and evolve SQL plan baselines. Other management functions include changing attributes (like enabled status and plan name) of plans or dropping plans. You need the <span style="font-family:courier new;">ADMINISTER SQL MANAGEMENT OBJECT</span> privilege to execute this package.<br /><br /><span style="FONT-WEIGHT: bold;">Viewing the plan history</span><br /><br />Regardless of how a plan history is created, you can view details about the various plans in the view <span style="font-family:courier new;">DBA_SQL_PLAN_BASELINES</span>. At the end of Part 3 of this blog, we saw that the SQL statement had two accepted plans:<br /><br /><span style="font-family:courier new;font-size:80%;">SQL> select sql_text, sql_handle, plan_name, enabled, accepted<br />  2  from dba_sql_plan_baselines;</p></span><span style="font-family:courier new;font-size:80%;"><br /><br />SQL_TEXT                 SQL_HANDLE               PLAN_NAME                     ENA ACC<br />------------------------ ------------------------ ----------------------------- --- ---<br />select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b08cbcb825 YES YES<br />ount_sold, t.calendar_ye<br />ar<br />from sales s, products p<br />, times t<br />where s.prod_id = p.prod<br />_id<br />  and s.time_id = t.time<br />_id<br />  and p.prod_id < :pid<br /><br />select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES<br />ount_sold, t.calendar_ye<br />ar<br />from sales s, products p<br />, times t<br />where s.prod_id = p.prod<br />_id<br />  and s.time_id = t.time<br />_id<br />  and p.prod_id < :pid </span><p></span><br /><br />The SQL handle is a unique identifier for each SQL statement that you can use when managing your plan history using the <span style="font-family:courier new;">DBMS_SPM</span> package.<br /><br /><span style="FONT-WEIGHT: bold;">Creating an accepted plan by modifying the SQL text</span><br /><br />Some of you may be manually tuning SQL statements by adding hints or otherwise modifying the SQL text. If you enable automatic capture of SQL plans and then execute this statement, you will be creating a SQL plan baseline for this <b>modified</b> statement. What you most likely want, however, is to add this plan to the plan history of the <b>original</b> SQL statement. Here's how you can do this using the above SQL statement as an example.<br /><br />Let's modify the SQL statement, execute it and look at the plan:<br /><br /><span style="font-family:courier new;font-size:90%;"><br />SQL> var pid number<br />SQL> exec :pid := 100;<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select <b>/*+ leading(t) */</b> p.prod_name, s.amount_sold, t.calendar_year<br />2    from sales s, products p, times t<br />3    where s.prod_id = p.prod_id<br />4      and s.time_id = t.time_id<br />5      and p.prod_id < :pid;<br /><br />PROD_NAME AMOUNT_SOLD CALENDAR_YEAR<br />--------- ----------- -------------<br />...<br />9 rows selected.<br /><br />SQL> select * from table(dbms_xplan.display_cursor('b17wnz4y8bqv1', 0, 'basic note'));<br /><br />PLAN_TABLE_OUTPUT<br />-------------------------------------------------------------------------------<br />EXPLAINED SQL STATEMENT:<br />------------------------<br />select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year<br />from sales s, products p, times t where s.prod_id = p.prod_id   and<br />s.time_id = t.time_id   and p.prod_id < :pid<br /><br />Plan hash value: 2290436051<br /><br />---------------------------------------------------------------<br />| Id  | Operation                            | Name           |<br />---------------------------------------------------------------<br />|   0 | SELECT STATEMENT                     |                |<br />|   1 |  HASH JOIN                           |                |<br />|   2 |   HASH JOIN                          |                |<br />|   3 |    TABLE ACCESS FULL                 | TIMES          |<br />|   4 |    PARTITION RANGE ALL               |                |<br />|   5 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |<br />|   6 |      BITMAP CONVERSION TO ROWIDS     |                |<br />|   7 |       BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |<br />|   8 |   TABLE ACCESS BY INDEX ROWID        | PRODUCTS       |<br />|   9 |    INDEX RANGE SCAN                  | PRODUCTS_PK    |<br />---------------------------------------------------------------<br /><br /><br />23 rows selected.</span><br /><br />We can now create a new accepted plan for the original SQL statement by associating the modified statement's plan to the original statement's sql handle (obtained from <span style="font-family:courier new;">DBA_SQL_PLAN_BASELINES</span>):<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> var pls number<br />SQL> exec :pls := dbms_spm.load_plans_from_cursor_cache( -<br />>                    sql_id => 'b17wnz4y8bqv1', -<br />>                    plan_hash_value => 2290436051, -<br />>                    sql_handle => 'SYS_SQL_4bf04d85fcc170b0');</span><br /><br />If the original SQL statement does not already have a plan history (and thus no SQL handle), another version of <span style="font-family:courier new;">load_plans_from_cursor_cache</span> allows you to specify the original statement's text.<br /><br />To confirm that we now have three accepted plans for our SQL statement, let's check in <span style="font-family:courier new;">DBA_SQL_PLAN_BASELINES</span>:<br /><br /><span style="font-family:courier new;font-size:80%;">SQL> select sql_text, sql_handle, plan_name, enabled, accepted<br />  2  from dba_sql_plan_baselines;</p></span><span style="font-family:courier new;font-size:80%;"><br /><br />SQL_TEXT                 SQL_HANDLE               PLAN_NAME                     ENA ACC<br />------------------------ ------------------------ ----------------------------- --- ---<br />select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0888547d3 YES YES<br />ount_sold, t.calendar_ye<br />ar<br />from sales s, products p<br />, times t<br />where s.prod_id = p.prod<br />_id<br />  and s.time_id = t.time<br />_id<br />  and p.prod_id < :pid<br /><br />select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b08cbcb825 YES YES<br />ount_sold, t.calendar_ye<br />ar<br />from sales s, products p<br />, times t<br />where s.prod_id = p.prod<br />_id<br />  and s.time_id = t.time<br />_id<br />  and p.prod_id < :pid<br /><br />select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES<br />ount_sold, t.calendar_ye<br />ar<br />from sales s, products p<br />, times t<br />where s.prod_id = p.prod<br />_id<br />  and s.time_id = t.time<br />_id<br />  and p.prod_id < :pid </span><p></span><br /><br /><span style="FONT-WEIGHT: bold;">Displaying plans</span><br /><br />When the optimizer uses an accepted plan for a SQL statement, you can see it in the plan table (for explain) or <span style="font-family:courier new;">V$SQL_PLAN</span> (for shared cursors). Let's explain the SQL statement above and display its plan:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> explain plan for<br />  2  select p.prod_name, s.amount_sold, t.calendar_year<br />  3  from sales s, products p, times t<br />  4  where s.prod_id = p.prod_id<br />  5    and s.time_id = t.time_id<br />  6    and p.prod_id < :pid;<br /></p></span><span style="font-family:courier new;font-size:90%;"><p>Explained.<br /><br />SQL> select * from table(dbms_xplan.display('plan_table', null, 'basic note'));<br /><br />PLAN_TABLE_OUTPUT<br />-------------------------------------------------------------------------------<br />Plan hash value: 2787970893<br /><br />----------------------------------------------------------------<br />| Id  | Operation                             | Name           |<br />----------------------------------------------------------------<br />|   0 | SELECT STATEMENT                      |                |<br />|   1 |  NESTED LOOPS                         |                |<br />|   2 |   NESTED LOOPS                        |                |<br />|   3 |    HASH JOIN                          |                |<br />|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |<br />|   5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |<br />|   6 |     PARTITION RANGE ALL               |                |<br />|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |<br />|   8 |       BITMAP CONVERSION TO ROWIDS     |                |<br />|   9 |        BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |<br />|  10 |    INDEX UNIQUE SCAN                  | TIME_PK        |<br />|  11 |   TABLE ACCESS BY INDEX ROWID         | TIMES          |<br />----------------------------------------------------------------<br /><br />Note<br />-----<br />- SQL plan baseline "SYS_SQL_PLAN_fcc170b0a62d0f4d" used for this statement<br /><br />22 rows selected. </span><p></span><br /><br />The note at the bottom tells you that the optimizer used an accepted plan.<br /><br />A plan history might have multiple plans. You can see one of the accepted plans if the optimizer selects it for execution. But what if you want to display some or all of the other plans? You can do this using the <span style="font-family:courier new;">display_sql_plan_baseline</span> function in the <span style="font-family:courier new;">DBMS_XPLAN</span> package. Using the above example, here's how you can display the plan for all plans in the plan history.<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> select *<br />  2  from table(dbms_xplan.display_sql_plan_baseline(<br />  3               sql_handle => 'SYS_SQL_4bf04d85fcc170b0', format => 'basic'));</p></span><span style="font-family:courier new;font-size:90%;"><br /><br />PLAN_TABLE_OUTPUT<br />--------------------------------------------------------------------------------<br /><br />--------------------------------------------------------------------------------<br />SQL handle: SYS_SQL_4bf04d85fcc170b0<br />SQL text: select p.prod_name, s.amount_sold, t.calendar_year from sales s,<br />          products p, times t where s.prod_id = p.prod_id   and s.time_id =<br />          t.time_id   and p.prod_id < :pid<br />--------------------------------------------------------------------------------<br /><br />--------------------------------------------------------------------------------<br />Plan name: SYS_SQL_PLAN_fcc170b0888547d3<br />Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD<br />--------------------------------------------------------------------------------<br /><br />Plan hash value: 2290436051<br /><br />---------------------------------------------------------------<br />| Id  | Operation                            | Name           |<br />---------------------------------------------------------------<br />|   0 | SELECT STATEMENT                     |                |<br />|   1 |  HASH JOIN                           |                |<br />|   2 |   HASH JOIN                          |                |<br />|   3 |    TABLE ACCESS FULL                 | TIMES          |<br />|   4 |    PARTITION RANGE ALL               |                |<br />|   5 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |<br />|   6 |      BITMAP CONVERSION TO ROWIDS     |                |<br />|   7 |       BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |<br />|   8 |   TABLE ACCESS BY INDEX ROWID        | PRODUCTS       |<br />|   9 |    INDEX RANGE SCAN                  | PRODUCTS_PK    |<br />---------------------------------------------------------------<br /><br />--------------------------------------------------------------------------------<br />Plan name: SYS_SQL_PLAN_fcc170b08cbcb825<br />Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE<br />--------------------------------------------------------------------------------<br /><br />Plan hash value: 2361178149<br /><br />------------------------------------------<br />| Id  | Operation             | Name     |<br />------------------------------------------<br />|   0 | SELECT STATEMENT      |          |<br />|   1 |  HASH JOIN            |          |<br />|   2 |   HASH JOIN           |          |<br />|   3 |    PARTITION RANGE ALL|          |<br />|   4 |     TABLE ACCESS FULL | SALES    |<br />|   5 |    TABLE ACCESS FULL  | TIMES    |<br />|   6 |   TABLE ACCESS FULL   | PRODUCTS |<br />------------------------------------------<br /><br />--------------------------------------------------------------------------------<br />Plan name: SYS_SQL_PLAN_fcc170b0a62d0f4d<br />Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE<br />--------------------------------------------------------------------------------<br /><br />Plan hash value: 2787970893<br /><br />----------------------------------------------------------------<br />| Id  | Operation                             | Name           |<br />----------------------------------------------------------------<br />|   0 | SELECT STATEMENT                      |                |<br />|   1 |  NESTED LOOPS                         |                |<br />|   2 |   NESTED LOOPS                        |                |<br />|   3 |    HASH JOIN                          |                |<br />|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |<br />|   5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |<br />|   6 |     PARTITION RANGE ALL               |                |<br />|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |<br />|   8 |       BITMAP CONVERSION TO ROWIDS     |                |<br />|   9 |        BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |<br />|  10 |    INDEX UNIQUE SCAN                  | TIME_PK        |<br />|  11 |   TABLE ACCESS BY INDEX ROWID         | TIMES          |<br />----------------------------------------------------------------<br /><br />72 rows selected.</span><br /><br /><span style="FONT-WEIGHT: bold;">Parameters</span><br /><br />Two parameters allow you to control SPM. The first, <span style="font-family:courier new;">optimizer_capture_sql_plan_baselines</span>, which is FALSE by default, allows you to automatically capture plans. SPM will start managing every repeatable SQL statement that is executed and will create a plan history for it. The first plan that is captured will beautomatically accepted. Subsequent plans for these statements will not be accepted until they are evolved.<br /><br />The second parameter, <span style="font-family:courier new;">optimizer_use_sql_plan_baselines</span>, is TRUE by default. It allows the SPM aware optimizer to use the SQL plan baseline if available when compiling a SQL statement. If you set this parameter to FALSE, the SPM aware optimizer will be disabled and you will get the regular cost-based optimizer which will select the best plan based on estimated cost.<br /><br /><span style="FONT-WEIGHT: bold;">SPM and SQL profiles</span><br /><br />A SQL statement can have both a SQL profile and a SQL plan baseline. Such a case was described in Part 3 where we evolved a SQL plan baseline by accepting a SQL profile. In this case, the SPM aware optimizer will use both the SQL profile and the SQL plan baseline. The SQL profile contains additional information that helps the optimizer to accurately cost each accepted plan and select the best one. The SPM aware optimizer may choose a different accepted plan when a SQL profile is present than when it is not.<br /><br /><span style="FONT-WEIGHT: bold;">SPM and stored outlines</span><br /><br />It is possible for a SQL statement to have a stored outline as well as a SQL plan baseline. If a stored outline exists for a SQL statement and is enabled for use, then the optimizer will use it, ignoring the SQL plan baseline. In other words, the stored outline trumps a SQL plan baseline. If you are using stored outlines, you can test SPM by creating SQL plan baselines and disabling the stored outlines. If you are satisfied with SPM, you can either drop the stored outlines or leave them disabled. If SPM doesn't work for you (and we would love to know why), you can re-enable the stored outlines.<br /><br />If you are using stored outlines, be aware of their limitations:<br /><br /><UL><br /><LI>You can only have one stored outline at a time for a given SQL statement. This may be fine in some cases, but a single plan is not necessarily the best when the statement is executed under varying conditions (e.g., bind values).<br /><LI>The second limitation is related to the first. Stored outlines do not allow for evolution. That is, even if a better plan exists, the stored outline will continue to be used, potentially degrading your system's performance. To get the better plan, you have to manually drop the current stored outline and generate a new one.<br /><LI>If an access path (e.g., an index) used in a stored outline is dropped or otherwise becomes unusable, the partial stored outline will continue to be used with the potential of a much worse plan.<br /></UL><br /><br />One question that readers have is what we plan to do with the stored outlines feature. Here is the official word in Chapter 20 of Oracle's Performance Tuning Guide:<br /><br /><Blockquote>Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.<br /><br />If you have existing stored outlines, consider migrating them to SQL plan baselines by using the <span style="font-family:courier new;">LOAD_PLANS_FROM_CURSOR_CACHE</span> or <span style="font-family:courier new;">LOAD_PLANS_FROM_SQLSET</span> procedure of the <span style="font-family:courier new;">DBMS_SPM</span> package. When the migration is complete, you should disable or remove the stored outlines.</Blockquote><br /><br /><span style="FONT-WEIGHT: bold;">SPM and adaptive cursor sharing</span><br /><br /><a href="http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html">Adaptive cursor sharing</a> (ACS) may generate multiple cursors for a given bind sensitive SQL statement if it is determined that a single plan is not optimal under all conditions. Each cursor is generated by forcing a hard parse of the statement. The optimizer will normally select the plan with the best cost upon each hard parse.<br /><br />When you have a SQL plan baseline for a statement, the SPM aware optimizer will select the best accepted plan as the optimal plan. This also applies for the hard parse of a bind sensitive statement. There may be multiple accepted plans, each of which is optimal for different bind sets. With SPM and ACS enabled, the SPM aware optimizer will select the best plan for the current bind set.<br /><br />Thus, if a hard parse occurs, the normal SPM plan selection algorithm is used regardless of whether a statement is bind sensitive. <br /><br /><span style="FONT-WEIGHT: bold;">Enterprise Manager</span><br /><br />You can view SQL plan baselines and configure and manage most SPM tasks through the Enterprise Manager. The screenshots below show two of these tasks.<br /><br /><Blockquote><span style="FONT-WEIGHT: bold;">Setting init.ora parameters for SPM</span></Blockquote><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgehY4KOqVcEwLENYjNIV-zNaigptTs33j0Z7UDp8_fPCHgUm8G6W01JOgHi-AbcNPfZlp1OTizVBWCPJKG0c5JxasB_M-FDPhlnaOI9ryehmyyvjf_lDsS1-UHw1Aof_ESfIYH_e2CV4g/s1600-h/spm_set_initora.GIF"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 242px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgehY4KOqVcEwLENYjNIV-zNaigptTs33j0Z7UDp8_fPCHgUm8G6W01JOgHi-AbcNPfZlp1OTizVBWCPJKG0c5JxasB_M-FDPhlnaOI9ryehmyyvjf_lDsS1-UHw1Aof_ESfIYH_e2CV4g/s400/spm_set_initora.GIF" alt="" id="BLOGGER_PHOTO_ID_5296884442596860354" border="0" /></a><br /><br /><Blockquote><span style="FONT-WEIGHT: bold;">Loading SQL plan baselines from cursor cache</span></Blockquote><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6UbIFSEwDVeKh6xQAnmWP9D4NTXToDsSlSs77MwXXpGWZ4LZ16M_94DiruSU-C-kiZXjnuZ1n9xeIcvFTEuvodGeyhHOELwgeQgbsJWLIx4dSe22qRqd34m0dajNFtwC02gCDO4yC0mU/s1600-h/SPM_Loading+from_cursor_cache.bmp"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 249px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6UbIFSEwDVeKh6xQAnmWP9D4NTXToDsSlSs77MwXXpGWZ4LZ16M_94DiruSU-C-kiZXjnuZ1n9xeIcvFTEuvodGeyhHOELwgeQgbsJWLIx4dSe22qRqd34m0dajNFtwC02gCDO4yC0mU/s400/SPM_Loading+from_cursor_cache.bmp" alt="" id="BLOGGER_PHOTO_ID_5296884574461725554" border="0" /></a><br /><br /><span style="FONT-WEIGHT: bold;">Further Reading</span><br /><br />More details about SPM are available in the Oracle documentation, especially Chapter 15 of the Performance Tuning Guide. There is also a <a href="http://www.oracle.com/technology/products/bi/db/11g/pdf/twp_bidw_sql_plan_mgmt_11gr1.pdf">whitepaper</a>, and a <a href="http://www.vldb.org/pvldb/1/1454175.pdf">paper</a> published in the VLDB 2008 conference. The VLDB paper also has experimental results that show how SPM prevents performance regressions while simultaneously allowing better plans to be used.Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-8983770110875596532009-01-26T10:33:00.000-08:002009-11-17T15:13:03.995-08:00SQL Plan Management (Part 3 of 4): Evolving SQL Plan BaselinesIn the example in Part 2, we saw that the optimizer used an accepted plan instead of a brand new plan. The statement has two plans in its plan history, but only one is accepted and thus in the SQL plan baseline:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;</p></span><span style="font-family:courier new;font-size:90%;"><br />SQL_TEXT                                 PLAN_NAME                      ENA ACC<br />---------------------------------------- ------------------------------ --- ---<br />select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825  YES NO<br />endar_year<br />from sales s, products p, times t<br />where s.prod_id = p.prod_id<br />  and s.time_id = t.time_id<br />  and p.prod_id < :pid<br /><br />select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d  YES YES<br />endar_year<br />from sales s, products p, times t<br />where s.prod_id = p.prod_id<br />  and s.time_id = t.time_id<br />  and p.prod_id < :pid </span><p></span><br /><br />Non-accepted plans can be verified by executing the <span style="font-family:courier new;">evolve_sql_plan_baseline</span> function. This function will execute the non-accepted plan and compare its performance to the best accepted plan. The execution is performed using the conditions (e.g., bind values, parameters, etc.) in effect at the time the non-accepted plan was added to the plan history. If the non-accepted plan's performance is better, the function will make it accepted, thus adding it to the SQL plan baseline. Let's see what happens when we execute this function:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> var report clob;</p></span><span style="font-family:courier new;font-size:90%;"><br />SQL> exec :report := dbms_spm.evolve_sql_plan_baseline();<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> print :report<br /><br />REPORT<br />-------------------------------------------------------------------------------<br /><br />-------------------------------------------------------------------------------<br />                        Evolve SQL Plan Baseline Report<br />-------------------------------------------------------------------------------<br /><br />Inputs:<br />-------<br />  SQL_HANDLE =<br />  PLAN_NAME  =<br />  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT<br />  VERIFY     = YES<br />  COMMIT     = YES<br /><br />Plan: SYS_SQL_PLAN_fcc170b08cbcb825<br />-----------------------------------<br />  Plan was verified: Time used .1 seconds.<br />  Passed performance criterion: Compound improvement ratio >= 10.13<br />  Plan was changed to an accepted plan.<br /><br />                      Baseline Plan      Test Plan     Improv. Ratio<br />                      -------------      ---------     -------------<br />  Execution Status:        COMPLETE       COMPLETE<br />  Rows Processed:               960            960<br />  Elapsed Time(ms):              19             15              1.27<br />  CPU Time(ms):                  18             15               1.2<br />  Buffer Gets:                 1188            116             10.24<br />  Disk Reads:                     0              0<br />  Direct Writes:                  0              0<br />  Fetches:                        0              0<br />  Executions:                     1              1<br /><br />-------------------------------------------------------------------------------<br />                                 Report Summary<br />-------------------------------------------------------------------------------<br />Number of SQL plan baselines verified: 1.<br />Number of SQL plan baselines evolved: 1. </span><p></span><br /><br />The plan verification report shows that the new plan's performance was better and so it was made accepted and became part of the SQL plan baseline. We can confirm it by looking in the <span style="font-family:courier new;">dba_sql_plan_baselines</span> view:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;</p></span><span style="font-family:courier new;font-size:90%;"><br />SQL_TEXT                                 PLAN_NAME                      ENA ACC<br />---------------------------------------- ------------------------------ --- ---<br />select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825  YES YES<br />endar_year<br />from sales s, products p, times t<br />where s.prod_id = p.prod_id<br />  and s.time_id = t.time_id<br />  and p.prod_id < :pid<br /><br />select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d  YES YES<br />endar_year<br />from sales s, products p, times t<br />where s.prod_id = p.prod_id<br />  and s.time_id = t.time_id<br />  and p.prod_id < :pid </span><p></span><br /><br />The SQL plan baseline now has two accepted plans: <span style="font-family:courier new;">SYS_SQL_PLAN_fcc170b08cbcb825</span> is now accepted.<br /><br />You can either execute the <span style="font-family:courier new;">evolve_sql_plan_baseline()</span> function manually or schedule it to run automatically in a maintenance window.<br /><br />Another way of evolving a SQL plan baseline is to use the SQL Tuning Advisor. Instead of executing <span style="font-family:courier new;">evolve_sql_plan_baseline</span>, suppose we start from the original state where we have one accepted and one non-accepted plan:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;</p></span><span style="font-family:courier new;font-size:90%;"><br />SQL_TEXT                                 PLAN_NAME                      ENA ACC<br />---------------------------------------- ------------------------------ --- ---<br />select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825  YES NO<br />endar_year<br />from sales s, products p, times t<br />where s.prod_id = p.prod_id<br />  and s.time_id = t.time_id<br />  and p.prod_id < :pid<br /><br />select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d  YES YES<br />endar_year<br />from sales s, products p, times t<br />where s.prod_id = p.prod_id<br />  and s.time_id = t.time_id<br />  and p.prod_id < :pid </span><p></span><br /><br />You can execute the SQL Tuning Advisor on the cursor in the cursor cache:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> var tname varchar2(30);</p></span><span style="font-family:courier new;font-size:90%;"><br />SQL> exec :tname := dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc');<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> exec dbms_sqltune.execute_tuning_task(task_name => :tname);<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select dbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual;<br /><br />DBMS_SQLTUNE.REPORT_TUNING_TASK(:TNAME,'TEXT','BASIC')<br />-------------------------------------------------------------------------------<br />GENERAL INFORMATION SECTION<br />-------------------------------------------------------------------------------<br />Tuning Task Name   : TASK_505<br />Tuning Task Owner  : SH<br />Workload Type      : Single SQL Statement<br />Scope              : COMPREHENSIVE<br />Time Limit(seconds): 1800<br />Completion Status  : COMPLETED<br />Started at         : 11/11/2008 16:43:12<br />Completed at       : 11/11/2008 16:43:13<br /><br />-------------------------------------------------------------------------------<br />Schema Name: SH<br />SQL ID     : bfbr3zrg9d5cc<br />SQL Text   : select p.prod_name, s.amount_sold, t.calendar_year<br />             from sales s, products p, times t<br />             where s.prod_id = p.prod_id<br />               and s.time_id = t.time_id<br />               and p.prod_id < :pid<br /><br />-------------------------------------------------------------------------------<br />FINDINGS SECTION (1 finding)<br />-------------------------------------------------------------------------------<br />1- A potentially better execution plan was found for this statement.<br /><br />-------------------------------------------------------------------------------<br />EXPLAIN PLANS SECTION<br />-------------------------------------------------------------------------------<br /><br />1- Original With Adjusted Cost<br />------------------------------<br />Plan hash value: 2787970893<br /><br /><br />----------------------------------------------------------------<br />| Id  | Operation                             | Name           |<br />----------------------------------------------------------------<br />|   0 | SELECT STATEMENT                      |                |<br />|   1 |  NESTED LOOPS                         |                |<br />|   2 |   NESTED LOOPS                        |                |<br />|   3 |    HASH JOIN                          |                |<br />|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |<br />|   5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |<br />|   6 |     PARTITION RANGE ALL               |                |<br />|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |<br />|   8 |       BITMAP CONVERSION TO ROWIDS     |                |<br />|   9 |        BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |<br />|  10 |    INDEX UNIQUE SCAN                  | TIME_PK        |<br />|  11 |   TABLE ACCESS BY INDEX ROWID         | TIMES          |<br />----------------------------------------------------------------<br /><br />2- Original With Adjusted Cost<br />------------------------------<br />Plan hash value: 2787970893<br /><br /><br />----------------------------------------------------------------<br />| Id  | Operation                             | Name           |<br />----------------------------------------------------------------<br />|   0 | SELECT STATEMENT                      |                |<br />|   1 |  NESTED LOOPS                         |                |<br />|   2 |   NESTED LOOPS                        |                |<br />|   3 |    HASH JOIN                          |                |<br />|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |<br />|   5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |<br />|   6 |     PARTITION RANGE ALL               |                |<br />|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |<br />|   8 |       BITMAP CONVERSION TO ROWIDS     |                |<br />|   9 |        BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |<br />|  10 |    INDEX UNIQUE SCAN                  | TIME_PK        |<br />|  11 |   TABLE ACCESS BY INDEX ROWID         | TIMES          |<br />----------------------------------------------------------------<br /><br />3- Using SQL Profile<br />--------------------<br />Plan hash value: 2361178149<br /><br /><br />------------------------------------------<br />| Id  | Operation             | Name     |<br />------------------------------------------<br />|   0 | SELECT STATEMENT      |          |<br />|   1 |  HASH JOIN            |          |<br />|   2 |   HASH JOIN           |          |<br />|   3 |    PARTITION RANGE ALL|          |<br />|   4 |     TABLE ACCESS FULL | SALES    |<br />|   5 |    TABLE ACCESS FULL  | TIMES    |<br />|   6 |   TABLE ACCESS FULL   | PRODUCTS |<br />------------------------------------------<br /><br />-------------------------------------------------------------------------------<br /><br /><br />SQL> exec dbms_sqltune.accept_sql_profile(task_name => :tname);<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;<br /><br />SQL_TEXT                                 PLAN_NAME                      ENA ACC<br />---------------------------------------- ------------------------------ --- ---<br />select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825  YES YES<br />endar_year<br />from sales s, products p, times t<br />where s.prod_id = p.prod_id<br />  and s.time_id = t.time_id<br />  and p.prod_id < :pid<br /><br />select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d  YES YES<br />endar_year<br />from sales s, products p, times t<br />where s.prod_id = p.prod_id<br />  and s.time_id = t.time_id<br />  and p.prod_id < :pid<br /><br />SQL> select sql_text, type, status from dba_sql_profiles;<br /><br />SQL_TEXT                                 TYPE    STATUS<br />---------------------------------------- ------- --------<br />select p.prod_name, s.amount_sold, t.cal MANUAL  ENABLED<br />endar_year<br />from sales s, products p, times t<br />where s.prod_id = p.prod_id<br />  and s.time_id = t.time_id<br />  and p.prod_id < :pid </span><p></span><br /><br />What we see here is that SQL Tuning Advisor found a tuned plan (that coincidentally happened to be the non-accepted plan in our plan history). When we accepted the recommended SQL profile, the SQL Tuning Advisor created a SQL profile and also changed the non-accepted plan to accepted status, thus evolving the SQL plan baseline to two plans.<br /><br />Note that the SQL Tuning Advisor may also find a completely new tuned plan, one that is not in the plan history. If you then accept the recommended SQL profile, the SQL Tuning Advisor will create a SQL profile and also add the tuned plan to the SQL plan baseline.<br /><br />Thus, you can evolve a SQL plan baseline either by executing the <span style="font-family:courier new;">evolve_sql_plan_baseline</span> function or by using the SQL Tuning Advisor. New and provably better plans will be added by either of these methods to the SQL plan baseline.Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-88475235003140147952009-01-20T09:59:00.000-08:002009-11-17T15:13:03.995-08:00SQL Plan Management (Part 2 of 4): SPM Aware Optimizer(Keep sending your feedback and questions. We'll address them in Part 4.)<br /><br />In Part 1, we saw how you can create SQL plan baselines. After you create a SQL plan baseline for a statement, subsequent executions of that statement will use the SQL plan baseline. From all the plans in the SQL plan baseline, the optimizer will select the one with the best cost in the current environment (including bind values, current statistics, parameters, etc.). The optimizer will also generate the best-cost plan that it would otherwise have used without a SQL plan baseline. However, this best-cost plan will not be used but instead added to the statement's plan history for later verification. In other words, the optimizer will use a known plan from the SQL plan baseline instead of a new and hitherto unknown plan. This guarantees no performance regression.<br /><br />Let's see this plan selection process in action. First, we create a SQL plan baseline by enabling automatic plan capture and executing the query twice:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> alter session set optimizer_capture_sql_plan_baselines = true;</p></span><span style="font-family:courier new;font-size:90%;"><p>Session altered.<br /><br />SQL> var pid number<br />SQL> exec :pid := 100;<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select p.prod_name, s.amount_sold, t.calendar_year<br />2    from sales s, products p, times t<br />3    where s.prod_id = p.prod_id<br />4      and s.time_id = t.time_id<br />5      and p.prod_id < :pid;<br /><br />PROD_NAME AMOUNT_SOLD CALENDAR_YEAR<br />--------- ----------- -------------<br />...<br />9 rows selected.<br /><br />SQL> select * from table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));<br /><br />PLAN_TABLE_OUTPUT<br />-------------------------------------------------------------------------------<br />EXPLAINED SQL STATEMENT:<br />------------------------<br />select p.prod_name, s.amount_sold, t.calendar_year from sales s,<br />products p, times t where s.prod_id = p.prod_id and s.time_id =<br />t.time_id and p.prod_id < :pid<br /><br />Plan hash value: 2787970893<br /><br />----------------------------------------------------------------<br />| Id  | Operation                             | Name           |<br />----------------------------------------------------------------<br />|   0 | SELECT STATEMENT                      |                |<br />|   1 |  NESTED LOOPS                         |                |<br />|   2 |   NESTED LOOPS                        |                |<br />|   3 |    HASH JOIN                          |                |<br />|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |<br />|   5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |<br />|   6 |     PARTITION RANGE ALL               |                |<br />|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |<br />|   8 |       BITMAP CONVERSION TO ROWIDS     |                |<br />|   9 |        BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |<br />|  10 |    INDEX UNIQUE SCAN                  | TIME_PK        |<br />|  11 |   TABLE ACCESS BY INDEX ROWID         | TIMES          |<br />----------------------------------------------------------------<br /><br />25 rows selected.<br /><br />SQL> select p.prod_name, s.amount_sold, t.calendar_year<br />2    from sales s, products p, times t<br />3    where s.prod_id = p.prod_id<br />4      and s.time_id = t.time_id<br />5      and p.prod_id < :pid;<br /><br />PROD_NAME AMOUNT_SOLD CALENDAR_YEAR<br />--------- ----------- -------------<br />...<br />9 rows selected.<br /><br />SQL> alter session set optimizer_capture_sql_plan_baselines = false;<br /><br />Session altered.<br /><br />SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;<br /><br />SQL_TEXT                                 PLAN_NAME                      ENA ACC<br />---------------------------------------- ------------------------------ --- ---<br />select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d  YES YES<br />endar_year<br />from sales s, products p, times t<br />where s.prod_id = p.prod_id<br />and s.time_id = t.time_id<br />and p.prod_id < :pid </span><p></span><br /><br />We can see that a SQL plan baseline was created for the statement. Suppose the statement is hard parsed again (we do it here by flushing the shared pool). Let's turn off SQL plan management and execute the query with a different bind value:<br /><br /></p><p><br /><span style="font-family:courier new;font-size:90%;">SQL> exec :pid := 100000;</p></span><span style="font-family:courier new;font-size:90%;"><p>PL/SQL procedure successfully completed.<br /><br />SQL> alter system flush shared_pool;<br /><br />System altered.<br /><br />SQL> alter session set optimizer_use_sql_plan_baselines = false;<br /><br />Session altered.<br /><br />SQL> select p.prod_name, s.amount_sold, t.calendar_year<br />2    from sales s, products p, times t<br />3    where s.prod_id = p.prod_id<br />4      and s.time_id = t.time_id<br />5      and p.prod_id < :pid;<br /><br />PROD_NAME AMOUNT_SOLD CALENDAR_YEAR<br />--------- ----------- -------------<br />...<br />960 rows selected.<br /><br />SQL> select * from table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));<br /><br />PLAN_TABLE_OUTPUT<br />-------------------------------------------------------------------------------<br />EXPLAINED SQL STATEMENT:<br />------------------------<br />select p.prod_name, s.amount_sold, t.calendar_year from sales s,<br />products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and<br /> p.prod_id < :pid<br /><br />Plan hash value: 2361178149<br /><br />------------------------------------------<br />| Id  | Operation             | Name     |<br />------------------------------------------<br />|   0 | SELECT STATEMENT      |          |<br />|   1 |  HASH JOIN            |          |<br />|   2 |   HASH JOIN           |          |<br />|   3 |    PARTITION RANGE ALL|          |<br />|   4 |     TABLE ACCESS FULL | SALES    |<br />|   5 |    TABLE ACCESS FULL  | TIMES    |<br />|   6 |   TABLE ACCESS FULL   | PRODUCTS |<br />------------------------------------------<br /><br />20 rows selected. </span><p></span><br /><br />We can see that the optimizer selected a different plan because the new bind value makes the predicate less selective. Let's turn SQL plan management back on and re-execute the query with the same bind value:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> alter session set optimizer_use_sql_plan_baselines = true;</p></span><span style="font-family:courier new;font-size:90%;"><p>Session altered.<br /><br />SQL> select p.prod_name, s.amount_sold, t.calendar_year<br />2    from sales s, products p, times t<br />3    where s.prod_id = p.prod_id<br />4      and s.time_id = t.time_id<br />5      and p.prod_id < :pid;<br /><br />PROD_NAME AMOUNT_SOLD CALENDAR_YEAR<br />--------- ----------- -------------<br />...<br />960 rows selected.<br /><br />SQL> select * from table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));<br /><br />PLAN_TABLE_OUTPUT<br />-------------------------------------------------------------------------------<br />EXPLAINED SQL STATEMENT:<br />------------------------<br />select p.prod_name, s.amount_sold, t.calendar_year from sales s,<br />products p, times t where s.prod_id = p.prod_id and s.time_id =<br />t.time_id and p.prod_id < :pid<br /><br />Plan hash value: 2787970893<br /><br />----------------------------------------------------------------<br />| Id  | Operation                             | Name           |<br />----------------------------------------------------------------<br />|   0 | SELECT STATEMENT                      |                |<br />|   1 |  NESTED LOOPS                         |                |<br />|   2 |   NESTED LOOPS                        |                |<br />|   3 |    HASH JOIN                          |                |<br />|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |<br />|   5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |<br />|   6 |     PARTITION RANGE ALL               |                |<br />|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |<br />|   8 |       BITMAP CONVERSION TO ROWIDS     |                |<br />|   9 |        BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |<br />|  10 |    INDEX UNIQUE SCAN                  | TIME_PK        |<br />|  11 |   TABLE ACCESS BY INDEX ROWID         | TIMES          |<br />----------------------------------------------------------------<br /><br />Note<br />-----<br /> - SQL plan baseline SYS_SQL_PLAN_fcc170b0a62d0f4d used for this statement<br /><br />29 rows selected. </span><p></span><br /><br />The note at the bottom tells you that the optimizer is using the SQL plan baseline. In other words, we can see that the optimizer used an accepted plan in the SQL plan baseline in favor of a new plan. In fact, we can also check that the optimizer inserted the new plan into the statement's plan history:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;</p></span><span style="font-family:courier new;font-size:90%;"><br />SQL_TEXT                                 PLAN_NAME                      ENA ACC<br />---------------------------------------- ------------------------------ --- ---<br />select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825  YES NO<br />endar_year<br />from sales s, products p, times t<br />where s.prod_id = p.prod_id<br />  and s.time_id = t.time_id<br />  and p.prod_id < :pid<br /><br />select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d  YES YES<br />endar_year<br />from sales s, products p, times t<br />where s.prod_id = p.prod_id<br />  and s.time_id = t.time_id<br />  and p.prod_id < :pid </span><p></span><br /><br />The <span style="font-family:courier new;">'NO'</span> value for the accepted column implies that the new plan is in the plan history but is not available for use until it is verified to be a good plan. The optimizer will continue to use an accepted plan until new plans are verified and added to the SQL plan baseline. If there is more than one plan in the SQL plan baseline, the optimizer will use the one with the best cost under the then-current conditions (statistics, bind values, parameter settings and so on).<br /><br />When you create a SQL plan baseline for a SQL statement, the SPM aware optimizer thus guarantees that no new plans will be used other than the ones in the SQL plan baseline. This prevents unexpected plan changes that sometimes lead to performance regressions.<br /><br />Preventing new plans from being used is fine, but what if the new plans are in fact better? In Part 3, we will describe how new and improved plans are added to a SQL plan baseline.</p>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-63135830833272726592009-01-08T15:58:00.000-08:002009-11-17T15:13:03.996-08:00Plan regressions got you down? SQL Plan Management to the rescue!<span style="font-weight: bold;">Part 1 of 4: Creating SQL plan baselines</span><br /><br /><span style="line-height: 1.4em;">Do you ever experience performance regressions because an execution plan has changed for the worse? If you have, then we have an elegant solution for you in 11g called SQL Plan Management (SPM). The next four posts on our blog will cover SPM in detail. Let's begin by reviewing the primary causes for plan changes.<br /><br />Execution plan changes occur due to various system changes. For example, you might have (manually or automatically) updated statistics for some objects, or changed a few optimizer-related parameters. A more dramatic change is a database upgrade (say from 10gR2 to 11g). All of these changes have the potential to cause new execution plans to be generated for many of your SQL statements. Most new plans are obviously improvements because they are tailored to the new system environment, but some might be worse leading to performance regressions. It is the latter that cause sleepless nights for many DBAs.<br /><br />DBAs have several options for addressing these regressions. However, what most DBAs want is simple: plans should only change when they will result in performance gains. In other words, the optimizer should not pick bad plans, period.<br /><br />This first post in our series, describes the concepts of SQL Plan Management and how to create SQL plan baselines. The second part will describe how and when these SQL plan baselines are used. The third part will discuss evolution, the process of adding new and improved plans to SQL plan baselines. Finally, the fourth part will describe user interfaces and interactions with other Oracle objects (like stored outlines).</span><br /><br /><span style="font-weight: bold;">Introduction</span><br /><br /><span style="line-height: 1.4em;">SQL Plan Management (SPM) allows database users to maintain stable yet optimal performance for a set of SQL statements. SPM incorporates the positive attributes of plan adaptability and plan stability, while simultaneously avoiding their shortcomings. It has two main objectives: <ol><li>prevent performance regressions in the face of database system changes </li> <li>offer performance improvements by gracefully adapting to database system changes</li></ol>A managed SQL statement is one for which SPM has been enabled. SPM can be configured to work automatically or it can be manually controlled either wholly or partially (described later). SPM helps prevent performance regressions by enabling the detection of plan changes for managed SQL statements. For this purpose, SPM maintains, on disk, a plan history consisting of different execution plans generated for each managed SQL statement. An enhanced version of the Oracle optimizer, called SPM aware optimizer, accesses, uses, and manages this information which is stored in a repository called the SQL Management Base (SMB).<br /><br />The plan history enables the SPM aware optimizer to determine whether the best-cost plan it has produced using the cost-based method is a brand new plan or not. A brand new plan represents a plan change that has potential to cause performance regression. For this reason, the SPM aware optimizer does not choose a brand new best-cost plan. Instead, it chooses from a set of accepted plans. An accepted plan is one that has been either verified to not cause performance regression or designated to have good performance. A set of accepted plans is called a SQL plan baseline, which represents a subset of the plan history.<br /><br />A brand new plan is added to the plan history as a non-accepted plan. Later, an SPM utility verifies its performance, and keeps it as a non-accepted plan if it will cause a performance regression, or changes it to an accepted plan if it will provide a performance improvement. The plan performance verification process ensures both plan stability and plan adaptability.<br /><br />The figure below shows the SMB containing the plan history for three SQL statements. Each plan history contains some accepted plans (the SQL plan baseline) and some non-accepted plans.</span><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlUhnCgD80q_v2nqh0qGqSpqSep5IkLzLDc1R9pfoiBX6VWxvzGcJrv-V1FmOFL0ZGlkyxO-e9VrDXCJnF_4T3TX5cam4R2TDYkoeLYUpDcfgoTfotsyVoD4f7NQg276_s7UCEriF8_Dc/s1600-h/SQL_plan_base.GIF"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 235px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlUhnCgD80q_v2nqh0qGqSpqSep5IkLzLDc1R9pfoiBX6VWxvzGcJrv-V1FmOFL0ZGlkyxO-e9VrDXCJnF_4T3TX5cam4R2TDYkoeLYUpDcfgoTfotsyVoD4f7NQg276_s7UCEriF8_Dc/s400/SQL_plan_base.GIF" alt="" id="BLOGGER_PHOTO_ID_5288707833685556834" border="0" /></a><br /><br />(Click on the image for a larger view.)<br /><br /><span style="line-height: 1.4em;">You can create a SQL plan baseline in several ways: using a SQL Tuning Set (STS); from the cursor cache; exporting from one database and importing into another; and automatically for every statement. Let's look at each in turn. The examples in this blog entry use the Oracle Database Sample Schemas so you can try them yourself.</span><br /><p><br /><span style="font-weight: bold;">Creating SQL plan baselines from STS</span><br /><br />If you are upgrading from 10gR2 or have an 11g test system, you might already have an STS containing some or all of your SQL statements. This STS might contain plans that perform satisfactorily. Let's call this STS MY_STS. You can create a SQL plan baseline from this STS as follows:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> variable pls number;<br />SQL> exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name => 'MY_STS', -<br />>                   basic_filter => 'sql_text like ''select%p.prod_name%''');</span></span><br /><br />This will create SQL plan baselines for all statements that match the specified filter.<br /><br /><span style="font-weight: bold;">Creating SQL plan baselines from cursor cache</span><br /><br />You can automatically create SQL plan baselines for any cursor that is currently in the cache as follows:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> exec :pls := dbms_spm.load_plans_from_cursor_cache( -<br />>                   attribute_name => 'SQL_TEXT', -<br />>                   attribute_value => 'select%p.prod_name%');</span></span></span><br /><br />This will create SQL plan baselines for all statements whose text matches the specified string. Several overloaded variations of this function allow you to filter on other cursor attributes.<br /><br /><span style="font-weight: bold;">Creating SQL plan baselines using a staging table</span><br /><br />If you already have SQL plan baselines (say on an 11g test system), you can export them to another system (a production system for instance).<br /><br />First, on the test system, create a staging table and pack the SQL plan baselines you want to export:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'MY_STGTAB', -<br />>           table_owner => 'SH');</span> </span></p><p><span style="font-family:courier new;font-size:90%;">PL/SQL procedure successfully completed.</span> </span><br /></p><p><span style="font-family:courier new;font-size:90%;">SQL> exec :pls := dbms_spm.pack_stgtab_baseline( -<br />>                   table_name => 'MY_STGTAB', -<br />>                   table_owner => 'SH', -<br />>                   sql_text => 'select%p.prod_name%');</span><br /><br />This will pack all SQL plan baselines for statements that match the specified filter. The staging table, MY_STGTAB, is a regular table that you should export to the production system using Datapump Export.<br /><br />On the production system, you can now unpack the staging table to create the SQL plan baselines:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> exec :pls := dbms_spm.unpack_stgtab_baseline( -<br />>                   table_name => 'MY_STGTAB', -<br />>                   table_owner => 'SH', -<br />>                   sql_text => 'select%p.prod_name%');</span><br /><br />This will unpack the staging table and create SQL plan baselines. Note that the filter for unpacking the staging table is optional and may be different than the one used during packing. This means that you can pack several SQL plan baselines into a staging table and selectively unpack only a subset of them on the target system.<br /><br /><span style="font-weight: bold;">Creating SQL plan baselines automatically</span><br /><br />You can create SQL plan baselines for all repeatable statements automatically by setting the parameter <span style="font-family:courier new;">optimizer_capture_sql_plan_baselines</span> to TRUE (default is FALSE). The first plan captured for any statement is automatically accepted and becomes part of the SQL plan baseline, so enable this parameter only when you are sure that the default plans are performing well.<br /><br />You can use the automatic plan capture mode when you have upgraded from a previous database version. Set <span style="font-family:courier new;">optimizer_features_enable</span> to the earlier version and execute your workload. Every repeatable statement will have its plan captured thus creating SQL plan baselines. You can reset <span style="font-family:courier new;">optimizer_features_enable</span> to its default value after you are sure that all statements in your workload have had a chance to execute.<br /><br />Note that this automatic plan capture occurs only for repeatable statements, that is, statements that are executed at least twice. Statements that are only executed once will not benefit from SQL plan baselines since accepted plans are only used in subsequent hard parses.<br /><br />The following example shows a plan being captured automatically when the same statement is executed twice:<br /><br /><span style="font-family:courier new;font-size:90%;">SQL> alter session set optimizer_capture_sql_plan_baselines = true;</p></span><span style="font-family:courier new;font-size:90%;"><p>Session altered.<br /><br />SQL> var pid number<br />SQL> exec :pid := 100;<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select p.prod_name, s.amount_sold, t.calendar_year<br />2    from sales s, products p, times t<br />3    where s.prod_id = p.prod_id<br />4      and s.time_id = t.time_id<br />5      and p.prod_id < :pid;<br /><br />PROD_NAME AMOUNT_SOLD CALENDAR_YEAR<br />--------- ----------- -------------<br />...<br />9 rows selected.<br /><br />SQL> select p.prod_name, s.amount_sold, t.calendar_year<br />2    from sales s, products p, times t<br />3    where s.prod_id = p.prod_id<br />4      and s.time_id = t.time_id<br />5      and p.prod_id < :pid;<br /><br />PROD_NAME AMOUNT_SOLD CALENDAR_YEAR<br />--------- ----------- -------------<br />...<br />9 rows selected.<br /><br />SQL> alter session set optimizer_capture_sql_plan_baselines = false;<br /><br />Session altered.</span><br /><br /><span style="line-height: 1.4em;">Automatic plan capture will not occur for a statement if a stored outline exists for it and is enabled and the parameter <span style="font-family:courier new;">use_stored_outlines</span> is TRUE. In this case, turn on incremental capture of plans into an STS using the function <span style="font-family:courier new;">capture_cursor_cache_sqlset()</span> in the <span style="font-family:courier new;">DBMS_SQLTUNE</span> package. After you have collected the plans for your workload into the STS, manually create SQL plan baselines using the method described earlier. Then, disable the stored outlines or set <span style="font-family:courier new;">use_stored_outlines</span> to FALSE. From now on, SPM will manage your workload and stored outlines will not be used for those statements.<br /><br />In this article, we have seen how to create SQL plan baselines. In the next, we will describe the SPM aware optimizer and how it uses SQL plan baselines.</span>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-9222874843256880202008-10-14T13:40:00.001-07:002009-11-17T15:08:50.477-08:00Oracle Open World follow upWe were delighted to see so many people turn up for our Open World session - Inside the 11g Optimizer - so early on Tuesday morning! A lot of people have been asking where they can find more information on the topics covered especially the demos that were shown. You can find similar worked examples for most of the new 11g Optimizer features on the <a href="http://www.oracle.com/technology/obe/11gr1_db/index.htm">Oracle By Example</a> website. You can also get more information on SQL Plan Management in the following <a href="http://www.oracle.com/technology/products/bi/db/11g/pdf/twp_bidw_sql_plan_mgmt_11gr1.pdf">white paper</a>. Our blog entry from <a href="http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html">December 2007 </a>has more information on Adaptive Cursor Sharing while the <a href="http://optimizermagic.blogspot.com/2008/01/improvement-of-auto-sampling-statistics.html">January 2008</a> entry gives more details on the enhancements made to statistics. We hope you enjoyed Oracle Open World as much as we did!Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-21709980863323170942008-07-24T15:50:00.000-07:002011-09-21T16:47:23.094-07:00Will the Optimizer development team be at Oracle Open World?The largest gathering of Oracle customers, partners, developers, and technology enthusiasts will happen in September when Oracle will host its annual user conference Open World in San Francisco and the Optimizer development group will be there! You will have two opportunities to meet the team -- attend the technical presentation "Inside the 11g Optimizer - Removing the mystery" on Tuesday morning at 9am or stop by the Oracle demo grounds (in Moscone West) to see all of the demos for the 11g new features and ask the development team any burning questions you may have!<br /><br /><a href="https://plus.google.com/115225673827938473361" rel="author">Maria Colgan</a>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-18222315482137607902008-06-26T15:35:00.000-07:002009-11-17T15:13:27.003-08:00Why are some of the tables in my query missing from the plan?We apologize for our brief hiatus from blogging. We've been busy working on improvements to the optimizer.<br /><br />In 10gR2, we introduced a new transformation, table elimination (alternately called "join elimination"), which removes redundant tables from a query. A table is redundant if its columns are only referenced to in join predicates, and it is guaranteed that those joins neither filter nor expand the resulting rows. There are several cases where Oracle will eliminate a redundant table. We will discuss each case in turn.<br /><br /><span style="FONT-WEIGHT: bold">Primary Key-Foreign Key Table Elimination</span><br /><br />Starting in 10gR2, the optimizer eliminates tables that are redundant due to primary key-foreign key constraints. Consider the following example tables:<br /><br /><span style="font-family:courier new;">create table jobs </span><br /><span style="font-family:courier new;">(<br />job_id NUMBER PRIMARY KEY,</span><br /><span style="font-family:courier new;">job_title VARCHAR2(35) NOT NULL,</span><br /><span style="font-family:courier new;">min_salary NUMBER,</span><br /><span style="font-family:courier new;">max_salary NUMBER<br />);</span><br /><span style="font-family:courier new;">create table departments </span><br /><span style="font-family:courier new;">(</span><br /><span style="font-family:courier new;">department_id NUMBER PRIMARY KEY,<br />department_name VARCHAR2(50)<br /></span><span style="font-family:courier new;">);</span><br /><span style="font-family:courier new;">create table employees</span><br /><span style="font-family:courier new;">(</span><br /><span style="font-family:courier new;">employee_id NUMBER PRIMARY KEY,</span><br /><span style="font-family:courier new;">employee_name VARCHAR2(50),</span><br /><span style="font-family:courier new;">department_id NUMBER REFERENCES departments(department_id),<br />job_id NUMBER REFERENCES jobs(job_id)<br /></span><span style="font-family:courier new;">);</span><span style="font-family:courier new;"></span><span style="font-family:courier new;"></span><br /><br />and the query:<br /><br /><span style="font-family:courier new;">select e.employee_name</span><br /><span style="font-family:courier new;">from employees e, departments d</span><br /><span style="font-family:courier new;">where e.department_id = d.department_id; </span><br /><br />In this query, the join to departments is redundant. The only column referenced in the query appears in the join predicate, and the primary key-foreign key constraint guarantees that there is at most one match in departments for each row in employees. Hence, the query is equivalent to:<br /><br /><span style="font-family:courier new;">select e.employee_name</span><br /><span style="font-family:courier new;">from employees e</span><span style="font-family:courier new;"><br />where e.department_id is not null; </span><br /><br />The optimizer will generate this plan for the query:<br /><br /><span style="font-family:courier new;"><pre>-------------------------------------------<br /> Id Operation Name <br />-------------------------------------------<br /> 0 SELECT STATEMENT <br />* 1 TABLE ACCESS FULL EMPLOYEES <br />-------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /> 1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)<br /></pre></span><br />Note that the <span style="font-family:courier new;">IS NOT NULL</span> predicate is not necessary if the column has a NOT NULL constraint on it.<br /><br />Starting in 11gR1, the optimizer will also eliminate tables that are semi-joined or anti-joined. Consider the following query:<br /><span style="font-family:courier new;"><pre><br />select e.employee_id, e.employee_name<br />from employees e<br />where not exists (select 1<br /> from jobs j<br /> where j.job_id = e.job_id);</pre></span><br /><br />Since <span style="font-family:courier new;">employees.job_id</span> is a foreign key to <span style="font-family:courier new;">jobs.job_id</span>, any non-null value in <span style="font-family:courier new;">employees.job_id</span> must have a match in <span style="font-family:courier new;">jobs</span>. So only employees with null values for <span style="font-family:courier new;">employees.job_id</span> will appear in the result. Hence, this query is equivalent to:<br /><br /><span style="font-family:courier new;">select e.employee_id, e.employee_name</span><br /><span style="font-family:courier new;">from employees e<br />where job_id is null;<br /><br /></span>and the optimizer can choose this plan:<span style="font-family:courier new;"><br /><pre><br />-------------------------------------------<br /> Id Operation Name <br />-------------------------------------------<br /> 0 SELECT STATEMENT <br />* 1 TABLE ACCESS FULL EMPLOYEES <br />-------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br />1 - filter("E"."JOB_ID" IS NULL)<br /></pre><br /></span>Suppose <span style="font-family:courier new;">employees.job_id</span> has a NOT NULL constraint:<br /><br /><span style="font-family:courier new;">alter table employees modify job_id not null;</span><br /><br />In this case, there could not possibly be any rows in <span style="font-family:courier new;">EMPLOYEES</span>, and the optimizer could choose this plan:<br /><br /><span style="font-family:courier new;"><pre>-------------------------------------------<br /> Id Operation Name <br />-------------------------------------------<br /> 0 SELECT STATEMENT <br />* 1 FILTER <br /> 2 TABLE ACCESS FULL EMPLOYEES <br />-------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /> 1 - filter(NULL IS NOT NULL)<br /></pre></span><br />The "<span style="font-family:courier new;">NULL IS NOT NULL</span>" filter is a false constant predicate, that will prevent the table scan from even taking place.<br /><br />Also in 11gR1, the optimization became available for ANSI compliant joins. For this query:<br /><br /><span style="font-family:courier new;">select employee_name</span><br /><span style="font-family:courier new;">from employees e inner join jobs j </span><br /><span style="font-family:courier new;">on e.job_id = j.job_id;</span><br /><br />the optimizer can eliminate <span style="font-family:courier new;">JOBS</span> and produce this plan:<br /><br /><span style="font-family:courier new;"><pre>-------------------------------------------<br /> Id Operation Name <br />-------------------------------------------<br /> 0 SELECT STATEMENT <br /> 1 TABLE ACCESS FULL EMPLOYEES <br />-------------------------------------------<br /></pre></span><br /><span style="FONT-WEIGHT: bold">Outer Join Table Elimination</span><br /><br />In 11gR1, a new form of table elimination was introduced for outer joins, which does not require PK-FK constraints. For the example, we require a new table and an addition to EMPLOYEES:<br /><br /><span style="font-family:courier new;">create table projects</span><br /><span style="font-family:courier new;">(</span><br /><span style="font-family:courier new;">project_id NUMBER UNIQUE,</span><br /><span style="font-family:courier new;">deadline DATE,</span><br /><span style="font-family:courier new;">priority NUMBER</span><br /><span style="font-family:courier new;">);</span><br /><br /><span style="font-family:courier new;">alter table employees add project_id number;</span><br /><br />Now consider a query that outer joins <span style="font-family:courier new;">employees</span> and <span style="font-family:courier new;">projects</span>:<br /><br /><span style="font-family:courier new;">select e.employee_name, e.project_id</span><br /><span style="font-family:courier new;">from employees e, projects p</span><br /><span style="font-family:courier new;">where e.project_id = p.project_id (+);</span><br /><br />The outer join guarantees that every row in <span style="font-family:courier new;">employee</span>s will appear at least once in the result. The unique constraint on <span style="font-family:courier new;">projects.project_id</span> guarantees that every row in <span style="font-family:courier new;">employees</span> will match at most one row in <span style="font-family:courier new;">projects</span>. Together, these two properties guarantee that every row in <span style="font-family:courier new;">employees</span> will appear in the result exactly once. Since no other columns from <span style="font-family:courier new;">projects</span> are referenced, <span style="font-family:courier new;">projects</span> can be eliminated, and the optimizer can choose this plan:<br /><br /><span style="font-family:courier new;"><pre>-------------------------------------------<br /> Id Operation Name <br />-------------------------------------------<br /> 0 SELECT STATEMENT <br /> 1 TABLE ACCESS FULL EMPLOYEES <br />-------------------------------------------<br /></pre></span><br /><span style="FONT-WEIGHT: bold">Why Would I Ever Write Such a Query?<br /><span style="FONT-WEIGHT: bold"><span style="FONT-WEIGHT: bold"></span><br /></span></span>All of the example queries in this post are very simple, and one would be unlikely to write a query where the join is so obviously unnecessary. There are many real world scenarios where table elimination may be helpful, including machine-generated queries and elimination of tables in views. For example, a set of tables might be exposed as a view, which contains a join. The join may be necessary to retrieve all of the columns exposed by the view. But some users of the view may only access a subset of the columns, and in this case, the joined table can be eliminated.<br /><br />For example, consider the view:<br /><br /><span style="font-family:courier new;">create view employee_directory_v as</span><br /><span style="font-family:courier new;">select e.employee_name, d.department_name, j.job_title</span><br /><span style="font-family:courier new;">from employees e, departments d, jobs j</span><br /><span style="font-family:courier new;">where e.department_id = d.department_id</span><br /><span style="font-family:courier new;">and e.job_id = j.job_id;</span><br /><br />This view might be exposed to a simple employee directory application. To lookup employee names by job title, the application issues a query:<br /><br /><span style="font-family:courier new;">select employee_name</span><br /><span style="font-family:courier new;">from employee_directory_v</span><br /><span style="font-family:courier new;">where department = 'ACCOUNTING';</span><br /><br />Since the <span style="font-family:courier new;">job_title</span> column is not referenced, <span style="font-family:courier new;">jobs</span> can be eliminated from the query, and the optimizer can choose this plan:<br /><br /><span style="font-family:courier new;"><pre>--------------------------------------------<br /> Id Operation Name <br />--------------------------------------------<br /> 0 SELECT STATEMENT <br />* 1 HASH JOIN <br /> 2 TABLE ACCESS FULL EMPLOYEES <br />* 3 TABLE ACCESS FULL DEPARTMENTS<br />--------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /> 1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")<br /> 3 - filter("D"."DEPARTMENT_NAME"='ACCOUNTING')<br /></pre></span><br /><span style="FONT-WEIGHT: bold">Known Limitations<br /></span><br />There are currently a few limitations of table elimination:<br /><ul><li>Multi-column primary key-foreign key constraints are not supported.</li><li>Referring to the join key elsewhere in the query will prevent table elimination. For an inner join, the join keys on each side of the join are equivalent, but if the query contains other references to the join key from the table that could otherwise be eliminated, this prevents elimination. A workaround is to rewrite the query to refer to the join key from the other table (we realize this is not always possible).<br /></li></ul><span style="FONT-WEIGHT: bold"></span><span style="FONT-WEIGHT: bold"><span style="FONT-WEIGHT: bold"></span></span>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com0tag:blogger.com,1999:blog-3086558868352622884.post-64137335791165274752008-03-14T12:31:00.001-07:002009-11-17T15:13:54.321-08:00Oracle keeps closing my TAR because I cannot provide a testcase, can you help?<span style="LINE-HEIGHT: 1.6em">The answer to this question is yes, as Oracle Database 11g provides a new diagnostic tool called <span style="FONT-WEIGHT: bold">SQL Test Case Builder</span>. In this article, we explain what SQL Test Case Builder is, and how to use it with examples.<br /><br /><strong>Why SQL Test Case Builder?</strong><br /><br />For most SQL problems, the single most important factor for a speedy bug resolution is to obtain a reproducible test case. However, this is normally the longest and most painful step for customers. The goal of the SQL Test Case Builder (<span style="FONT-WEIGHT: bold">TCB</span>) is to automatically gather as much information as possible related to a SQL incident (problem) and package it in a way that allows a developer or a support engineer to reproduce the problem on his or her own machine quickly.<br /><br />At a very high-level, SQL Test Case Builder can be seen as a way to export a SQL. Currently, Oracle export (<span style="FONT-WEIGHT: bold">expdp</span>) takes a schema or a set of tables and exports all the dependents objects. SQL Test Case Builder provides the same service but takes a SQL statement as input.<br /><br /><strong>What's Inside Test Case Builder?</strong><br /><br />The main input of SQL Test Case Builder is a SQL object. A SQL object is defined as the SQL text plus all the information required to compile it on a particular database instance (this contains the parsing user name, for example).<br /><br />Logically, a SQL test case appears as a script containing all the necessary commands to recreate the objects, the user, the statistics, and the environment.<br /><br />Within the Oracle Diagnosability infrastructure, TCB compiles the problem SQL in a special capture mode to obtain the set of objects to export. A test case captures two types of information:<br /><br /><ol><li><span style="FONT-WEIGHT: bold">Permanent information</span></li><br /><ul><li>SQL text</li><br /><li>PL/SQL functions, procedures, packages</li><br /><li>Statistics</li><br /><li>Bind variables</li><br /><li>Compilation environment</li><br /><li>User information (like privileges)</li><br /><li>SQL profiles, stored outlines, or other SQL Management Objects</li><br /><li>Meta data on all the objects involved</li><br /><li>Optimizer statistics</li><br /><li>The execution plan information</li><br /><li>The table content (sample or full). This is optional.</li></ul><br /><li><span style="FONT-WEIGHT: bold">Transient information</span></li><br />For most of the SQL test cases, the permanent information above is enough to reproduce a problem. There are however cases where this is not enough and additional information about the context in which this SQL was compiled is required. Therefore, in addition to the permanent information, SQL Test Case Builder captures transient information, e.g. information that is only available as part of the compilation of the SQL statement. This includes dynamic sampling results, cached information, some run time information, like the actual degree of parallelism used, etc.<br /><br />As part of creating a SQL test case, the SQL object is reloaded and all the diagnostic information available generated and gathered automatically. This information will be made available to Oracle support and developers.<br /><br /></ol><br /><strong>How do I use the SQL Test Case Builder?</strong><br /><br />The task of creating a SQL test case can be performed in two ways:<br /><ul><li>From <span style="FONT-WEIGHT: bold">EM (Enterprise Manager)</span>, where TCB is invoked on user-demand via IPS (Incident Packaging Service) after a SQL incident occurred. The user can also manually create an incident for a problem query for building test case purpose.</li><br /><li>From <span style="FONT-WEIGHT: bold">SQLPLUS</span>, where you can directly invoke one of the PL/SQL API functions in the SQL Diagnostic package. We will give examples of using the APIs below.</li></ul><br />All the new PL/SQL procedures supporting SQL Test Case Builder are part of a new PL/SQL package called dbms_sqldiag (see dbmsdiag.sql for details). The two main features related to TCB in this package are <span style="FONT-WEIGHT: bold">export</span> and <span style="FONT-WEIGHT: bold">import</span> test cases.<br /><br /><ul><li>Procedure <span style="FONT-WEIGHT: bold">dbms_sqldiag.export_sql_testcase</span> exports a SQL test case for a given SQL statement to a given directory.</li><br /><li>Procedure <span style="FONT-WEIGHT: bold">dbms_sqldiag.import_sql_testcase</span> imports a test case from a given directory.</li></ul><br />To build (or export) a test case, the simplest form would be something like:<br /><span style="LINE-HEIGHT: 1.2em;font-family:courier new;font-size:90;" ><br /><pre> dbms_sqldiag.export_sql_testcase(<br /> directory => 'TCB_DIR_EXP',<br /> sql_text => 'select count(*) from sales',<br /> testcase => tco)</pre></span><br />Here <em>directory</em> and <em>sql_text</em> are inputs which specify where the test case will be stored, and the problem query statement, respectively. <em>Testcase</em> specifies the test case metadata as output.<br /><br />For security reason, the user data are not exported by default. You have the option to set <em>exportData</em> to TRUE to include the data. You can also set <em>samplingPercent</em> if you are exporting with data. To protect users proprietary codes, TCB will not export PL/SQL package body by default.<br /><br />Once the test case has been built, you can copy all the files under the export directory to your test environment. Note there is a file called xxxxxxxxmain.xml, for example, <em>oratcb1_03C600800001main.xml</em>, which contains the metadata of the test case.<br /><br />Now importing the test case can be as simple as:<br /><br /><span style="LINE-HEIGHT: 1.2em;font-family:courier new;font-size:90;" ><br /><pre> dbms_sqldiag.import_sql_testcase(<br /> directory => 'TEST_DIR',<br /> filename => 'oratcb1_03C600800001main.xml')</pre></span><br />To verify that the test case is successfully rebuilt, you can just issue an <span style="FONT-WEIGHT: bold">explain</span> command for the problem query. However, if you want to actully run the query, then you need to have the data available.<br /><br />You can refer to dbmsdiag.sql for more information about other options available for these procedures.<br /><br /><strong>Example</strong> - We now show the typical steps of using TCB by a sample query with materialized view. In this exmaple, we set the <em>exportData</em> option to TRUE, so we can re-run the same query after the TCB task is completed.<br /><br /><ol><li>Setup</li><br /><span style="LINE-HEIGHT: 1.2em;font-family:courier new;font-size:90;" ><br /><pre>SQL> connect / as sysdba<br />Connected.<br />SQL><br />SQL> create or replace directory TCB_DIR_EXP as<br /> 2 '/net/tiger/apps/tcb_exp';<br />Directory created.<br />SQL><br />SQL> grant dba to apps;<br />Grant succeeded.<br />SQL><br />SQL> connect apps/apps<br />Connected.<br />SQL><br />SQL> create materialized view scp_mvu<br /> 2 parallel 2<br /> 3 as<br /> 4 select p.prod_name, c.cust_gender,<br /> 5 max(s.amount_sold) max_amount_sold<br /> 6 from sales s, products p, customers c<br /> 7 where s.prod_id = p.prod_id<br /> 8 and s.cust_id = c.cust_id<br /> 9 group by p.prod_name, c.cust_gender;<br /><br />Materialized view created.<br /><br />SQL><br />SQL> desc scp_mvu;<br /> Name Null? Type<br /> ----------------------------------------- -------- ------------<br /> PROD_NAME NOT NULL VARCHAR2(50)<br /> CUST_GENDER CHAR(1)<br /> MAX_AMOUNT_SOLD NUMBER<br /><br />SQL><br />SQL> select * from scp_mvu where max_amount_sold > 7000 order by 3;<br /><br />PROD_NAME C MAX_AMOUNT_SOLD<br />-------------------------------------------------- - ---------------<br />Joseph Sportcoat F 7400.8<br />Kenny Cool Leather Skirt M 7708<br />Leather Boot-Cut Trousers M 8184<br /><br />3 rows selected.</pre></span><br /><li>Export as user <span style="FONT-WEIGHT: bold">APPS</span></li><br /><span style="LINE-HEIGHT: 1.2em;font-family:courier new;font-size:90;" ><br /><pre>SQL> connect apps/apps<br />Connected.<br /><br />SQL><br />SQL> Rem define the problem SQL statement<br />SQL> create or replace package define_vars is<br /> 2 sql_stmt1 varchar2(2000) := q'# select * from scp_mvu<br /> 3 where max_amount_sold > 7000<br /> 4 order by 3<br /> 5 #';<br /> 6 end;<br /> 7 /<br /><br />Package created.<br />SQL> <br />SQL> set serveroutput on<br />SQL><br />SQL> declare<br /> 2 tco clob;<br /> 3 begin<br /> 4 -- Export test case<br /> 5 dbms_sqldiag.export_sql_testcase<br /> 6 (<br /> 7 directory => 'TCB_DIR_EXP',<br /> 8 sql_text => define_vars.sql_stmt1,<br /> 9 user_name => 'APPS',<br /> 10 exportData => TRUE,<br /> 11 testcase => tco<br /> 12 );<br /> 13 <br /> 14 end;<br /> 15 /<br /><br />PL/SQL procedure successfully completed.<br />SQL><br />SQL> Rem Drop MV before importing<br />SQL> drop materialized view scp_mvu;<br /><br />Materialized view dropped.</pre></span><br />At this stage, the export procedure has successfully completed. The next commands prepare a directory for import purpose. The directory could be on a different machine.<br /><span style="LINE-HEIGHT: 1.2em;font-family:courier new;font-size:90;" ><br /><pre>SQL> conn / as sysdba<br />Connected.<br />SQL> create or replace directory TCB_DIR_IMP<br /> 2 as '/net/lion/test/tcb_imp';<br />Directory created.<br />SQL><br />SQL> grant dba to test;<br />Grant succeeded.</pre></span><br />As the export has finished successfully, you can now transfer all the files under TCB_DIR_EXP to a directory in test environment, for example, TCB_DIR_IMP as created above. Again, look up and make note of the TCB metadata file xxxxxxxxmain.xml, which will be used below.<br /><br /><li>Import as user <span style="FONT-WEIGHT: bold">TEST</span></li><br /><span style="LINE-HEIGHT: 1.2em;font-family:courier new;font-size:90;" ><br /><pre>SQL> connect test/test<br />Connected.<br />SQL><br />SQL> set serveroutput on<br />SQL><br />SQL> begin<br /> 2 -- Import test case<br /> 3 dbms_sqldiag.import_sql_testcase<br /> 4 (<br /> 5 directory => 'TCB_DIR_IMP',<br /> 6 filename => 'oratcb3_05e803500001main.xml',<br /> 7 importData => TRUE<br /> 8 );<br /> 9 <br /> 10 end;<br /> 11 /<br /><br />PL/SQL procedure successfully completed.</pre></span><br /><li>Verification. This is to check that now all relevant objects were imported successfully.</li></ol><span style="LINE-HEIGHT: 1.2em;font-family:courier new;font-size:90;" ><br /><pre>SQL> desc scp_mvu;<br /> Name Null? Type<br /> ----------------------------------------- -------- ------------<br /> PROD_NAME NOT NULL VARCHAR2(50)<br /> CUST_GENDER CHAR(1)<br /> MAX_AMOUNT_SOLD NUMBER<br />SQL><br />SQL> select * from scp_mvu where max_amount_sold > 7000 order by 3;<br /><br />PROD_NAME C MAX_AMOUNT_SOLD<br />-------------------------------------------------- - ---------------<br />Joseph Sportcoat F 7400.8<br />Kenny Cool Leather Skirt M 7708<br />Leather Boot-Cut Trousers M 8184<br /><br />3 rows selected.</pre></span><br />Finally, we also have good news for <span style="FONT-WEIGHT: bold">10g</span> users: SQL Test Case Builder has been backported to 10.2.0.4!</span><span style="LINE-HEIGHT: 1.6em"></span>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com4tag:blogger.com,1999:blog-3086558868352622884.post-75704797769739376412008-02-26T13:34:00.000-08:002009-11-17T15:12:12.502-08:00Upgrading from Oracle Database 9i to 10g: What to expect from the OptimizerOne of the most daunting activities a DBA can undertake is upgrading the database to a new version. Having to comprehend all of the new features and to deal with potential plan changes can be overwhelming. In order to help DBA's upgrade from Oracle Database 9i to 10g a new whitepaper called "<a href="http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_bidw_optimizer_10gr2_0208.pdf">Upgrading from Oracle Database 9i to 10g: What to expect from the Optimizer</a>" has recently been posted on <a href="http://www.oracle.com/technology/index.html">Oracle Technology Network </a>(OTN). This paper aims to explain in detail what to expect from the CBO when you upgrade from Oracle database 9i to 10g and describes what steps you should take before and after the upgrade to minimize any potential SQL regressions. This is a must read for any DBA planning on upgrading from 9i to 10g in the near future!Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com1tag:blogger.com,1999:blog-3086558868352622884.post-55444471408023623252008-02-07T12:37:00.000-08:002009-11-17T15:10:31.688-08:00Displaying and reading the execution plans for a SQL statementGenerating and displaying the execution plan of a SQL statement is a common task for most DBAs, SQL developers, and preformance experts as it provides them information on the performance characteristics of a SQL statement. An execution plan shows the detailed steps necessary to execute a SQL statement. These steps are expressed as a set of database operators that consumes and produces rows. The order of the operators and their implentation is decided by the query optimizer using a combination of query transformations and physical optimization techniques.<br /><br />While the display is commonly shown in a tabular format, the plan is in fact tree-shaped. For example, consider the following query based on the SH schema (Sales History):<br /><br /><br /><pre><br />select prod_category, avg(amount_sold)<br />from sales s, products p<br />where p.prod_id = s.prod_id<br />group by prod_category;</pre><br /><br />The tabular representation of this query's plan is:<br /><br /><br /><pre><br />------------------------------------------<br /> Id Operation Name <br />------------------------------------------<br /> 0 SELECT STATEMENT <br /> 1 HASH GROUP BY <br /> 2 HASH JOIN <br /> 3 TABLE ACCESS FULL PRODUCTS<br /> 4 PARTITION RANGE ALL <br /> 5 TABLE ACCESS FULL SALES <br />------------------------------------------<br /></pre><br />While the tree-shaped representation of the plan is:<br /><pre><br /> GROUP BY<br /> |<br /> JOIN<br /> _____|_______<br /> | |<br />ACCESS ACCESS<br />(PRODUCTS) (SALES)<br /></pre><br /><p><br /><br /><br />When you read a plan tree you should start from the bottom up. In the above example begin by looking at the access operators (or the leaves of the tree). In this case the access operators are implemented using full table scans. The rows produced by these tables scans will be consumed by the join operator. Here the join operator is a hash-join (other alternatives include nested-loop or sort-merge join). Finally the group-by operator implemented here using hash (alternative would be sort) consumes rows produced by the join-opertor.<br /><br />The execution plan generated for a SQL statement is just one of the many alternative execution plans considered by the query optimizer. The query optimizer selects the execution plan with the lowest cost. Cost is a proxy for performance, the lower is the cost the better is the performance. The cost model used by the query optimizer accounts for the IO, CPU, and network usage in the query.<br /><br />There are two different methods you can use to look at the execution plan of a SQL statement:<br /><ol><br /><li><b>EXPLAIN PLAN command</b> - This displays an execution plan for a SQL statement without actually executing the statement.</li><br /><li><b>V$SQL_PLAN</b> - A dictionary view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor in the cursor cache.</li><br /></ol><br />Under certain conditions the plan shown when using EXPLAIN PLAN can be different from the plan shown using V$SQL_PLAN. For example, when the SQL statement contains bind variables the plan shown from using EXPLAIN PLAN ignores the bind variable values while the plan shown in V$SQL_PLAN takes the bind variable values into account in the plan generation process.<br /><br />Displaying an execution plan has been made easier after the introduction of the dbms_xplan package in Oracle 9i and by the enhancements made to it in subsequent releases. This packages provides several PL/SQL procedures to display the plan from different sources:<br /><br /><ol><br /><li>EXPLAIN PLAN command</li><br /><li>V$SQL_PLAN</li><br /><li>Automatic Workload Repository (AWR)</li><br /><li>SQL Tuning Set (STS)</li><br /><li>SQL Plan Baseline (SPM)</li><br /></ol><br />The following examples illustrate how to generate and display an execution plan for our original SQL statement using the different functions provided in the dbms_xplan package.<br /><br /><strong>Example 1</strong> Uses the EXPLAIN PLAN command and the dbms_xplan.display function.<br /><br /><pre><br />SQL> EXPLAIN PLAN FOR<br /> 2 select prod_category, avg(amount_sold)<br /> 3 from sales s, products p<br /> 4 where p.prod_id = s.prod_id<br /> 5 group by prod_category;<br /><br />Explained.<br /></pre><br /><br /><pre><br />SQL> select plan_table_output<br /> 2 from table(dbms_xplan.display('plan_table',null,'basic'));<br /><br />------------------------------------------<br /> Id Operation Name <br />------------------------------------------<br /> 0 SELECT STATEMENT <br /> 1 HASH GROUP BY <br /> 2 HASH JOIN <br /> 3 TABLE ACCESS FULL PRODUCTS<br /> 4 PARTITION RANGE ALL <br /> 5 TABLE ACCESS FULL SALES <br />------------------------------------------<br /></pre><br /><br />The arguments are for dbms_xplan.display are:<br /><br /><ul><br /><li>plan table name (default 'PLAN_TABLE'),</li><br /><li>statement_id (default null),</li><br /><li>format (default 'TYPICAL') </li><br /></ul><br />More details can be found in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.<br /><br /><strong>Example 2</strong> Generating and displaying the execution plan for the last SQL statement executed in a session:<br /><br /><br /><pre><br />SQL> select prod_category, avg(amount_sold)<br /> 2 from sales s, products p<br /> 3 where p.prod_id = s.prod_id<br /> 4 group by prod_category;<br /><br />no rows selected<br /></pre><br /><pre><br />SQL> select plan_table_output<br /> 2 from table(dbms_xplan.display_cursor(null,null,'basic'));<br /><br />------------------------------------------<br /> Id Operation Name <br />------------------------------------------<br /> 0 SELECT STATEMENT <br /> 1 HASH GROUP BY <br /> 2 HASH JOIN <br /> 3 TABLE ACCESS FULL PRODUCTS<br /> 4 PARTITION RANGE ALL <br /> 5 TABLE ACCESS FULL SALES <br />------------------------------------------<br /></pre><br /><br />The arguments used by dbms_xplay.dispay_cursor are:<br /><br /><ul><br /><li>SQL ID (default null, null means the last SQL statement executed in this session),</li><br /><li>child number (default 0),</li><br /><li>format (default 'TYPICAL')</li><br /></ul><br />The details are in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.<br /><br /><br /><strong>Example 3</strong> Displaying the execution plan for any other statement requires the SQL ID to be provided, either directly or indirectly:<br /><br /><ol><li>Directly:<pre><br />SQL> select plan_table_output from<br /> 2 table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));</pre><br /><br /></li><li>Indirectly:<pre><br />SQL> select plan_table_output<br /> 2 from v$sql s,<br /> 3 table(dbms_xplan.display_cursor(s.sql_id,<br /> 4 s.child_number, 'basic')) t<br /> 5 where s.sql_text like 'select PROD_CATEGORY%';</pre><br /></li></ol><br /><strong>Example 4</strong> - Displaying an execution plan corresponding to a SQL Plan Baseline. SQL Plan Baselines have been introduced in Oracle 11g to support the SQL Plan Management feature (SPM). In order to illustrate such a case we need to create a SQL Plan Baseline first.<br /><br /><pre><br />SQL> alter session set optimizer_capture_sql_plan_baselines=true;<br /><br />Session altered.<br /><br />SQL> select prod_category, avg(amount_sold)<br /> 2 from sales s, products p<br /> 3 where p.prod_id = s.prod_id<br /> 4 group by prod_category;<br /><br />no rows selected<br /></pre><br />If the above statement has been executed more than once, a SQL Plan Baseline will be created for it and you can verified this using the follows query:<br /><br /><pre><br />SQL> select SQL_HANDLE, PLAN_NAME, ACCEPTED<br /> 2 from dba_sql_plan_baselines<br /> 3 where sql_text like 'select prod_category%';<br /><br />SQL_HANDLE PLAN_NAME ACC<br />------------------------------ ------------------------------ ---<br />SYS_SQL_1899bb9331ed7772 SYS_SQL_PLAN_31ed7772f2c7a4c2 YES<br /></pre><br /><br />The execution plan for the SQL Plan Baseline created above can be displayed either directly or indirectly:<br /><br /><ol><li>Directly<br /><pre>select t.* from<br />table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed7772',<br /> format => 'basic')) t</pre><br /><br /></li><li>Indirectly<br /><pre>select t.*<br /> from (select distinct sql_handle<br /> from dba_sql_plan_baselines<br /> where sql_text like 'select prod_category%') pb,<br /> table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle,<br /> null,'basic')) t;</pre><br /></li></ol><br /><br />The output of either of these two statements is:<br /><br /><br /><pre><br />----------------------------------------------------------------------------<br />SQL handle: SYS_SQL_1899bb9331ed7772<br />SQL text: select prod_category, avg(amount_sold) from sales s, products p<br /> where p.prod_id = s.prod_id group by prod_category<br />----------------------------------------------------------------------------<br /><br />----------------------------------------------------------------------------<br />Plan name: SYS_SQL_PLAN_31ed7772f2c7a4c2<br />Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE<br />----------------------------------------------------------------------------<br /><br />Plan hash value: 4073170114<br /><br />---------------------------------------------------------<br /> Id Operation Name <br />---------------------------------------------------------<br /> 0 SELECT STATEMENT <br /> 1 HASH GROUP BY <br /> 2 HASH JOIN <br /> 3 VIEW index$_join$_002 <br /> 4 HASH JOIN <br /> 5 INDEX FAST FULL SCAN PRODUCTS_PK <br /> 6 INDEX FAST FULL SCAN PRODUCTS_PROD_CAT_IX<br /> 7 PARTITION RANGE ALL <br /> 8 TABLE ACCESS FULL SALES <br />---------------------------------------------------------<br /></pre><br /><br /><p><strong></strong> </p><p><span style="font-size:130%;">Formatting</span></p><br />The format argument is highly customizable and allows you to see as little (high-level) or as much (low-level) details as you need / want in the plan output. The high-level options are:<br /><br /><ol><li><strong>Basic</strong><br />The plan includes the operation, options, and the object name (table, index, MV, etc)<br /></li><li><strong>Typical</strong><br />It includes the information shown in BASIC plus additional optimizer-related internal information such as cost, size, cardinality, etc. These information are shown for every operation in the plan and represents what the optimizer thinks is the operation cost, the number of rows produced, etc. It also shows the predicates evaluation by the operation. There are two types of predicates: ACCESS and FILTER. The ACCESS predicates for an index are used to fetch the relevant blocks because they apply to the search columns. The FILTER predicates are evaluated after the blocks have been fetched.<br /></li><li><strong>All</strong><br />It includes the information shown in TYPICAL plus the lists of expressions (columns) produced by every operation, the hint alias and query block names where the operation belongs. The last two pieces of information can be used as arguments to add hints to the statement.<br /></li></ol>The low-level options allow the inclusion or exclusion of find details, such as predicates and cost.<br />For example,<br /><br /><br /><pre>select plan_table_output<br />from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));<br /><br />-------------------------------------------------------<br /> Id Operation Name Cost (%CPU)<br />-------------------------------------------------------<br /> 0 SELECT STATEMENT 17 (18)<br /> 1 HASH GROUP BY 17 (18)<br />* 2 HASH JOIN 15 (7)<br /> 3 TABLE ACCESS FULL PRODUCTS 9 (0)<br /> 4 PARTITION RANGE ALL 5 (0)<br /> 5 TABLE ACCESS FULL SALES 5 (0)<br />-------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br />2 - access("P"."PROD_ID"="S"."PROD_ID")<br /></pre><br /><br /><br /><pre>select plan_table_output from<br />table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));<br /><br />----------------------------------------------------------------------------<br /> Id Operation Name Rows Time Pstart Pstop<br />----------------------------------------------------------------------------<br /> 0 SELECT STATEMENT 4 00:00:01 <br /> 1 HASH GROUP BY 4 00:00:01 <br />* 2 HASH JOIN 960 00:00:01 <br /> 3 TABLE ACCESS FULL PRODUCTS 766 00:00:01 <br /> 4 PARTITION RANGE ALL 960 00:00:01 1 16<br /> 5 TABLE ACCESS FULL SALES 960 00:00:01 1 16<br />----------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br />2 - access("P"."PROD_ID"="S"."PROD_ID")<br /></pre><br /><p><strong></strong> </p><p><span style="font-size:130%;">Note Section</span></p><br />In addition to the plan, the package displays notes in the NOTE section, such as that dynamic sampling was used during query optimization or that star transformation was applied to the query.<br />For example, if the table SALES did not have statistics then the optimizer will use dynamic sampling and the plan display will report it as follows (see '+note' detail in the query):<br /><br /><br /><pre>select plan_table_output<br />from table(dbms_xplan.display('plan_table',null,'basic +note'));<br /><br />------------------------------------------<br /> Id Operation Name <br />------------------------------------------<br /> 0 SELECT STATEMENT <br /> 1 HASH GROUP BY <br /> 2 HASH JOIN <br /> 3 TABLE ACCESS FULL PRODUCTS<br /> 4 PARTITION RANGE ALL <br /> 5 TABLE ACCESS FULL SALES <br />------------------------------------------<br /><br />Note<br />-----<br />- dynamic sampling used for this statement<br /></pre><br /><br /><p><strong></strong> </p><p><span style="font-size:130%;">Bind peeking</span></p><br /><br />The query optimizer takes into account the values of bind variable values when generation an execution plan. It does what is generally called bind peeking. See the first post in this blog about the concept of bind peeking and its impact on the plans and the performance of SQL statements.<br />As stated earlier the plan shown in V$SQL_PLAN takes into account the values of bind variables while the one shown from using EXPLAIN PLAN does not. Starting with 10gR2, the dbms_xplan package allows the display of the bind variable values used to generate a particular cursor/plan. This is done by adding '+peeked_binds' to the format argument when using display_cursor().<br />This is illustrated with the following example:<br /><br /><br /><pre><br />variable pcat varchar2(50)<br />exec :pcat := 'Women'<br /><br />select PROD_CATEGORY, avg(amount_sold)<br />from sales s, products p<br />where p.PROD_ID = s.PROD_ID<br />and prod_category != :pcat<br />group by PROD_CATEGORY;<br /><br />select plan_table_output<br />from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'));<br /><br />------------------------------------------<br /> Id Operation Name <br />------------------------------------------<br /> 0 SELECT STATEMENT <br /> 1 HASH GROUP BY <br /> 2 HASH JOIN <br /> 3 TABLE ACCESS FULL PRODUCTS<br /> 4 PARTITION RANGE ALL <br /> 5 TABLE ACCESS FULL SALES <br />------------------------------------------<br /><br />Peeked Binds (identified by position):<br />--------------------------------------<br /><br />1 - :PCAT (VARCHAR2(30), CSID=2): 'Women'<br /></pre>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com3tag:blogger.com,1999:blog-3086558868352622884.post-77247540263041787312008-01-22T15:34:00.001-08:002009-11-17T15:11:30.098-08:00Improvement of AUTO sampling statistics gathering feature in Oracle 11g<div style="text-align: justify;">Optimizer statistics in Oracle are managed via a pl/sql package, dbms_stats. It provides several pl/sql procedures to gather statistics for a table, schema, or a database. For example, gather_table_statistics is used to gather statistics on a table. This procedure has an estimate_percent parameter, which specifies the sampling percentage of the statistics gathering. The users can specify any number between 0 ~ 100 for this parameter. For example, suppose you have a table BIGT, you can specify a 1% sampling percentage as follows: </div> <div style="text-align: justify;"><br /><pre>exec dbms_stats.gather_table_stats(null, 'BIGT', <br /> estimate_percent => 1);</pre><br /><br />It is not always easy for users to pick the appropriate sampling percentage. If the specified sampling percentage is too high, it can take longer to gather statistics. On the contray, if the data is skewed and the specified sampling percentage is too low, the resulting statistics can be inaccurate.<br /><br />For this reason, Oracle introduced the AUTO value for the estimate_percent parameter. For example, you can gather statistics on BIGT as follows:<br /><br /><pre>exec dbms_stats.gather_table_stats(null, 'BIGT', <br /> estimate_percent => dbms_stats.auto_sample_size);</pre><br /><br /></div> <div style="text-align: justify;">The advantage of using AUTO sample size over a fixed number is two-folds. First, when AUTO sample size is specified, the system automatically determines the appropriate sampling percentage. Second, AUTO sample size is more flexible than a fixed sampling percentage. A fixed sampling percentage size that was good at some point in time may not be appropriate after the data distribution in the table has changed. On the other hand when AUTO value is used Oracle will adjust the sample size when the data distribution changes. </div> <div style="text-align: justify;"><br />When AUTO is used Oracle picks a sample size where the statistics quality is good enough. However, it does not work very well under cases of extreme skew in the data. In Oracle 11g, we improved the behavior when the AUTO value is used. First, AUTO sampling now generates deterministic statistics. Second, and more importantly, AUTO sampling generates statistics that are almost as accurate as 100% sampling but takes much less time than 100% sampling. To illustrate these merits, we compare the performance of using a fixed sampling percentage, AUTO sample size in Oracle 10g and AUTO sample size in Oracle 11g.<br /><br />We used the standard TPC-D data generator to generate a Lineitem table. The Lineitem table is about 230G and contains 1.8 million rows with 16 columns. The schema of the lineitem table is as follows:<br /><br /><table border="1"><tr><td>column name</td><td>column type</td></tr><tr><td>l_shipdate</td><td>date</td></tr><tr><td>l_orderkey</td><td>number</td></tr><tr><td>l_discount</td><td>number</td></tr><tr><td>l_extendedprice</td><td>number</td></tr><tr><td>l_suppkey</td><td>number</td></tr><tr><td>l_quantity</td><td>number</td></tr><tr><td>l_returnflag</td><td>varchar2</td></tr><tr><td>l_partkey</td><td>number</td></tr><tr><td>l_linestatus</td><td>varchar2</td></tr><tr><td>l_tax</td><td>number</td></tr><tr><td>l_commitdate</td><td>date</td></tr><tr><td>l_receiptdate</td><td>date</td></tr><tr><td>l_shipmode</td><td>varchar2</td></tr><tr><td>l_linenumber</td><td>number</td></tr><tr><td>l_shipinstruct</td><td>varchar2</td></tr><tr><td>l_comment</td><td>varchar2</td></tr></table><br />Table 1 gives the elapsed time of gathering statistics on the <span class="blsp-spelling-error" id="SPELLING_ERROR_4">Lineitem</span> table by different sampling percentages.<br /><br /><table border="1"><tr><td>Sampling Percentage</td><td>Elapsed Time (sec) </td></tr><tr><td>1% sampling </td><td>797</td></tr><tr><td>100% sampling (Compute)</td><td>18772</td></tr><tr><td>Auto sampling in Oracle 10g </td><td>2935</td></tr><tr><td>Auto sampling in Oracle 11g </td><td>1908</td></tr></table><br /><br />Table 1: Statistics gathering time on 230G <span class="blsp-spelling-error" id="SPELLING_ERROR_5">TPC</span>-D <span class="blsp-spelling-error" id="SPELLING_ERROR_6">Lineitem</span> Table Using Different Estimate Percentages<br /><br />We also compare the quality of the statistics gathered using different estimate percentages. Among all the statistics of a column, number of distinct values (NDV) is the one whose accuracy used to be an issue. We define the accuracy rate of NDV of a column as follows:<br /><br /><pre><br />accuracy rate = 1 - (|estimated NDV - actual NDV|)/actual NDV. </pre><br /><br />The accuracy rate ranges from 0% to 100%. The higher the accuracy rate is, the more accurate the gathered statistics are. Since 100% sampling always lead to an accuracy rate of 100%, we do not report it. We focus on the columns which has at least one statistics accuracy rate below 99.9% when using different estimate percentages. Table 2 illustrates the accurate rates of the columns.<br /><br /><table border="1"><tr><td>Column Name</td><td>Actual NDV </td><td>Auto Sampling in Oracle 11g </td><td> 1% Sampling </td></tr><tr><td> orderkey </td><td>450,000,000</td><td> 98.0% </td><td> 50% </td></tr><tr><td> comment </td><td>181,122,127</td><td> 98.60% </td><td> 4.60% </td></tr><tr><td> partkey </td><td>60,000,000</td><td> 99.20% </td><td> 98.20%</td></tr><tr><td> suppkey </td><td>3,000,000 </td><td> 99.60% </td><br /><td> 99.90% </td></tr><tr><td> extendedprice </td><td>3,791,320</td><td> 99.60% </td><td> 94.30% </td></tr></table><br /><br />Table 2: Accuracy Rate of Gathering <span class="blsp-spelling-error" id="SPELLING_ERROR_15">NDV</span> <span class="blsp-spelling-error" id="SPELLING_ERROR_16">LineItem</span> Using Different Estimate Percentages<br /><br />In short, the elapsed time of ''AUTO sampling in Oracle 11g'' is 10 times faster than 100% sampling but yields the statistics of similar quality (accuracy rate close to 100%).<br /><br /><br /></div>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com5tag:blogger.com,1999:blog-3086558868352622884.post-59714076388953717832007-12-11T13:00:00.000-08:002009-11-17T15:13:27.003-08:00Outerjoins in OracleSince release 6, Oracle has supported a restricted form of left outerjoin, which uses Oracle-specific syntax. In 9i, Oracle introduced support for ANSI SQL 92/99 syntax for inner joins and various types of outerjoin. The Oracle syntax for left outerjoin and that of ANSI SQL 92/99 are not equivalent, as the latter is more expressive.<br /><br />There appears to be some confusion about equivalence between ANSI outer join and Oracle outer join syntaxes. The following examples explain the equivalences and inequivalences of these two syntaxes.<br /><br /><strong>Oracle-Specific Syntax</strong><br /><br />Consider query A, which expresses a left outerjoin in the Oracle syntax. Here T1 is the left table whose non-joining rows will be retained and all non-joining rows of T2 will be null appended.<br /><br /><br />A.<br /> SELECT T1.d, T2.c<br /> FROM T1, T2<br /> WHERE T1.x = T2.x (+);<br /><br /><br />ANSI Left Outerjoin<br /><br />In the ANSI outer join syntax, query A can be expressed as query B.<br /><br /><br />B.<br /> SELECT T1.d, T2.c<br /> FROM T1 LEFT OUTER JOIN T2<br /> ON (T1.x = T2.x);<br /><br /><br /><strong>Equivalence</strong><br /><br />Consider the following queries. In the Oracle semantics, the presence of (+) on the filter predicate (e.g., T2.y (+) > 5 in query C) indicates that this filter must be applied to the table T2 before the outer join takes place.<br /><br /> <br />C.<br /> SELECT T1.d, T2.c<br /> FROM T1, T2<br /> WHERE T1.x = T2.x (+) and T2.y (+) > 5;<br /><br /><br />The ANSI left outer join query D is equivalent to C. Applying the filter on the right table in the left outer join is the same as combining the filter with the join condition.<br /><br /><br />D.<br /> SELECT T1.d, T2.c<br /> FROM T1 LEFT OUTER JOIN T2<br /> ON (T1.x = T2.x and T2.y > 5);<br /><br /><br />Similarly, the presence of (+) on the filter predicate, T2.y (+) IS NULL, in query E indicates that this filter must be applied to the table T2 before the outer join takes place.<br /><br /><br />E.<br /> SELECT T1.d, T2.c<br /> FROM T1, T2<br /> WHERE T1.x = T2.x (+) and T2.y (+) IS NULL;<br /><br /><br />The ANSI left outer join query F is equivalent to E.<br /><br /><br />F.<br /> SELECT T1.d, T2.c<br /> FROM T1 LEFT OUTER JOIN T2<br /> ON (T1.x = T2.x and T2.y IS NULL);<br /><br /><br />Consider query G. Oracle will apply the filter, T2.y IS NULL, in query G after the outer join has taken place. G will return only those rows of T2 that failed to join with T1 or those whose T2.y values happen to be null.<br /><br /><br />G.<br /> SELECT T1.d, T2.c<br /> FROM T1, T2<br /> WHERE T1.x = T2.x (+) and T2.y IS NULL;<br /><br /><br />The ANSI left outer join query H is equivalent to G, as the WHERE clause in H is applied after the left outer join is performed based on the condition specified in the ON clause.<br /><br /><br />H.<br /> SELECT T1.d, T2.c<br /> FROM T1 LEFT OUTER JOIN T2<br /> ON (T1.x = T2.x)<br /> WHERE T2.y IS NULL;<br /><br /><br />Consider query I, where the filter on the left table is applied before or after the outer join takes place.<br /><br /><br />I.<br /> SELECT T1.d, T2.c<br /> FROM T1, T2<br /> WHERE T1.x = T2.x (+) and T1.Z > 4;<br /><br /><br />The ANSI left outer join query J is equivalent to query I.<br /><br /><br />J.<br /> SELECT T1.d, T2.c<br /> FROM T1 LEFT OUTER JOIN T2<br /> ON (T1.x = T2.x)<br /> WHERE T1.Z > 4;<br /><br /><br /><strong>Lateral Views</strong><br /><br />In Oracle, ANSI left and right outerjoins are internally expressed in terms of left outerjoined lateral views. In many cases, a left outerjoined lateral view can be merged and the ANSI left (or right) outerjoin can be expressed entirely in terms of Oracle native left outerjoin operator. (A lateral view is an inline view that contains correlation referring to other tables that precede it in the FROM clause.)<br /><br />Consider the ANSI left outer join query K, which is first represented internally as L.<br /><br /><br />K.<br /> SELECT T1.d, T2.c<br /> FROM T1 LEFT OUTER JOIN T2<br /> ON (T1.x = T2.x and T2.k = 5);<br /><br />L.<br /> SELECT T1.d, LV.c<br /> FROM T1,<br /> LATERAL (SELECT T2.C<br /> FROM T2<br /> WHERE T1.x = T2.x and T2.k = 5)(+) LV;<br /><br /><br />The lateral view in query L is merged to yield query M.<br /><br /><br />M.<br /> SELECT T1.d, T2.c<br /> FROM T1, T2<br /> WHERE T1.x = T2.x (+) and T2.k (+)= 5;<br /><br /><br />Consider query N, which expresses a left outerjoin in the ANSI join syntax. Currently query N cannot be expressed using the Oracle native left outer join operator.<br /><br /><br />N.<br /> SELECT T1.m, T2.n<br /> FROM T1 LEFT OUTER JOIN T2<br /> ON (T1.h = 11 and T1.y = T2.y)<br /> WHERE T1.q > 3;<br /><br /><br />The query N is converted into query O with a left outer-joined lateral view. The lateral view in O cannot be merged, since the filter on the left table specified in the ON clause must be part of the left outerjoin condition.<br /><br /><br />O.<br /> SELECT T1.m, LV.n<br /> FROM T1,<br /> LATERAL(SELECT T2.n<br /> FROM T2<br /> WHERE T1.h = 11 and T1.y = T2.y)(+) LV<br /> WHERE T1.q > 3;<br /><br /><br />Consider query P with a disjunction in the outer join condition. Currently N cannot be expressed using the Oracle native left outer join operator.<br /><br /><br />P.<br /> SELECT T1.A, T2.B<br /> FROM T1 LEFT OUTER JOIN T2<br /> ON (T1.x = T2.x OR T1.Z = T2.Z);<br /><br /><br />The query P will be converted into Q with a left outer-joined lateral view containing the disjunctive join condition.<br /><br /><br />Q.<br /> SELECT T1.A, LV.B<br /> FROM T1,<br /> LATERAL (SELECT T2.B<br /> FROM T2<br /> WHERE T1.x = T2.x OR T1.Z = T2.Z) (+) LV;<br /><br /><br /><strong>ANSI Full Outerjoin</strong><br /><br />Before Oracle 11gR1 all ANSI full outerjoins were converted into a UNION ALL query with two branches, where one branch contained a left outerjoined lateral view and the other branch contained a NOT EXISTS subquery. A native support for hash full outerjoin was introduced in 11gR1 to overcome this problem. When the native full outerjoin, cannot be used, Oracle reverts to the pre-11gR1 strategy.<br /><br />Consider query R, which specifies an ANSI full outerjoin.<br /><br /><br />R.<br /> SELECT T1.c, T2.d<br /> FROM T1 FULL OUTER JOIN T2<br /> ON T1.x = T2.y;<br /><br /><br />Before 11gR1, Oracle would internally convert query R into S.<br /><br /><br />S.<br /> SELECT T1.c, T2.d<br /> FROM T1, T2<br /> WHERE T1.x = T2.y (+)<br /> UNION ALL<br /> SELECT NULL, T2.d<br /> FROM T2<br /> WHERE NOT EXISTS<br /> (SELECT 1 FROM T1 WHERE T1.x = T2.y);<br /><br /><br />With the native support of hash full outerjoin, R will be simply expressed as query T, where the view, VFOJ, is considered unmergeable.<br /><br /><br />T.<br /> SELECT VFOJ.c, VFOJ.d<br /> FROM (SELECT T1.c, T2.d<br /> FROM T1, T2<br /> WHERE T1.x F=F T2.y) VFOJ;<br /><br /><br /><strong>Conversion of Outerjoin into Inner Join</strong><br /><br />Consider query U. Here the filter predicate on the outer-joined table T2 does not contain the outerjoin operator (+); thus it will be applied after the left outerjoin has taken place. This will result in the elimination of all null appended rows of T2. Hence, Oracle converts the outer join into an inner join.<br /><br /><br />U.<br /> SELECT T1.d, T2.c<br /> FROM T1, T2<br /> WHERE T1.x = T2.x (+) and T2.y > 5;<br /><br /><br />The ANSI left outer join query V is equivalent to query U, as the WHERE clause in V is applied after the left outer join is performed based on the condition specified in the ON clause.<br /><br /><br />V.<br /> SELECT T1.d, T2.c<br /> FROM T1 LEFT OUTER JOIN T2<br /> ON (T1.x = T2.x)<br /> WHERE T2.y > 5;<br /><br /><br />Oracle converts the queries U and V into query W with an inner join.<br /><br /><br />W.<br /> SELECT T1.d, T2.c<br /> FROM T1, T2<br /> WHERE T1.x = T2.x and T2.y > 5;<br /><br /><br /><br /><strong>Q&A</strong><br /><br />Q1: I do not understand the queries N and O. What is the difference between<br /> a filter appearing in the ON Clause or a filter appearing in the WHERE<br /> clause?<br /><br />A1: Consider two tables T11 and T22.<br /><br />T11:<br /> A | B<br /> 1 | 2<br /> 2 | 3<br /> 3 | 5<br /><br />T22: <br /> X | Y<br /> 7 | 2<br /> 8 | 4<br /> 9 | 4<br /><br />The following ANSI left outer join query N’ involving <br />the tables T11 and T22 will return three rows, since <br />the filter, which always fails, is part of the join <br />condition. Although this join condition, which <br />comprises both the predicates in the ON clause, <br />always evaluates to FALSE, all the rows of the left <br />table T11 are retained in the result.<br /><br />N’.<br />SELECT *<br />FROM T11 LEFT OUTER JOIN T22 <br /> ON (T11.A > 9 and T11.B = T22.Y);<br /><br /> A B X Y<br /> ------ ---------- ---------- ---------<br /> 1 2<br /> 2 3<br /> 3 5 <br /> <br />However, if the filter, T11.A > 9, is moved to the WHERE clause, <br />the query will return zero rows.<br /><br /><br />Q2: Is the outer to inner join conversion a new feature?<br /><br />A2: No. This feature has been avaliable since Release 7.<br /><br /><br />Q3: Has native full outer join been made available in <br /> versions prior to 11gR1?<br /><br />A3: Yes. It is available in 10.2.0.3 and 10.2.0.4, but not by<br /> default.Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com10tag:blogger.com,1999:blog-3086558868352622884.post-52419143529053773582007-12-03T15:05:00.000-08:002009-11-17T15:12:39.011-08:00Why are there more cursors in 11g for my query containing bind variables?<span style="line-height: 1.6em;">Oracle introduced a new feature, adaptive cursor sharing, in 11g, to improve the plans that are selected for queries containing bind variables. This feature can result in more cursors for the same query containing bind variables. We'll explain why in this article. Before we get into the details, let's review a little history.<br /><br />Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables.<br /><br />However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, it is not guaranteed that this plan will be good for all possible values for the bind variable. In other words, the plan is optimized for the peeked value of the bind variable, but not for all possible values.<br /><br />In 11g, the optimizer has been enhanced to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value. Let's look at an example to see exactly how this works.<br /><br />Assume I have simple table <span style="font-weight: bold;">emp</span> which has 100,000 rows and has one index called <span style="font-weight: bold;">emp_i1</span> on <span style="font-weight: bold;">deptno</span> column.<br /><span style="line-height: 1.2em;font-family:courier new;font-size:90;" ><br /><pre>SQL> desc emp<br /><br />Name Null? Type<br />---------------------- -------- ----------------------------------<br />ENAME VARCHAR2(20)<br />EMPNO NUMBER<br />PHONE VARCHAR2(20)<br />DEPTNO NUMBER<br /></pre></span><br />There is a data skew in the <span style="font-weight: bold;">deptno</span> column, so when I gathered statistics on the <span style="font-weight: bold;">emp</span> table, Oracle automatically created a histogram on the <span style="font-weight: bold;">deptno</span> column.<br /><span style="line-height: 1.2em;font-size:90;" ><span style="font-family:courier new;"><br /><pre>SQL> select table_name, column_name, histogram from user_tab_cols;<br /><br />TABLE_NAME COLUMN_NAME HISTOGRAM<br />------------------ ------------------ ---------------<br />EMP DEPTNO HEIGHT BALANCED<br />EMP EMPNO NONE<br />EMP ENAME NONE<br />EMP PHONE NONE<br /></pre></span></span><br /><br />Now I will execute a simple select on my emp table, which has a single WHERE<br />clause predicate on the <span style="font-weight: bold;">deptno</span> column. The predicate contains a bind variable. We will begin by using the value 9 for this bind variable. The value 9 occurs 10 times in the table, i.e. in 0.0001% of the rows.<br /><span style="line-height: 1.2em;font-size:90;" ><span style="font-family:courier new;"><br /><pre>SQL> exec :deptno := 9<br /><br />SQL> select /*ACS_1*/ count(*), max(empno)<br />2 from emp<br />3 where deptno = :deptno;<br /><br /><br />COUNT(*) MAX(EMPNO)<br />---------- ----------<br /> 10 99</pre></span></span>Given how selective the value 9 is, we should expect to get an index range scan for this query. Lets check the execution plan.<br /><span style="line-height: 1.2em;font-size:90;" ><span style="font-family:courier new;"><br /><pre>SQL> select * from table(dbms_xplan.display_cursor);<br /><br />PLAN_TABLE_OUTPUT<br />-------------------------------------------------------------------------<br />SQL_ID 272gr4hapc9w1, child number 0<br />------------------------------------------------------------------------<br />select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno<br /><br />Plan hash value: 3184478295<br />------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|<br />------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 2 (100)|<br />| 1 | SORT AGGREGATE | | 1| 16 | |<br />| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|<br />| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|<br />------------------------------------------------------------------------<br /></pre><br /></span></span><br />So we got the index range scan that we expected. Now let's look at the execution statistics for this statement<br /><span style="line-height: 1.2em;font-size:90;" ><span style="font-family:courier new;"><br /><pre>SQL> select child_number, executions, buffer_gets,<br />2 is_bind_sensitive, is_bind_aware<br />3 from v$sql<br />4 where sql_text like 'select /*ACS_1%';<br /><br />CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE<br />------------ ---------- ----------- ----------------- -------------<br /> 0 1 53 Y N<br /></pre></span></span><br />You can see we have one child cursor that has been executed once and has a small number of buffer gets. We also see that the cursor has been marked bind sensitive. A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for. This cursor was marked bind sensitive because the histogram on the deptno column was used to compute the selectivity of the predicate "where deptno = :deptno". Since the presence of the histogram indicates that the column is skewed, different values of the bind variable may call for different plans.<br /><br />Now let's change the value of the bind variable to 10, which is the most popular value for the deptno column. It occurs 99900 times in the table, i.e in 99.9% of the rows.<br /><span style="line-height: 1.2em;font-size:90;" > <span style="font-family:courier new;"><br /><pre>SQL> exec :deptno := 10<br /><br />SQL> select /*ACS_1*/ count(*), max(empno)<br />2 from emp<br />3 where deptno = :deptno;<br /><br />COUNT(*) MAX(EMPNO)<br />---------- ----------<br /> 99900 100000<br /></pre></span></span><br />We expect to get the same plan as before for this execution because Oracle initially assumes it can be shared. Let's check:<br /><span style="line-height: 1.2em;font-size:90;" ><span style="font-family:courier new;"><br /><pre>SQL> select * from table(dbms_xplan.display_cursor);<br /><br />PLAN_TABLE_OUTPUT<br />------------------------------------------------------------------------<br />SQL_ID 272gr4hapc9w1, child number 0<br />------------------------------------------------------------------------<br />select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno<br /><br />Plan hash value: 3184478295<br />------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|<br />------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 2 (100)|<br />| 1 | SORT AGGREGATE | | 1| 16 | |<br />| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|<br />| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|<br />------------------------------------------------------------------------<br /></pre></span></span><br />The plan is still an index range scan as before, but if we look at the execution statistics, we should see two executions and a big jump in the number of buffer gets from what we saw before.<br /><span style="line-height: 1.2em;font-size:90;" ><span style="font-family:courier new;"><br /><pre>SQL> select child_number, executions, buffer_gets,<br /> 2 is_bind_sensitive, is_bind_aware<br /> 3 from v$sql<br /> 4 where sql_text like 'select /*ACS_1%';<br /><br />CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE<br />------------ ---------- ----------- ----------------- -------------<br /> 0 2 1007 Y N<br /></pre></span></span><br />You should also note that the cursor is still only marked bind sensitive and not bind aware at this point. So let's re-execute the statement using the same popular value, 10.<br /><span style="line-height: 1.2em;font-family:courier new;font-size:90;" ><br /><pre>SQL> exec :deptno := 10<br /><br />SQL> select /*ACS_1*/ count(*), max(empno)<br />2 from emp<br />3 where deptno = :deptno;<br /><br />COUNT(*) MAX(EMPNO) <br />---------- -----------<br />99900 100000<br /></pre></span><br />Behind the scenes during the first two executions, Oracle was monitoring the behavior of the queries, and determined that the different bind values caused the data volumes manipulated by the query to be significantly different. Based on this difference, Oracle "adapts" its behavior so that the same plan is not always shared for this query. Hence a new plan is generated based on the current bind value, 10.<br /><br />Let's check what the new plan is.<br /><span style="line-height: 1.2em;font-size:90;" ><span style="font-family:courier new;"><br /><pre>SQL> select * from table(dbms_xplan.display_cursor);<br /><br />PLAN_TABLE_OUTPUT<br />--------------------------------------------------------------------<br />SQL_ID 272gr4hapc9w1, child number 1<br />--------------------------------------------------------------------<br />select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno<br /><br />Plan hash value: 2083865914<br />-------------------------------------------------------------------- <br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|<br />--------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 240 (100)|<br />| 1 | SORT AGGREGATE | | 1 | 16 | |<br />|* 2 | TABLE ACCESS FULL | EMP | 95000 | 1484K | 240 (1)|<br />-------------------------------------------------------------------- <br /></pre></span></span><br /><br />Given how unselective the value 10 is in the table, it's not surprising that the new plan is a full table scan. Now if we display the execution statistics we should see an additional child cursor (#1) has been created. Cursor #1 should show a number of buffers gets lower than cursor #0 and it is marked both bind sensitive and bind aware. A bind aware cursor may use different plans for different bind values, depending on how selective the predicates containing the bind variable are.<br /><br />Looking at the execution statistics:<br /><span style="line-height: 1.2em;font-size:90;" ><span style="font-family:courier new;"><br /><pre>SQL> select child_number, executions, buffer_gets,<br /> 2 is_bind_sensitive, is_bind_aware<br /> 3 from v$sql<br /> 4 where sql_text like 'select /*ACS_1%';<br /><br />CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE<br />------------ ---------- ----------- ----------------- -------------<br /> 0 2 1007 Y N<br /> 1 1 821 Y Y <br /></pre></span></span><br />we see that there is a new cursor, which represents the plan which uses a table scan. But if we execute the query again with a more selective bind value, we should use the index plan:<br /><span style="line-height: 1.2em;font-size:90;" ><span style="font-family:courier new;"><br /><pre>SQL> exec :deptno := 9<br /><br />SQL> select /*ACS_1*/ count(*), max(empno)<br />2 from emp<br />3 where deptno = :deptno;<br /><br />COUNT(*) MAX(EMPNO)<br />---------- ----------<br /> 10 99<br /></pre></span></span><span style="line-height: 1.2em;font-size:90;" ><span style="font-family:courier new;"><br /><pre>SQL> select * from table(dbms_xplan.display_cursor);<br /><br />PLAN_TABLE_OUTPUT<br />------------------------------------------------------------------------<br />SQL_ID 272gr4hapc9w1, child number 2<br />------------------------------------------------------------------------<br />select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno<br /><br />Plan hash value: 3184478295<br />------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|<br />------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 2 (100)|<br />| 1 | SORT AGGREGATE | | 1| 16 | |<br />| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|<br />| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|<br />------------------------------------------------------------------------<br /></pre><br /></span></span> The proper plan was chosen, based on the selectivity produced by the current bind value.<br /><br />There is one last interesting thing to note about this. If we look at the execution statistics again, there are three cursors now:<br /><span style="line-height: 1.2em;font-size:90;" ><span style="font-family:courier new;"><br /><pre><br />SQL> select child_number, executions, buffer_gets,<br /> 2 is_bind_sensitive, is_bind_aware, is_shareable<br /> 3 from v$sql<br /> 4 where sql_text like 'select /*ACS_1%';<br /><br />CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_B_SENS IS_B_AWAR IS_SHAR<br />------------ ---------- ----------- --------- --------- ----------<br /> 0 2 957 Y N N<br /> 1 1 765 Y Y Y<br /> 2 2 6 Y Y Y<br /></pre></span></span><br />The original cursor was discarded when the cursor switched to bind aware mode. This is a one-time overhead. Note that the cursor is marked as not shareable (is_shareable is "N"), which means that this cursor will be among the first to be aged out of the cursor cache, and that it will no longer be used. In other words, it is just waiting to be garbage collected.<br /><br />There is one other reason that you may notice additional cursors for such a query in 11g. When a new bind value is used, the optimizer tries to find a cursor that it thinks will be a good fit, based on similarity in the bind value's selectivity. If it cannot find such a cursor, it will create a new one (like above, when one (#1) was created for unselective "10" and one (#2) was created for highly-selective "9"). If the plan for the new cursor is the same as one of the existing cursors, the two cursors will be merged, to save space in the cursor cache. This will result in one being left behind that is in a not shareable state. This cursor will be aged out first if there is crowding in the cursor cache, and will not be used for future executions.<br /><br /></span><span style="line-height: 1.6em;"><span style="font-weight: bold;">Q & A<br /></span>Instead of answering the questions in your comments one by one, I am going to summarize the questions and provide my answers here.<br /><br />Q: Is this behavior managed by 11g optimizer automatically and we don't need cursor_sharing anymore?<br />A: We have not changed the behavior of the cursor_sharing parameter yet, for backwards compatibility purposes. So if you set it to similar, adaptive cursor sharing will only kick in for queries where the literals are replace with binds. We hope that in the future, this feature will persuade people to set cursor_sharing to force.<br /><br />Q: Would it have any impact like holding library cache latches for longer time to search for appropriate child cursor.<br />A: Any additional overhead in matching a cursor is always a concern, and we strive to minimize the impact. There is of course some increase in the code path to match a bind-aware cursor, since it requires more intelligent checks. This feature should not, however, impact cursors which are not yet marked bind-aware.<br /><br />Q: What triggers a cursor to be marked "bind sensitive"?<br />A: Our goal is to consider many types of predicates where the selectivity can change when the bind value changes. </span>In this first version of the feature, we only handle equality predicates where a histogram exists on the column and range predicates (with or without histogram). <span style="line-height: 1.6em;"> We do not currently consider LIKE predicates, but it is on the top of our list for future work.<br /><br />Q: Also it sounds like the optimizer is using the number of rows returned to decided that it's time for a new plan...<br />A: I am not going to go into the details of the "special sauce" for how we decide to mark a cursor bind-aware. The number of rows processed is one input.<br /><br />Q: Are you planning a hint to mark statements as bind-aware ?<br />A: Yes, we plan to add this in the future. This will allow users to bypass the startup cost of automatically determining that a query is a good candidate for bind-aware cursor sharing.</span>Optimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.com16