Oracle Scratchpad

Public Appearances

10th – 11th September Poland: POUG Hybrid Event – 2021: I’ve submitted an abstract for a presentation (in two parts, 45 minutes each) for this event, in the hope of being able to present in person rather than online.

The topic will be Transformations – the Things the Optimizer does. See the Trivadis event in October for the abstract.

26th – 27th October Zurich, Switzerland: Trivadis Performance Days 2021: I’ll be presenting (in person, I hope) two sessions this year “Transformations – the Things the Optimizer does” on the Tuesday 15:20 – 16:40, and “Hinting in all its forms – Pros and Cons” on the Wednesday 09:10 – 10:30.Transformations – the Things the Optimizer does

WYSIWYG (what you see is what you get) is an abbreviation that does not apply to the optimizer. When you give Oracle an SQL the first thing the optimizer does is rewrite it before doing the arithmetic that tells it the best way to walk through the data to create the result you want.

In this presentation, we will review examples of the transformations available to deal with such things as subqueries, aggregates, non-mergeable views, partitioned tables and partial indexes. Once we can recognise the connection between the SQL we’ve written and the transformed SQL, we will have a better idea of why a query may be doing more work than we expect, and be in a better position to pick the transformations that we want Oracle to use without having to rewriting the query by hand.

Hinting in All its Forms – Pros and Cons

Hints are orders – and should be obeyed if they are legal. This raises two questions: How do you make sure you’ve created the correct set of hints? What’s the best way of connecting the hints with the SQL?

Oracle gives us four mechanisms for hinting our SQL: SQL Plan Baselines, SQL Profiles, SQL Patches, and hints embedded in the SQL. The first two are things that should be created by licensed Oracle mechanisms, but can be created manually. The last two are purely manual mechanisms.

In this presentation we examine all four options, reviewing their similarities and differences, considering their strengths and weaknesses, and offering suggestions of when you should and should not use (or abuse) each mechanism.

1 Comment »

  1. Hi Jonathan,

    Trust all’s well at your end.

    It’s after long you have announced online event, eagerly waiting for your this session and hopefully others this year.


    Comment by vijaysehgal — February 25, 2020 @ 4:27 pm GMT Feb 25,2020 | Reply

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.

Website Powered by