Performance Optimization for Developers - SQL and Optional PL/SQL (Day 3)
Hotsos has created this Performance Optimization for Developers course just for you. If developers don't need it, it's not in this class! The content is focused just on what's relevant and nothing more. This course focuses on the following:
- Write high-performance code that scales as your application grows
- Learn to gather a complete accounting of your unit test run-time
- Understand Oracle access paths, execution plans, and how the Cost-Based Optimizer makes these choices
- Get the Hotsos SQL Test Harness for evaluating competitive execution options
What You Will Learn:
- What is a Logical IO, and why is it important?
- SQL statement execution fundamentals
- Tools for SQL statement problem diagnosis and repair
- Access methods
- Coding techniques that may prevent index use
- Join methods
- Understanding complex Explain Plans
- What are things that I can do to write better SQL?
Upon completion of the course, students will have the knowledge and skills to:
- Understand the internals of Oracle from a performance perspective,
- Methodically interpret the execution plan of an SQL statement,
- Measure the efficiency of their SQL or PL/SQL code,
- Determine exactly what is contributing to the performance issues with their code,
- Develop the optimal execution plan for their SQL statement, and
- Replace "worst" with "best" practices in their code.
This course is designed to teach application developers the essentials of optimizing their SQL code. The students will be taught what the primary and secondary indicators of performance are and how to measure the efficiency of their code. To ensure an understanding of why these indicators are important, students will gain insight on how the Oracle database works internally. Strong emphasis will be placed on understanding the different access paths and join methods used by the Cost-Based Optimizer (CBO) and when it's advantageous to use a particular access path or join method. Throughout the course, students will learn how to interpret complex execution plans rendered by the CBO. Many different scenarios will be discussed and coding techniques for each scenario compared and contrasted.
PL/SQL optimization techniques are an additional optional day. Topics that will be covered include: efficient code logic ordering, implicit vs. explicit cursors, datatype issues, bind variables, cursor sharing, collections, bulk operations, REF CURSOR types and cursor variables, table functions, pipeline functions and more.
This course is designed for the application developers wishing to deepen their knowledge and skill level for optimizing SQL code.
Students are expected to have a basic working knowledge of Oracle, Oracle SQL, Oracle PL/SQL and some experience as an application developer.
The course is organized into the following segments:
Welcome and Introductions
- Terms and Definitions:
- LIO, PIO, latching, buffer cache, library cache, etc.
- Maximum LIO tolerated SQL statement
- SQL statement execution fundamentals:
- PARSE, hard vs. soft, use of bind variables
- FETCH, arraysize issues
- Tools for SQL statement problem diagnosis and repair:
- Statistics for CBO
- Data distribution
- Execution plans
- Extended SQL trace data (10046)
- Optimizer trace data (10053)
- Using the Hotsos SQL Test Harness to measure and store test results
- Access methods (compare, contrast and recommend when to use each)
- TABLE ACCESS FULL
- INDEX ACCESS BY ROWID (Index access paths)
- Index unique scan
- Index skip scan
- Index fast full scan
- Index full scan
- Index joins
- Coding techniques that may prevent index use:
- Column expressions
- Implicit datatype conversions
- Indexing null values
- Join methods (compare, contrast and recommend when to use each)
- Nested loop
- Hash join
- Sort/merge join
- Understanding complex Explain Plans
- How to read complex explain plans
- Where is the performance problem?
- Types of sub-plans
- Determining where the performance problem starts
- What are things I can do to write better SQL? (Entire section will cover the technique and when to and not to use it.)
- IN-Lists: how they work and alternatives
- Using CASE and decode to minimize work in the database
- SEMI and ANTI joins
- Analytic functions
- Scalar subqueries
- WITH clause subquery factoring
- The MERGE command
For questions and discussion about the course, please visit our forum.
This two-day course is approximately 60% lecture and 40% instructor-led demonstrations to ensure a concrete understanding of the course content. The course is conducted in English.
Each student receives the following materials:
- A PDF copy of the presentation is provided prior to the beginning of training. Please download this prior to start of training.
- Tools, which allows access to download the Hotsos SQL Test Harness. This tool is used during the class to measure and compare the performance aspects of SQL code.
- List Price: $1250.00
- Alumni Price: $400.00*