INTRODUCTION

ABOUT THIS TEXTBOOK

Objectives

Target Audience

Course Prerequisites

Suggested Next Courses

Certification Examination

Instructor-Led Training [ILT] / Live Virtual Training Schedule [kit]

Learning Resources

Resource Files

ABOUT THE SAMPLE DATABASES

WORKSHOP SETUP GUIDE

ABOUT THE WORKSHOP SETUP

SECTION 1

TUNING & THE ORACLE DATABASE ADVISORY FRAMEWORK

SECTION OVERVIEW

THE CHALLENGES OF TUNING

Isolating The Problem

About Response Time

Identifying External Factors

Application & Logical Database Design

PERFORMANCE METRICS

Timed Statistics

CPU Statistics

I/O Statistics

Wait Events

Timings

Response Time

Database Time

Elapsed Time

MANAGEMENT & ADVISORY FRAMEWORK

How Does This Help?

ADDM & AWR

SQL TUNING PRIVILEGES

Object Privileges

EM Database Privileges

SECTION REVIEW

WORKSHOP SECTION

TUNING & THE ORACLE DATABASE ADVISORY FRAMEWORK

EXERCISES

What You Will Do Within This Workshop

ADVISORY FRAMEWORK-1

ADVISORY FRAMEWORK-2

ADVISORY FRAMEWORK-3

SOLUTIONS

ADVISORY FRAMEWORK-1

ADVISORY FRAMEWORK-2

ADVISORY FRAMEWORK-3

SECTION 2

VIEWING & MONITORING THE EXECUTION PLAN

SECTION OVERVIEW

ABOUT THE EXECUTION PLAN

Sample Execution Plan

Reading An Execution Plan

Using The Execution Plans For Tuning

COLLECTING PERFORMANCE STATISTICS

Database Performance Statistics

SQL Tracing

VIEWING THE EXECUTION PLAN

Create PLAN_TABLE

Populate & Examine PLAN_TABLE

The SQL Statement EXPLAIN PLAN

Generating & Storing The Plan

Viewing The Plan

Deleting The Plan Details

The utlxpl*.sql Scripts

Viewing The Output

The System-supplied Package DBMS_XPLAN()

Controlling The Level Of Detail

SQL*Plus Command AUTOTRACE

Enabling The Utility

SET AUTOTRACE Options

Using AUTOTRACE

V$SQL_PLAN View

Using EM

Home Page, Active Sessions

Top Activity

Search Sessions

Session Details (General)

Session Details (Activity)

Session Details (Statistics)

Session Details (Blocking Tree)

Session Details (Wait Event History)

Session Details (Open Cursors)

SQL Details (Statistics)

SQL Details (Plan)

REAL-TIME SQL MONITORING

About Real-Time Automatic SQL Monitoring

Session Details, SQL Details

SECTION REVIEW

WORKSHOP SECTION

VIEWING & MONITORING THE EXECUTION PLAN

EXERCISES

What You Will Do Within This Workshop

VIEW PLAN-1

VIEW PLAN-2

VIEW PLAN-3

VIEW PLAN-4

VIEW PLAN-5

VIEW PLAN-6

VIEW PLAN-7

VIEW PLAN-8

VIEW PLAN-9

VIEW PLAN-10

VIEW PLAN-11

VIEW PLAN-12

SOLUTIONS

VIEW PLAN-1

VIEW PLAN-2

VIEW PLAN-3

VIEW PLAN-4

VIEW PLAN-5

VIEW PLAN-6

VIEW PLAN-7

VIEW PLAN-8

VIEW PLAN-9

VIEW PLAN-10

VIEW PLAN-11

VIEW PLAN-12

SECTION 3

UNDERSTANDING THE OPTIMIZER

SECTION OVERVIEW

OPTIMIZATION METHODS

What Are The Optimizer Statistics?

OPTIMIZATION GOALS

OPTIMIZER_MODE

Current Session Setting

Specific SQL Statement Setting

Which Instance Mode Is In Effect?

OPTIMIZER_FEATURES_ENABLE

Why Does This Parameter Exist?

How This Parameter Should Be Used

OPTIMIZER COMPONENTS

Optimization Overview

What Is SQL Statement Tuning?

Transformation Engine

Estimator

Selectivity

Cardinality

Cost

Plan Generator

Why Multiple Plans Are Needed?

EXECUTION PLAN OPERATIONS

Access Methods

Join Methods

Data Operations (Sort)

SECTION REVIEW

WORKSHOP SECTION

UNDERSTANDING THE OPTIMIZER

EXERCISES

What You Will Do Within This Workshop

OPTIMIZER-1

OPTIMIZER-2

OPTIMIZER-3

OPTIMIZER-4

OPTIMIZER-5

OPTIMIZER-6

OPTIMIZER-7

SOLUTIONS

OPTIMIZER-1

OPTIMIZER-2

OPTIMIZER-3

OPTIMIZER-4

OPTIMIZER-5

OPTIMIZER-6

OPTIMIZER-7

SECTION 4

EXECUTION PLAN METHODS & OPERATIONS

SECTION OVERVIEW

TABLE ACCESS METHODS

More About Full Table Scans

JOIN METHODS

Nested Loop Join Enhancement

Join Sub-methods

INDEX OPERATIONS

DATA OPERATIONS

SECTION REVIEW

WORKSHOP SECTION

EXECUTION PLAN METHODS & OPERATIONS

EXERCISES

What You Will Do Within This Workshop

OPERATIONS-1

OPERATIONS-2

OPERATIONS-3

SOLUTIONS

OPERATIONS-1

OPERATIONS-2

OPERATIONS-3

SECTION 5

MANAGING OPTIMIZER STATISTICS

SECTION OVERVIEW

MORE ABOUT OPTIMIZER STATISTICS

Statistics Collection Algorithms

Estimated Optimizer Statistics

Effect Upon Parsed SQL Statements

AUTOMATIC MAINTENANCE TASKS

About Auto-Task

Auto-Task Architecture & Implementation

Scheduler Windows

Managing Auto-Task Using EM

Configure Auto-Task

Managing Auto-Task Using SQL

Enable & Disable Tasks

Enable & Disable Auto-Task

Enable & Disable Scheduler Windows

MANUALLY GATHERING STATISTICS

Why Manually Gather Statistics?

Dictionary Objects & Fixed Objects

Managing Optimizer Statistics Using EM

Gather Optimizer Statistics

About The Related Links

Object Statistics

Managing Optimizer Statistics Using DBMS_STATS()

GATHER_TABLE_STATS()

General Parameters

Functional Parameters

Statistics Table Parameters

GATHER_INDEX_STATS()

GATHER_SCHEMA_STATS()

OPTIONS Parameter

objList Parameter

About dbms_stats.objectTab

objList Example

obj_filter_list Parameter

GATHER_DATABASE_STATS()

GATHER_SYS Parameter

Dictionary & Fixed Objects Statistics Calls

GATHER_DICTIONARY_STATS()

GATHER_FIXED_OBJECTS_STATS()

USING HISTORICAL STATISTICS

Example

Managing Historical Statistics

DYNAMIC SAMPLING

Why Use Dynamic Sampling?

Forcing Dynamic Sampling

Controlling Dynamic Sampling

LOCKING STATISTICS

Lock Statistics

Unlock Statistics

Metadata Storage

SECTION REVIEW

WORKSHOP SECTION

MANAGING OPTIMIZER STATISTICS

EXERCISES

What You Will Do Within This Workshop

STATS-1

STATS-2

STATS-3

STATS-4

STATS-5

STATS-6

STATS-7

STATS-8

STATS-9

STATS-10

STATS-11

SOLUTIONS

STATS-1

STATS-2

STATS-3

STATS-4

STATS-5

STATS-6

STATS-7

STATS-8

STATS-9

STATS-10

STATS-11

SECTION 6

ENHANCED OPTIMIZER STATISTICS

SECTION OVERVIEW

ABOUT OPTIMIZER SYSTEM STATISTICS

Gathering System Statistics

Which System Statistics May Be Collected?

MANAGE SYSTEM STATISTICS

CREATE_STAT_TABLE(), DROP_STAT_TABLE()

CREATE_STAT_TABLE[]

DROP_STAT_TABLE[]

GATHER_SYSTEM_STATS()

Parameters

GET_SYSTEM_STATS()

Parameters

SET_SYSTEM_STATS()

IMPORT_SYSTEM_STATS(), EXPORT_SYSTEM_STATS()

IMPORT_SYSTEM_STATS[]

EXPORT_SYSTEM_STATS[]

DELETE_SYSTEM_STATS()

PENDING & PUBLISHED STATISTICS

What Can Go Wrong

How This Can Be Addressed

DBMS_STATS[] Preferences

Collecting Pending Statistics

Generating Pending Statistics

Using Pending Statistics

Publish Pending Statistics

SECTION REVIEW

WORKSHOP SECTION

ENHANCED OPTIMIZER STATISTICS

EXERCISES

What You Will Do Within This Workshop

ENHANCED STATS-1

ENHANCED STATS-2

ENHANCED STATS-3

ENHANCED STATS-4

ENHANCED STATS-5

ENHANCED STATS-6

ENHANCED STATS-7

ENHANCED STATS-8

ENHANCED STATS-9

SOLUTIONS

ENHANCED STATS-1

ENHANCED STATS-2

ENHANCED STATS-3

ENHANCED STATS-4

ENHANCED STATS-5

ENHANCED STATS-6

ENHANCED STATS-7

ENHANCED STATS-8

ENHANCED STATS-9

SECTION 7

HISTOGRAMS & EXTENDED STATISTICS

SECTION OVERVIEW

WHY ARE HISTOGRAMS NEEDED?

Execution Plans Without Histograms

Execution Plans With Histograms

HISTOGRAMS INTERNAL STRUCTURE

Frequency Histogram

Histogram Limitation

MANUALLY MANAGING HISTOGRAMS

method_opt Parameter

EXPRESSION STATISTICS

Why Expression Statistics Are Needed

Gathering Extended Statistics

Impact Of Extended Statistics

Dropping Extended Statistics

MULTICOLUMN STATISTICS

Selectivity & Multiple Column Predicates

A Multiple Column Scenario

Why MultiColumn Statistics Are Needed

Gathering MultiColumn Statistics

Impact Of MultiColumn Statistics

Extended Statistics Summary

SECTION REVIEW

WORKSHOP SECTION

HISTOGRAMS & MULTICOLUMN STATISTICS

EXERCISES

What You Will Do Within This Workshop

EXTENDED STATS-1

EXTENDED STATS-2

EXTENDED STATS-3

EXTENDED STATS-4

EXTENDED STATS-5

EXTENDED STATS-6

EXTENDED STATS-7

EXTENDED STATS-8

EXTENDED STATS-9

EXTENDED STATS-10

EXTENDED STATS-11

EXTENDED STATS-12

SOLUTIONS

EXTENDED STATS-1

EXTENDED STATS-2

EXTENDED STATS-3

EXTENDED STATS-4

EXTENDED STATS-5

EXTENDED STATS-6

EXTENDED STATS-7

EXTENDED STATS-8

EXTENDED STATS-9

EXTENDED STATS-10

EXTENDED STATS-11

EXTENDED STATS-12

Section 8

APPLICATION TRACING

SECTION OVERVIEW

APPLICATION TRACING PACKAGES

Granting Access To The Packages

Enabling Tracing With DBMS_SESSION()

SET_SQL_TRACE[]

TRACE_ENABLE[]

SET_IDENTIFIER[]

Labeling With DBMS_APPLICATION_INFO()

Identifying A Client

Identifying A Module

Identifying An Action

Collecting Additional Statistics With DBMS_MONITOR()

Aggregating Statistics

Disabling Aggregation

AWR Storage Using DBMS_WORKLOAD_REPOSITORY()

Complete Example

Examining The Metadata

EM APPLICATION MONITORING & TUNING

SQL Tuning Goals

REAL-TIME MONITORING

SQL Response Time

Top Consumers

Top Services

Top Modules

Top Actions

Top Clients

SQL Details

USING TRCSESS & TKPROF

tkprof Output

Why Use These Utilities?

Assigning A Trace File Identifier

Format The Trace File(s) With tkprof

Generating The Report With tkprof

About Recursive SQL

Viewing The Report

Interpreting The Output

Header

Body

Summary

Drawing Correct Conclusions About The Statistics

What The Statistics Mean

Consolidating Trace Files With trcsess

trcsess & tkprof Example

trcsess Parameters

SECTION REVIEW

WORKSHOP SECTION

APPLICATION TRACING

EXERCISES

What You Will Do Within This Workshop

About This Workshop

APPLICATION TRACING-1

APPLICATION TRACING-2

APPLICATION TRACING-3

APPLICATION TRACING-4

APPLICATION TRACING-5

APPLICATION TRACING-6

APPLICATION TRACING-7

SOLUTIONS

APPLICATION TRACING-1

APPLICATION TRACING-2

APPLICATION TRACING-3

APPLICATION TRACING-4

APPLICATION TRACING-5

APPLICATION TRACING-6

APPLICATION TRACING-7

SECTION 9

ADDM & THE SQL TUNING ADVISOR

SECTION OVERVIEW

The SQL Advisors

ADDM PERFORMANCE ANALYSIS

Automatic ADDM Tasks

Historical ADDM Tasks

Manual ADDM Tasks

USING THE SQL TUNING ADVISOR

About The Recommendations

Scheduling During Top Activity Real-Time Monitoring

Viewing The Results

SQL Details [Tuning History]

SQL Tuning Result Summary

AUTOMATIC SQL TUNING

What Is Enhanced Tuning Mode?

Examining The Results

Task Status / Summary Time Period

Overall Task Statistics

Statistics Finding / Index Finding Summary

Understanding SQL Profiling Analysis

What Is SQL Profiling?

Implementing A Profile

Key SQL Profiles

Automatic Implementation

Understanding SQL Structure Analysis

Semantic-Based Constructs

Syntax-Based Constructs

Design Issues

Understanding Alternative Plan Analysis

Access Path Analysis

Optimizer Statistics Analysis

CONFIGURING AUTOMATIC SQL TUNING

Enabling & Disabling Automatic SQL Tuning

Configuring Automatic SQL Tuning Using EM

Configuring Automatic SQL Tuning Using EM

SECTION REVIEW

WORKSHOP SECTION

ADDM & THE SQL TUNING ADVISOR

EXERCISES

What You Will Do Within This Workshop

SQL TUNING-1

SQL TUNING-2

SQL TUNING-3

SOLUTIONS

SQL TUNING-1

SQL TUNING-2

SQL TUNING-3

SECTION 10

THE SQL ACCESS ADVISOR

SECTION OVERVIEW

USING THE SQL ACCESS ADVISOR

About This Advisor

Workload Usage

Analysis

Scheduling A Task

Initial Options

Workload Source

Recommendation Options

Schedule

Review

Viewing The Recommendations

Summary

Recommendations

SQL Statements

Details

Implementing The Recommendations

SQL ACCESS ADVISOR TEMPLATES

PERFORMING A QUICK TUNE TASK

Example

Recommendations

INDEX DATABASE PARAMETERS

OPTIMIZER_INDEX_COST_ADJ

OPTIMIZER_INDEX_CACHING

SKIP_UNUSABLE_INDEXES

OPTIMIZER_USE_INVISIBLE_INDEXES

SECTION REVIEW

WORKSHOP SECTION

THE SQL ACCESS ADVISOR

EXERCISES

What You Will Do Within This Workshop

SQL ACCESS-1

SQL ACCESS-2

SQL ACCESS-3

SQL ACCESS-4

SQL ACCESS-5

SOLUTIONS

SQL ACCESS-1

SQL ACCESS-2

SQL ACCESS-3

SQL ACCESS-4

SQL ACCESS-5

SECTION 11

PLAN MANAGEMENT

SECTION OVERVIEW

WHY IS PLAN MANAGEMENT NEEDED?

When To Use Plan Management

SQL MANAGEMENT BASE ARCHITECTURE

Managing The SQL Management Base

About Fixed SQL Plans

About Enabled Plans

CREATING PLAN BASELINES

optimizer_capture_sql_plan_baselines

Manual Plan Capture

LOAD_PLANS_FROM_SQLSET()

LOAD_PLANS_FROM_CURSOR_CACHE()

USING PLAN BASELINES

optimizer_use_sql_plan_baselines

Baseline Evolution

Verify Only

Parameters

Commit Baseline Evolution

PLAN MANAGEMENT USING EM

Controlling Plan Management

Controlling Specific Plans

Loading Plans From An STS

SECTION REVIEW

WORKSHOP SECTION

PLAN MANAGEMENT

EXERCISES

What You Will Do Within This Workshop

PLAN MANAGEMENT-1

PLAN MANAGEMENT-2

PLAN MANAGEMENT-3

PLAN MANAGEMENT-4

PLAN MANAGEMENT-5

PLAN MANAGEMENT-6

PLAN MANAGEMENT-7

PLAN MANAGEMENT-8

PLAN MANAGEMENT-9

PLAN MANAGEMENT-10

PLAN MANAGEMENT-11

PLAN MANAGEMENT-12

PLAN MANAGEMENT-13

PLAN MANAGEMENT-14

PLAN MANAGEMENT-15

SOLUTIONS

PLAN MANAGEMENT-1

PLAN MANAGEMENT-2

PLAN MANAGEMENT-3

PLAN MANAGEMENT-4

PLAN MANAGEMENT-5

PLAN MANAGEMENT-6

PLAN MANAGEMENT-7

PLAN MANAGEMENT-8

PLAN MANAGEMENT-9

PLAN MANAGEMENT-10

PLAN MANAGEMENT-11

PLAN MANAGEMENT-12

PLAN MANAGEMENT-13

PLAN MANAGEMENT-14

PLAN MANAGEMENT-15

SECTION 12

MANAGING CURSOR SHARING

SECTION OVERVIEW

ABOUT CURSOR SHARING

The Importance Of Cursor Sharing

About Cursor Sharing From SQL Cache

About Cursor Sharing From PL/SQL Cache

BIND VARIABLES & CURSOR SHARING

Using A Bind Variable

About Cursor Peeking (Bind Variable Peeking)

Limitations To Cursor Peeking

Disable Cursor Sharing

Adaptive Cursor Sharing

Monitoring Adaptive Cursor Sharing

THE CURSOR_SHARING PARAMETER

Setting CURSOR_SHARING

Setting The Parameter To EXACT

Setting The Parameter To SIMILAR

When To Use SIMILAR

Setting The Parameter To FORCE

FINDING CANDIDATES FOR REWRITE

SECTION 13

OPTIMIZER HINTS

SECTION OVERVIEW

WHAT ARE HINTS?

Example Scenario

Should One Really Use Hints?

Hint Syntax

Referencing Query Blocks

Additional Rules

HINT EXPLANATIONS

Optimizer Mode Hints

Table Access Method Hints

Index Scan Hints

General Join Operation Hints

Miscellaneous Hints

SECTION REVIEW

WORKSHOP SECTION

OPTIMIZER HINTS

EXERCISES

What You Will Do Within This Workshop

HINTS-1

HINTS-2

HINTS-3

HINTS-4

HINTS-5

HINTS-6

SOLUTIONS

HINTS-1

HINTS-2

HINTS-3

HINTS-4

HINTS-5

HINTS-6