Oracle Scratchpad

Public Appearances

 

 

20th – 23rd Nov 2018 Nürnberg, Germany: I’ll have two sessions at the DOAG conference 2018. My sessions are scheduled for 9:00 am and 10:00 am on Thursday 22nd. They take the masterclass that I will be presenting at the UKOUG conference in December “The Basics of Understanding Execution Plans” and split it into two pieces of 45 minutes each.
2nd – 5th Dec 2018 Liverpool, England: The UKOUG Annual Conference Tech 18 will be held in Liverpool this year. I’ll be presenting a standard session on Statistics (Monday 13:35) a masterclass (double session) on Execution Plans (Tuesday 09:00) and I’ve also been asked to chair the ever-popular panel session on the Cost Based Optimizer (Tuesday 15:20) as well as a round-table on CBO (Tuesday 12:35).

And in a late-breaking arrangement I’ll be doing a session on reading the 10053 (CBO) trace file on “Super Sunday” at 3:00 pm

Presentation Abstracts:

You probably don’t need to read a 10053 trace file.

It’s almost true to say that the only time you should read a 10053 trace file is when you don’t need to. It’s also probably a fairly accurate claim that the only good reason for reading a 10053 trace file is to prove that you didn’t need to read the 10053 trace file.

In all my years of consulting and trouble-shooting I think I’ve only used the 10053 trace file a couple of times to demonstrate the cause of a performance issue and suggest a work-around – but having spent a silly amount of time generating and reviewing trace files for increasingly complicated statements I found it easy to pick the bit of the trace file I needed to examine to confirm a hypothesis and explain the evidence to management (and Oracle Support).

The Basics of Understanding Execution Plans

This is a masterclass for those who haven’t yet mastered execution plans. No rocket science, no exotic surprises, no cunning plans, just 2 solid hours of the most common everyday requirements. The session will be restricted to basic, serial execution plans, we will not be examining parallelism, partitioning, or distributed execution.

Being able to read and interpret execution plans is one of the top skills you need to master to be able to solve performance problems, but it’s easy to spend a lot of time following the wrong ideas if you don’t know where the traps are. The topics I’ll cover are:

  • the options and traps when finding or creating an execution plan, particularly the threats of explain plan and autotrace.
  • the purpose of each of the areas of the full execution plan and the extreme importance of the predicate section
  • the “first child first” algorithm, the small group of exceptions, and the error in “near the top, over to the right”
  • SQL that “distorts” execution plans – GTTs, WITH subqueries, constant subqueries, pushed subqueries, inline scalar subqueries
  • the meaning of Cost, and Cost compared with Time; E-rows vs. A-rows; and the correlation between A-Rows and Starts
  • the difference between Access predicates and Filter predicates
  • A couple of bugs that cloud the interpretation of a plan.

By the end of this session you will know enough about execution plans to avoid most of the common errors that I see on-line as people try to work out why a query is doing too much work and taking a long time to run.

Struggling with Statistics

When someone requests help on the Oracle forums to address the problem of a query picking a bad execution plan, one of the commonest (and most rapid) responses is the suggestion to make sure the statistics are up to date. Sometimes this will solve the problem, sometimes it won’t solve the problem but will produce a change that makes it easier to identify the problem, sometimes it just won’t help at all.

“Up to date” statistics, “accurate” statistics and histograms aren’t necessarily what you need to get Oracle to produce the execution plan you want, and in this presentation we look at some of the ways in which “good” statistics are not “good enough”, and come up with some strategies for recognising when we have to work around Oracle’s statistics and how we can work around them with the minimum of effort and risk.

16th/17th Jan 2019 London, UK: Oracle OpenWorld is coming to Europe. If there’s a call for papers from the public then I’ll submit an abstract just in case the organisers aren’t going to stick with nothing but Cloud.
7th/8th Feb 2019 Belgium: OBUG is aiming to host an Oracle Database Tech Event on or near these dates, and I’ve submitted a couple of abstracts:

Why Oracle statistics aren’t enough.

No matter how recently you collected the most accurate possible statistics that Oracle’s routines allow you to gather, there are still many cases where you can’t leave it all to Oracle to get the right execution plan. This presentation describes several of the errors and omissions in Oracle’s stats collection, tells you how to identify them, and suggests the best ways to work around them.

Reading Execution Plans.

“Near the top, over to the right” is an ancient rule of thumb for working out what happened first. It’s a common side effect of a much better soundbite which is “First child first – recursive descent”. The latter is still not accurate though because it applies only to a single query block, and then there are a few special cases. In this presentation we do a quick review of the basic mechanism and then examine the special cases, the extra steps needed to handle multiple query blocks in a single plan, and examine the important question of how many times each step of the plan will happen.

 

 

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.