오라클 튜닝 가이드 - olakeul tyuning gaideu

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Changes in This Release for Oracle Database Performance Tuning Guide

• Changes in Oracle Database 12c Release 1 (12.1.0.2)
  • New Features
• Changes in Oracle Database 12c Release 1 (12.1.0.1)
  • New Features
  • Other Changes

Part I Database Performance Fundamentals

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
    • Software Components
  • 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 Tables
    • Using a Different Index Type
    • 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
  • 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
  • Undo Space
  • Redo Log Files
  • Tablespaces
• Creating and Maintaining Tables for Optimal Performance
  • Table Compression
  • Reclaiming Unused Space
  • Indexing Data
• Performance Considerations for Shared Servers
  • Identifying and Reducing Contention Using the Dispatcher-Specific Views
  • Identifying Contention for Shared Servers

Part II Diagnosing and Tuning Database Performance

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
    • Dropping Snapshots
    • Modifying Snapshot Settings
  • Managing Baselines
    • User Interface for Managing Baselines
    • Creating a Baseline
    • Dropping a Baseline
    • Renaming a Baseline
    • Displaying Baseline Metrics
    • Resizing the Default Moving Window Baseline
  • Managing Baseline Templates
    • User Interfaces for Managing Baseline Templates
    • Creating a Single Baseline Template
    • Creating a Repeating Baseline Template
    • Dropping a Baseline Template
  • 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 Database
    • Generating an Oracle RAC AWR Report for a Specific Database
    • Generating an AWR Report for a SQL Statement on the Local Database
    • Generating an AWR Report for a SQL Statement on a Specific Database
• 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 Active Report
  • 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-Line Interface
    • 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 for the Local Database
    • Generating an Oracle RAC AWR Compare Periods Report for a Specific Database
• 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
    • 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 History
• 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 Monitoring Tools
    • 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
    • Segment-Level Statistics
  • Implement and Measure Change
• Interpreting Oracle Database Statistics
  • Examine Load
  • Using Wait Event Statistics to Drill Down to Bottlenecks
  • Table of Wait Events and Potential Causes
  • Additional Statistics
• Wait Events Statistics
  • Changes to Wait Event Statistics from Past Releases
  • buffer busy waits
  • db file scattered read
  • db file sequential read
  • direct path read and direct path read temp
  • direct path write and direct path write temp
  • enqueue (enq:) waits
  • events in wait class other
  • free buffer waits
  • Idle Wait Events
  • latch events
  • log file parallel write
  • library cache pin
  • library cache lock
  • log buffer space
  • log file switch
  • log file sync
  • rdbms ipc reply
  • SQL*Net Events
• Tuning Instance Recovery Performance: Fast-Start Fault Recovery
  • About Instance Recovery
    • Cache Recovery (Rolling Forward)
    • Transaction Recovery (Rolling Back)
    • Checkpoints and Cache Recovery
  • 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: Scenario
      • 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_ADVICE
    • Determine the Optimal Size for Redo Logs

Part III Tuning Database Memory

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 Segment
  • 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
      • About V$JAVA_POOL_ADVICE and V$JAVA_LIBRARY_CACHE_MEMORY
    • 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_CACHE
      • Viewing Memory Usage Statistics for the Server Result Cache
      • 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$SQL_WORKAREA_HISTOGRAM View
    • Using the V$WORKAREA_ACTIVE View
    • Using the V$SQL_WORKAREA View
  • Tuning PGA_AGGREGATE_TARGET
    • Enabling Automatic Generation of PGA Performance Advisory Views
    • Using the V$PGA_TARGET_ADVICE View
    • Using the V$PGA_TARGET_ADVICE_HISTOGRAM View
    • Using the V$SYSSTAT and V$SESSTAT Views
    • Tutorial: How to Tune PGA_AGGREGATE_TARGET
• Limiting the Size of the Program Global Area
  • About PGA_AGGREGATE_LIMIT
  • Setting PGA_AGGREGATE_LIMIT

Part IV Managing System Resources

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 Boundaries
    • 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

Index