JL Computer Consultancy

Optimising Oracle - Performance by Design

seminar.jpg

Oracle Author of the Year 2006

Select Magazine Editors’ Choice 2007


 

Introduction

Management Summary

Timetable

A review

Comments from previous delegates

Introduction

Following on from the publication of my Practical Oracle 8i, I designed a three-day, intensive, seminar on how to get the best out of the Oracle database engine. The seminar is aimed largely at DBA's, or developers/designers with a strong interest in the database engine. It is hard work and covers a lot of ground, so audience sizes are kept low and the course notes, sample scripts, and utilities are very detailed. The seminar evolves in line with the ever-changing versions of Oracle as old problems disappear and new features (and issues) appear.

Dates for the next UK seminars

 

 

 

 

 

Dates for International Presentations

 

 

 

 

Warning - this is not just another three days packed with sound-bites and quick tips that might work on your particular system. This seminar is designed to help you to understand much of the infrastructure that Oracle is built on. We examine how Oracle works, and teach you how to investigate for yourselves the cost, risks, and benefits of different approaches to handling your design requirements.

To see the type of material you get, download a small PowerPoint extract from one of the sessions, or download a zipped copy (17K) of the index to the seminar notes to get a taste of the detail covered.

There are very few quick answers with an Oracle database, and even they tend to go out of date - the methods and strategies you learn here will always be relevant.

Suggestion: If you can't persuade management to finance you for the three day seminar, or let you have the time to attend, then perhaps you should look at the One-day Tutorials on Indexing, Trouble-shooting, Cost Base Optimiser, Explain Plan, and Optimal SQL.

Management Summary

Why should you send your DBAs and senior developers on this course? Because you want to make the best use of their time, and you want to install systems that don’t exceed budget, waste time, and perform badly. If you’ve paid the Oracle licence fees, you need people who understand how it works so that they can avoid the worst design errors, and get the best value for money from your investment.

There are only a few special mechanisms that are critical to building, or maintaining, an efficient Oracle system. Once your DBAs understand them, and have the tools to help them investigate what’s going on, they will be able to solve problems more quickly, and be in a much better position to identify safe and cost-effective opportunities for improving your systems.

The course contains a lot of answers to current questions – but more importantly, it shows people how they can rapidly work out answers to the new questions that keep appearing. If you are interested in attending any of the courses, please send an email to jonathan@jlcomp.demon.co.uk. Please note, any email will be taken purely as a notification of interest, not as any sort of commitment. But for the Manchester course see below.

Comments from previous attendees

Description of Materials

Back to Main Index of Topics

Back to Main Index


Day 1 – Infrastructure

Sign-on and breakfast (residential courses only)

 

Introduction

Who am I, how the course works. Who are you, what do you particularly want from the course.

Session 1

1.5 hours

Parser and Optimiser costs

Before you design, or understand the problems of, an Oracle-based system you need to understand the fundamental mechanisms of the database, acquire a feel for how much work Oracle does behind the scenes and be familiar with the potential for contention this work causes. The primary interface between Oracle and the user is the SQL language, and this session discusses the costs of translating human-readable SQL into something that Oracle can use to access the data. By the end of the session you will be familiar with the different overheads of the three main strategies for writing SQL, and understand the costs and benefits of the features that Oracle supplies for damage limitation.

Break – coffee and informal discussion: 30 minutes

Session 2

1.5 hours

Latches

Oracle is a multi-user system - yet there are some activities that must be serialised to protect the integrity of the system. In general, for example, you cannot allow two different processes to update the same block of memory at the same time in case their updates overlap, and corrupt each other. To deal with this issue Oracle has several mechanisms, principally latching, to protect small amounts of memory for very short periods of time. This session examines the effects of latching, particularly the way in which some activities can result in excessive latch contention as the number of concurrent users increases. We also look at the evolving strategies within Oracle for reducing latch activity so that we can design our code towards the most scalable mechanisms that Oracle offers.

 

Break – lunch and informal discussion: one hour

Session 3

1.5 hours

Undo

Behind any simple action to modify the data held in the Oracle database, the database engine does an enormous amount of work in order to be able to offer maximum concurrency to the users whilst still maintaining consistency without conflict. This session describes the three critical jobs that are supported by the implementation of the undo (rollback) segments. The session details the costs of the implementation and examines possible strategies for minimising those costs. In particular, the infamous ORA-01555 error will be examined and explained, as will Oracle 9i's strategy for undo tablespaces. We will also take a brief look at the way in which Oracle 10g has been engineered to reduce the costs and contention of undo.

Break – coffee and informal discussion: 30 minutes

Session 4

1.5 hours

Redo

Not only does Oracle offer an extreme level of consistency with concurrency, it also offers an extremely robust facility for handling recovery in the event of all sorts of disasters. This recovery strategy is based on redo generation and the redo log. This session discusses the features and costs of redo; highlights ways of avoiding these costs and the possible penalties of avoidance; and concludes with a discussion of some of the more recent functionality supplied through redo and redo logs. In particular, the benefits and risks of nologging objects, the significance of global temporary tables, and the added load to the redo introduced by Oracle 9i, and the re-engineering to reduce redo costs in Oracle 10g will be examined.

Dinner (residential courses only)

Day 2 – Application Structure

Breakfast (residential courses only)

 

Debrief - questions arising from previous day

Session 1

1.5 hours

Locking and Queueing

A process is either working or waiting - if it is waiting there are three possible reasons for the wait: (a) there is nothing to do, (b) a required internal resource is busy elsewhere and unavailable, or (c) a required external resource is busy elsewhere and unavailable. This session describes the mechanisms used by Oracle for protecting critical resources from concurrent and possibly conflicting actions, and identifies the strategies needed to minimise the impact of such mechanisms. We will discuss how to diagnose locking issues, whether it is data locking or Oracle’s internal memory locking. Since queueing is often an important feature of real-life data handling, we also examine how we can take advantage of the way that Oracle has made its internal locking mechanisms available to use through the dbms_lock package.

Break – coffee and informal discussion: 30 minutes

Session 2

1.5 hours

Understanding EXPLAIN PLAN

If you are lucky, your biggest performance problems will be a few statements using poor execution paths. In this session we examine execution paths, how the explain plan utility describes an execution path, and how to make Oracle take a specific path, even when you are unable to modify 3rd-party source code. We also highlight the occasions when explain plan can let us down, and we have to fall back on tkprof the raw trace files. For users of Oracle 9, we comment on the new dynamic performance views that hold run-time execution plans.

Break – lunch and informal discussion: one hour

Session 3

1.5 hours

Trouble-shooting – Why are we waiting

In many cases, problems appear only after a system has gone into production. At this point it is necessary to identify the issues which give you the best three-way trade-off between best cost , biggest benefit and least risk so that you can schedule your repair tasks most effectively. There are really only two fundamental issues: a process is doing too much work, or it is unable to work. In this session we focus on blocking issues – cost-effective diagnosis, and possible fixes.

Break – coffee and informal discussion: 30 minutes

Session 4

1.5 hours

Trouble-shooting – How to avoid work

It is very easy to do a lot of redundant work in an Oracle system. This session examines methods for finding out where the excess work is, the impact it is having, and methods for avoiding the work whilst still getting the results. Some options for work avoidance will inevitably introduce side-effects, and some will undesirable levels of risk, and we will consider methods for predicting the problems that our fixes might introduce.

Gala Dinner (residential courses only)

Day 3 - Optimising SQL

Breakfast (residential courses only)

 

Debrief - questions arising from previous day

Session 1

1.5 hours

Cost-based Optimisation – Basic Arithmetic

Oracle sometimes surprises even the most experienced DBA by the execution paths it takes to resolve a query. This session starting by talking about the optimizer environment, then examines the basic strategy that Oracle uses to decide on an execution path. With this understanding, it is often possible to predict the execution paths that Oracle will take, and the conditions that will cause Oracle to switch paths dramatically – but we also look at a few of the surprises that the optimizer can spring on you.

Break – coffee and informal discussion: 30 minutes

Session 2

1.5 hours

Cost-based Optimisation – Joins

Oracle is very keen to transform a complex query into a “simple” join – so we examine the mechanics of a two table join, and review the trace files that Oracle offers for debugging join problems. We then take an informal look at the arithmetic relating to join cardinality, noting some of the unexpected side-effects of the variations that Oracle can use in the calculation of join selectivity

Break – lunch and informal discussion: one hour

Session 3

1.5 hours

Cost-based Optimisation – Helping the Optimizer

We examine the use of the dbms_stats package, then review how histograms work and how to use them well. After a short review of the 10053 (CBO) trace file, we finish by looking at the use of hints, stored outlines and Oracle 10g’s profiles.

Break – coffee and informal discussion: 30 minutes

Session 4

1.5 hours

Open Forum and round-up.

Any intensive seminar is bound to throw up topics that you think you would like to discuss in more depth, so the last session is the opportunity to pursue any points that particularly interest you. Topics can be tabled for the Open Forum at any time during the preceding 72 hours, and need not be restricted to areas covered in the rest of the seminar. Delegates are also invited to table specific issues that they need to address on their own systems. Any issue that the group would like to pursue will be open for discussion.

Materials

The seminar comprises eleven structured presentations of about 1.5 hours each (with a 5-minute break in the middle, if someone reminds me to take it), as well as a time-tabled open discussion session and several breaks to allow informal discussion.

Paper copies of the presentations (about 400 Powerpoint slides with their accompanying notes) will be supplied to the delegates at the start of the seminar in a format large enough to allow easy annotation, but small enough to carry home without suffering serious injury. There is a 20-page index to the slide notes covering around 1,400 topics. A recent version of this index can be downloaded if you want to get an idea of the material covered in the seminar. This download is a zipped rtf file (size 17K).

Various scripts to generate examples, demonstrate principles, and take snapshots of the dynamic performance views will be mentioned during the course of the seminar - these will be available on CD to allow the attendee to repeat and expand the experiments on their own systems.

A review

In my experience there are two paths to the job title "DBA".  The first is a consequence of successful university grades, the milk round, a blue chip corporation with training courses and colleagues as mentors leading to an immersion in a customer base rich in diverse technologies and experiences. 

The other comes from lingering too long in a server room; you look up from your workstation only to notice that everyone else has left and you are the last man (or woman) standing.  There's a server, a manual and a password somewhere.  Oh and all these irate users who cannot log on. 

If yours was the first scenario then congratulate yourself on your extraordinary good luck.  The rest of us to some degree have started out as a job title in search of skill-set. 

But why bother with training courses?  They are expensive.  Oracle has put all its documentation on the Internet.  And there's always Google.  It's all there for there taking . . .  And that is the problem.  There is too much information from the well-intentioned wrong to the criminally malicious, from the redundant to the recondite, from the nervous tyro to the nebulous tyrant.  There are egos and agendas to navigate.  It does not matter if there are ten ways to fix your problem; you want to know how to pick the one for you.   You need a guide for best practice, someone you can trust.  And this is where Jonathan Lewis comes in. 

Russell Ackoff defined an "hierarchy of understanding" with the following categories; data, information, knowledge and wisdom.  Most training courses that I have attended have provided information and at best a little knowledge.  Jonathan Lewis's was the first to offer some much-needed wisdom. 

It is very rare to find someone with his depth of expertise (on any subject).  It is very rare to find as engaging a teacher (on any subject).  To find both talents in the same individual is phenomenal.  (I have had one trainer whose total experience of the subject was when he sat as a student in the same course the previous time it was run.)

There is something very refreshing about the Lewis teaching style.  It is a reversion to the traditional classroom.  No time is wasted on:

         ice-breaker student monologues (no hyperbole can describe how little I care about what my neighbour thinks is interesting about himself),

         facile hands-on exercises (where the class plods at the pace of the slowest typist)

         pointless end-of-course tests (it's about enabling you to find solutions for yourself, not a memory test)

The course comprises four ninety-minute (and liable to overrun) lectures per day for three days.  It is exhausting.  You need to sustain high concentration levels.  If that makes it sound daunting, it should.  It requires commitment and a certain sense of professional pride. 

If you are a CBA DBA, don't bother.  However if you care about your work and want it to be as good as it can be, I cannot recommend this seminar high enough. 

Comments from previous attendees

Gave me a lot of insight into the areas that typically cause problems in the real-world application of Oracle features – Anon (UK)

The sessions on CBO cleared up a lot of the mysteries we have encountered – Anon (UK)

The detailed descriptions under the slides are just as good as a book – Anon (Denmark).

This seminar taught me a whole new way of thinking about Oracle problems – Anon (USA)

The library of test cases is fantastic – Anon (USA)

Jonathan’s ability to explain complex topics concisely was good, and his willingness to answer questions was much appreciated – Anon (USA)

Even experience DBAs will learn about lots of things that they never knew existed, or never had time to test. – Anon (UK)

Jonathan doesn’t recite from the Oracle manuals, he explains how Oracle really works. Finally I understand latching and parsing. – Anon (UK)

The instructor was an excellent presenter and obviously brilliant. He transferred an enormous amount of information in a relatively short period of time and managed to hold everyone's interest. -- Pamela Watson (USA)

This class is fast moving, and full of relevant job related information - I got some great ideas in the Cost Based Opimizer section that I can try right away. Great class for the DBA whose time is too valuable to waste in bad, slow-moving classes. -- Anon (USA)

Even though I've used explain plan quite a bit, this was the clearest explanation I have seen by far. -- Joseph Spivey (USA)

The course textbook is great. The notes under the slides were excellent content, quality and quantity. -- Anon (USA)

I think this was the most valuable Oracle training I have ever received. -- Mark Powell (USA)

It was very authoritative, and yet still encourages you to test and verify ideas for yourself. All DBA's and developers should be required to attend. Finally Cost Based Optimisation makes sense ! -- Rommel Pascual (Australia)

The depth of Jonathan's knowledge was particularly beneficial, and every point made was backed up with scripts for test cases. I would recommend this course to anyone interested in understanding more about the inner workings of Oracle, and the contention that can exist in an application. Anon (Australia)

This was a good opportunity to sit and listen to a lot of low-level "why and how does Oracle do that" information. Excellent for DBAs, and good for developers with some database understanding. I'll certainly recommend that some of my colleagues go. -- Anon (UK).

This course is extremely useful for giving you new ideas of where to investigate where problems may be. -- Richard Woodward (UK)

Mr. Lewis has such a profound knowledge, and can explain the stuff extremely well. The practical demonstration of "snapshot too old" was extremely good - I've never seen it better explained. -- Frank Haas (Switzerland).

This course combines how Oracle really works with how to establish for yourself how Oracle really works. So much information on other courses is transient; that's why the approach of validation from first principles is invaluable. A section on 'folklore' would have been amusing - so much was exposed 'in passing'. -- Gavin Ritchie (UK)

Mr. Lewis talks about stuff he has tested. No marketing, no myths, just reality -- Anon (Switzerland)

Another top-notch three days. I was a little worried that it would be the same content as the last time I attended, but that certainly wasn't the case. It was good to see all the new stuff. -- Connor McDonald (Australia/UK)

I would not hesitate to recommend the course to others because I feel that Jonathan can answer any questions about optimisation. -- Steffen Dyhrberg (Denmark)

The course material is written in such a way that I think it can be read as a book. Furthermore it will be a good reference when solving problems. -- Poul :Ludwig (Denmark)

Almost every statement you make, you prove; and the scripts you supply allow us to continue testing on our own. -- Jean Remacle (Luxembourg)

I particularly appreciated the way in which the course passed on experiences derived from real production systems -- Steffan Agel (Germany)

Jonathan's enormous knowledge about any detail in the Oracle database is beyond my comprehension. I would definitely recommend this course to others. -- Niels Martin Nielsen (Denmark).

In more than fifteen years, I have not seen anything this good. Jonathan has an amazing knowledge, views things from all angles, and explains things well -- Anon (Denmark).


Manchester

The seminars in Manchester will be run by Edenbrook Ltd in their training rooms in Bishopsgate. All arrangements for the course when it is run in Manchester are handled by Edenbrook Ltd. in their training rooms in Bishopsgate. Contact Vicki Campion (info@edenbrook.co.uk) on 0161 2477771 for further details. For the Manchester course, the maximum class size is 12.


Back to Main Index of Topics

Back to Main Index