Oracle9i SQL Tuning

This 5-day course is tailored for developers to write efficient, well tuned SQL statements. An in-depth discussion of the Oracle optimizer is considered including cost based optimization, optimization modes and altering behavior of the optimizer. Various utilities which assist in SQL statement tuning are also presented including EXPLAIN PLAN, TKPROF and AUTOTRACE. Special techniques such as histograms and specialty indexes are also considered. On course completion, students will be able to:

• Examine the execution plan of the Oracle optimizer
• Alter the mode and goals of the optimizer
• Collect statistics for database objects
• Interpret and influence SQL statement execution plans
• Exploit Oracle9i plan stability techniques
  

Target audience

This course has been developed for all Oracle client/server and web developers. Students should have attended the following course or have equivalent knowledge and experience:

• Introduction To Oracle9i: SQL
• Introduction To Oracle9i: Advanced SQL
• Introduction To Oracle9i PL/SQL Language
• Oracle9i Architecture For Developers

Course content

Understanding the tuning issues OLTP versus data warehouses • Database tuning • Infrastructure and network issues

SQL statement execution Parse phase • Execution phase • Fetch phase • Dedicated server processes • Multi-threaded server processes • Parallel query processes

Explain plan utility Generating the execution plan • Viewing the execution plan • Interpreting the execution plan

Cost-based optimizer (CBO) Optimization methods • Collecting statistics • ANALYZE • DBMS_STATS() • Chained rows

Collecting statistics Statistics collection methods • Monitoring object modifications • Using the DBMS_STATS() package • Using the ANALYZE command • CREATE INDEX…COMPUTE STATISTICS

Optimizer operations Table scan • Join operations • Hash operations

Indexes and the execution plan B-tree indexes • Bitmap indexes • Function-based indexes

Optimizer hints

Using TKPROF and AUTOTRACE

Histograms Creating histograms • Data dictionary storage

Using plan stability Preparing for stored outlines • Creating and using stored outlines • Data dictionary storage • Managing stored outlines

Editing private outlines Preparing for private outlines • Creating private outlines • Editing private outlines • Utilizing private outlines

Collecting system statistics Managing system statistics • Developing a tuning strategy

Exploiting and managing cursor sharing Bind variables and cursor peeking • Using the CURSOR_SHARING parameter

Understanding the rule-based optimizer About the RBO • Activating the RBO explicitly • How the RBO works • The access paths • RBO join execution plan selection

Tuning with the rule-based optimizer Correctly building indexes • Understanding when indexes will be ignored • Rewriting queries to avoid index suppression • Rewriting queries to force index suppression

About Courseware Company :: Instructor-led training courseware :: Self-study solutions :: Buy online today