Oracle Scratchpad

Public Appearances



19th/20th Sept 2018 Zurich, Switzerland: I’ll be speaking at the Trivadis Performance Days along with several of the mostly highly skilled and well-informed individuals in the Oracle arena. I will be presenting two topics “Struggling with Statistics” and “Execution Plans – after the first steps”.

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.

Execution Plans – after the first steps

We will spend just a few minutes on a basic review of how to read execution plans using the traditional  “first child first, recursive descent” (or “near the top and over to the right”) method before moving on to more sophisticated details including (a) recognising when this rule doesn’t apply and (b) understanding that the JOIN order doesn’t always match the EXECUTION order and (c) realising that what happens is only part of the problem, and how many times it happens is important and can be understood.

We will make time to look at the effects of scalar subqueries, common table expressions, partitioning and parallel execution.The tools we cover will include a couple of trace events (other than 10046), the parallel query execution stats (v$pq_tqstat), rowsource executions stats, and SQL monitoring.

26th Sept 2018 London, England: I’ve submitted  “Struggling with Statistics part 2” (The second half of the presentation I’ve prepared for Trivadis in September) for the UKOUG SIG event. Part 1 described a number of problems, Part 2 spends more time talking about solutions.
23rd Oct 2018 San Francisco, CA., USA: I won’t be at OpenWorld, or even in town, but I’ll be speaking at Oak Table World in the Children’s Creativity Museum through a remote link. The working title for my presentation is“When Object Statistics aren’t enough”
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.
3rd – 5th Dec 2018 Liverpool, England: The UKOUG Annual Conference Tech 18 will be held in Liverpool this year. I’ll be presenting a masterclass (double session) on Execution Plans and a session on Statistics. I’ve also been asked to chair the ever-popular panel session on the Cost Based Optimizer, as well as a round-table on CBO.

Presentation Abstracts:

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

This will be a shorter version of the presentation I’ve prepared for Trivadis in September.


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: Logo

You are commenting using your 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