This course will show you how to capture and read execution plans, and how to detect and fix the common problems that prevent SQL from running well.
Writing SQL that performs well means understanding what Oracle is doing to the SQL: how it is executing the code, and why it is executing it that way. Then make changes needed to let Oracle run the code in a more efficient manner.
In just two half days, we will empower you: your SQL will never be the same again.
This class is a combination of lecture, live demonstrations and hands-on workshops to be completed out of hours.
If you prefer a more in-depth, broader coverage of SQL Tuning, consider our 3-Day SQL Tuning Class
Prerequisite Knowledge
Solid experience coding Oracle SQL SELECT statements, including ANSI Join Syntax. (Refer to this tutorial for a primer on ANSI Join syntax.) A basic understanding of SQL Functions and Procedures and some familiarity with SQLPlus is also required.
Technical Requirements
Nothing is required for labs (workshops). SkillBuilders will provide everything you need for all hands-on labs. Modest PC or Mac specs are required to connect to the online class. Detailed specs can be found here.
Also, if possible, we recommend using two monitors for this class. One monitor can be used for viewing the instructor screen, a second can be used for your connection to the lab environment.
Course Features
- Lecture 0
- Quiz 0
- Duration 1 day
- Skill level All levels
- Language English
- Students 11
- Assessments Yes
Basics
- The SQL execution cycle
- Cost based optimization
- Detecting badly performing SQLs
Capture and Read Plans
- EXPLAIN PLAN
- AUTOTRACE
- DBMS_XPLAN
Optimizer Decisions
- Join order, join method, access method
- Why does the CBO sometimes get it wrong?
- The importance of statistics
- Detect and fix incorrect or inadequate statistics
The Methodology
- Common programmer errors
- Identify “suspicious” SQL constructs
- Use hints to test alternative execution plans
- Learn a proven technique for addressing problem SQLs
