Day 1 – Troubleshooting
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.
| Session 1
| Trouble-shooting or Tuning
What's the difference? What are the strategies. Why tuning is hard but trouble-shooting is easy. Key targets, indicators and mechanism for producing a well-tuned system on day one. Strategies for dealing with badly performing systems after go-live.
|
| Session 2 | Frequently Occurring Problems
Some of the most commonly occurring issues that affect performance after a system has gone into production. Methods for spotting them, measuring the impact, and dealing with the cost/risk/benefit triangle involved in fixing them. Getting into the habit of pre-emptive analysis and pro-active fixing.
|
| Session 3 | Quick Fixes
Methods, workarounds, dirty tricks and parameters for dealing with classic performance problems when the system is in production. There really aren’t many quick fixes that can be applied across the board – each one needs careful examination of costs, risk, and benefits. In this session we consider some of the options that are most likely to be worthwhile.
|
| Session 4 | V$ and X$
It's a good idea to be familiar with just a few of the dynamic performance views - and there are a couple of items in the still hidden away in the X$ objects that can add a little value. This session will describe the views that are most commonly of use, and explain the meaning of some of the more useful items.
|
Day 2 – Writing Optimal SQL
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.
| Session 5
| Background
In which we examine the need for a global view point and a general strategy for minimising work while recognizing that there will always be a conflict between local and global optimization. A quick review of use of indexes, including some of the less common uses and common errors in index design. Closing with the need to be able to investigate and understand the data and business requirements.
|
| Session 6 | Basic Practices
How to approach the task of translating business requirements into SQL. The need for clarity in code, with a suggested set of standards for presentation to improve ease of understanding. An introduction to the essential strategy for writing SQL that gets its result by doing the minimum work, and a model for investigating and re-engineering badly performing statements.
|
| Session7 | Sundry Methods
A brief discussion of the mechanics (and costing) of nested loop and hash joins, and why it’s so easy it is for the optimizer to pick the wrong option. A few examples demonstrating classic problems, and solutions that take advantage of some of the slightly exotic options of SQL and Oracle’s available features. And a few closing comments about the need for the front-end code to co-operate with the database engine in certain circumstances.
|
| Session 8 | More Methods
Fixing the code without touching the code – the latest options, including a little time looking at the ways in which you can use OEM to assist with tuning. Problems and options with knowing internal mechanisms and taking advantage of them. A look at a couple of popular requirements, and strategies for addressing them as efficiently as possible. And a final look at the threats of hints and how to use them properly. |
