SQL Issues and Tuning Considerations for Different Applications

Objectives

Objectives

The Role of the DBA

Diagnostic Tools Overview

Explain Plan

SQL Trace and TKPROF

Enabling and Disabling SQL Trace

Formatting the Trace File
with TKPROF

TKPROF Statistics

SQL*Plus AUTOTRACE

Optimizer Modes

Setting the Optimizer Mode

Managing Statistics

Table Statistics

Index Statistics

Column Statistics

Histograms

Copying Statistics Between Databases

Example: Copying Statistics

Optimizer Plan Stability

Plan Equivalence

Creating Stored Outlines

Using Stored Outlines

Maintaining Stored Outlines

Data Access Methods

B-Tree Index

Bitmap Index

Bitmap Indexes

Creating and Maintaining
Bitmap Indexes

Comparing B-Tree and
Bitmap Indexes

Reverse Key Index

Creating Reverse Key Indexes

Index-Organized Tables

Index-Organized Tables
Compared with Regular Tables

Creating Index-Organized Tables

IOT Row Overflow

IOT Dictionary Views

Clusters

Cluster Types

Situations in Which Clusters
Are Useful

Materialized Views

 Materialized Views: Manual Refresh

Query Rewrites

Query Rewrites

Materialized Views and
Query Rewrites: Example

Materialized Views and
Query Rewrites: Example

Enabling and Controlling
Query Rewrites

Disabling Query Rewrites: Example

OLTP Systems

OLTP Requirements

OLTP Requirements

OLTP Application Issues

DSS Systems

DSS Requirements

DSS Requirements

DSS Application Issues

Multipurpose Applications

Hybrid Systems

Parameters for Hybrid Systems

Hybrid Systems Configuration

Summary

Summary

Summary