Oracle 12c Database Tuning Online Training

Oracle 12c Database Tuning Online Training Course Details

Oracle 12c Database Tuning Online Training Course Content

1 Performance Tuning Overview

Introduction to Performance Tuning

  • Performance Planning
  • Instance Tuning
  • Performance Principles.
  • Baselines.
  • The Symptoms and the Problems.
  • When to Tune .
  • Proactive Monitoring
  • Bottleneck Elimination
  • SQL Tuning
  • Query Optimizer and Execution Plans

Introduction to Performance Tuning Features and Tools

  • Automatic Performance Tuning Features.
  • Additional Oracle Database Tools
  • V$ Performance Views

2 Designing and Developing for Performance

Oracle Methodology

Understanding Investment Options

Understanding Scalability

  • What is Scalability?
  • System Scalability
  • Factors Preventing Scalability

System Architecture

  • Hardware and Software Components
  • Hardware Components.
  • CPU.
  • Memory
  • I/O Subsystem.
  • Network
  • Software Components.
  • Managing the User Interface
  • Implementing Business Logic
  • Managing User Requests and Resource Allocation
  • Managing Data and Transactions
  • Configuring the Right System Architecture for Your Requirements.

Application Design Principles

  • Simplicity In Application Design.
  • Data Modeling
  • Table and Index Design.
  • Appending Columns to an Index or Using Index-Organized Tab
  • Using a Different Index Type.
  • B-Tree Indexes .
  • Bitmap Indexes .
  • Function-based Indexes
  • Partitioned Indexes
  • Reverse Key Indexes.
  • Finding the Cost of an Index.
  • Serializing within Indexes
  • Ordering Columns in an Index
  • Using Views
  • SQL Execution Efficiency.
  • Implementing the Application
  • Trends in Application Development

Workload Testing, Modeling, and Implementation

  • Sizing Data.
  • Estimating Workloads.
  • Extrapolating From a Similar System
  • Benchmarking.
  • Application Modeling
  • Testing, Debugging, and Validating a Design

Deploying New Applications

  • Rollout Strategies.
  • Performance Checklist.

3 Performance Improvement Methods

The Oracle Performance Improvement Method

  • Steps in the Oracle Performance Improvement Method
  • A Sample Decision Process for Performance Conceptual Modeling.
  • Top Ten Mistakes Found in Oracle Systems.

Emergency Performance Methods

Steps in the Emergency Performance Method.

4 Configuring a Database for Performance

Performance Considerations for Initial Instance Configuration

  • Initialization Parameters.
  • Configuring Undo Space
  • Sizing Redo Log Files
  • Creating Subsequent Tablespaces
  • Creating Permanent Tablespaces – Automatic Segment-Space
  • Creating Temporary Tablespaces.

Creating and Maintaining Tables for Optimal Performance

  • Table Compression
  • Estimating the Compression Factor
  • Tuning to Achieve a Better Compression Ratio
  • Using Attribute-Clustered Tables
  • Reclaiming Unused Space.
  • Indexing Data.
  • Specifying Memory for Sorting Data.

Performance Considerations for Shared Servers

  • Identifying Contention Using the Dispatcher-Specific Views
  • Reducing Contention for Dispatcher Processes.
  • Identifying Contention for Shared Servers.

5 Measuring Database Performance

About Database Statistics

  • Time Model Statistics.
  • Active Session History Statistics
  • Wait Events Statistics.
  • Session and System Statistics.

Interpreting Database Statistics

  • Using Hit Ratios
  • Using Wait Events with Timed Statistics
  • Using Wait Events without Timed Statistics.
  • Using Idle Wait Events.
  • Comparing Database Statistics with Other Factors
  • Using Computed Statistics.

6 Gathering Database Statistics

About Gathering Database Statistics

  • Automatic Workload Repository.
  • Snapshots
  • Baselines
  • Fixed Baselines.
  • Moving Window Baselines
  • Baseline Templates.
  • Single Baseline Templates
  • Repeating Baseline Templates
  • Space Consumption.
  • Adaptive Thresholds.
  • Percentage of Maximum Thresholds
  • Significance Level Thresholds

Managing the Automatic Workload Repository

  • Enabling the Automatic Workload Repository
  • Managing Snapshots
  • User Interfaces for Managing Snapshots
  • Creating Snapshots.
  • Creating Snapshots Using the Command-Line Interface.
  • Dropping Snapshots
  • Dropping Snapshots Using the Command-Line Interface
  • Modifying Snapshot Settings.
  • Modifying Snapshot Settings Using the Command-Line Int
  • Managing Baselines.
  • User Interface for Managing Baselines.
  • Creating a Baseline.
  • Creating a Baseline Using the Command-Line Interface.
  • Dropping a Baseline
  • Dropping a Baseline Using the Command-Line Interface
  • Renaming a Baseline
  • Renaming a Baseline Using the Command-Line Interface
  • Displaying Baseline Metrics
  • Displaying Baseline Metrics Using the Command-Line Inte
  • Resizing the Default Moving Window Baseline.
  • Resizing the Default Moving Window Using the Command
  • Managing Baseline Templates.
  • User Interfaces for Managing Baseline Templates.
  • Creating a Single Baseline Template
  • Creating a Single Baseline Template Using the Command
  • Creating a Repeating Baseline Template.
  • Creating a Repeating Baseline Template Using the Comm
  • Dropping a Baseline Template
  • Dropping a Baseline Template Using the Command-Line I
  • Transporting Automatic Workload Repository Data.
  • Extracting AWR Data
  • Loading AWR Data.
  • Using Automatic Workload Repository Views

Generating Automatic Workload Repository Reports

User Interface for Generating an AWR Report.

Generating an AWR Report Using the Command-Line Interface.

Generating an AWR Report for the Local Database.

Generating an AWR Report for a Specific Database

Generating an Oracle RAC AWR Report for the Local Databas

Generating an Oracle RAC AWR Report for a Specific Databas

Generating an AWR Report for a SQL Statement on the Local

Generating an AWR Report for a SQL Statement on a Specific

Generating Performance Hub Active Report

  • Overview of Performance Hub Active Report.
  • About Performance Hub Active Report Tabs
  • About Performance Hub Active Report Types
  • Command-Line User Interface for Generating a Performance Hub A
  • Generating a Performance Hub Active Report Using a SQL Script

7 Automatic Performance Diagnostics

Overview of the Automatic Database Diagnostic Monitor

  • ADDM Analysis
  • Using ADDM with Oracle Real Application Clusters
  • Real-Time ADDM Analysis.
  • Real-Time ADDM Connection Modes.
  • Real-Time ADDM Triggers
  • Real-Time ADDM Trigger Controls.
  • ADDM Analysis Results.
  • Reviewing ADDM Analysis Results: Example

Setting Up ADDM.

Diagnosing Database Performance Problems with ADDM

  • Running ADDM in Database Mode
  • Running ADDM in Instance Mode
  • Running ADDM in Partial Mode.
  • Displaying an ADDM Report

Views with ADDM Information

8 Comparing Database Performance Over Time

About Automatic Workload Repository Compare Periods Reports

Generating Automatic Workload Repository Compare Periods Reports

  • User Interfaces for Generating AWR Compare Periods Reports
  • Generating an AWR Compare Periods Report Using the Command
  • Generating an AWR Compare Periods Report for the Local Database
  • Generating an AWR Compare Periods Report for a Specific Database
  • Generating an Oracle RAC AWR Compare Periods Report
  • Generating an Oracle RAC AWR Compare Periods Report for a

Interpreting Automatic Workload Repository Compare Periods Reports

  • Summary of the AWR Compare Periods Report
  • Snapshot Sets.
  • Host Configuration Comparison
  • System Configuration Comparison.
  • Load Profile
  • Top 5 Timed Events
  • Details of the AWR Compare Periods Report
  • Time Model Statistics
  • Operating System Statistics
  • Wait Events.
  • Service Statistics.
  • SQL Statistics
  • Top 10 SQL Comparison by Execution Time.
  • Top 10 SQL Comparison by CPU Time.
  • Top 10 SQL Comparison by Buffer Gets.
  • Top 10 SQL Comparison by Physical Reads.
  • Top 10 SQL Comparison by Executions
  • Top 10 SQL Comparison by Parse Calls.
  • Complete List of SQL Text.
  • Instance Activity Statistics.
  • Key Instance Activity Statistics
  • Other Instance Activity Statistics
  • I/O Statistics
  • Tablespace I/O Statistics.
  • Top 10 File Comparison by I/O
  • Top 10 File Comparison by Read Time.
  • Top 10 File Comparison by Buffer Waits.
  • Advisory Statistics.
  • PGA Aggregate Summary.
  • PGA Aggregate Target Statistics.
  • Wait Statistics
  • Buffer Wait Statistics
  • Enqueue Activity
  • Undo Segment Summary
  • Latch Statistics
  • Segment Statistics
  • Top 5 Segments Comparison by Logical Reads
  • Top 5 Segments Comparison by Physical Reads
  • Top 5 Segments Comparison by Row Lock Waits.
  • Top 5 Segments Comparison by ITL Waits.
  • Top 5 Segments Comparison by Buffer Busy Waits
  • In-Memory Segment Statistics
  • Dictionary Cache Statistics.
  • Library Cache Statistics
  • Memory Statistics
  • Process Memory Summary
  • SGA Memory Summary.
  • SGA Breakdown Difference.
  • Streams Statistics.
  • Supplemental Information in the AWR Compare Periods Report.
  • init.ora Parameters
  • Complete List of SQL Text

9 Analyzing Sampled Data

About Active Session History9-1.

Generating Active Session History Reports

  • User Interfaces for Generating ASH Reports.
  • Generating an ASH Report Using the Command-Line Interface
  • Generating an ASH Report on the Local Database Instance
  • Generating an ASH Report on a Specific Database Instance.
  • Generating an ASH Report for Oracle RAC
  • Interpreting Results from Active Session History Reports
  • Top Events.
  • Top User Events.
  • Top Background Events
  • Top Event P1/P2/P3
  • Load Profile
  • Top Service/Module
  • Top Client IDs.
  • Top SQL Command Types.
  • Top Phases of Execution
  • Top SQL.
  • Top SQL with Top Events.
  • Top SQL with Top Row Sources.
  • Top SQL Using Literals.
  • Top Parsing Module/Action.
  • Complete List of SQL Text
  • Top PL/SQL
  • Top Java.
  • Top Sessions
  • Top Sessions
  • Top Blocking Sessions
  • Top Sessions Running PQs
  • Top Objects/Files/Latches
  • Top DB Objects.
  • Top DB Files.
  • Top Latches
  • Activity Over Time

10 Instance Tuning Using Performance Views

Instance Tuning Steps

  • Define the Problem.
  • Examine the Host System
  • CPU Usage.
  • Non-Oracle Processes.
  • Oracle Processes.
  • Oracle Database CPU Statistics
  • Interpreting CPU Statistics.
  • Identifying I/O Problems
  • Identifying I/O Problems Using V$ Views
  • Identifying I/O Problems Using Operating System Monitori
  • Identifying Network Issues
  • Examine the Oracle Database Statistics
  • Setting the Level of Statistics Collection.
  • Wait Events.
  • Dynamic Performance Views Containing Wait Event Statistics.
  • System Statistics.
  • V$LATCH.
  • Segment-Level Statistics.
  • Implement and Measure Change.

Interpreting Oracle Database Statistics

  • Examine Load
  • Changing Load
  • High Rates of Activity
  • Using Wait Event Statistics to Drill Down to Bottlenecks.
  • Table of Wait Events and Potential Causes.
  • Additional Statistics
  • Redo Log Space Requests Statistic
  • Read Consistency
  • Table Fetch by Continued Row.
  • Parse-Related Statistics

Wait Events Statistics

  • Changes to Wait Event Statistics from Past Releases.
  • buffer busy waits
  • Causes
  • Actions
  • segment header.
  • data block.
  • undo header.
  • undo block
  • db file scattered read
  • Actions
  • Managing Excessive I/O.
  • Inadequate I/O Distribution.
  • Finding the SQL Statement executed by Sessions Waiting for I/O
  • Finding the Object Requiring I/O.
  • db file sequential read
  • Actions
  • direct path read and direct path read temp
  • Causes
  • Actions
  • Sorts to Disk
  • Full Table Scans
  • Hash Area Size
  • direct path write and direct path write temp
  • Causes
  • Actions
  • enqueue (enq:) waits
  • Finding Locks and Lock Holders.
  • Actions
  • ST enqueue.
  • HW enqueue
  • TM enqueue.
  • TX enqueue.
  • events in wait class other.
  • free buffer waits
  • Causes
  • Actions
  • Writes.
  • Cache is Too Small
  • Cache Is Too Big for One DBWR
  • Consider Multiple Database Writer (DBWR) Processes or I/O S
  • Choosing Between Multiple DBWR Processes and I/O Slav
  • Idle Wait Events.
  • latch events
  • Actions
  • Example: Find Latches Currently Waited For.
  • Shared Pool and Library Cache Latch Contention
  • Unshared SQL.
  • Reparsed Sharable SQL
  • By Session.
  • cache buffers lru chain.
  • cache buffers chains
  • row cache objects
  • log file parallel write
  • library cache pin.
  • library cache lock
  • log buffer space
  • log file switch
  • Actions
  • log file sync.
  • rdbms ipc reply.
  • SQL*Net Events.
  • SQL*Net message from client.
  • Network Bottleneck
  • Resource Bottleneck on the Client Process.
  • SQL*Net message from dblink
  • SQL*Net more data to client.

Tuning Instance Recovery Performance: Fast-Start Fault Recovery

  • About Instance Recovery.
  • Cache Recovery (Rolling Forward).
  • Transaction Recovery (Rolling Back)
  • Checkpoints and Cache Recovery
  • How Checkpoints Affect Performance.
  • Fast Cache Recovery Tradeoffs
  • Configuring the Duration of Cache Recovery: FAST_START_MTTR_TARGET
  • Practical Values for FAST_START_MTTR_TARGET.
  • Reducing Checkpoint Frequency to Optimize Run-Time Performance
  • Monitoring Cache Recovery with V$INSTANCE_RECOVERY
  • Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor.
  • Calibrate the FAST_START_MTTR_TARGET.
  • Determine the Practical Range for FAST_START_MTTR_TARGET
  • Determining Lower Bound for FAST_START_MTTR_TARGET
  • Determining Upper Bound for FAST_START_MTTR_TARGET
  • Selecting Preliminary Value for FAST_START_MTTR_TARGET
  • Evaluate Different Target Values with MTTR Advisor
  • Enabling MTTR Advisor.
  • Using MTTR Advisor
  • Viewing MTTR Advisor Results: V$MTTR_TARGET_ADVISORY
  • Determine the Optimal Size for Redo Logs

11 Database Memory Allocation

About Database Memory Caches and Other Memory Structures

Database Memory Management Methods

  • Automatic Memory Management.
  • Automatic Shared Memory Management.
  • Manual Shared Memory Management.
  • Automatic PGA Memory Management.
  • Manual PGA Memory Management.

Using Automatic Memory Management

Monitoring Memory Management

12 Tuning the System Global Area .

Using Automatic Shared Memory Management

  • User Interfaces for Setting the SGA_TARGET Parameter
  • Setting the SGA_TARGET Parameter in Oracle Enterprise Manager
  • Setting the SGA_TARGET Parameter in the Command-Line Interface
  • Setting the SGA_TARGET Parameter.
  • Enabling Automatic Shared Memory Management.
  • Disabling Automatic Shared Memory Management

Sizing the SGA Components Manually

  • SGA Sizing Unit
  • Maximum Size of the SGA.
  • Application Considerations.
  • Operating System Memory Use.
  • Reduce Paging
  • Fit the SGA into Main Memory
  • Viewing SGA Memory Allocation.
  • Locking the SGA into Physical Memory.
  • Allow Adequate Memory to Individual Users.
  • Iteration During Configuration

Monitoring Shared Memory Management

Configuring the In-Memory Column Store

  • About the In-Memory Column Store.
  • Performance Benefits of Using the In-Memory Column Store.
  • Estimating the Required Size of the In-Memory Column Store.
  • Sizing the In-Memory Column Store

13 Tuning the Database Buffer Cache

About the Database Buffer Cache

Configuring the Database Buffer Cache

  • Using the V$DB_CACHE_ADVICE View.
  • Calculating the Buffer Cache Hit Ratio.
  • Interpreting the Buffer Cache Hit Ratio
  • Increasing Memory Allocated to the Database Buffer Cache
  • Reducing Memory Allocated to the Database Buffer Cache.

Configuring Multiple Buffer Pools

  • Considerations for Using Multiple Buffer Pools
  • Random Access to Large Segments.
  • Oracle Real Application Cluster Instances
  • Using Multiple Buffer Pools
  • Using the V$DB_CACHE_ADVICE View for Individual Buffer Pools.
  • Calculating the Buffer Pool Hit Ratio for Individual Buffer Pools.
  • Examining the Buffer Cache Usage Pattern.
  • Examining the Buffer Cache Usage Pattern for All Segments
  • Examining the Buffer Cache Usage Pattern for a Specific Segments
  • Configuring the KEEP Pool
  • Configuring the RECYCLE Pool.

Configuring the Redo Log Buffer

  • Sizing the Redo Log Buffer
  • Using Redo Log Buffer Statistics

Configuring the Database Caching Mode

  • Default Database Caching Mode.
  • Force Full Database Caching Mode.
  • Determining When to Use Force Full Database Caching Mode
  • Verifying the Database Caching Mode.

14 Tuning the Shared Pool and the Large Pool

About the Shared Pool

  • Benefits of Using the Shared Pool.
  • Shared Pool Concepts.
  • Library Cache Concepts
  • Data Dictionary Cache Concepts
  • SQL Sharing Criteria.
  • Using the Shared Pool
  • Use Shared Cursors.
  • Use Single-User Logon and Qualified Table Reference
  • Use PL/SQL.
  • Avoid Performing DDL Operations.
  • Cache Sequence Numbers
  • Control Cursor Access.
  • Controlling Cursor Access Using OCI.
  • Controlling Cursor Access Using Oracle Precompilers
  • Controlling Cursor Access Using SQLJ
  • Controlling Cursor Access Using JDBC
  • Controlling Cursor Access Using Oracle Forms
  • Maintain Persistent Connections

Configuring the Shared Pool

  • Sizing the Shared Pool.
  • Using Library Cache Statistics
  • Using the V$LIBRARYCACHE View
  • Calculating the Library Cache Hit Ratio.
  • Viewing the Amount of Free Memory in the Shared Pool.
  • Using Shared Pool Advisory Statistics
  • About the V$SHARED_POOL_ADVICE View.
  • About the V$LIBRARY_CACHE_MEMORY View.
  • Using Dictionary Cache Statistics.
  • Increasing Memory Allocated to the Shared Pool.
  • Reducing Memory Allocated to the Shared Pool
  • Deallocating Cursors
  • Caching Session Cursors.
  • About the Session Cursor Cache
  • Enabling the Session Cursor Cache.
  • Sizing the Session Cursor Cache.
  • Sharing Cursors.
  • About Cursor Sharing
  • Forcing Cursor Sharing.
  • Keeping Large Objects to Prevent Aging.
  • Configuring the Reserved Pool
  • Sizing the Reserved Pool
  • Increasing Memory Allocated to the Reserved Pool.
  • Reducing Memory Allocated to the Reserved Pool

Configuring the Large Pool

  • Configuring the Large Pool for Shared Server Architecture.
  • Configuring the Large Pool for Parallel Query
  • Sizing the Large Pool.
  • Limiting Memory Use for User Sessions
  • Reducing Memory Use Using Three-Tier Connections

15 Tuning the Result Cache

About the Result Cache

  • Server Result Cache Concepts
  • Benefits of Using the Server Result Cache.
  • Understanding How the Server Result Cache Works.
  • How Results are Retrieved in a Query.
  • How Results are Retrieved in a View.
  • Client Result Cache Concepts.
  • Benefits of Using the Client Result Cache
  • Understanding How the Client Result Cache Works.

Configuring the Result Cache

  • Configuring the Server Result Cache.
  • Sizing the Server Result Cache Using Initialization Parameters
  • Managing the Server Result Cache Using DBMS_RESULT_CA
  • Viewing Memory Usage Statistics for the Server Result Ca
  • Flushing the Server Result Cache.
  • Configuring the Client Result Cache
  • Setting the Result Cache Mode
  • Requirements for the Result Cache.
  • Read Consistency Requirements
  • Query Parameter Requirements.
  • Restrictions for the Result Cache

Specifying Queries for Result Caching

  • Using SQL Result Cache Hints
  • Using the RESULT_CACHE Hint
  • Using the NO_RESULT_CACHE Hint
  • Using the RESULT_CACHE Hint in Views.
  • Using Result Cache Table Annotations
  • Using the DEFAULT Table Annotation.
  • Using the FORCE Table Annotation.

Monitoring the Result Cache

16 Tuning the Program Global Area .

About the Program Global Area

Work Area Sizes.

Sizing the Program Global Area

  • Configuring Automatic PGA Memory Management.
  • Setting the Initial Value for PGA_AGGREGATE_TARGET.
  • Monitoring Automatic PGA Memory Management.
  • Using the V$PGASTAT View
  • Using the V$PROCESS View.
  • Using the V$PROCESS_MEMORY View.
  • Using the V$WORKAREA_ACTIVE View.
  • Using the V$SQL_WORKAREA View.
  • Enabling Automatic Generation of PGA Performance Advisory
  • Using the V$PGA_TARGET_ADVICE View.
  • Using the V$SYSSTAT and V$SESSTAT Views
  • Tutorial: How to Tune PGA_AGGREGATE_TARGET

Limiting the Size of the Program Global Area


17 I/O Configuration and Design

About I/O

I/O Configuration

  • Lay Out the Files Using Operating System or Hardware Striping.
  • Requested I/O Size.
  • Concurrency of I/O Requests
  • Alignment of Physical Stripe Boundaries with Block Size Bound
  • Manageability of the Proposed System.
  • Manually Distributing I/O
  • When to Separate Files
  • Tables, Indexes, and TEMP Tablespaces
  • Redo Log Files
  • Archived Redo Logs
  • Three Sample Configurations.
  • Stripe Everything Across Every Disk.
  • Move Archive Logs to Different Disks.
  • Move Redo Logs to Separate Disks
  • Oracle Managed Files
  • Choosing Data Block Size
  • Reads
  • Writes
  • Block Size Advantages and Disadvantages

I/O Calibration Inside the Database

  • Prerequisites for I/O Calibration.
  • Running I/O Calibration

I/O Calibration with the Oracle Orion Calibration Tool

  • Introduction to the Oracle Orion Calibration Tool.
  • Orion Test Targets
  • Orion for Oracle Administrators.
  • Getting Started with Orion
  • Orion Input Files.
  • Orion Parameters.
  • Orion Required Parameter
  • Orion Optional Parameters.
  • Orion Command Line Samples.
  • Orion Output Files
  • Orion Sample Output Files
  • Orion Troubleshooting

18 Managing Operating System Resources

Understanding Operating System Performance Issues

  • Using Operating System Caches.
  • Asynchronous I/O
  • FILESYSTEMIO_OPTIONS Initialization Parameter
  • Limiting Asynchronous I/O in NFS Server Environments.
  • Memory Usage.
  • Buffer Cache Limits.
  • Parameters Affecting Memory Usage.
  • Using Operating System Resource Managers.

Resolving Operating System Issues

  • Performance Hints on UNIX-Based Systems.
  • Performance Hints on Windows Systems
  • Performance Hints on HP OpenVMS Systems

Understanding CPU

Resolving CPU Issues

  • Finding and Tuning CPU Utilization.
  • Checking Memory Management.
  • Paging and Swapping .
  • Oversize Page Tables .
  • Checking I/O Management.
  • Checking Network Management.
  • Checking Process Management.
  • Scheduling and Switching .
  • Context Switching .
  • Starting New Operating System Processes.
  • Managing CPU Resources Using Oracle Database Resource Manager
  • Managing CPU Resources Using Instance Caging