Oracle SQL Statement Tuning

PT25003
Summary
This course is designed to give students a foundation in SQL statement tuning. They are provided the necessary knowledge and skills to effectively tune SQL statements against the Oracle18c server. Course content is applicable to Oracle 12c, Oracle 18c & Oracle 19c. Students will focus on gaining an understanding of the behavior of the Oracle Cost-Based Optimizer (CBO) and how to accomplish their performance tuning goals. The students learn to use the Oracle diagnostic tools and facilities: EXPLAIN, AUTOTRACE and other tools. Gathering and utilizing object and system statistics is covered in detail. In addition, the participants also learn to influence the behavior of the CBO by hinting and modifying physical database objects.
Prerequisites
Knowledge of SQL join syntax and 3 to 6 months of experience writing SQL in Oracle version 11g or higher are require for this course.
Duration
3 Days/Lecture & Lab
Audience
This course is designed for experienced Oracle developers and application development support DBAs
Topics
  • Oracle’s SQL tool
  • The Petsaver database
  • Introduction to tuning
  • SQL statement processing
  • Optimizing SHARED_POOL usage
  • Effective Indexing
  • Using the EXPLAIN PLAN utility
  • SQL*Plus tuning tools
  • Collecting Statistics
  • Using TKPROF
  • Choosing the driving table in JOINS
  • Using Hints to influence the Optimizer
  • Optimizing sorts
  • Advanced Indexes
  • Optimizing PL/SQL

Related Scheduled Courses