Find it. Fix it. Real-World SQL Tuning Cases - Jan. 13 Webinar Followup

If you missed my January 13th webinar entitled "Find it. Fix it. Real-World SQL Tuning Cases" you can now access the recording and download the presentation file using the following links.

Presentation PDF
Webinar recording




Abstract
There are many ways to find SQL that is performing poorly. The hard part is what to do with a bad SQL statement once you have it. In this session, several real-world examples will be reviewed to help you learn how to evaluate poorly performing SQL. Each example will demonstrate a commonly occurring SQL performance problem and provide a method to solve it.

Key points:
1. Review ways to identify poorly performing SQL.
2. Learn a simple method for evaluating a problem SQL statement.
3. Identify key performance inhibitors in SQL execution plans.
4. Determine the best and simplest solution.

Webinar Followup (Nov. 12) - In Search of Plan Stability - Part 2

Sorry for the delay in getting this posted, but thanks to everyone who attended my November 12th webinar entitled In Search of Plan Stability - Part 2. You can download the presentation materials from these links:

Presentation PDF
Recording

Thanks!

Blog articles at Toad World

I just wanted to point you to the blog articles I've been posting over at Toad World in case you're wondering why there aren't many articles showing up here.

So, go take a look!

Karen's Toad World Blog

Webinar Followup (Aug. 27): In Search of Plan Stability - Part 1

Thanks to everyone who attended my August 27th webinar entitled In Search of Plan Stability - Part 1. You can download the presentation materials from these links:

Presentation PDF
Scripts
Q&A
Recording

Come back in November for Part 2. Hope to see you then!

Thanks!

In Search of Plan Stability (Part 1) - August 27 webinar

Register by clicking here.

Hope you'll join me!

New Oracle Bug alert (Bug 19384287)

Heads up to all the folks running 11.2.0.4 and above if you're using function-based indexes! There's a new Oracle bug 19384287. I'll fill you in with a complete post over at Toad World.

Young Programmer's Camp

I made a short post over at ToadWorld about bringing my nephew to my office in Dallas this week for a Young Programmer's camp. He had a great time as far as I could gauge the excitement of a 16 year old boy (errrrrr....young man)! He rocked it out creating several different game knock-offs and got a feel for what it would be like to be a software developer. Fun stuff!

Tracing Code with DBMS_MONITOR

Take a look at the follow-up to my DBMS_APPLICATION_INFO post over at ToadWorld. I discuss how to use DBMS_MONITOR to trace the specific sections of code you registered with DBMS_APPLICATION_INFO.  

Full article here

Instrumenting Your Code using DBMS_APPLICATION_INFO

Head on over to my post at Toad World to read all the details about instrumenting your code with DBMS_APPLICATION_INFO.

Blogging at Toad World

In order to help me stick to my commitment to blog more regularly, I've joined the blogging community at Toad World. I'll link to most posts from there as I make them (I love knocking down two targets with one arrow) and keep on track for at least one post a week moving forward. Yea!

You can read my brief hello over at Toad World here. Stay tuned in the weeks ahead for a series of posts on Oracle performance, SQL and the optimizer. I'm going to start with a few posts on code instrumentation and move on to cover many of the suggested topics I received from you all. 

Cheers!

Weekly posts - Anyone got any ideas?

I've decided to get back in the groove of making more regular blog posts and have an intention to blog at least once a week in the coming months to get myself back in the habit. While I always seem to have a head full of stuff that never makes it to the blog, I thought I'd ask you all if you have any topic suggestions?

At this point in time, I'm not sure anyone drops by here much since my posts have been mainly announcing webinars and providing follow-ups. But, I figured I'd put it out there and see if anyone still has their ears on.  :)  So, if you've got something you'd like to hear about, leave me a comment and I'll see if I can supply something suitable.

If I don't get any suggestions, I'll just ramble on what happens to be in the front of my brain at the moment.  :)

Cheers everyone!

Writing SQL Right - May 20 webinar wrap-up

Thanks to everyone for attending today's Writing SQL Right webinar sponsored by Embarcadero. For attendees, Embarcadero will send out a link via email to the recording and PDF of the presentation, but I also wanted to post it here.

Presentation PDF
Webinar recording



Thanks again and stay tuned for additional webinars coming soon!

Upcoming Webinar: Writing SQL Right on May 20

Coming up on May 20, I'll be delivering a webinar entitled Oracle SQL Performance: Writing SQL Right. Registration is now open. Once again the webinar will be hosted by Embarcadero and there will be two sessions - one at 10am ET and one at 2pm ET.


Abstract:
There are many ways to write a SQL statement that may lead to the functionally correct answer. However, we often get stuck in a rut using the same SQL syntax over and over even when there may be a better way to write the SQL to enhance performance. Some techniques worked great in previous Oracle versions but changes in the optimizer with the later versions made the behavior of those techniques change and, in many cases, regress.

This session covers a number of common anti-patterns that can be rewritten to provide enhanced performance and will help you learn how to:

• Recognize certain patterns that can be sub-optimal. 
• Analyze the "performance footprint" of certain patterns. 
• Rewrite the anti-patterns to use less resources and take less time to execute. 

I look forward to "seeing" you there!

Effective Indexing Webinar

Thanks to everyone for attending today's Effective Indexing webinar sponsored by IOUG with Embarcadero. For attendees, IOUG will likely send out a link to the recording and PDF of the presentation, but I also wanted to post it here.

Presentation PDF
Webinar recording



Please note that during the webinar I mentioned the new clustering factor calculation available in 12c and also available in some patchsets for 11g releases. The patch number I referred to is incorrect. Instead, please search for the Bug/Enhancement article in MOS. You can find it by searching for "Bug 13262857 - Enh: provide some control over DBMS_STATS index clustering factor computation (Doc ID 13262857.8)."

Thanks again and stay tuned for additional webinars coming soon!

Webinar Follow-up: Everyday Oracle Pro

Thanks to everyone who attended the November 12 webinar!

Presentation PDF
Webinar recording

Becoming an Everyday Oracle Pro - November 12 Webinar

Register for my next webinar on November 12 entitled "Becoming an Everyday Oracle Pro".

About the webinar
Whether you are new to Oracle or a seasoned veteran, you want to do your job to the best of your ability. Each one of us can become an everyday Oracle pro if we strive towards one basic truth: doing something well isn't only about what you know, but about how you apply what you know. Even if you have memorized a lot of information, it's not much good if you can't apply it, and more importantly, understand how and when to apply that knowledge effectively.

You can become an everyday Oracle pro and make even greater contributions to the success and effectiveness of your organization by focusing on a few basic principles.

In this session, you will learn:

• The 3 R's of being an everyday Oracle Pro (Research, Remember, Replicate)
• The difference between memorization and knowledge
• How to think clearly about problem solving
• How to collect and grow your personal collection of helpful tools

This will be the sixth, and final, Embarcadero sponsored webinar for 2013. See you then!

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

My day with the Ohio Oracle Users Group

Thanks so much to everyone who attended the Ohio Oracle User's Group event with me on July 18. I really enjoyed the opportunity to be there. The group appears to be vibrant and growing and everyone involved made it a top-notch experience (for me at least!).

My topic for the day was "SQL Stuff You Should Know" and was a mash-up of lots of different bits and pieces of information about SQL Tuning and the Oracle Optimizer. Links to my presentation slides will be made available on the OOUG web site, but I thought I'd also provide them here.

Downloads:
Presentation slides
Scripts

User groups are a fantastic way to network and have easily accessible and inexpensive opportunities to learn. I'm always happy to be invited to participate in an event and even more happy when I see a thriving community of my Oracle peers! Thanks again OOUG!!

Follow-up: Visual SQL Tuning Webinar

Thanks to everyone who attended my Visual SQL Tuning webinar. My goal was to keep it simple and show the value of using VST to help you know what execution plans "should" do.

Presentation PDF
Webinar recording


I want to thank:
Kyle Hailey for his extensive work on the subject and his recent great detailed VST presentation from KScope13.
Craig Martin for his building join order diagram I used but incorrectly attributed authorship to Kyle (sorry Craig!).

I hope to see everyone in September for my next webinar! Stay tuned for details.