Oracle 10G SQL Tuning Techniques

AUDIENCE: This course is ideal for: Database administrators, Developers, Power end users proficient in basic SQL,
Data warehouse support team,
First line support, BusinessObjects "managers",
SQL support, Central Oracle support team members.
PREREQUISITES: General familiarity with Oracle as an SQL end user.
DURATION: 2 days. Hand on.
OBJECTIVES: The Oracle RDBMS is typically deployed throughout a business, from the corporate mainframe, to enterprise Unix servers, down to departmental level intel platforms. For a database of any significant size, performance immediately becomes an issue. Users may resent a query taking 3.5h, for instance. Or on the other hand, an update locks a table for 30 mins while it modifies data, which denies application access to the table(s) concerned.
There will always be a platform ceiling set by hardware and o/s kernel performance but wouldn''t it be nice to use the gap between what we are getting today, and what we could get from our platform investment?
Essentially there are two main avenues for RDBMS optimisation. Strategies deployed by the DBA, such as cache management and DBWR and LGWR optimisation. The other approach is to investigate how the SQL is running, to change either the SQL or its execution environment and see if that made a difference.
Enlightened end users and developers can do much to get the best out of their system without necessarily involving the DBA in system change. If the SQL approach to optimisation is what you need to learn, then this is just the course for you.
The approach taken is to understand what Oracle does with your SQL and why. Then to research the system to find out how things are at the moment. To change the SQL approach and finally, the inevitable question, "did it work?".

Expects to get lots of hands-on practice, to generate and see problems and finally to solve them. The training is strongly lab based and takes a real world view of everyday business computing problems.
COURSE CONTENT:
1. Introduction to Tuning
Causes of performance problems
A tuning methodology
Tools for tuning
Timing techniques

2. Oracle memory Structures
Overview
Block buffer cache
Shared pool
SQL statement processing
Minimizing parsing
Bind variables

3. Tracking SQL
EXPLAIN PLAN
AUTOTRACE
EXPLAIN Output
Invoking the SQL trace facility
Formatting trace files with TKPROF
ANALYZE table/index
Histograms

5. The optimiser
Rule-Based Optimization
Cost-Based Optimization
Influencing the optimizer

6. Indexes
Identifying row access methods
Review of index types
Index usage in execution plans
Effects of expressions on index usage

7. Sorting and Joining
Sorting Guidelines
Nested Loops Joins
Sort/Merge Joins
Outer Joins
Star Joins
Hash Joins
Effects of Wildcards
Implicit Data Type Conversion
NULL Values and Negations

8. Sorting, Aggregation, and Set Operators
Group Functions
order by
select distinct
group by, having
union, minus, intersect, and union all

9. Joins
Nested loops joins with and without indexes
Sort-Merge Joins
Outer Joins
Hash Joins

10. Subqueries
Regular subqueries
Correlated subqueries
Antijoins and semijoins

MB07/01