Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

Overview

VTC author, Gavin Powell, covers material in the 1Z0-033 Oracle 9i tuning certification examination, including the basics of performance tuning and tuning tools. Memory tuning covers the shared pool, the database buffer cache and other buffers. From an I/O perspective, topics covered are physical file structures of the database, tablespaces, datafiles and at the block level. Also covered are sorting, locking, latches and wait events, shared or dedicated server processes, statistics, SQL code tuning and resource management. There is also brief coverage of tuning in the operating system, plus specialized objects use. To begin learning today, simply click on one of the Oracle Performance Tuning (1Z0-033) lessons.

Subscriber Reviews

Average Rating: 4.25 out of 5 rating Based on 4 Ratings

No Subscribers have provided a review for this video.

Table of Contents

Chapter/Selection

Time

Introduction

Welcome

Preview

00:00:55

Prerequisites for this Course

Preview

00:05:53

About this Course pt. 1

Preview

00:04:28

About this Course pt. 2

Preview

00:02:40

Oracle Tuning Documentation

Preview

00:01:06

What is Performance Tuning?

Who Tunes and What Do They Tune?

Preview

00:02:07

The Different Stages of Tuning

Preview

00:04:09

Development/Testing/Production

Preview

00:04:02

Statistics and Performance Evaluation

Preview

00:03:30

Goal Oriented Tuning

Preview

00:05:06

Common Problems pt. 1

Preview

00:03:16

Common Problems pt. 2

Preview

00:04:37

Performance Versus Integrity

Preview

00:01:53

Oracle Enterprise Manager (OEM)

Preview

00:02:55

Performance Tools

The Alert Log

Preview

00:04:37

Process Trace Files

Preview

00:04:25

User Trace Files

Preview

00:01:33

Setting Up Tracing

Preview

00:04:52

TKPROF

Preview

00:03:02

Manually Collecting Statistics

Preview

00:01:46

STATSPACK and Collecting Statistics

Preview

00:01:33

Collecting Statistics in OEM

Preview

00:04:22

Introducing the Wait Event Interface

Preview

00:04:34

What to Tune

Preview

00:05:12

Tuning the Shared Pool

The Shared Pool

Preview

00:02:52

Shared Pool Efficiency

Preview

00:05:28

Shared Pool and Reserved Shared Pool

Preview

00:04:14

Library and Dictionary Cache Hit Ratios

Preview

00:04:53

Pinning Objects in the Shared Pool

Preview

00:04:44

The Large Pool

Preview

00:01:54

Caching Cursors

Preview

00:02:18

OEM and the Shared Pool

Preview

00:02:09

Performance Views

Preview

00:00:57

The Database Buffer Cache

Database Buffer Cache Efficiency

Preview

00:03:19

OLTP Versus Data Warehouse

Preview

00:02:56

Configure the Database Buffer Cache

Preview

00:03:58

Examine the Database Buffer Cache

Preview

00:03:20

The Buffer Cache Hit Ratio

Preview

00:03:19

Processes and the Buffer Cache

Preview

00:04:56

How to Tune Buffer Cache

Preview

00:04:48

How Else to Tune Buffer Cache

Preview

00:03:29

Free List Contention

Preview

00:05:07

Multiple Database Writers and Slaves

Preview

00:03:40

Monitor Buffer Cache and Subset Pools

Preview

00:04:32

OEM and the Database Buffer Cache

Preview

00:04:37

Performance and Metadata Views

Preview

00:00:30

Other Buffers

Tuning the Redo Log Buffer

Preview

00:02:46

Sizing the Redo Log Buffer

Preview

00:03:48

The Java Pool

Preview

00:02:25

Configuring Automatic PGA Memory

Preview

00:03:30

Configuring Manual PGA Memory

Preview

00:04:11

Tuning PGA Memory

Preview

00:03:06

Changing SGA Memory Online

Preview

00:02:02

SGA_MAX_SIZE

Preview

00:04:11

Changing Non SGA Memory Online

Preview

00:02:13

Performance Views

Preview

00:03:56

Tuning I/O: Physical File Structures

Datafiles/Tablespaces/Objects

Preview

00:02:16

Control Files and Recoverability

Preview

00:02:47

Distribution of Database Files

Preview

00:04:50

Dividing Data Across Tablespaces

Preview

00:03:06

Oracle Partitioning

Preview

00:01:16

Tablespace Storage Structures

Preview

00:04:18

Dictionary Managed Tablespaces

Preview

00:04:22

Locally Managed Tablespaces

Preview

00:01:48

Finding Tablespace Problems

Preview

00:05:18

Redo and Archive Log Files pt. 1

Preview

00:03:56

Redo and Archive Log Files pt. 2

Preview

00:02:56

Physical Structures in OEM

Preview

00:01:32

Performance Views

Preview

00:05:53

Metadata Views

Preview

00:01:05

Tuning I/O at the Block Level

Segments

Preview

00:03:16

Extents and Blocks

Preview

00:02:45

The High Water Mark

Preview

00:01:26

Reclaiming Unused Space

Preview

00:01:16

Row Chaining and Row Migration

Preview

00:05:22

Reorganizing Indexes

Preview

00:03:19

Detecting Problems With Indexes

Preview

00:04:07

Metadata Views

Preview

00:01:42

Tuning Sorts

How Does Sorting Work?

Preview

00:03:16

Which SQL Code Causes Sorting?

Preview

00:01:53

Demonstrating SQL Code Sorting

Preview

00:05:32

Sorting in Memory and on Disk

Preview

00:02:25

Analyzing Sorting

Preview

00:04:48

Minimizing Sorting

Preview

00:03:04

Sorting in OEM

Preview

00:04:55

Performance Views

Preview

00:05:21

Locking Issues

Locking Levels

Preview

00:03:04

Types of Contention pt. 1

Preview

00:02:31

Types of Contention pt. 2

Preview

00:03:52

Types of Locks

Preview

00:03:37

Detecting Lock Contention

Preview

00:02:06

How to Avoid Locking

Preview

00:04:48

Lock Monitoring and Removal in OEM

Preview

00:03:39

Performance and Metadata Views pt. 1

Preview

00:04:49

Performance and Metadata Views pt. 2

Preview

00:01:43

Latches and Wait Events

What is a Latch?

Preview

00:01:31

Different Types of Latches

Preview

00:03:30

Shared Pool Latches pt. 1

Preview

00:03:18

Shared Pool Latches pt. 2

Preview

00:02:35

Database Buffer Cache Latches pt. 1

Preview

00:03:23

Database Buffer Cache Latches pt. 2

Preview

00:03:12

Database Buffer Cache Latches pt. 3

Preview

00:03:34

Redo Log Latches

Preview

00:02:20

Session Connection Latches

Preview

00:01:54

Latch Spins, Misses and Sleeps

Preview

00:03:28

Latch Miss Rate

Preview

00:02:14

Latch Miss Total Time

Preview

00:03:30

What is a Wait Event?

Preview

00:02:20

Event Types and TIMED_STATISTICS

Preview

00:02:06

Idle Wait Events

Preview

00:01:48

Querying Significant Wait Events

Preview

00:03:40

Wait Events in an Active Database

Preview

00:04:43

Analyzing Significant Wait Events

Preview

00:05:17

The Wait Event Interface

Preview

00:05:56

The Wait Event Interface in OEM

Preview

00:05:23

Performance Views

Preview

00:03:05

Shared Servers and Dedicated Servers

Tuning Oracle Net Services

Preview

00:02:33

Shared Versus Dedicated Servers

Preview

00:02:14

Shared Server Configuration

Preview

00:03:20

Oracle Net Manager in OEM

Preview

00:01:23

Wait Events and Performance Views pt. 1

Preview

00:04:13

Wait Events and Performance Views pt. 2

Preview

00:05:24

Statistics

What are Statistics?

Preview

00:02:04

The Importance of Realistic Statistics

Preview

00:03:40

Statistics and OPTIMIZER_MODE

Preview

00:02:26

Statistics and CURSOR_SHARING

Preview

00:03:47

CBO Parameters pt. 1

Preview

00:03:10

CBO Parameters pt. 2

Preview

00:04:05

CBO Parameters pt. 3

Preview

00:02:48

Gathering Statistics using ANALYZE

Preview

00:04:51

Histograms and the ANALYZE Command

Preview

00:04:29

Gathering Statistics using DBMS_STATS

Preview

00:03:52

Gathering Statistics using OEM

Preview

00:02:23

What is STATSPACK?

Preview

00:03:18

Installing and Using STATSPACK

Preview

00:04:43

Metadata Views

Preview

00:01:36

Applications and Specialized Objects

Database Type Requirements

Preview

00:04:01

Oracle Special Storage Structures pt. 1

Preview

00:03:53

Oracle Special Storage Structures pt. 2

Preview

00:04:50

Applying Storage Structures

Preview

00:05:11

Creating BTree and Bitmap Indexes Syntax

Preview

00:04:45

Creating BTree and Bitmap Indexes Examples

Preview

00:02:49

Creating Index Organized Tables

Preview

00:03:20

Creating Clusters

Preview

00:02:14

Creating Materialized Views

Preview

00:03:39

Oracle Partitioning Methods Syntax

Preview

00:04:24

Oracle Partitioning Methods Examples pt. 1

Preview

00:05:10

Oracle Partitioning Methods Examples pt. 2

Preview

00:02:25

Metadata Views

Preview

00:02:05

SQL Code Tuning

Building Efficient SQL Code

Preview

00:01:56

Tuning Query Clauses pt. 1

Preview

00:04:32

Tuning Query Clauses pt. 2

Preview

00:03:33

Tuning Query Clauses pt. 3

Preview

00:04:46

Comparisons

Preview

00:03:09

Joins

Preview

00:05:09

Other Factors pt. 1

Preview

00:03:57

Other Factors pt. 2

Preview

00:03:41

SQL Code Execution Process

Preview

00:03:50

The Optimizer and What It Does

Preview

00:03:02

The Internal Machinations of the Optimizer pt. 1

Preview

00:04:16

The Internal Machinations of the Optimizer pt. 2

Preview

00:04:29

Hints

Preview

00:04:58

Types of Hints

Preview

00:01:36

Influence the Optimizer pt. 1

Preview

00:03:50

Influence the Optimizer pt. 2

Preview

00:04:55

Alter Table Scans

Preview

00:01:05

Alter Index Scans pt. 1

Preview

00:03:57

Alter Index Scans pt. 2

Preview

00:04:01

Modify Joins pt. 1

Preview

00:03:54

Modify Joins pt. 2

Preview

00:05:01

Perform SQL in Parallel

Preview

00:02:40

Change Queries and Subqueries

Preview

00:02:02

Finding Queries with Problems

Preview

00:00:56

EXPLAIN PLAN and the PLAN_TABLE

Preview

00:04:49

Autotrace in SQL*Plus

Preview

00:00:27

SQL Trace and TKPROF pt. 1

Preview

00:03:25

SQL Trace and TKPROF pt. 2

Preview

00:03:41

Finding Bad Queries in OEM

Preview

00:01:59

Plan Stability and Stored Outlines

Preview

00:04:13

Performance and Metadata Views

Preview

00:02:22

Resource Management

The Oracle Resource Manager

Preview

00:00:46

Resource Manager Components

Preview

00:01:26

Resource Plan Directives

Preview

00:02:40

Create Groups and Plans and Allocate

Preview

00:02:46

Metadata and Performance Views

Preview

00:01:02

The Operating System

Basic Operating System Tuning

Preview

00:04:33

Virtual Memory/Paging/Swapping

Preview

00:03:35

What Determines I/O Distribution?

Preview

00:01:58

Database Function and Methods of Distribution

Preview

00:01:57

Oracle Database File Types Distribution

Preview

00:04:59

Database Block Size

Preview

00:03:20

Resource Consumption in OEM

Preview

00:01:29

Conclusion

Wrapping it All Up!

Preview

00:04:45

Credits

About the Author

Preview

00:01:14