Retour en haut de la page

Oracle Database 11g - SQL Tuning Workshop

3 jours - 21 heures

Objectifs

Identify problem SQL statements Modify a SQL statement to perform at its best Trace an application Understand how the Query Optimizer makes decisions about how to access data Interpret execution plans Use optimizer hints effectively Generate a load test

Participants

Application Developers Consultant Technique Data Warehouse Developer Developer Développeurs Développeurs d'applications Ingénieurs support Support Engineer Technical Consultant

Prérequis

Cours pré-requis obligatoire(s) Oracle Database 11g: Introduction to SQL Oracle Database 11g: SQL Fundamentals I

Pédagogie

La pédagogie est basée sur le principe de la dynamique de groupe avec alternance d'apports théoriques, de phases de réflexion collectives et individuelles, d'exercices, d'études de cas et de mises en situations observées. Formation / Action participative et interactive : les participants sont acteurs de leur formation notamment lors des mises en situation car ils s'appuient sur leurs connaissances, les expériences et mettront en œuvre les nouveaux outils présentés au cours de la session.

Profil de l’intervenant

Consultant-formateur expert. Suivi des compétences techniques et pédagogiques assuré par nos services.

Moyens techniques

Encadrement complet des stagiaires durant la formation. Espace d’accueil, configuration technique des salles et matériel pédagogique dédié pour les formations en centre. Remise d’une documentation pédagogique papier ou numérique à échéance de la formation.

Méthodes d’évaluation des acquis

Exercices individuels et collectifs durant la formation. La feuille d’émargement signée par demi-journée ainsi que l’évaluation des acquis de fin de stage sont adressées avec la facture.

Programme

Exploring the Oracle Database Architecture

Oracle Database Server Architecture: Overview

Oracle Database Memory Structures: Overview

Background Process Roles

Automatic Shared Memory Management

Automated SQL Execution Memory Management

Automatic Memory Management

Database Storage Architecture

Logical and Physical Database Structures

Application Tracing

End-to-End Application Tracing Challenge

Location for Diagnostic Traces

What Is a Service?

Use Services with Client Applications

Trace Your Own Session

SQL Trace File Contents

Formatting SQL Trace Files: Overview

Invoking the tkprof Utility

Automating SQL Tuning

Tuning SQL Statements Automatically

Application Tuning Challenges

SQL Tuning Advisor: Overview

Stale or Missing Object Statistics

SQL Statement Profiling

Plan Tuning Flow and SQL Profile Creation

Database Control and SQL Tuning Advisor

Implementing Recommendations

Introduction to SQL Tuning

Reasons for Inefficient SQL Performance

Performance Monitoring Solutions

Monitoring and Tuning Tools: Overview

EM Performance Pages for Reactive Tuning

CPU and Wait Time Tuning Dimensions

Scalability with Application Design, Implementation, and Configuration

Common Mistakes on Customer Systems

Proactive Tuning Methodology

Introduction to the Optimizer

Structured Query Language

SQL Statement Representation, Implementation & Processing: Overview

SQL Statement Parsing: Overview

Why Do You Need an Optimizer?

Optimization During Hard Parse Operation

Cost-Based Optimizer

Controlling the Behavior of the Optimizer

Optimizer Features and Oracle Database Releases

Optimizer Operators

Row Source Operations

Main Structures and Access Paths

Full Table Scan

Indexes: Overview

Using Indexes: Considering Nullable Columns

Bitmap Indexes, Composite Indexes &Invisible Index

Guidelines for Managing Indexes

Clusters

Interpreting Execution Plans

Case Study: Star Transformation

Optimizer Statistics

Optimizer Statistics & Types of Optimizer Statistics

Multicolumn Statistics: Overview

Expression Statistics: Overview

Gathering System Statistics

Statistic Preferences: Overview

Optimizer Dynamic Sampling: Overview

Locking Statistics

Using Bind Variables

Cursor Sharing and Different Literal Values

Cursor Sharing and Bind Variables

Bind Variables in SQL*Plus & Enterprise Manager

Cursor Sharing Enhancements

Adaptive Cursor Sharing: Overview

Interacting with Adaptive Cursor Sharing

Using Optimizer Hints

Optimizer Hints: Overview

Types of Hints

Specifying Hints

Rules for Hints

Hint Recommendations

Hint Categories

Optimization Goals and Approaches

Additional Hints

1202 log-522.pdf