Oracle Performance Tuning

Table of Contents:

1.       Basics of Oracle Internals

a.       Oracle Architecture (Most Important One in tuning Queries).

b.      Data files, control files, redo log files, init.ora, sp file, pwd file. Etc…

c.       DB blocks, extents, segments, tablespaces…

2.       Introduction to query Tuning.

a.       Basic Steps to be followed before tuning.

b.      Identifying the bottlenecks

c.       Short term Fix and Long Term Fixes

d.      Best practices to be followed while creating objects and writing queries.

e.      Do I need to use SQL or PLSQL for specific requirement?

3.       Internal processing of SQL statements

a.       Statement Processing

b.      Soft Parsing

c.       Hard Parsing

d.      SQL Semantic Analysis

e.      Generating the execution plan

f.        Using optimizer plan stability

g.       Using the v$sql view

h.      Using the v$sql_plan view

i.         Exercise – Query the library cache

4.       Gathering Statistics.

a.       Purpose of statistics

b.      When to gather and its impact

c.       Types of statistics (table, column, system)

d.      Histogram statistics

e.      Dynamic sampling

f.        using dbms_stats

g.       using analyze statements

h.      Exporting/importing statistics

i.         Statistics management

5.       Internals of Query Optimizer

a.       Different modes of SQL optimization

b.      Rule-based optimization

c.       Cost-based optimization

d.      All rows optimization

e.      First_rows optimization

f.        Components of the Optimizer

g.       Bind variables.

6.       Optimizer Access Paths and table joins

a.       Basics of file I/O

b.      Sequential reads vs. scattered reads

c.       Table Full Scan, Rowid Scan, Index Scan

d.      Nested Loop Joins

e.      Sort Merge Joins

f.        Outer Joins

g.       Hash Joins

h.      Cartesian Joins

i.         Internal structure of Indexes storage

j.        Index Range Scan

k.       Index Fast scan /Index Fast Full scan

l.         Index (Max/Min) scan

m.    Index Skip Scan

n.      Histogram

o.      Drawback of Bitmap Index (In OLTP Applications)

p.      Nested Loop /Hash Join/Sort-Merge Join/ Hash Outer Join

q.      Sequential Read/ Scattered Read

r.        Dead Lock Solution for Wait Event Problems etc

7.       Execution Plan

a.       What is execution plan?

b.      How to generate plan.

c.       PLAN_TABLE Table

d.      Reading Execution Plan

e.      Parallel Execution Plan

f.        Modes of Tracing

g.       How To Read Smartly TKPROF

h.      Raw trace files

i.         Autotrace and Explain plan

j.        Reading AWR Reports

k.       Table join order evaluation

l.         Using the ordered hint

m.    Re-writing SQL queries 

8.       Optimizer Hints

a.       when to use Hints

b.      Impact of Hints on Execution Plan

c.       Scope of hints (session-level, statement level)

d.      Types of Hints

e.      Forcing index usage

f.        Using hints in subqueries

9.       Index Optimization and Table compression

a.       Types of Indexes

b.      When to use which type of index

c.       B-Tree indexes

d.      Bitmap Indexes

e.      Function-based Indexes

f.        Clustered indexes

g.       Index-only tables

h.      Table Partitioning methodology for performance

10.   DML  Tuning

a.       Optimizing Oracle SQL insert performance         

b.      High Impact insert Tuning Techniques   

c.       Block size and insert performance           

d.      Oracle Delete & Update Tuning

e.      With clause

11.   Tuning Subqueries

a.       Tuning Subqueries

b.      Types of SQL Subqueries

c.       Subqueries in the where Clause

d.      In vs. exists Subqueries

e.      Joins

f.        Correlated Subquery

g.       Tuning Scalar Subqueries

h.      Scalar Subquery Performance

i.         Internals of Temporary Tables

j.        Subquery Hint Tuning

k.       Subquery Tuning with Index Hints

l.         Materialized Views and its impact in performance

m.    Query rewriting using MViews

n.      With Clause

12.    PLSQL Tuning

a.       Do’s and Don’ts while writing PLSQL code.

b.      Using Bind variables.

c.       Improving Performance using Pipelined Functions

d.      Bulk collect

e.      FORALL

f.        Creating Packages/ Procedures/ Functions/ Triggers

g.       Exception Handling

h.      Drawback of Triggers

i.         Autonomous Transaction etc

j.        Optimizer Compiler

k.       Data Caching Techniques (Package , Deterministic Function, Result Cache…etc)

l.         Hierarchical Profile

m.    NOCOPY Hint

Salient Features.

1.       Course will be covered in real time point of view.

2.       Day wise notes.

3.       Day wise workouts and interview questions on the topics covered.