Oracle SQL Tuning

Duration: 
1 days
Codes: 
SQL

Overview

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

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, up to and including 12c.

Prerequisites

General familiarity with Oracle as an SQL end user.

Course Outline

  • 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

Related Courses

 

Thinking about Onsite?

If you need training for 3 or more people, you should ask us about onsite training. Putting aside the obvious location benefit, content can be customised to better meet your business objectives and more can be covered than in a public classroom. It's a cost effective option.

Submit an enquiry from any page on this site, and let us know you are interested in the requirements box, or simply mention it when we contact you.

Upcoming Dates

  • GREEN This class is Guaranteed To Run.
  • SPVC - Self-Paced Virtual Class.
  • Click a Date to Enroll.
Course Location Days Cost Date
Somerset Bath1 400 £400 2019-11-07