Utilities and Dynamic Performance Views

Objectives

After completing this lesson, you should be able to

do the following:

Collect statistics through:

Available dynamic troubleshooting and performance views

The UTLBSTAT/UTLESTAT report output

Oracle wait events

Appropriate Enterprise Manager (EM) tuning tools

Define the latch types

Use EM to set events for predefined situations

Views, Utilities, and Tools

Dynamic troubleshooting, performance and dictionary views

V$xxx dynamic troubleshooting and performance views

DBA_ xxx dictionary views

UTLBSTAT.SQL and UTLESTAT.SQL scripts

Oracle Wait events

Enterprise Manager event service

Oracle Diagnostics and Tuning packs

Dictionary and Special Views

Dictionary and special views provide useful statistics after you run the ANALYZE command:

DBA_TABLES, DBA_TAB_COLUMNS

DBA_CLUSTERS

DBA_INDEXES, INDEX_STATS

INDEX_HISTOGRAM, DBA_HISTOGRAMS

This statistics information is static until you reexecute the ANALYZE command.

Dynamic Troubleshooting and Performance Views

V$ views

Based on X$ tables

Listed in V$FIXED_TABLE

X$ tables

Not usually queried directly

Dynamic and constantly changing

Names abbreviated and obscure

Populated at startup and cleared at shutdown

Topics for Troubleshooting and Tuning

Collecting System-Wide Statistics

Collecting Session-Related Statistics

UTLBSTAT and UTLESTAT Scripts

Gather performance figures over a defined period

Produce a hard-copy report

Use UTLBSTAT.SQL and UTLESTAT.SQL scripts

Run the scripts from SQL*Plus connected as SYSDBA

Set TIMED_STATISTICS to TRUE

Gather Statistics

The Statistics Report

Library cache statistics

System statistics

Wait events statistics

Latch statistics

Rollback contention statistics

Buffer Busy Wait Statistics

Dictionary cache statistics

I/O statistics per data file and tablespace

Period of measurement

Library Cache Statistics

I/O Statistics

Latches: Overview

What is contention?

A contention exists when multiple server processes contend for the same resources.

Latches

Contention areas that the DBA can tune:

Redo allocation latch

Redo copy latch

LRU latch

Latch Types

Willing-To-Wait:

GETS

MISSES

SLEEPS

Immediate:

IMMEDIATE GETS

IMMEDIATE MISSES

Oracle Wait Events

The V$EVENT_NAME view lists a collection of Wait events that provide information on the sessions that had to wait to be processed:

EVENT#

NAME

PARAMETER1

PARAMETER2

PARAMETER3

V$EVENT_NAME View

Statistics Event Views

V$SYSTEM_EVENT: Total waits for an event, all sessions together

V$SESSION_EVENT: Waits for an event for each session that had to wait

V$SESSION_WAIT: Waits for an event for current active sessions that are waiting

V$SYSTEM_EVENT View

V$SESSION_EVENT View

V$SESSION_WAIT View

Enterprise Manager (EM Version 2)

Event Management System

Monitors for unusual conditions in databases, nodes, and network by creating events

Automates problem detection by registering events

Automates problem correction by applying fixit jobs

Shares events and notifies administrators of event occurrences

Has five predefined event test categories: Space, Fault, Resource, Performance, and Audit Management

Predefined Event Tests

Fault management event tests:

Database Alert (database)

Database UpDown (database)

Archiver Hung (database)

Database Probe (database)

Data Block Corruption (database)

Node UpDown (node)

Session Terminated (database)

Predefined Event Tests

Space management events:

Alert File Large (database)

Chunk Small (database)

Disk Full (node)

Dump Full (database)

Fast Segment Growth (database)

Maximum Extents (database)

Tablespace Full (database)

Predefined Event Tests

Resource management events:

Datafile Limit (database)

Lock Limit (database)

Process Limit (database)

User Limit (database)

Session Limit (database)

Predefined Event Tests

Performance management events:

Buffer Cache (database)

Chain Row (database)

CPU Utilization (node)

Disk I/O (node)

In Memory Sorts (database)

Library Cache (database)

Rollback Contention (database)

Event Parameters

Parameters:

Warning and alert thresholds

Number of occurrences

Focused monitoring:

SEGMENT_OWNER

SEGMENT_TYPE

SEGMENT_NAME

Any criteria related to the area

Fix the Problem Detected by the Event

Manually

Automatically by fixit jobs

DBA-Developed Tools

Develop your own scripts

Use the Supplied Packages for tuning

Schedule periodic performance checking

Take advantage of the EM Job service to automate the regular execution of these administrative tasks

Take advantage of the EM Event service to track specific situations

Take advantage of the EM Job service to apply tasks that automatically solve problems detected by EM event service

Oracle Packs

Oracle Diagnostics Pack:

Performance Manager

TopSessions

Oracle Trace Manager

Trace Data Viewer

Capacity Planner

Oracle Tuning Pack:

Tablespace Manager

SQL Analyze

Oracle Expert

Performance Manager

Predefined scopes of statistics:

I/O

Contention

Database instance

Load

Memory

Top resource consumers

Overview of performance

Overview of cache utilization

Overview of user activity

Overview of throughput

Overview of performance default chart

User-defined charts

TopSessions

TopSessions: Locks

Tablespace Manager

Common Uses of Oracle Trace Manager

Resource-usage information collection

Performance analysis

Database tuning

Application tuning

Input to expert systems

Overview of Oracle Expert Tuning Methodology

Tuning Session Scope

Four major tuning categories:

Instance optimizations

SQL reuse opportunities

Appropriate space management

Optimal data access

Tuning Recommendations

1. Collect the data.

2. Review the recommendations:

Session data report

Analysis report

3. Implement recommendations:

Summary

In this lesson, you should have learned how to:

Collect statistics from dictionary and dynamic performance troubleshooting views

Collect statistics from report.txt output of UTLBSTAT and UTLESTAT scripts

Define latch types

Retrieve Oracle Wait events information

Set alerts through EM events

Collect statistics using the GUI tools of Oracle Enterprise Manager, such as the Diagnostics Pack and Tuning Pack


		After completing this lesson, you should be able to
		do the following:
		Collect statistics through:
			Available dynamic troubleshooting and performance views
			The UTLBSTAT/UTLESTAT report output
			Oracle wait events
			Appropriate Enterprise Manager (EM) tuning tools
		Define the latch types
		Use EM to set events for predefined situations


		Dynamic troubleshooting, performance and dictionary views
			V$xxx dynamic troubleshooting and performance views
			DBA_ xxx dictionary views
		UTLBSTAT.SQL and UTLESTAT.SQL scripts
		Oracle Wait events
		Enterprise Manager event service
		Oracle Diagnostics and Tuning packs


	Dictionary and special views provide useful statistics after you run the ANALYZE command:
		DBA_TABLES, DBA_TAB_COLUMNS
		DBA_CLUSTERS
		DBA_INDEXES, INDEX_STATS
		INDEX_HISTOGRAM, DBA_HISTOGRAMS
	This statistics information is static until you reexecute the ANALYZE command.


	V$ views
		Based on X$ tables
		Listed in V$FIXED_TABLE
	X$ tables
		Not usually queried directly
		Dynamic and constantly changing
		Names abbreviated and obscure
Populated at startup and cleared at shutdown


		Gather performance figures over a defined period
		Produce a hard-copy report
		Use UTLBSTAT.SQL and UTLESTAT.SQL scripts
		Run the scripts from SQL*Plus connected as SYSDBA
		Set TIMED_STATISTICS to TRUE


		Library cache statistics
		System statistics
		Wait events statistics
		Latch statistics
		Rollback contention statistics
		Buffer Busy Wait Statistics
		Dictionary cache statistics
		I/O statistics per data file and tablespace
		Period of measurement


		What is contention?
		A contention exists when multiple server processes contend for the same resources.


	Contention areas that the DBA can tune:
		Redo allocation latch
		Redo copy latch
		LRU latch


		Willing-To-Wait:
			GETS
			MISSES
			SLEEPS
		Immediate:
			IMMEDIATE GETS
			IMMEDIATE MISSES


	The V$EVENT_NAME view lists a collection of Wait events that provide information on the sessions that had to wait to be processed:
		EVENT#
		NAME
		PARAMETER1
		PARAMETER2
		PARAMETER3


		V$SYSTEM_EVENT: Total waits for an event, all sessions together
		V$SESSION_EVENT: Waits for an event for each session that had to wait
		V$SESSION_WAIT: Waits for an event for current active sessions that are waiting


		Monitors for unusual conditions in databases, nodes, and network by creating events
		Automates problem detection by registering events
		Automates problem correction by applying fixit jobs
		Shares events and notifies administrators of event occurrences
		Has five predefined event test categories: Space, Fault, Resource, Performance, and Audit Management


	Fault management event tests:
		Database Alert (database)
		Database UpDown (database)
		Archiver Hung (database)
		Database Probe (database)
		Data Block Corruption (database)
		Node UpDown (node)
		Session Terminated (database)


	Space management events:
		Alert File Large (database)
		Chunk Small (database)
		Disk Full (node)
		Dump Full (database)
		Fast Segment Growth (database)
		Maximum Extents (database)
		Tablespace Full (database)


	Resource management events:
		Datafile Limit (database)
		Lock Limit (database)
		Process Limit (database)
		User Limit (database)
		Session Limit (database)


	Performance management events:
		Buffer Cache (database)
		Chain Row (database)
		CPU Utilization (node)
		Disk I/O (node)
		In Memory Sorts (database)
		Library Cache (database)
		Rollback Contention (database)


Parameters:
	Warning and alert thresholds
	Number of occurrences
	Focused monitoring:
		SEGMENT_OWNER
		SEGMENT_TYPE
		SEGMENT_NAME
		Any criteria related to the area


		Develop your own scripts
		Use the Supplied Packages for tuning
		Schedule periodic performance checking
		Take advantage of the EM Job service to automate the regular execution of these administrative tasks
		Take advantage of the EM Event service to track specific situations
		Take advantage of the EM Job service to apply tasks that automatically solve problems detected by EM event service


		Oracle Diagnostics Pack:
			Performance Manager
			TopSessions
			Oracle Trace Manager
			Trace Data Viewer
			Capacity Planner
		Oracle Tuning Pack:
			Tablespace Manager
			SQL Analyze
			Oracle Expert


		Predefined scopes of statistics:
		I/O
		Contention
		Database instance
		Load
		Memory
		Top resource consumers
		Overview of performance
			Overview of cache utilization
			Overview of user activity
			Overview of throughput
			Overview of performance default chart
		User-defined charts


	Resource-usage information collection
	Performance analysis
	Database tuning
	Application tuning
	Input to expert systems


	Four major tuning categories:
		Instance optimizations
		SQL reuse opportunities
		Appropriate space management
		Optimal data access


	1. Collect the data.
	2. Review the recommendations:
			Session data report
			Analysis report
	3. Implement recommendations:


	In this lesson, you should have learned how to:
		Collect statistics from dictionary and dynamic performance troubleshooting views
		Collect statistics from report.txt output of UTLBSTAT and UTLESTAT scripts
		Define latch types
		Retrieve Oracle Wait events information
		Set alerts through EM events
		Collect statistics using the GUI tools of Oracle Enterprise Manager, such as the Diagnostics Pack and Tuning Pack