Oracle Scratchpad

Tutorials

Following up on the books that I have written about Oracle, I have been running short courses, both public and in-house, focusing on particular aspects of Oracle performance and handling, The courses are lecture-based, and most of them consist of four sessions (eight in the case of the two-day event) of about 90 minutes each. The materials for the course consist of printouts of the Powerpoint slides in “hand-out” format, roughly 32 – 40 slides per session, with a footnote on each slide highlighting particular points made by the slide. The newest courses (Oracle Mechanisms and Oracle Structures) adopt a slightly different format, with some pages of notes interspersed with slide pages.

The titles and contents of the standard courses are listed below with hyperlinks to each agenda. If you would like to bring me in to your company to present to your DBA or developer teams drop me an email and we can talk about tailoring a course from the available presentations, and even using your development or production systems as a hands-on vehicle for demonstrating ideas in the coursework.

Current dates, locations and links to registration details can be found on the “public appearances” page. If you need to persuade anyone that they should give you the time and money to attend one of my full-day events you might want to show them these blog reports of three of my one-day tutorials:

Oracle Structures(Two day event)

This course is new for 2013 and if you ever read Practical Oracle 8i you will recognise that this course is going to do for the latest versions of Oracle what that book did for Oracle 8i. The aim of the course is to think about how the various physical structures we can use in an Oracle database allow us to do less work – either in terms of application resource utilisation or in terms of human house-keeping time.

Oracle Mechanisms(Two day event)

This course is new for 2013 and is, in effect, a walk through the book Oracle Core. Over the course of the two days we aim to cover the core mechanisms of how the central portion of the basic database engine works. There are eight chapters in the book, are reflected in eight topics that we cover.  As we examine the mechanisms we will constantly be asking the question – why does knowing how this works allow me to use my time more efficiently, or make the database run faster.

Cost Based Optimisation

Is there a go_faster parameter ? Which parameters affect the Cost Based Optimizer (CBO) most? Just how does Oracle decide whether or not to use an index? Why does Oracle do a Hash Join instead of a Nested Loop? Why is Oracle ignoring my hints?

All these, and many other questions about the working of the Cost Based Optimizer will be answered by this tutorial.

Indexing Strategies

One of the most important, yet overlooked, aspects of efficiency is the need for a good indexing strategy.

Indexes improve the speed of queries, but increase the cost of DML. Given a thorough understanding of indexes you have the option for deciding the optimal set of indexes to suit the needs of your application. Everything you might want to know about indexes will be in this tutorial.

Trouble-shooting and tuning

The users are complaining right now ! How do I find the problem – what’s the short term fix – what’s the long term fix?

The overnight batch ran on until 9:30 this morning when it’s supposed to complete at 5:00 a.m. – what went wrong?

Good performance starts with good design – which means you have to know how much data you have, where to put it, and how you’re going to use it. Working out the best strategies is called tuning. After go-live you don’t do tuning, you do trouble-shooting, and  the strategies have to change to suit the circumstances.

In this one-day course, we learn how to think around design issues (the tuning) as well as looking at strategies – and some fixes – for addressing implementation problems (the trouble-shooting). The final session of the course examines some of the dynamic performance views, what you can do with them, and how their contents are reported in the Statspack and AWR reports.

One Day Developer

This is a course aimed at developers who wants to learn what the database can do to help them produce better applications. Its focus is very much on the database engine – what the engine hopes to be given as a workload. The tutorial covers overheads, structural features, coding strategies, indexing, and execution plans.

Although the course is intended for the more senior developers, it can also act as a rapid introduction to critical mechanisms of the database that the junior DBA should need to know..

Note – the material is about the database and using it properly; it is not about programming and programming languages. This day is most suitable for the enthusiastic developer who wants to be more aware of how the database and application can co-operate to work as efficiently as possible.

Explain Plan

One of the critical skills for tuning SQL lies in understanding what is happening, and what is possible. Oracle gives you several tools for examining (or predicting) the execution path for a query.

In this tutorial we look at the available options, examine their strengths and weaknesses, and learn how easy it is to understand what an execution plan is telling you..

Designing Optimal SQL

This tutorial has two targets. First: how do you improve the performance of a production system by attacking inefficient SQL; secondly: how do you design a system so that you can get data into and out of it efficiently.

For most people, the design has already happened, nevertheless you need to be aware that the two targets overlap significantly. As you struggle to address a particularly inefficient piece of SQL you need to be aware that there are structural features of the database that may help you find a cost effective solution despite the problems imposed by the SQL itself.

The course will focus on the SQL – falling back to structural issues as an aid to improving the mechanical efficiency of the SQL solution – and will spend some time explaining the pros and cons of various structural options as they become relevant to the SQL.

The course will cover methods of reviewing data distribution patterns, use of indexes, use of views (stored and inline), analytic functions, subquery factoring, as well as a brief review on hints and execution plans.

Beating the Optimizer

This is a follow-on from the course “Designing Optimal SQL”, and focuses on writing SQL to emulate transformations that are currently not available to the Oracle Optimizer. The other course was essentially a guide to writing “normal” SQL in the best possible way – this course is about writing “abnormal” SQL because that’s the only efficient thing to do.

Optimising Oracle – Foundations

(Two-day event)

This tutorial is a compilation from the other tutorial sessions. The first day is mainly about how the Cost Based Optimizer does it’s arithmetic and how you can compare your data with the optimizer’s “picture” of your data. The last session of the day will be about finding and interpreting execution plans.

The second day starts with a session on the problems that we can run into when looking for, or interpreting, execution plans, followed by some time looking at indexing strategies, the way Oracle can use indexes, and some common misunderstandings about the strengths and weaknesses of Oracle’s B*Tree implementation.

We end the day by revisiting the optimizer to ask (and answer) the question – what do we do to understand and design for our data, and how do we make sure the optimizer gets a good picture of what our data looks like.

1 Comment »

  1. Hey Jonathan…
    Lovely writeup?
    I specially like your “Super Comment” that “Tuning cannot be done after Go-Live and it is only troubleshooting that is possible”

    Thanks a bunch to let the world know that DB design is always so imp. for an optimal DB performance.

    Ta…
    Sanjeeth

    Comment by Sanjeet Bose — September 30, 2011 @ 11:10 am BST Sep 30,2011 | Reply


RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,877 other followers