Oracle9i SQL Tuning for Developers and DBAs
- 24.5 Hour Online Class or 3.5 Day Classroom Workshop
Focusing on SQL Statement tuning and creating scalable, high-performance SQL-intensive Oracle applications, this class is intended for developers and DBAs with a strong Oracle SQL background and some PL/SQL experience. Covers an introduction to SQL tuning including what can be tuned and accomplished; SQL Statement Processing including the SGA and PGA; Execution Plans; SQL Trace and 10053 Event; an overview of Cost Benefit Optimization (CBO); Indexes; Oracle Access Paths; CBO Transformations; SQL coding tips; Join Methods; Bind Variables and Table Types. Optional appendices include Optimizer hints, new Performance and Tuning features in Oracle 10g and Concurrency Control.
This course will provide you with the skills needed to tune SQL statements and create scalable, high performance SQL-intensive Oracle applications.
Particular attention is paid to:
Understanding Oracle architecture (one benefit: correct use of the library cache) and SQL statement processing (one benefit: how to avoid excessive soft and hard parsing).
Understanding the statistics used by the cost-based optimizer (CBO) such as cardinality and selectivity and the calculations CBO performs using these statistics.
Understanding the access paths, join methods and execution plans chosen by CBO for a query.
Using traces to reveal CBO decisions. This allows you to, for example, answer the common question, "why won't CBO use my index and better yet, should it?"
Testing various SQL (and PL/SQL) formulations to find the best performing option. You'd be amazed at how fast a query can run if you know what Oracle features has to offer. For example, are you aware of subquery factoring, function-based indexes, index-organized tables, interMedia Text indexes?
Choosing the correct data storage option (e.g. when to use index-organized tables as opposed to heap tables).
Approximately 50% of class time is spent on hands-on workshops and case studies where you will often be given an underperforming query and the challenge of "make this query perform better."
Working examples are included throughout the course book (see the sample chapter). Electronic versions of all course book examples and workshop solutions are also provided.
This course now includes an optional lesson on Oracle10g New Tuning Features.
Audience: Application developers and database administrators. Not intended for end-users.
Prerequisites: Programming experience with Oracle databases. This includes a strong Oracle SQL background as well as some PL/SQL programming experience. This knowledge can be obtained via the following SkillBuilders courses:
Objectives: After successfully completing this course, you will be able to:
Understand a great deal about Oracle's cost-based optimizer including the calculations used to cost many access paths. This greatly reduces the guesswork often involved in many tuning exercises.
Recognize the access paths used by Oracle and understand why CBO chose a path for a given object access.
Understand the impact that various statistics have on access path selection such as cardinality, clustering, selectivity (filter factor) and block count.
Create histograms to educate the optimizer about skewed (non-uniform) data and provide accurate selectivity and cardinality statistics to optimization calculations.
Make effective and efficient use of DBMS_STATS to collect optimizer statistics including options such as sample size, histograms and more.
Recognize the join methods (hash, nested-loops, anti, semi) and understand why CBO has chosen a method for the given query.
Use the EXPLAIN PLAN statement and V$SQL_PLAN dynamic performance view to externalize access path and execution plan choices made by the optimizer.
Use SQL Trace to externalize runtime statistics, row source operations, parse counts and more.
Use the 10053 Event Trace to determine what access paths the optimizer is considering for a query and more.
Successfully improve the performance SQL statements by testing various coding formulations and choosing the most efficient option including the use of techniques such as subquery factoring, interMedia indexes (for pattern-match searches), CASE operator (to reduce database calls) and much more.
Recognize the query transformations performed by the optimizer and understand why it was done.
Recognize non-mergeable and non-pushable views and understand the ramifications of these constructs.
Be able to choose which table type best fits for a given scenario including heap or indexed-organized. This includes discussions and workshops on table options such as compression and nologging.
Be able to choose which index type best fits for a given scenario (if applicable), create, collect statistics and determine if the index helped your situation. This includes discussions and workshops on index options such as key compression and function-based indexes.
Understand the purpose of the Oracle memory structures including the shared pool, library cache and database buffer cache. This includes learning correct usage of the library cache and knowing when the buffer cache is undersized.
Understand Oracle query processing including soft and hard parse, dynamic sampling, execution, fetching, and bulk processing.
