Oracle Scratchpad

Public Appearances



21st June 2018 Edinburgh, Scotland: I’ll be presenting “Struggling with Statistics” (Part 1 of the presentation I’ve prepared for Trivadis in September) at OUG Scotland 2018 and to cover a late gap as a late addition“Partitioning Enhancements in 12c Release 2”, based on the following abstract:

Oracle Corp. keeps enhancing the way in which we can partition data, aiming to make it easier to define, use and maintain partitioned data.

In this presentation we will touch on a few of the features that appeared in the first release of 12c plug gaps in the functionality of 11g and reduce maintenance effort; then we’ll move on to the new features of 12.2 that are likely to be of most use to designers and DBAs – particularly in the task of converting non-partitioned objects into partitioned objects, and cleaing up existing partitioned objects.

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” (a longer version of the presentation I’ve offered to OUG Scotland) 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).
20th – 23rd Nov 2018 Nürnberg, Germany: I’ve submitted a pair of abstracts – parts 1 and 2 of “The basics of understanding execution plans” that I hope to be presenting as a single masterclass at the UKOUG conference in December.
3rd – 5th Dec 2018 Liverpool, England: The UKOUG Annual Conference Tech 18 will be held in Liverpool this year. I’ve submitted three abstracts, the first as a mastercalls (double session), the other two as standard presentations:

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.

Basic Guide to Trouble-shooting

The performance questions that come up on the Internet often say things like: “This batch job is taking too long should I increase the db_file_multiblock_read_count?”; and you are often left wondering why such a specific idea was seen as an appropriate way to address the time spent in the batch. It’s too easy to fall into the trap of trying to fix the problem without really knowing what the problem is and where to focus the attention.

  • This presentation outlines a mental pattern for trouble-shooting:
  • First – what problem are we trying to solve
  • Second – why has that come to our attention as a problem
  • Third – what tools do we use to get better information about the problem
  • Finally – what are we likely to see and how do we interpret it.

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