Oracle Scratchpad

Public Appearances

26th – 27th October Zurich, Switzerland: Trivadis Performance Days 2021: I’ll be presenting (from Zurich, I hope) two sessions this year “Transformations – the Things the Optimizer does” on the Tuesday 9:10 – 10:30 (CET), 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.

29th – 30th November The Oval, London: UKOUG Together 2021. I’ve had two of the three abstracts accepted for this event:

Monday 1145 – 1230: The Big Delete (Database stream)

Your database has been in operation and growing for years, and one day the management tells you that it’s time for you to delete (or archive) all the transaction data that’s more than 7 years old. Where do you go from there?

One common response is to post the question “How do I delete X million rows from my table?” on one of the Oracle forums or listservers; but there’s no generic answer to that question. The amount of background information you need before you can devise a good strategy is significant, and it’s very easy to run into nasty surprises and big mistakes because your planning didn’t allow for all the constraints imposed by the nature of “the system”.

This will be a chatty presentation covering as many issues as I can remember about “the big delete” and why, ultimately, the DELETE command is only a minor detail of the entire operation. There will be no rocket-science and no arithmetic, although there were will be a few technical terms (such as “redo”, “referential integrity”, “materialized views” that everyone should know) that will get a mention.

Tuesday 1500 – 1545: 3 Decades – 3 principles:  (Development stream)

I’ve been advising, trouble-shooting and giving training on Oracle-based systems for about 30 years, and time after time I’ve seen problems created, or solutions missed, because of a failure to answer three fundamental questions:

    • What does the data look like?
    • What do the users need to do?
    • What can the Oracle RDBMS do?

If you’re in the enviable position of designing a new system then the first two questions should be ranked in the opposite order (and “what does the data look like” becomes “what will the data look like”, but most of the time developers and DBAs spend on Oracle systems seems to be about working around the mistakes of existing systems – so understanding what’s sitting in the database tends to be the first priority.

In this session I’ll start at the top level with these questions and talk about why these are the fundamental 3 things that need to be addressed – working my way closer and closer to the mechanics of the database and the problems of scaling and concurrency as I do so, with a minimum of high-tech detail but bringing up a few thumbnail sketches of some features of the Oracle RDBMS and how to assess their relevance to current requirements.

The third abstract wasn’t accepted, but I’ll save it for some other occasion.

A Beginners’ Guide to the Cost-Based Optimizer

This presentation will be aimed at the novice level and should allow attendees to acquire a more intuitive feel of what the optimizer is trying to do when it generates an execution plan for a query, why it makes bad decisions, and how you can help it to do better.

Topics we will cover include: a) What “the cost” mean to the optimizer b) table scans and indexed access – and the mistakes the optimizer makes c) cardinality estimates – and the mistakes the optimizer makes d) the 3 join methods – and why the optimizer can pick the wrong one e) transformations – why the optimizer does them, and why you may need to control them

Any SQL examples used in this presentation will be very simple, and the display of Execution plans will be kept to a minimum.


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.

Website Powered by

%d bloggers like this: