Oracle Performance Tuning

Developers, DBA, QA personnel, project managers, team leaders

Experience with SQL language

Oracle architecture – Overview

• Database and instance
• The Buffer cache and the Shared pool
• Logical storage structure – Tablespaces and segments.

Indexes – introduction

• The B*Tree Index structure
• Indexes vs. Full Scans in table data access
• Unique and non-unique indexes
• Single column and composite indexes

SQL Tuning and execution plans

• Identifying the high load SQL statements
• GUI performance tools (OEM), AWR report, Oracle Tracing
• Tools for retrieving execution plans and execution statistics
• Autotrace, DBMS_XPLAN, EXPLAIN PLAN FOR, Developers Graphical tools
• The Oracle Optimizer and basic concepts of execution plans
• What is “Cost”, “Cardinality”, “Selectivity” and so on.

Affecting execution plans

• Hints, Optimizer statistics, Parameters, re-write SQL

Basic access paths

• Full scans, Indexes and Row-id access paths
• Common index access paths Unique scan, Range scan, Full scan, Fast full scan
• Common join algorithms Nested loops, Sort-Merge, Hash-Join

Execution plan real time statistics

• SQL Monitoring (11g), Gather plan statistics (10g/11g) (View Actual values of the execution compared to the optimizer estimated ones)
• Gets (logical IO) vs. Reads (physical IO)

Re-writing the SQL to perform less work

• Tips, tricks and real-life examples.

Application tracing

• Using SQL trace and TKPROF
• Using DBMS_APPLICATION_INFO, DBMS_MONITOR and TRCSESS

Generating the Oracle performance reports via developer tools:

• AWR reports (General / SQL level)
• ASH reports (General / SQL level)
• ADDM reports

Advanced Indexes issues – tips, tricks and real-life examples

• Using indexes efficiently – tips, tricks and real-life examples
• Various Index types and index considerations
• Indexes and expressions, Composite indexes, Invisible
• Indexes Unusable indexes, Function based indexes, Indexes and
• NULL values, indexes and DMLs.

Partitioned tables

• Partitioned tables – Overview
• Global and Local indexes – Pros, cons and considerations
• Partition pruning – Utilizing and monitoring

Cleaning old data

• The high-water mark and full scans.
• Delete vs. Truncate vs. Drop
• Using partitions to purge old data efficiently

Optimizer Statistics

• Gathering statistics at different levels (Table, Schema, Database)
• Histograms and column statistics
• Oracle automatic mechanism for statistics gathering
• Extended statistics
• Statistics and Plan stability – the trade off
• When to lock statistics
• How to restore statistics

SQL Tuning – Real world examples, tips and tricks

• These will be scattered throughout the course