Oracle SQL Tuning

Oracle SQL Tuning

Audience

This course is aimed at those already experienced with SQL for data retrieval. It covers a range of facilities to help understand how Oracle implements queries and a range of techniques to make them run faster and more efficiently.

This course is suitable for all recent versions of Oracle including 12c, 18c, and 19c.

Prerequisites

General familiarity with Oracle as an SQL end user.

Duration

1 day. Hands on.

NOTE: This course may be combined with Oracle SQL and run over a total of four days, please contact us for more information.

Course Objectives

This course is highly interactive and consists of theory coupled with plenty of practical sessions.

Together with Oracle SQL, this course provides the perfect prerequisite knowledge to embark on training for Oracle PL/SQL Programming, or administrative roles such as Oracle Database Administration.

End users and developers can do much to get the best out of their system without necessarily involving the DBA.

After completion of the course each delegate should know the following:

  • How to monitor how Oracle runs an SQL script and identify problem areas
  • How the Oracle query optimiser uses the data dictionary
  • How to explore and implement better querying access paths

Course Content

Overview
Common causes of performance problems
Strategies & methodologies
Diagnostic tools

Tracking SQL
Interpretation of EXPLAIN PLAN
Interpretation of AUTOTRACE
Reading and configuring SQL TRACE output

Tables
Use of column datatypes
Use of constraints
Use of different storage techniques
Different table access types in EXPLAIN PLAN & AUTOTRACE

Indexes
Use of different index types
Different index access types in EXPLAIN PLAN & AUTOTRACE
Effects of different SQL expressions on index usage

SQL Construction & Design
Different ways of defining joins
Different ways of defining WHERE clauses
Different ways of minimizing overheads or parsing and executing SQL
Effects of non-summary functions
Effects of Wildcards
Implicit Data Type Conversion
NULL values
Negations
Variations in the ORDER BY clause
Variations in aggregation functions
Variations in subqueries

Data Dictionary
Interpretation of table statistics
Interpretation of index statistics
Use of histograms

Virtual Courses

ALL of our courses can be delivered virtually. And our Bath public schedule of courses are now available as live virtual sessions, using the popular Zoom Virtual Classroom and remote labs. Delegates can test their access at: www.zoom.us/test

Public Courses

On-Site Courses

Can't attend one of our public classes? Booking for multiple people?

All our courses are available on your site! Delivered for your staff, at your premises.

Contact us to find out more...