Notes

Outline

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