Oracle Database Performance Tuning for Administrators

SEE SCHEDULE

Duration: 4 Days

Method: Instructor led, Hands-on workshops

Price: $2450.00

Course Code: OR5490

Audience

Oracle Database Administrators (DBAs)

Description

In this course students will clearly define the various methodologies one can use to tune an Oracle Database. The course covers the various components of an Oracle Database where potential performance bottlenecks could occur and how to monitor and tune them. Throughout the course, students experience hands-on exercises in tuning a database.

Objectives

Upon successful completion of this course, the student will be able to:

Identify the potential performance bottlenecks in an Oracle database
Understand which database parameters are important to performance
Query various data dictionary and dynamic performance views for performance information
Use various utilities to help diagnose database performance problems
Understand and use the database advisors
Set up an Oracle database for efficient performance

Prerequisites

OR5510 Oracle Database Administration or Oracle DBA experience

Topics

I. Database Tuning Overview
- Set appropriate tuning goals
- Apply a tuning methodology
- Identify potential tuning problems
II. Database Architecture
- Understand all database architecture components
- The Database Architecture
  - Memory Structures
  - The SGA
  - The Database Buffer Cache
  - The Shared Pool
  - Other Buffer Caches
  - The KEEP Cache
  - The RECYCLE Cache
  - Non-Standard Block Size Caches
  - The Large Pool
  - The Java Pool
  - The Streams Pool
  - The Result Cache
  - Background Processes
  - Checkpoints
  - Server Process
  - Client Processes
  - Database Files
  - Database Objects
  - Blocks
- Understand potential performance bottlenecks
III. Automatic Memory Management (AMM and ASMM)
- Enable Automatic Shared Memory
  - The SGA_TARGET and STATISTICS_LEVEL Parameters
  - The MEMORY_TARGET parameter
- The MMAN Background Process
- Areas Managed by AMM and ASMM
- Monitoring AMM and ASMM Events
  - V$SGA_DYNAMIC_COMPONENTS
  - V$MEMORY_DYNAMIC_COMPONENTS
  - V$SGA_RESIZE_OPS
  - V$MEMORY_RESIZE_OPS
IV. Tuning the Shared Pool
- Diagnose shared pool problems
- Size the shared pool
- Size the reserved area
- Keep objects in the shared pool
- SQL Tuning and the Shared Pool
- Listing the SQL Statement Processing Steps
  - Parsing
  - Syntax Check
  - Semantic Check
  - Hard Parse vs. Soft Parse
- Identifying Means to Minimize Hard Parsing
  - SQL Coding Standards
  - Identifying Identical Statements
  - The CURSOR_SHARING Parameter
-  EXACT, SIMILAR, FORCE Values
  - Using Bind Variables
  - Sizing the Shared Pool Memory Structure
  - Pinning SQL in the Shared Pool
- Monitoring SQL Usage and Parsing
  - Data Dictionary Views
  - V$SQL, V$SQLAREA, V$SQLTEXT
  - V$SYSSTAT, V$SESSTAT
V. Tuning the Buffer Cache
- Describe the buffer cache architecture
- Size the buffer cache
  - The DB_CACHE_SIZE Parameter
- Resolve common performance issues related to the buffer cache
- Use common diagnostic indicators to suggest a possible solution
- Other Buffer Caches
  - When to use
  - KEEP, RECYCLE, Non-Standard Block Sizes
  - Setting up
VI. Tuning PGA and Temporary Space
- Private PGA vs Public PGA
- Diagnose PGA memory issues
- Size the PGA memory
  - SORT_AREA_SIZE vs PGA_AGGREGATE_TARGET
- Diagnose temporary space issues
- Specify temporary tablespace parameters for efficient operation
- Temporary Tablespace Groups (TTG)
  - What they do and what they don’t do for you
  - Creating a TTG
- Monitoring Sort Efficiencies
  - V$SYSSTAT
  - V$SESSTAT
  - AUOTRACE Output
VII. Checkpoint and Redo Tuning
- Diagnose Checkpoint and Redo Issues
- Setting Checkpoint Performance Goals
- Checkpoint Frequency Issues
  - Too Often
  - Not Often Enough
- The FAST_START_MTTR_TARGET Parameter
- Other Parameters
  - LOG_CHECKPOINT_TIMEOUT
  - LOG_CHECKPOINT_INTERVAL
  - LOCK_CHECKPOINTS_TO_ALERT
  - FAST_START_IO_TARGET
- Multiple database writers
- Parameters
  - DB_WRITERS
  - DBWR_IO_SLAVES
- Tune the redo chain
- Monitor Redo Log Group Switches
- When should we switch
  - Size the redo log files
  - Size the redo log buffer
  - Latch Issues
VIII. Tuning Block Space Usage
- Tune segment space management
  - Dictionary vs Locally managed tablespaces
  - Automatic vs Manual segment space management
- Tune block space management
- Diagnose and correct row chaining
- Choosing a Block Size
- Using Multiple Block Sizes in a Database
IX. Statistics and Wait Events
- Identify dynamic performance views useful in tuning
- Identify key tuning components of the alert log file
- Identify key tuning components of user trace files
- Use dynamic performance views to view statistics and wait events
X. The Automatic Workload Repository (AWR) and the Advisory Framework
- The Advisory Framework Architecture
  - OEM and Non-OEM Usage
  - Performance Statistics Sources
  - ASH
  - The AWR
  - SGA Advisor
  - UNDO Advisor
  - SQL Tuning Advisor
  - The Segment Advisor
  - SQL Access Advisor
- Create and Manage AWR Snapshots
- Generate AWR reports
- The awrinfo.sql script
- The awrrpt.sql script
- AWR Data Dictionary Views
- Create snapshot sets (baselines) and compare periods
- Generate ADDM reports
  - The addmrpt.sql script
XI. Additional SQL Tuning Topics
- Indexing
  - Types of Indexes
  - Negating the use of indexes
  - Invisible Indexes
  - Virtual Indexes
- Object Statistics
  - Automatic Statistics Gathering Job / Task
  - Publishing Statistics / Private Statistics
  - Extended Statistics
  - Expression Statistics
XII. Oracle 18c New Features
- SQL Tuning Advisor Exadata Enhancements
- New SQL Tuning Set API
- Concurrent SQL Execution with SQL Performance Analyzer
- SQL Performance Analyzer Result Set Validation
- Memoptimized Rowstore
- Private Temporary Tables
- Manual termination of Run-Away Queries
- PL/SQL Hierarchical Profiler (DBMS_HPROF) Enhancements
- SQL*Plus Changes
- SQL Developer Changes
- Miscellaneous Database Changes
- Deprecated Features
- Desupported Features
XIII.
- Miscellaneous Tuning Tips