SQL Tuning  «Prev  Next»

Lesson 1

Oracle SQL Tuning Introduction

Welcome to Tuning Oracle SQL

Since each course is unique, make sure that you read the course-specific information about what you should expect in this course.
  1. This course takes an in-depth look into the concepts and skills you need to tune Oracle with SQL.
  2. This course will discuss the broader issues of tuning with SQL
  3. The course will delve into tuning with the Optimizers, the Explain Plan Utility, Hints, and other tuning techniques.
After completing this course, you will be able to tune most Oracle SQL and take the actions required to ensure optimal SQL performance.

Course goals

After completing the course, you will be able to:
  1. Define the major features of Oracle SQL
  2. Tune using ANSI extensions
  3. Tune using the Explain Plan Utility
  4. Understand SQL Optimizer modes
  5. Tune using the rule-based optimizer
  6. Tune using the cost-based optimizer
  7. Tune using SQL hints

The OCP Exam Series

As of September 2021, Oracle Corporation offered a variety of certification exams that incorporated elements of SQL tuning. While not all of these exams focused solely on SQL tuning, it was a significant component in a number of them, particularly for roles involving database administration or development.
Two notable Oracle exams featuring SQL tuning were:
  1. Oracle Database SQL Certified Associate (Exam Code: 1Z0-071): This certification tested a candidate's knowledge of SQL, including how to optimize and tune SQL queries. The focus here was more on understanding SQL concepts and syntax, writing SQL queries, and manipulating data in an Oracle Database.
  2. Oracle Database 12c: Performance Management and Tuning (Exam Code: 1Z0-064): This certification was part of the Oracle Certified Expert (OCE) pathway and had a major emphasis on performance management and tuning, including SQL tuning. The exam covered performance tuning concepts, automatic tuning features, SQL tuning, and various other aspects of ensuring an efficient Oracle Database environment.

Keep in mind that Oracle's certification paths and exams often evolve in response to changing technology trends and product updates. Therefore, the specifics mentioned here may have changed by 2023.
To get the most current and accurate information, please visit the Oracle University website or contact Oracle Education directly. They can provide up-to-date information about the current exams, prerequisites, training resources, and any changes to the Oracle Certification Program.
Upon completion of this course, you will have the skills required to quickly identify and correct non-optimal SQL.

Five-part series

The five courses in the series allow you to delve in detail into the various concepts that comprise Oracle performance tuning.
  1. Performance Tuning Basics
  2. Tuning the Oracle Instance
  3. Tuning Oracle SQL
  4. Backup Recovery Concepts
  5. Advanced Oracle Tuning Concepts

Learning Technologies

In this course, you will learn and practice SQL tuning using the following widgets:
  1. SlideShow (A series of images that are automatically displayed in numerical order)
  2. MouseOver, (a tooltip over an image that provies additional information)

The series

Tuning Oracle SQL is the third of five courses in the Oracle Performance Tuning Certification Series
Certification Series.

Performance Planning

Based on years of designing and performance experience, Oracle has designed a performance methodology. This part describes activities that can dramatically improve system performance and contains the following topics:
  1. Understanding Investment Options
  2. Understanding Scalability
  3. System Architecture
  4. Application Design Principles
  5. Workload Testing, Modeling, and Implementation
  6. Deploying New Applications

The next lesson discusses the prerequisites for the course.