|
|
JL Computer Consultancy |
|
Single-day Tutorials |
Finding the time for three consecutive days of seminar-based training is not easy, and you may not be interested in all aspects of making Oracle work better for you - especially if you have a legacy system to support. To help you out, I have prepared several separate, stand-alone, training courses focusing on particular aspects of Oracle performance.
The courses are lecture-based, and consist of four sessions (9 in the case of the two-day event) of about 80 – 90 minutes each. The materials for the course consists of printouts of the Powerpoint slides, roughly 40 per session, with a footnote on each slide highlight particular points made by the slide.
Dates
|
Dates currently arranged for the next |
|
|||||||
|
|
|
|
|
|
|
|
|
|
|
Dates currently arranged for International Tutorials |
|
|||||||
|
|
|
6th – 8th May |
|
|
(Miracle |
|
||
|
|
|
21st – 22nd
May |
|
|
( |
|
||
|
|
|
2nd June |
|
|
|
|
||
|
|
|
9th – 10th
June |
|
|
Contact Mike Swing |
|
||
|
|
|
11th – 13th
June |
|
|
Contact: John
Goodhue |
|
||
|
|
|
25th – 26th
June |
|
|
( |
|
||
|
|
|
14th – 16th
July |
|
Three tutorials Titles to be selected |
|
Contact: Daniel Morgan |
|
|
|
|
|
6th – 7th Oct |
|
Agenda to be decided |
|
Greeve (Oracle Univerity) |
|
|
|
|
|
11th - 12th Nov |
|
Two tutorials Titles to be selected |
|
In conjunction with the NOCOUG Fall event |
|
|
|
|
|
w/c 24th Nov |
|
Agenda to be decided |
|
|
|
|
|
|
|
9th – 10th Feb |
|
Two tutorials Titles to be selected |
|
Ireland (Dublin) |
|
|
If you would be 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.
|
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. |
|
|
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. |
|
|
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? How do I predict and pre-empt the problems before they actually happen? |
|
|
If you need to make sure that your developers have a reasonable understanding of how to handle an Oracle-based development, and test their code properly, then this is a good place to start them off. It covers a lot of ground, but focuses on the most common requirements of getting it right. Including overheads, coding strategies, indexes, and execution plans. Note – the material is about the database and using it properly; it is not about programming and programming languages. |
|
|
One of the critical areas of tuning SQL is 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.. |
|
|
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 statistics, hints, and a brief review of execution plans. |
|
|
A special combination of sessions from the CBO, Explain Plan, and Indexing tutorials. |
Format
The tutorials will be lecture style with Powerpoint presentations, using in a classroom-style arrangement. Each tutorial will consist of four presentations of about 1.5 hours each (with a short break in the middle of each, if someone reminds me to take it). The Powerpoint slides which will be available to the delegates in printed form at the start of the tutorial. There will be breaks for refreshment and lunch between presentations.
I can also run any of the courses as an in-house tutorial for larger companies who have suitable facilities on-site.
Arrangements for international events will generally be similar - but where the event is organised by a third party you may expect some variation in the format.
|
Understanding and Assisting the
Cost Based Optimizer – Agenda |
|
|
Session 1 1.5 hours |
Why isn't Oracle using my index? In this session we aim to acquire a visual, rather than highly mathematical, understanding of when Oracle will ignore an index. We start with a discussion of the strategic direction that we should be going with the cost based optimizer and then examine a simple example to pin-point the traditional reasons why the cost based optimizer in versions prior to 9.0 could so easily produce inappropriate execution plans. We examine a couple of commonly used tuning parameters which give Oracle better information about our system and allow it to identify the appropriate execution path more frequently, identifying the risks of using these parameters too extravagantly, and end with a demonstration of how using CPU costing solves the traditional problems with much less risk of error. |
|
Break – coffee and informal discussion: 30 minutes |
|
|
Session 2 1.5 hours |
Mechanisms of Joins We start by asking why it is so important to examine and understand join mechanisms and examine some of the transformations that convert complex queries to simple join forms. Then we look at details (and anomalies) of the nested loop, sort-merge, and hash joins; identifying strengths, weaknesses, and costs of each in turn. In the case of sort-merge and hash joins, we examine trace events, and their output, that allow us to investigate what is happening when response times become extreme. |
|
Break – Lunch and informal discussion: 1 hour |
|
|
Session 3 1.5 hours |
Selectivity, Joins, and hints In this session we move on from the simple example of the first session to investigate what happens in the more general cases of using an index. We extend the arithmetic to the calculations involved in joining tables, and note some of the anomalies and problems that joins can cause. Finally we ask the question “What is a hint?” and try to answer the question by examining what really happens inside the optimizer when we start adding hints to our SQL. |
|
Break – coffee and informal discussion: 30 minutes |
|
|
Session 4 1.5 hours |
Maximising the Truth If there is any information we can give the optimizer about our data, we should do so; otherwise it will be less able to produce the appropriate execution. We start this session by showing how a little extra information can help the optimizer find new execution paths. Then we examine the optimizer’s dependence on statistics, and its need to get a correct numeric representation of your data. We move on to see how strange data patterns, and bad database design can stop the optimizer from choosing a sensible execution path; and end with a couple of techniques for forcing the optimizer to do what we want. |
|
|
|
|
Session 1 1.5 hours |
The Structure of Indexes What is a B-tree index, what is a Bitmap index and why are the two so different? How does Oracle build indexes? Are there differences in reverse indexes, function-based indexes, cluster indexes, global partitioned indexes, indexed organized tables, and secondary indexes? With the aid of a few block dumps and carefully monitored test results we examine the way that different types of indexes are built and behave. |
|
Break – coffee and informal discussion: 30 minutes |
|
|
Session 2 1.5 hours |
The Uses of Indexes How many different ways can Oracle take advantage of an index. We have primary keys and unique keys - do we need unique indexes to enforce them. We may have problems with foreign keys if we don't have related indexes. We need optimal access paths - but will indexing help and what will it cost? When will Oracle use an index to accelerate a query? Can we assist performance by understanding the mechanisms. |
|
Break – Lunch and informal discussion: 1 hour |
|
|
Session 3 1.5 hours |
Popular misconceptions There are several surprising ideas floating around the Internet about how Oracle indexes work. Sometimes these ideas are irrelevant and have no use or impact. Sometimes they have a serious impact on your effectiveness. They may cause you to do work that need not, or should not, be done. They may introduce down-time that you do not need. They may make you avoid a feature that is really the perfect feature for your application. This session examines some of the more popular and potentially damaging misconceptions about indexes. |
|
Break – coffee and informal discussion: 30 minutes |
|
|
Session 4 1.5 hours |
Maximum benefit, minimum cost We all know that indexes are supposed to make things go faster - but sometimes we forget that they have maintenance costs - of two types. How do you identify the optimal set of indexes for your application? How do you identify redundant indexes? How do you check whether or not an index has become inefficient and needs to be rebuilt - and how do you rebuild it most cost-effectively? This session discusses strategies for ensuring that you don't waste effort supporting indexes that shouldn't exist, and don't waste valuable batch time rebuilding indexes that don't need it. |
|
|
|
|
Note: this tutorial
has recently been heavily revised to include more information about the
different approaches needed for dealing with different types of situation;
plus an increased volume of information about understanding and interpreting
the content of Statspack (and AWR) reports |
|
|
Session 1 1.5 hours |
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. |
|
Break – coffee and informal discussion: 30 minutes |
|
|
Session 2 1.5 hours |
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. |
|
Break – Lunch and informal discussion: 1 hour |
|
|
Session 3 1.5 hours |
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. |
|
Break – coffee and informal discussion: 30 minutes |
|
|
Session 4 1.5 hours |
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. |
|
|
|
|
Session 1 1.5 hours |
Overheads A basic introduction to the costs of the infrastructure that makes Oracle what it is. Topics include connection costs, network traffic, parsing and optimising costs, undo generation and use, and redo. |
|
Break – coffee and informal discussion: 30 minutes |
|
|
Session 2 1.5 hours |
Coding Strategies There are good ways to code and bad ways to code. This session demonstrates the problems with the bad coding strategies and shows how much difference it makes when you do it right. |
|
Break – Lunch and informal discussion: 1 hour |
|
|
Session 3 1.5 hours |
Indexing A description of indexes, largely devoted to B-tree indexes, talking about structure, how they work, and the paths Oracle is able to take through them. Indications of how to get the most out of indexes, and minimise the cost of indexing, with warnings about side effects of the different strategies for making an index more useful. |
|
Break – coffee and informal discussion: 30 minutes |
|
|
Session 4 1.5 hours |
Execution paths In this session, we take an introductory view on how the cost based optimizer works, including a brief overview of the different join mechanisms. Then we examine execution paths and the developer-oriented ways of acquiring them. Finally we learn how to read and interpret execution paths. |
|
|
|
|
Session 1 1.5 hours |
How to find execution paths. There are several different methods for generating, or examining, execution paths, from the simple autotrace in SQL*Plus through to interrogating the dynamic performance views such as v$sql_plan_statistics_all in great detail. The different methods have their own strengths and weaknesses, and these vary with version of Oracle. In this first session, we discuss the different options, what’s visible in them, and to choose the most appropriate one for the task in hand. |
|
Break – coffee and informal discussion: 30 minutes |
|
|
Session 2 1.5 hours |
Intepreting Plans Once you’ve got an execution plan, how do you decide what it means ? An often-quoted clue is that ‘it starts near the top and over to the right’. This hint is a consequence of a simple algorithm for reading execution plans – no matter how complex – in a fashion that can be described in just two steps. |
|
Break – Lunch and informal discussion: 1 hour |
|
|
Session 3 1.5 hours |
Problems with Plans Although there are several ways of acquiring execution plans, none of them is perfect. Some options simply omit critical details; some options are unable of guaranteeing the truth. In this session, we look at the various omissions and errors that can make you jump to the wrong conclusion when you are trying to understand how a query might run. |
|
Break – coffee and informal discussion: 30 minutes |
|
|
Session 4 1.5 hours |
Advanced Features Once you have mastered the standard features of execution plans, you can easily extend your understanding to any new feature that Oracle throws at you. In this session, we take a look some of the features that are not seen so commonly in production systems, namely , distributed joins, parallel execution, and partitioned tables. |
|
|
|
|
Session 1 1.5 hours |
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. |
|
Break – coffee and informal discussion: 30 minutes |
|
|
Session 2 1.5 hours |
Basic Practices How to approach the task of translating business requirments 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. |
|
Break – Lunch and informal discussion: 1 hour |
|
|
Session 3 1.5 hours |
Sundry Methods A few examples demonstrating classic problems and their solutions that use some of the less well-known features of SQL. A brief discussion of the mechanics (and costing) of nested loop and hash joins. Warnings about the abuse of views, and the problems of predicates. And a few closing comments about tuning distributed queries. |
|
Break – coffee and informal discussion: 30 minutes |
|
|
Session 4 1.5 hours |
More Methods Fixing the code without touching the code – the latest option. Problems and options with knowing internal mechanisms and taking advantage of them. A little time with OEM, and a look at the threats of hints and how to use them properly. |
|
|
|
|
Session 1 1.5 hours |
CBO session 1: Why isn't Oracle using my index? |
|
Break – coffee and informal discussion: 20 minutes |
|
|
Session 2 1.5 hours |
CBO session 2: Mechanisms of Joins |
|
Break – Lunch and informal discussion: 45 minutes |
|
|
Session 3 1.5 hours |
CBO session 3: Selectivity, Joins, and hints |
|
Break – coffee and informal discussion: 20 minutes |
|
|
Session 4 1.5 hours |
Explain Plan session 1: Finding Plans |
|
Break – coffee and informal discussion: 15 minutes |
|
|
Session 4 45 mins |
Explain Plan session 2: |
|
The Two-day
combination – Agenda Day 2 |
|
|
Session 1 1.5 hours |
Explain plan session 3: Problems with plans |
|
Break – coffee and informal discussion: 20 minutes |
|
|
Session 2 1.5 hours |
Indexes session 2: The use of indexes |
|
Break – Lunch and informal discussion: 45 minutes |
|
|
Session 3 1.5 hours |
Indexes session 3: Popular misconceptions |
|
Break – coffee and informal discussion: 20 minutes |
|
|
Session 4 1.5 hours |
CBO session 4: Maximising the truth |
|
|
|