Realdolmen Education

Details

Oracle SQL Performance Tuning

This course is currently not scheduled on the open calendar, but can be organized on request.

Request Course

Duration

2 day(s)

Audience

Analist-programmers, technical developers, DBA's, functional analysts.

Prerequisites

A basic knowledge of SQL and Oracle database structure is required. (cf. Course ORA010 - Writing SQL queries for Oracle DB).

Objectives

After having followed this training the participants will be able to write more performant SQL and PL/SQL code and to evaluate and improve the performance of existing coding.

Methods

Classical training with exercices. Each student will dispose of a workstation to perform the exercices.

Description

After an introduction concerning the main causes of performance problems, and the first actions to improve this performance, we focus on indexes. Understanding how indexes work is a first step in writing efficient SQL-statement that use these indexes. In a next step the tools (EXPLAIN, TRACE,...) available in Oracle to identify performance problems will be highlighted, and the different aspects of cost based optimizing explained.

Since a lot of performance problems occur when joining tables, we will explain in detail the different join techniques, and evaluate which technique is preferred to be used in different situations. Optimizer hints are introduced here in order to force the Oracle optimizer to use a specific join technique. Also the possibilities to force specific execution and access paths via optimizer hints will be handled. Finally, since parallel execution and caching can be useful to improve performance, also the hints concerning these topics will be introduced

In a next part of the training we will focus on specific problem SQL queries, and find a solution (via alternative coding or via an alternative database structure) to these performance issues.

Since a lot of coding occurs in PL/SQL, we will also have a look to the possibilities to enhance performance in this area. Finally some advanced techniques that can help in resolving performance problems will be explained : parallel features, partitioning of tables, storing execution plans,.....

During several exercices we will test and analyse alternative SQL coding.

Contents

  • An introduction to performance tuning
    • Causes of performance problems
    • Quick goals to instantly improve performance
    • Limit users by using profiles
  • Basic index principles
    • Basic index concepts
      • B-tree
      • Selectivity
      • Clustering factor
      • Number of levels
      • Histograms
    • Index information retrieval
    • Types of indexes
      • Index-organized tables
      • Bitmap indexes
      • Function based indexes
      • Hash indexes
      • Domain indexes
    • Fast index rebuilding
    • Suppressing index usage
  • Different table types
    • Index-organized table
    • Hash clustered table
    • Nested table
    • Object table
    • Temporary table
    • External table
  • Identifying performance problems
    • Using the TRACE utility
    • Using TKPROF
    • Using EXPLAIN
    • Columns in the PLAN_TABLE
    • The AUTOTRACE command
    • Finding high memory and/or high disk reads without using TRACE
    • Finding which queries to tune
    • Helpful Oracle-supplied packages
  • The SQL optimization process
    • Working of the Cost-based optimizer
    • Details of the Cost calculation
    • Statistics management
  • Table join methods
    • NESTED LOOPS join
    • SORT-MERGE join
    • CLUSTER joins
    • HASH joins
    • INDEX joins
  • Using hints in SQL statements
    • Execution path hints
    • Access method hints
    • Join order hints
    • Join operation hints
    • Parallel execution hints
    • Cache hints
  • Query tuning
    • The SQL Tuning Advisor
    • The SQL Access Advisor
    • When should an index be used ?
    • What happens when you forget the index ?
    • What if you create bad indexes ?
    • Be carefull when dropping indexes
    • Indexing the SELECT and WHERE
    • The fast full scan
    • Use of concatenated indexes
    • Caching a table in memory
    • Using multiple indexes
    • Suppressing index usage
    • The OR-statement
    • Inequalities in predicates
    • The EXISTS function
    • Index-breakpoint
    • The IN-operator
    • Distributed queries
  • PL/SQL to enhance performance
    • Reusing memory with PL/SQL
    • Using bind-variables
    • Finding reusable PL/SQL
    • Caching PL/SQL object statements into memory
    • The DBMS_PROFILER package
    • The DBMS_TRANSACTION package
  • Advanced performance optimization techniques
    • Using parallel features
    • Using partitioned tables
    • Plan stability: the use of outlines