Oracle Scratchpad

Public Appearances

Sept 28th / 29th

Switzerland / Online

I’ll be giving two presentations at Trivadis Performance Days 2022:

3 Decades – 3 principles

I’ve been advising, trouble-shooting and giving training on Oracle-based systems for around 30 years, and time after time I’ve seen problems created, or solutions missed, because three fundamental questions haven’t been addressed properly:

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

For new systems the first question tends to be addressed fairly well, but the 2nd and 3rd are often poorly addressed. Most of the time, however, developers and DBAs spend on Oracle systems seems to be about working around the mistakes of existing systems – so the questions are more likely to be re-ordered and modified slightly to:

    • What does the data look like? (that’s an easy one)
    • What do the users really need to do? (that’s probably a lot clearer)
    • How can the Oracle RDBMS help? (maybe considered a little more carefully)

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 from the business requirement to the mechanics of the database and the problems of scaling and concurrency as I do so, with a minimum of high-tech detail.

The Big Delete

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.

Dec 1st / 2nd

Birmingham, UK

I’ve submitted an abstract for the UKOUG Breakthrough event in Birmingham.

Trouble-Shooting – it’s not (usually) rocket science

Taking the “helicopter view” all you do with an Oracle database comes down to:

    • Changing the data (insert, update, delete)
    • Querying the data (select)
    • Moving the data (specifically the redo) to another location

The performance of a “system” is dependent only two things

    • how efficiently you can to these things “in a vacuum”.
    • how much competition you suffer when you’re not working in a vacuum.

Although you will occasionally run into exotic Oracle bugs that require obscure methods to investigate, most performance problems come down to identifying where the important inefficiencies are or what’s introducing the most serious competition.

This presentation will avoid almost all technical detail about how Oracle works, use a couple of simple diagrams to outline a couple of important points, then categorise:

    • the most likely errors that cause performance problems
    • methods for identifying those errors easily
    • ideas on how to address those errors

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 )

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: