Oracle Query Tuning

Faculty profile:

Real time professionals with 16+ years of experience.

Duration:

18 - 20 Hrs.

Audience:

Database Administrators, Developers, PL/SQL Developers and Support Engineers

Software needed on participant’s PC:

Linux OS on laptop or desktop,  Oracle Database 11g

Course Objectives:

Oracle Query Tuning, Understanding Execution Plans, PL/SQL Optimisations, Oracle CBO Behaviour and etc.

Course Topics:

Oracle Architecture

SGA and PGA

Shared Pool Architecture and Operation

The library Cache & Latch and Mutex

Avoiding Hard & Soft Parses

Data Dictionary Cache & SQL Query Result Cache

Query Execution Plans

Generating Execution Plans using dbms_xplan

Interpreting Execution Plans

Access Paths and Optimiser Joins

Understanding Oracle Optimiser

Parameter that impact CBO

Influencing the optimiser

Functions of the query optimiser, Selectivity, Cardinality and Cost and changing optimiser behavior

Using Hints

Controlling the Behaviour of the optimiser with parameters

Enabling Query Optimiser Features and influencing Optimiser Approach

Optimising SQL Statements, Access Paths & Choosing an Access Path

Join & Sort Operations

How the Query Optimiser Chooses Execution Plans for Joins

Reducing the Cost

Optimize Statistics

Generating statistics using dbms_stats

Optimiser Statistics and extended statistics

Identifying Problem SQL Statements

Using AWR and ASH Report to Identify Problem Queries

Using v$session, v$session_wait, v$sysstat, v$active_session_history view to Identify the Problem Queries

10046 tracing and analysing 

Generating TKPROF output and reading it.

PL/SQL Optimisation

Bulk Collections

Understanding Indexes and its internals

B-Tree index

Bitmap index

Tables/Partitioned Tables

Points to be considered while creating Tables and Partitioned Tables

SQL Baseline

Understanding SQL Baselines

DBMS_PARALLEL

Uses of dbms_parallel

Table Redefinition

Oracle Parallelism