Webinar Follow-up: Execution Plans - Learn by Example

Thanks to everyone who attended the September 17 webinar!

Presentation PDF
Webinar recording



Q&A
Q: Do all these methods of showing execution plans work (dbms_sqltune, dbms_xplan) with Oracle Standard Edition?  What about creating extended statistics in Oracle SE? Do all these tips work with SE? Is SQL Monitor a licensed product?

A: You cannot use SQL Monitor reports (dbms_sqltune) with Oracle Standard Edition as they are produced using elements included in the Tuning Pack license which is *not* available on SE. However, you can use dbms_xplan without restriction and all the tips for how to read and analyze plans are the same regardless of the method you use to display plan data.

Q: We have a big table with 80 partitions.. for analyze it is taking long time.. is there any easy way analyze can done quickly?

A: You could consider using incremental partition statistics if many of the partitions have data that changes infrequently. Generally speaking, make sure to use the default collection parameters (like estimate_percent=>auto_sample_size) and only collect stats when you really need to (after data changes by > 10%). The following two links to the Optimizer Development Team's blog may also be of some help:
https://blogs.oracle.com/optimizer/entry/maintaining_statistics_on_large_partitioned_tables
https://blogs.oracle.com/optimizer/entry/incremental_statistics_maintenance_what_statistics

Q: How can the order of filters, joins, etc in the where clause be controlled to manually keep a minimum dataset through the execution or just force a different one for educational / what if purposes?

A: You can control plan operations and the order in which they are executed using hints. Simply inject the hints that specify access operations (FULL, INDEX) and join methods (USE_HASH, USE_NL) and join order (LEADING). The more hints you provide, the more control you can apply to the plan operations.

Q: How do we know if the current execution plan is the best plan or if there is ANY execution plan  better  than the current execution plan?

A: Test! Remember that the optimizer has gone through numerous alternatives before settling in on the final plan. If the plan chosen isn't performing as well as you'd like, then you must try to determine alternatives (by using hints to force some choices or by rewriting the SQL or adjusting statistics...). There is also the Visual SQL Tuning method you could use to "map" the best order of operations for a SQL statement (see my July webinar for more on VST). The bottom-line is that you have to test to understand the performance of the chosen plan and then find the reasons why it under-performs and correct those root causes.

Q: Basically the SQL execution is in the AWR SQL history but not in shared pool. and we would like to see the execution plan using dbms_xplan. how can we do that?

A: DBMS_XPLAN.DISPLAY_AWR will do the trick. You'll provide the SQL_ID and PLAN_HASH_VALUE (optionally) and the FORMAT parameters you desire.

Q: is there a way to force FAST FULL INDEX SCAN instead of INDEX FULL SCAN?

A: Hints. The INDEX_FFS (table index) hint would force an INDEX FAST FULL SCAN. Don't use the hint indiscriminately as if the optimizer "thinks" the fast full scan would be better it would have costed it as such and selected that operation. If you believe you should be getting a fast full scan and are not, try and verify why before you hint the SQL.

Q: When will optimizer choose INDEX FAST FULL SCAN over INDEX FULL SCAN?

A: A fast full scan is similar to a full table scan in that it will read all the blocks (using multiblock reads) in the index without maintaining order. You often see this operation when the query result set can be satisfied from the index contents alone without having to do additional data block accesses. The bottom line (and somewhat cheeky response) is that it will be chosen when the optimizer thinks it is the best choice. If you find otherwise, it's going to be up to you to research and test to discover why the optimizer thought it was best when it actually wasn't.

Q: Is there a way to display the lines of execution plan by the order in which the lines are executed, instead of  using the indentation which can be VERY tideous for  100+ lines of SQL statement?

A: My favorite script for doing this from Randolf Geist and you can find it on his blog at http://oracle-randolf.blogspot.com/2011/12/extended-displaycursor-with-rowsource.html. Of course, you could write your own but no need to reinvent the wheel.

Q: In SQL monitor report what do the columns Time Active (s) and start active (S) mean. I never found a good documentation explaining these 2?

A: The Time Active(s) column shows how long the operation has been active (the delta in seconds between the first and the last active time). The
Start Active column shows, in seconds, when the operation in the execution plan started relative to the SQL statement execution start time.

Q: Can you please tell in which case FULL TABLE SCAN is OK to see in the Explain Plan? or IS Full Table Scan is always bad?

A: All operations have a good use case! It's critical to **NOT** assign a "good" or "bad" judgment to any of them. Each operation may be optimal given the context in which it is used. With full table scans, you typically hope to see them being used when a significant amount of data from an object is needed (as determined by the number of blocks that must be accessed in order to retrieve the needed rows).

Q: What are the trade-offs between SQL Tuning Advisor and Execution Plans?

A: I wouldn't say there are any "trade-offs". SQL Tuning Advisor can be used to identify possible changes that could be helpful to your query's performance. One of the options STA may offer is the option to create a SQL Profile. The Profile provides some additional statistical information to the optimizer so it can/should produce a more effective execution plan. I personally think of STA as a tool to point me towards things I need to investigate.

Q: When running an execution plan, I see "- dynamic sampling used for this statement (level=6)" even though all of the objects in the query (table and indexes) have good statistics and optimizer_dynamic_sampling=2.  Is there a way (without setting a 10053 trace) to find out why dynamic sampling was used, and at what part of the plan it was used?

A: From Oracle Database 11g Release 2 onwards the optimizer will automatically decide if dynamic sampling will be useful and what dynamic sampling level will be used for SQL statements executed in parallel. This decision is based on size of the tables in the statement and the complexity of the predicates. However, if the optimizer_dynamic_sampling parameter is explicitly set to a non-default value, then that specified value will be honored. When it does kick in at level 6, it is simply doing a 256 block sample to help the optimizer produce more accurate cardinality estimates on the objects being accessed in parallel.

Q: How do you find out about your extended stats, what query or view do I need?

A: I'm going to point you to a couple of blog articles from the Optimizer Development Team regarding extended stats which should answer this and any other questions you may have on extended stats.
https://blogs.oracle.com/optimizer/entry/extended_statistics
https://blogs.oracle.com/optimizer/entry/how_do_i_know_what_extended_statistics_are_needed_for_a_given_workload

Q: Does extended stats get maintained automatically or does it need to be manually collected again and again.

A: Please see the two links I provided for the previous question for more details. But, generally speaking, once you create an extended statistic, it will continue to be collected (if you use default collection parameters) until you specifically drop the extended stat.

Q: How to effectively trace an execution plan for a given SQL to understand why the Optimizer chose the specific execution plan?

A: You can capture a 10053 optimizer trace of the given SQL and review the trace file. However, that is not something I'd recommend as a primary method! You can "always" know that the optimizer chose a particular set of plan operations because those operations were the lowest costed options considered by the optimizer. So, if you really want to know why, you need to inspect the inputs the optimizer used to cost the various plans. The place to start is with object statistics and the execution plan rowsource statistics. You need to compare estimated cardinalities with the actual rows returned and find where discrepancies exist. When found, the discrepancies will lead you to the statistics you need to review or they can help you see where/how in the SQL the objects that have discrepancies are used. The bottom-line is that it's going to require you to research and evaluate the plan execution data to determine where the optimizer may have gone astray. Only in very rare cases would I resort to a 10053 trace.

Q: If the E-ROWS and A-ROWS differ a lot but still the execution plan did not change in comparing when E-rows and A-rows are matching, will there is a performance difference in the SQL?

A: You can't know that unless you find out why the estimates and actuals are different and take the necessary steps to correct things so that the difference is limited or eliminated. Once you find out why there was a difference and correct it, the plan may change and performance may improve. However, depending on the access paths and join methods available to the optimizer, it is entirely possible that the plan may not change after the estimates are improved and performance would therefore remain the same. 

Q: How to interpret the COST value/estimate of a given SQL?

A: Cost is the value computed by the optimizer that indicates the estimated amount of work (time and resources) required to produce the result set. It is computed based on statistical formulas utilized by the optimizer to assign a value to each viable set of plan operations possible for a given SQL statement. For the most part, cost is not something I focus on as it is a given that the cost of the plan selected by the optimizer was computed to be the lowest of all possible choices. Therefore, if the response time and resource usage for that plan doesn't meet my expectations, my next step is to determine where/how the optimizer "went wrong" in computing the selected option as the best/lowest cost.

Q: What is the difference between explain plan and execution plan?

A: An EXPLAIN PLAN is simply the proposed plan that the optimizer "might" choose when the query is executed. It is *NOT* a guarantee of the plan that will be used at runtime. The execution plan, on the other hand, is the actual plan that was selected by the optimizer and used to produce the query result set. So, the easiest way to differentiate the two is that EXPLAIN PLAN is the estimate, the execution plan is the actual.

Q: How to analyze or find which tables need histograms for the best execution plans depending on bind variable values?

A: When you're considering histograms, you're considering skew in your data. So, you're looking for columns that contain skewed data. If you simply collect statistics using the default METHOD_OPT parameter ('FOR ALL COLUMNS SIZE AUTO'), histograms will be collected automatically for you. Now, the collection may not be perfect, so you may need to use your own knowledge of the data to help you define specific columns that need histograms. Also, if you find that for queries that use binds your performance is wildly variable, that may be a red flag to point you towards columns that need histograms as well as being an indicator that you might need to adjust your use of bind variables to use some literals to help the optimizer make the best plan choices.

Q: Used Memory - what does (0) means?

A: This column displays the sum of the maximum amount of memory that was used in all execution of the specific plan operation.

Q: What are advantages using this tool over oracle RAT?

A: RAT, or Real Application Testing, is simply a way to do two things: 1) capture and replay executions of application code (SQL) for a representative period of workload and 2) compare the performance (i.e. plan changes and resulting differences in response times and resource usage) of an original workload and the workload after some change(s). The 2nd element (known as SQL Performance Analyzer) helps automate the process of comparing before and after execution plans and can highlight plans that change. The plans that change may change for the good (they improve) or for the bad (they regress). SPA captures the changes and helps you to focus in on the plans that regress so that you can do further work to find and correct the issues. STA simply compares performance and plans for the same SQL_IDs and displays the difference. You could do this yourself manually but SPA provides a separately licensable product to do much of the work for you. However, if you find a problem, you'll likely still have to do some work on your own to determine why the plans changed and how to correct them. So, this is not an "either/or" situation. RAT uses execution plans and simply automates a bit of the legwork for you.



Stay tuned for details of my November webinar coming soon! 

SQL Tuning Fundamentals: Execution Plans - September 17 Webinar

Register now and join me for my next webinar entitled "SQL Tuning Fundamentals: Execution Plans".

Few tools are as critical to SQL optimization as the execution plan. The ability to read and understand an execution plan allows us to evaluate and optimize SQL performance. Unfortunately, complex plans often seem daunting and can be difficult to understand. During this webinar, I'll walk you through a set of guidelines for how to read an execution plan and how to make sense of the operations and statistics (both estimated and actual) the plan output provides.

Topics covered in this webinar include:

• How to read an execution plan, and how various plan operations work 
• How to use the plan to pinpoint performance problems 
• Tools to accelerate your analysis of execution plan data