On-line training course catalogue

Oracle9i: SQL Tuning Workshop

Type of course: Classroom

Course details

Fee	£1260
Days	3
Course code	ORSTW

We currently do not have public dates scheduled for this course.
If you have a requirement for this course, please contact us for the latest course information.

Print this page | Download as Word document |

Overview

Please note that Oracle courses running at London training centres are subject to a 5% surcharge.

This course is intended for Application Developers, Database Administrators, Designer/Developers and Technical Support Personnel. This course helps delegates towards better SQL in programming and to give a firm foundation on SQL tuning. The delegate gains the necessary knowledge and skills to effectively tune SQL statements against the Oracle9i server. Delegates achieve a thorough conceptual understanding of the Oracle9i Rule and Cost based optimizers and learn to influence the behaviour of the optimizer by changing the physical schema and modifying SQL statement syntax. The following Oracle diagnostics tools and facilities are taught: EXPLAIN, SQL Trace, TKPROF, SQL*Plus and Autotrace.

Finally this course covers the SQL tuning related new features of the Oracle9i database, such as new DML syntax, outline editing and SQL:1999 join compliance.

Prerequisites

Delegates should also have attended course SQLINT (Introduction to Oracle9i: SQL) or SQLEXP (Introduction to Oracle9i for Experienced SQL Users). Previous attendance of PPLS (Oracle9i: Program with PL/SQL) would be beneficial. Delegates must have a minimum of 3 months experience with writing SQL statements.

Delegates will learn how to

• Describe the causes of performance problems
• Understand where SQL tuning fits in an overall tuning methodology
• Use the diagnostic tools to gather information about SQL statement processing
• Describe the basic steps in processing SQL statements
• Describe alternative methods of accessing data Influence the physical data model so as to avoid performance problems
• Understand Rule-Based optimiser (RBO) and Cost-Based optimiser (CBO) behaviour
• Influence the optimiser behaviour

Outline

• Introduction to Tuning
• SQL Statement Processing
• Oracle Memory Structures
• SQL*Plus AUTOTRACE
• SQL Trace and TKPROF
• RBO vs CBO
• Introduction to Indexes
• Collecting Statistics
• Influencing the Optimiser
• Sorting and Joining
• Optimizer Plan Stability
• Advanced Indexes
• Materialised Views and Temporary Tables
• Alternative Storage Techniques
• Data Warehousing Considerations
• OEM SQL Analyse
• Workshops