AT11g R2 - Oracle Database 11g: SQL Tuning Workshop

cursos oracle

Objetivos

Este workshop ajuda os desenvolvedores de banco de dados, DBAs e desenvolvedores de SQL na identificação e ajustes de instruções SQL ineficientes. Você vai explorar métodos de investigação para revelar níveis variados de detalhes sobre como o banco de dados Oracle executa a instrução SQL; Isso ajuda a determinar as causas das instruções SQL ineficientes.

Público

Desenvolvedores de aplicativos

Administrador de Data Warehouse

Desenvolvedor de Data Warehouse

Os administradores de banco de dados

Desenvolvedor PL / SQL

Engenheiro de suporte

Pré-requisitos

Requeridos:

Oracle Database: Introduction to SQL

Sugeridos:

Oracle Database: Fundamentos de SQL I

Ementa

Exploring the Oracle Database Architecture

Oracle Database Server Architecture: Overview

Connecting to the Database Instance

Physical Structure

Oracle Database Memory Structures: Overview

Automatic Shared Memory Management

Automated SQL Execution Memory Management

Database Storage Architecture, Logical and Physical Database Structures

Segments, Extents, and Blocks & SYSTEM and SYSAUX Tablespaces

Introduction to SQL Tuning

Reason for Inefficient SQL Performance

Performance Monitoring Solutions

Monitoring and Tuning Tools: Overview

CPU and Wait Time Tuning Dimensions

Scalability with Application Design, Implementation, and Configuration

Common Mistakes on Customer systems & Proactive Tuning Methodology

Simplicity in Application Design

Data Modeling, Table Design, Index Design, Using Views, SQL Execution Efficiency, Overview of SQL*Plus & SQL Developer

Introduction to the Optimizer

Structured Query Language

SQL Statement Parsing: Overview

Why Do You Need an Optimizer?

Optimization During Hard Parse Operation

Transformer & Estimator

Cost-Based Optimizer

Plan Generator

Controlling the Behavior of the Optimizer, Optimizer Features and Oracle Database Releases

Interpreting Execution Plans

What Is an Execution Plan? Where To Find Execution Plans and Viewing Execution Plans

Plan Table & AUTOTRACE

Using the V$SQL_PLAN View

Automatic Workload Repository (AWR)

SQL Monitoring: Overview

Interpreting an Execution Plan

Reading More Complex Execution Plans and Reviewing the Execution Plan

Looking Beyond Execution Plans

Application Tracing

End-to-End Application Tracing Challenge

Location for Diagnostic Traces

What is a Service? Use Services with Client Applications & Tracing Services

Use Enterprise Manager to Trace Services

Session Level Tracing: Example

The trcsess Utility and SQL Trace File Contents

Invoking the tkprof Utility and Output of the tkprof Command

tkprof Output with and without Index: Example

Optimizer: Table and Index Operations

Row Source Operations, Main Structures and Access Paths

Full Table Scan

Indexes: Overview and B*-tree Indexes and Nulls

Using Indexes: Considering Nullable Columns

Index-Organized Tables

Bitmap Indexes, Bitmap Operations and Bitmap Join Index

Composite Indexes and Invisible Index

Guidelines for Managing Indexes and Investigating Index Usage

Optimizer Join Methods

Nested Loops Join

Nested Loops Join: 11g Implementation

Sort Merge join

Hash Join and Cartesian Join

Equijoins and Nonequijoins

Outer Joins

Semijoins

Antijoins

Optimizer: Other Operators

When Are Clusters Useful?

Sorting Operators and Buffer Sort Operator

Inlist Iterator and View Operator

Count Stop Key Operator

Min/Max and First Row Operators and Other N-Array Operations

Filter operations and Concatenation Operations

UNION [ALL], INTERSECT, MINUS

Result Cache Operator

Case Study: Star Transformation

The Star Schema Model and The Snowflake Schema Model

Star Transformation

Retrieving Fact Rows from One Dimension and from All Dimensions

Joining the Intermediate Result Set with Dimensions

Star Transformation Plan Examples

Star Transformation Hints

Using Bitmap Join Indexes

Bitmap Join Indexes: Join Model 1 to 4

Optimizer Statistics

Types of Optimizer Statistics

Table, Index and Column Statistics

Index Clustering Factor

Histograms, Frequency Histograms and Histogram Considerations

Multicolumn Statistics and Expression Statistics Overview

Gathering System Statistics and Statistic Preferences

Manual Statistics Gathering

Locking Statistics, Export/Import Statistics and Set Statistics

Using Bind Variables

Cursor Sharing and Different Literal Values

Cursor Sharing and Bind Variables

Bind Variable Peeking

Cursor Sharing Enhancements

The CURSOR_SHARING Parameter

Forcing Cursor Sharing

Adaptive Cursor Sharing

Interacting with Adaptive Cursor Sharing

Using SQL Tuning Advisor

Tuning SQL Statements Automatically

Application Tuning Challenges

SQL Tuning Advisor: Overview

Stale or Missing Object Statistics and SQL Statement Profiling

Plan Tuning Flow and SQL Profile Creation

SQL Tuning Loop, Access Path Analysis and SQL Structure Analysis

Database Control and SQL Tuning Advisor

Implementing Recommendations

Using SQL Access Advisor

SQL Access Advisor: Overview

Possible Recommendations

SQL Access Advisor Session: Initial Options

SQL Access Advisor: Workload Source

SQL Access Advisor: Recommendation Options

SQL Access Advisor: Schedule and Review

SQL Access Advisor: Results

SQL Access Advisor: Results and Implementation

Using Automatic SQL Tuning

SQL Tuning Loop

Automatic SQL Tuning

Automatic Tuning Process

Configuring Automatic SQL Tuning

Automatic SQL Tuning: Result Summary

Automatic SQL Tuning: Result Details

Automatic SQL Tuning Result Details: Drilldown

Automatic SQL Tuning Considerations

SQL Performance Management

Maintaining SQL Performance and SQL Plan Management: Overview

SQL Plan Baseline: Architecture

Important Baseline SQL Plan Attributes

SQL Plan Selection

Possible SQL Plan Manageability Scenarios

SQL Performance Analyzer and SQL Plan Baseline Scenario

Loading a SQL Plan Baseline Automatically and Purging SQL Management Base Policy

Enterprise Manager and SQL Plan Baselines

Carga Horária

24 horas

Valor

R$ 3.016,00

Formato de Entrega

              
No momento não temos datas programadas, entre em contato através do info@multirede.com.br ou pelo chat em horário comercial.