Oracle Database 19c Performance Management and Tuning Training Course

1 Overview

Objectives 1-2

Performance Management Defined 1-3

Performance Accountability 1-4

Database Administrator Tuning Responsibilities 1-5

Categories of Performance Tuning 1-6

Systematic Tuning Approach 1-7

Establishing Effective Tuning Targets 1-9

Conducting a General Tuning Session 1-11

Optimizing a Container Database (CDB) 1-13

Performance Diagnostics 1-14

Diagnostic Features and Utilization Tools 1-15

Tuning Objectives Alignment 1-16

Chapter Summary 1-17

Practical Application Overview 1-18

2 Defining the Scope of Performance Issues

Objectives 2-2

Problem Definition Methodology 2-3

Containment and Scope Limitation 2-4

Establishing Tuning Priorities 2-5

Frequent Performance Challenges 2-6

Lifecycle Phases of Tuning 2-8

Integration Within the Development Lifecycle 2-9

Application Design and Construction 2-10

Validation: Database Configuration 2-11

Operational Deployment 2-12

Live Production Environment Management 2-13

System Migration, Upgrades, and Environmental Modifications 2-14

Automated Diagnostic Data Mining (ADDM) Session Analysis 2-15

Alignment of Performance Metrics with Business Requirements 2-16

Monitoring and Tuning Utilities: Summary 2-17

Chapter Summary 2-19

Practical Application Overview 2-20

3 Using the Time Model to Diagnose Performance Issues

Objectives 3-2

Time Model Framework: Introduction 3-3

Database Time (DB Time) Analysis 3-4

CPU and Wait Time Performance Dimensions 3-5

Hierarchical Structure of Time Model Statistics 3-6

Time Model: Illustrative Example 3-8

Identification of Top Timed Events 3-9

Chapter Summary 3-10

Practical Application Overview 3-11

4 Using Statistics and Wait Events to Diagnose Performance Issues

Objectives 4-2

Dynamic Performance Views Utilization 4-3

Application Scenarios for Dynamic Views 4-4

Operational Considerations for Dynamic Views 4-5

Granularity of Statistical Data 4-6

Instance Activity and Wait Event Metrics 4-8

Classification of System Statistics 4-9

Visualization of Statistical Data 4-10

Display of System Global Area (SGA) Statistics 4-11

Analysis of Wait Events 4-12

Utilization of the V$EVENT_NAME View 4-13

Categorization of Wait Classes 4-14

Retrieval of Wait Event Statistics 4-15

Prevalent Wait Event Classifications 4-17

Utilization of the V$SESSION_WAIT View 4-18

Accuracy and Precision of System Metrics 4-19

Chapter Summary 4-20

Practical Application Overview 4-21

5 Using Log and Trace Files to Monitor Performance

Objectives 5-2

Review of the Alert Log 5-3

Leverage Alert Log Data for Performance Management 5-5

Administration of DDL Log Files 5-6

Interpretation of Debug Log Content 5-7

User Session Trace File Analysis 5-8

Background Process Trace File Analysis 5-9

Chapter Summary 5-10

Practical Application Overview 5-11

6 Using Enterprise Manager Cloud Control and SQL Developer to Monitor Performance

Objectives 6-2

Enterprise Manager: Functional Overview 6-3

Configuration of Database Express Interface 6-4

Components of Oracle Enterprise Manager Cloud Control 6-5

Utilization of Oracle Management Packs and Options 6-6

Oracle SQL Developer Environment 6-7

SQL Developer Command Line Interface (SQLcl) 6-8

Chapter Summary 6-9

Practical Application Overview 6-10

7 Using Statspack to View Performance Data

Objectives 7-2

Introduction to the Statspack Utility 7-3

Execution of Statspack Scripts 7-4

Installation Procedure for Statspack 7-6

Capture of Statspack Snapshots 7-7

Configuration of Snapshot Data Collection 7-8

Classification of Statspack Snapshot Levels 7-9

Management of Baselines and Data Purging 7-11

Generation of Performance Reports via Statspack 7-12

Operational Considerations for Statspack 7-13

Structure of Statspack Reports 7-14

Analysis of Statspack Report Content 7-15

Detailed Sections within Statspack Reports 7-16

Interpretation Examples from Drilldown Data 7-18

Load Profile Section Analysis 7-19

Time Model Section Analysis 7-20

Integration of Statspack and AWR Methodologies 7-21

Chapter Summary 7-22

Practical Application Overview 7-23

8 Using Automatic Workload Repository

Objectives 8-2

Automatic Workload Repository (AWR): Framework Overview 8-3

Data Composition within the Automatic Workload Repository 8-4

Workload Repository Structure 8-5

Administration of AWR Components 8-6

Policy for AWR Snapshot Retention and Purging 8-7

Management of Snapshots via PL/SQL Procedures 8-8

Configuration of AWR Snapshot Intervals 8-9

Execution of Manual AWR Snapshots 8-10

Management of AWR Data in Multitenant Environments 8-11

Integration of AWR Snapshots and ADDM in Multitenant Architecture 8-12

Generation of AWR Performance Reports 8-13

Analysis of AWR Report Content 8-14

Interpretation of AWR Reports in Multitenant Contexts 8-15

Generation of AWR Reports Using SQL*Plus 8-16

Comparison of Statspack and AWR Reporting Capabilities 8-17

Analysis of Statspack or AWR Reports 8-18

Advantages of Period Comparison Analysis 8-19

Methodology for Snapshot and Period Comparison 8-20

Outcomes of Period Comparison Analysis 8-21

Presentation of Compare Periods Reports 8-22

Multitenant-Centric AWR Views 8-23

Chapter Summary 8-24

Practical Application Overview 8-25

9 Using Metrics and Alerts

Objectives 9-2

Framework of Metrics and Alert Mechanisms 9-3

Constraints of Base Statistical Data 9-4

Common Utility for Delta Analysis 9-5

Oracle Database Metric Definitions 9-6

Strategic Advantages of Metric Utilization 9-7

Retrieval of Historical Metric Data 9-8

Detailed Metric Information Retrieval 9-9

Statistical Histogram Analysis 9-10

Views for Histogram Data Access 9-11

Automated Server-Generated Alerts 9-12

Operational Model for Alert Utilization 9-13

Dictionary Views for Metrics and Alerts 9-14

Chapter Summary 9-15

Practical Application Overview 9-16

10 Using Baselines

Objectives 10-2

Comparative Performance Analysis via AWR Baselines 10-3

Automated Workload Repository Baseline Structures 10-4

Functionality of AWR Baselines 10-5

Classification of Baseline Types 10-6

Implementation of Moving Window Baselines 10-7

Configuration of Baselines in Performance Dashboards 10-8

Utilization of Baseline Templates 10-9

Creation Procedures for AWR Baselines 10-10

Generation of Individual AWR Baselines 10-11

Establishment of Repeating Baselines and Associated Templates 10-12

Management of Baselines via the DBMS_WORKLOAD_REPOSITORY
Package 10-13

Generation of Baseline Templates for Specific Time Intervals 10-14

Creation of Repeating Baseline Templates 10-15

Dictionary Views for Baseline Monitoring 10-16

Integration of Performance Monitoring and Baseline Data 10-17

Definition of Alert Thresholds via Static Baselines 10-19

Configuration of Fundamental Threshold Parameters 10-20

Chapter Summary 10-21

Practice Overview: Utilization of AWR Baselines 10-22

11 Managing Automated Maintenance Tasks

Objectives 11-2

Framework of Automated Maintenance Tasks 11-3

Configuration of Maintenance Windows 11-4

Default Maintenance Scheduling Plan 11-5

Priority Assignment for Automated Maintenance Tasks 11-6

Configuration Procedures for Automated Maintenance 11-7

Chapter Summary 11-8

Practical Application Overview 11-9

12 Using ADDM to Analyze Performance

Objectives 12-2

Database Time Model Integration with ADDM Monitoring 12-3

Relationship Between ADDM and Database Time 12-4

DB Time Graphing and ADDM Analytical Methodology 12-5

Identification of Critical Performance Issues 12-7

Formulation of ADDM Recommendations 12-8

Initiation of Manual ADDM Analysis Tasks 12-9

Execution of ADDM Tasks in Multitenant Architectures 12-10

Modification of ADDM Configuration Attributes 12-11

Retrieval of ADDM Reports via SQL Queries 12-12

Comparative Period Analysis using ADDM 12-13

Assessment of Workload Compatibility 12-14

Configuration of Automatic ADDM Analysis at the Pluggable Database (PDB) Level 12-15

Utilization of the DBMS_ADDM Package for Period Comparisons 12-16

Implementation Example: DBMS_ADDM Period Comparison 12-17

Chapter Summary 12-18

Practical Application Overview 12-19

13 Using Active Session History Data for First Fault System Analysis

Objectives 13-2

Active Session History (ASH): Framework Overview 13-3

Operational Mechanics of ASH 13-4

ASH Sampling Methodology: Case Study 13-5

Data Access Protocols for ASH Information 13-6

Analytical Techniques for ASH Data Interpretation 13-7

Generation of ASH Reports via Enterprise Manager 13-8

Report Generation Utilizing the ASH Script 13-9

Structural Components of an ASH Report 13-10

Identification of Data Origin Sources 13-11

Execution of Skew Analysis Procedures 13-12

Supplementary Automatic Workload Repository Views 13-13

Chapter Summary 13-14

Practical Application Overview 13-15

14 Using Emergency Monitoring and Real-Time ADDM to Analyze Performance Issues

Objectives 14-2

Operational Challenges in Emergency Monitoring 14-3

Strategic Goals of Emergency Monitoring 14-4

Execution of Root-Cause Analysis via Real-Time ADDM 14-5

Utilization Protocols for Real-Time ADDM 14-6

Operational Context of Real-Time ADDM within the Database 14-7

Implementation of Real-Time ADDM Procedures 14-9

Review of Real-Time ADDM Analytical Results 14-10

Chapter Summary 14-11

15 Overview of SQL Statement Processing

Objectives 15-2

Phases of SQL Statement Processing Lifecycle 15-3

Parsing Mechanisms and Procedures 15-4

Storage Architecture for SQL Cursors 15-5

Session-Specific Cursor Cache Management 15-6

Cursor Usage Patterns and Parsing Efficiency 15-7

Execution of Bind Variables in SQL Processing 15-8

Execution and Fetch Operations in SQL Processing 15-9

Processing Logic for Data Manipulation Language (DML) Statements 15-10

Commit Transaction Processing Procedures 15-12

Identification of Inefficient SQL Statements 15-13

Generation of Top SQL Performance Reports 15-14

Implementation of SQL Monitoring Capabilities 15-15

Analysis of Monitored SQL Execution Details 15-16

Chapter Summary 15-17

16 Maintaining Indexes

Objectives 16-2

Procedures for Index Creation 16-3

Utilization of Invisible and Unusable Indexes 16-4

Procedures for Index Dropping 16-5

Reduction of SQL Operation Costs via Indexing 16-6

Index Maintenance Protocols 16-7

Application of Advanced Index Compression Techniques 16-9

Alternative Index Configuration Options 16-10

Utilization of the SQL Access Advisor 16-11

Knowledge Assessment 16-12

Automation of Indexing Tasks 16-13

Workflow Procedures for Automated Indexing 16-15

Reporting Mechanisms for Automated Indexing 16-16

Dictionary Views for Automatic Indexing Monitoring 16-17

Chapter Summary 16-18

Practical Application Overview 16-19

17 Maintaining Tables

Objectives 17-2

Cost Reduction Strategies for SQL Operations 17-3

Table Maintenance for Performance Optimization 17-4

Methodologies for Table Reorganization 17-5

Storage Space Management Strategies 17-6

Extent Allocation and Management 17-7

Management of Locally Managed Extents 17-8

Operational Considerations for Large Extents 17-9

Data Storage Architecture for Tables 17-11

Structural Composition of Database Blocks 17-12

Minimization of Block Access Operations 17-13

Block Allocation Procedures 17-14

Implementation of Free Lists 17-15

Management of Block Space Allocation 17-16

Block Space Management via Free Lists 17-17

Implementation of Automatic Segment Space Management (ASSM) 17-19

Operational Mechanics of ASSM 17-20

Block Space Management Protocols with ASSM 17-21

Creation Procedures for Automatic Segment Space Management Segments 17-22

Analysis of Row Migration and Chaining 17-23

Guidelines for Configuration of PCTFREE and PCTUSED 17-25

Detection Mechanisms for Row Migration and Chaining 17-26

Selection Process for Migrated Rows 17-27

Procedures for Eliminating Migrated Rows 17-28

Overview of Segment Shrinking Operations 17-30

Operational Considerations for Segment Shrinking 17-31

Execution of Segment Shrinking via SQL Commands 17-32

Basic Execution Procedures for Segment Shrink 17-33

Critical Considerations During Segment Shrink Execution 17-34

Data Compression Methodologies 17-35

Overview of Advanced Row Compression Techniques 17-37

Conceptual Framework of Advanced Row Compression 17-38

Application of Advanced Row Compression in Operations 17-39

Impact of Advanced Row Compression on DML Operations 17-40

Implementation of Advanced Index Compression 17-41

Operational Mechanics of Hybrid Columnar Compression 17-42

Utilization of the Compression Advisor Utility 17-43

Application of the Compression Advisor for Index Optimization 17-44

Retrieval of Table Compression Metadata 17-45

Knowledge Assessment 17-46

Chapter Summary 17-47

Practical Application Overview 17-48

18 Introduction to Query Optimizer

Objectives 18-2

Functional Role of the Oracle Optimizer 18-3

Core Functions of the Query Optimizer Engine 18-5

Definition and Impact of Selectivity 18-7

Analysis of Cardinality and Cost Metrics 18-8

Mechanisms for Modifying Optimizer Behavior 18-9

Configuration and Inspection of Optimizer Parameters 18-10

Utilization of Initialization Parameters to Control Optimization Logic 18-11

Activation of Query Optimizer Features 18-13

Strategies for Influencing Optimizer Decision-Making 18-14

Procedures for Optimizing SQL Statement Execution 18-15

Classification of Access Paths 18-16

Methodology for Selecting Optimal Access Paths 18-17

Chapter Summary 18-18

19 Understanding Execution Plans

Objectives 19-2

Definition and Purpose of an Execution Plan 19-3

Methodologies for Viewing Execution Plans 19-4

Operational Applications of Execution Plans 19-5

Overview of the DBMS_XPLAN Package Utility 19-6

Utilization of the EXPLAIN PLAN Command 19-8

Illustrative Example of EXPLAIN PLAN Output 19-9

Interpretation of EXPLAIN PLAN Results 19-10

Techniques for Reading and Interpreting Execution Plans 19-11

Utilization of the V$SQL_PLAN View for Plan Retrieval 19-12

Query Methodologies for Accessing V$SQL_PLAN Data 19-13

Overview of the V$SQL_PLAN_STATISTICS View 19-14

Query Procedures for AWR Data Integration 19-15

Configuration and Usage of SQL*Plus AUTOTRACE 19-16

Practical Application of SQL*Plus AUTOTRACE 19-17

Analysis of AUTOTRACE Statistical Output 19-18

Knowledge Assessment 19-19

Implementation of Adaptive Execution Plans 19-20

Configuration and Functionality of Dynamic Plans 19-21

Adaptive Process Mechanisms in Dynamic Plans 19-22

Illustrative Case Study of Dynamic Plan Execution 19-23

Implementation of Continuous Adaptive Query Plans 19-24

Procedures for Automatic Re-Optimization Processes 19-25

Methodologies for Comparing Execution Plans 19-26

Chapter Summary 19-27

Practical Application Overview 19-28

20 Viewing Execution Plans by Using SQL Trace and TKPROF

Objectives 20-2

Framework of the SQL Trace Facility 20-3

Operational Procedures for Utilizing SQL Trace 20-5

Relevant Initialization Parameters for Trace Configuration 20-6

Activation Protocols for SQL Trace 20-8

Deactivation Protocols for SQL Trace 20-9

Formatting Procedures for Trace File Output 20-10

Configuration of TKPROF Command Options 20-11

Analysis of TKPROF Command Output Results 20-13

Example: TKPROF Output Without Index Utilization 20-18

Example: TKPROF Output With Index Utilization 20-19

Generation of Optimizer Trace Data 20-20

Chapter Summary 20-21

Practical Application Overview 20-22

21 Managing Optimizer Statistics

Objectives 21-2

Definition and Importance of Optimizer Statistics 21-3

Classification of Optimizer Statistics Types 21-4

Methodologies for Optimizer Statistics Collection 21-5

Implementation of Dynamic Statistics Collection 21-7

Procedures for Gathering Statistics and Configuring Preferences 21-8

Configuration of Statistic Preferences 21-9

Retrieval and Management of Optimizer Statistics Preferences 21-11

Implementation of Extended Statistics 21-12

Maintenance Protocols for Optimizer Statistics 21-13

Execution of Automated Maintenance Tasks for Statistics 21-14

Utilization of the Optimizer Statistics Advisor 21-15

Analysis of Optimizer Statistics Advisor Reports 21-16

Execution Procedures for Optimizer Statistics Advisor Tasks 21-17

Methodologies for Restoring Previous Statistics Sets 21-18

Overview of Deferred Statistics Publishing Mechanisms 21-19

Case Study: Implementation of Deferred Statistics Publishing 21-21

Management of Real-Time Statistics Collection 21-22

Configuration of High-Frequency Automatic Optimizer Statistics Collection 21-23

Chapter Summary 21-24

Practical Application Overview 21-25

22 Using Automatic SQL Tuning

Objectives 22-2

Automatic SQL Tuning: Functional Overview 22-3

Mechanisms of SQL Statement Profiling 22-4

Workflow for Plan Tuning and SQL Profile Creation 22-5

Execution Cycle of the SQL Tuning Loop 22-6

Application and Utilization of SQL Profiles 22-7

Chapter Summary 22-8

23 Using the SQL Plan Management Feature

Objectives 23-2

SQL Plan Management: Framework Overview 23-3

Architectural Structure of SQL Plan Baselines 23-4

Procedures for Loading SQL Plan Baselines 23-5

Importation of SQL Plan Baselines from AWR Data 23-6

Execution of SQL Plan Baseline Evolution Processes 23-7

Implementation of Adaptive SQL Plan Management 23-8

Automation of SQL Plan Baseline Evolution Procedures 23-9

Inclusion of Alternate Plans in the SPM Evolve Advisor Queue 23-10

Critical Attributes of Baseline SQL Plans 23-11

Methodology for SQL Plan Selection Processes 23-12

Scenarios Addressing SQL Plan Manageability Challenges 23-13

Integration of SQL Performance Analyzer and SQL Plan Baseline Workflows 23-14

Automation of SQL Plan Baseline Loading Procedures 23-15

Policy Framework for Purging the SQL Management Base 23-16

Integration of Enterprise Manager and SQL Plan Baselines 23-17

Knowledge Assessment 23-18

Chapter Summary 23-19

Practical Application Overview 23-20

24 Overview of the SQL Advisors

Objectives 24-2

Standardized SQL Tuning Process Workflow 24-3

SQL Tuning Advisor: Functional Overview 24-4

SQL Access Advisor: Functional Overview 24-6

SQL Performance Analyzer: Functional Overview 24-7

Chapter Summary 24-9

25 Using the SQL Tuning Advisor

Objectives 25-2

SQL Tuning Advisor: Functional Overview 25-3

Architectural Components of the SQL Tuning Advisor 25-6

Integration and Functionality of the Automatic Tuning Optimizer 25-7

Operational Procedures for Using the SQL Tuning Advisor 25-8

Configuration Options Available in the SQL Tuning Advisor 25-9

Interpretation of Recommendations Generated by the SQL Tuning Advisor 25-10

Identification and Evaluation of Alternative Execution Plans 25-11

Chapter Summary 25-13

Practical Application Overview 25-14

26 Using the SQL Access Advisor

Objectives 26-2

SQL Access Advisor: Functional Overview 26-3

Operational Procedures for Using the SQL Access Advisor 26-4

Retrieval and Analysis of Generated Recommendations 26-5

Detailed Examination of Recommendation Specifications 26-6

Chapter Summary 26-7

Practical Application Overview 26-8

27 Overview of Real Application Testing Components

Objectives 27-2

Real Application Testing: Functional Overview 27-3

Identified Use Cases for Real Application Testing 27-4

Chapter Summary 27-5

28 Using SQL Performance Analyzer to Determine the Impact of Changes

Objectives 28-2

Procedural Workflow of the SQL Performance Analyzer 28-3

Execution of Steps 6-7: Comparative Analysis and Tuning of Regressed SQL 28-5

Capture Methodologies for SQL Workloads 28-6

Creation Procedures for SQL Performance Analyzer Tasks 28-7

Configuration and Analysis of the SQL Performance Analyzer Task Page 28-8

Implementation Example: PL/SQL Utilization of the SQL Performance Analyzer 28-9

Resolution Procedures for Regressed SQL Statements 28-11

Dictionary Views Supporting the SQL Performance Analyzer 28-12

Knowledge Assessment 28-13

Chapter Summary 28-14

Practical Application Overview 28-15

29 Using Database Replay to Test System Performance

Objectives 29-2

Operational Framework for Using Database Replay 29-3

Strategic Overview of the Database Replay Process 29-4

System Architecture: Capture Phase Implementation 29-5

System Architecture: Workload Processing Mechanisms 29-7

System Architecture: Replay Execution Phase 29-8

Workflow Procedures for Database Replay in Enterprise Manager 29-9

Access Protocols for Database Replay within Enterprise Manager 29-10

Operational Considerations During the Capture Phase 29-11

Preparatory Considerations for Replay Execution 29-13

General Considerations for Replay Implementation 29-14

Configuration of Customized Replay Options 29-15

Analytical Procedures for Replay Results 29-16

Knowledge Assessment 29-17

Functional Packages for Database Replay 29-18

Dictionary Views Supporting Database Replay Monitoring 29-19

Implementation Example: PL/SQL Utilization of Database Replay 29-20

Calibration Procedures for Replay Client Systems 29-22

Execution of Capture and Replay Operations in CDB and PDB Environments 29-23

Reporting Mechanisms for Database Replay Results 29-24

Knowledge Assessment 29-25

Chapter Summary 29-26

Practical Application Overview 29-27

30 Implementing Real-Time Database Operation Monitoring

Objectives 30-2

Operational Framework Overview 30-3

Identified Use Cases for Real-Time Monitoring 30-4

Definition and Classification of a Database Operation 30-5

Scope Determination for Composite Database Operations 30-6

Conceptual Framework of Database Operations 30-7

Identification Methodology for Specific Database Operations 30-8

Activation Protocols for Database Operation Monitoring 30-9

Lifecycle Management: Initiation and Completion of Database Operations 30-10

Session-Level Monitoring of Database Operations 30-11

Progress Tracking Mechanisms for Database Operations 30-12

Monitoring Procedures for Load-Balanced Database Operations 30-13

Detailed Analysis of Monitored Load Database Operations 30-14

Utilization of the V$SQL_MONITOR View for Operational Data 30-15

Overview of Views Supporting Database Operation Monitoring 30-16

Generation of Operational Reports via Utilization Functions 30-17

Tuning Methodologies for Specific Database Operations 30-18

Chapter Summary 30-19

Practical Application Overview 30-20

31 Using Services to Monitor Applications

Objectives 31-2

Definition and Function of a Database Service 31-3

Configuration Attributes of Database Services 31-4

Classification of Service Types 31-5

Procedures for Service Creation 31-6

Management of Services Utilizing the DBMS_SERVICE Package 31-7

Operational Deployment Contexts for Services 31-8

Integration of Services with Client Application Architectures 31-9

Utilization of Services in Conjunction with the Resource Manager 31-10

Configuration of Consumer Group Mappings via Enterprise Manager 31-11

Integration Case Study: Services and Resource Manager 31-12

Creation of Job Classes via Enterprise Manager Interfaces 31-13

Execution of Job Creation Procedures via Enterprise Manager 31-14

Integration Case Study: Services and Database Scheduler 31-15

Configuration of Metric Thresholds in Conjunction with Services 31-16

Modification of Service Thresholds via Enterprise Manager 31-17

Integration Case Study: Services and Metric Thresholds 31-18

Methodologies for Service Aggregation and Trace Data Collection 31-19

Analysis of the Top Services Performance Dashboard Page 31-20

Configuration Parameters for Service Aggregation 31-21

Implementation Example: Service Aggregation Procedures 31-22

Methodologies for Client Identifier Aggregation and Tracing 31-23

Utilization of the TRCSESS Utility for Session Recovery 31-24

Dictionary Views Supporting Service Performance Monitoring 31-25

Chapter Summary 31-27

Practical Application Overview 31-28

32 Overview of Memory Structures

Objectives 32-2

Management of Memory Caches and Structural Components 32-3

Guidelines for Optimized Memory Utilization Efficiency 32-4

Chapter Summary 32-6

Practical Application Overview 32-7

33 Managing Shared Pool Performance

Objectives 33-2

Architectural Structure of the Shared Pool 33-3

Operational Mechanics of the Shared Pool 33-4

Functionality and Management of the Library Cache 33-5

Implementation of Latch and Mutex Mechanisms 33-6

Views and Statistics for Monitoring Latch and Mutex Activity 33-8

Diagnostic Utilities for Shared Pool Tuning 33-10

Analysis of AWR/Statspack Performance Indicators 33-11

Identification of Top Timed Events in Shared Pool Context 33-12

Application of Time Model Analysis to Shared Pool Data 33-13

Interpretation of Load Profile Metrics for Shared Pool Optimization 33-14

Assessment of Instance Efficiency Metrics 33-15

Analysis of Library Cache Activity Patterns 33-16

Strategies for Avoiding Hard Parse Operations 33-17

Verification of Cursor Sharing Effectiveness 33-18

Identification of Candidate Cursors for Sharing Optimization 33-19

Procedures for Enabling Cursor Sharing 33-20

Illustrative Example of Adaptive Cursor Sharing Implementation 33-21

Dictionary Views for Monitoring Adaptive Cursor Sharing 33-23

Operational Interaction with Adaptive Cursor Sharing Mechanisms 33-24

Strategies for Reducing the Cost of Soft Parse Operations 33-25

Knowledge Assessment 33-26

Methodologies for Sizing the Shared Pool Appropriately 33-27

Utilization of the Shared Pool Advisory Mechanism 33-28

Integration of Shared Pool Advisory Data in AWR Reports 33-29

Functional Overview of the Shared Pool Advisor Utility 33-30

Prevention and Mitigation of Memory Fragmentation 33-31

Management Strategies for Large Memory Requirement Scenarios 33-32

Tuning Procedures for the Shared Pool Reserved Pool Segment 33-34

Methods for Retaining Large Objects in Memory 33-36

Functionality and Management of the Data Dictionary Cache 33-38

Analysis and Mitigation of Dictionary Cache Misses 33-39

Overview of the SQL Query Result Cache Feature 33-40

Management Procedures for the SQL Query Result Cache 33-41

Utilization of the RESULT_CACHE Hint in SQL Statements 33-43

Control of Result Caching via Table Annotation Methods 33-44

Management of Result Caching via the DBMS_RESULT_CACHE Package 33-45

Retrieval of SQL Result Cache Metadata from Dictionary Views 33-46

Operational Considerations for the SQL Query Result Cache 33-47

Chapter Summary 33-48

Practical Application Overview 33-49

34 Managing Buffer Cache Performance

Objectives 34-2

Key Highlights of Buffer Cache Functionality 34-3

Architecture and Role of Database Buffers 34-4

Utilization of the Buffer Hash Table for Data Lookups 34-5

Analysis and Management of Working Sets 34-6

Definition of Tuning Goals and Corresponding Techniques 34-8

Identification of Symptoms Indicative of Buffer Cache Issues 34-10

Diagnosis and Mitigation of Cache Buffer Chains Latch Contention 34-11

Methodology for Identifying High-Activity ("Hot") Segments 34-12

Analysis and Resolution of Buffer Busy Wait Conditions 34-13

Calculation and Interpretation of the Buffer Cache Hit Ratio 34-14

Limitations of Reliance on the Buffer Cache Hit Ratio Alone 34-15

Correct Interpretation of Buffer Cache Hit Ratio Metrics 34-16

Analysis and Management of Read Wait Conditions 34-17

Diagnosis and Mitigation of Free Buffer Wait Conditions 34-18

Solution Frameworks for Addressing Buffer Cache Issues 34-19

Methodologies for Appropriately Sizing the Buffer Cache 34-20

Configuration of Parameters Controlling Buffer Cache Size 34-21

Dynamic Parameter for Buffer Cache Advisory Functions 34-22

Utilization of the Buffer Cache Advisor Dictionary View 34-23

Accessing and Interpretation of the V$DB_CACHE_ADVICE View 34-24

Operational Use of the Buffer Cache Advisory Interface 34-25

Implementation of Table Caching Strategies 34-26

Functionality and Management of Automatic Big Table Caching 34-27

Configuration Procedures for Automatic Big Table Caching 34-28

Operational Application of Automatic Big Table Caching 34-29

Monitoring Protocols for Automatic Big Table Caching Performance 34-30

Overview of the Memoptimized Rowstore Feature 34-31

Architecture and Function of the In-Memory Hash Index 34-32

Configuration and Management of Multiple Buffer Pools 34-33

Activation Procedures for Multiple Buffer Pool Environments 34-34

Calculation Methodology for Hit Ratios Across Multiple Pools 34-35

Implementation of Multiple Block Size Configurations 34-36

Configuration and Management of Multiple Database Writers 34-37

Setup and Utilization of Multiple I/O Slave Processes 34-38

Integration Strategies for Multiple Writers and I/O Slaves 34-39

Allocation of Private Pools for I/O-Intensive Operational Workloads 34-40

Implementation of Automatically Tuned Multiblock Read Operations 34-41

Overview of the Database Smart Flash Cache Feature 34-42

Operational Application of the Database Smart Flash Cache 34-43

Architectural Framework of the Database Smart Flash Cache 34-44

Configuration Procedures for the Database Smart Flash Cache 34-45

Sizing Methodologies for the Database Smart Flash Cache 34-46

Activation and Deactivation Procedures for Flash Storage Devices 34-47

Specification of Database Smart Flash Cache Assignment for Specific Tables 34-48

Implementation of Full Database In-Memory Caching Strategies 34-49

Configuration Procedures for Enabling Force Full Database Caching 34-50

Monitoring Protocols for Full Database In-Memory Caching Performance 34-51

Execution of Buffer Cache Flushing Operations (For Testing Purposes Only) 34-52

Chapter Summary 34-53

Practical Application Overview 34-54

35 Managing PGA and Temporary Space Performance

Objectives 35-2

Analysis of SQL Memory Usage Patterns 35-3

Assessment of Performance Impact on SQL Memory Allocation 35-4

Functionality of Automatic PGA Memory Management 35-5

Architecture and Operation of the SQL Memory Manager 35-6

Configuration Procedures for Automatic PGA Memory 35-7

Initial Configuration of the PGA_AGGREGATE_TARGET Parameter 35-8

Implementation of Limits on Program Global Area (PGA) Size 35-9

Management Strategies for PGA Allocation in Pluggable Databases (PDBs) 35-10

Monitoring Procedures for SQL Memory Utilization 35-11

Case Studies in Monitoring SQL Memory Usage 35-12

Tuning Methodologies for Optimizing SQL Memory Allocation 35-13

Analysis of PGA Target Advisory Statistical Data 35-14

Interpretation of PGA Target Advisory Histograms 35-15

Integration of Automatic PGA Management with Enterprise Manager 35-16

Extraction and Analysis of Automatic PGA Data from AWR Reports 35-17

Overview of Temporary Tablespace Management Strategies 35-18

Implementation and Management of Locally Managed Temporary Tablespaces 35-19

Configuration Procedures for Temporary Tablespace Allocation 35-20

Overview of Temporary Tablespace Group Architecture 35-22

Strategic Benefits of Implementing Temporary Tablespace Groups 35-23

Procedures for Creating Temporary Tablespace Groups 35-24

Maintenance Protocols for Temporary Tablespace Groups 35-25

Retrieval of Temporary Tablespace Group Definitions 35-26

Monitoring Procedures for Temporary Tablespace Utilization 35-27

Methodologies for Shrinking Temporary Tablespaces 35-28

Application of the Tablespace Option During Temporary Table Creation 35-29

Knowledge Assessment 35-30

Chapter Summary 35-31

Practical Application Overview 35-32

36 Configuring the Large Pool

Objectives 36-2

Overview of Large Pool Functionality 36-3

Tuning Methodologies for the Large Pool 36-4

Chapter Summary 36-5

37 Using Automatic Shared Memory Management

Objectives 37-2

Oracle Database Architecture in the Context of Memory Management 37-3

Concept and Application of Granules 37-4

Automatic Shared Memory Management (ASMM): Functional Overview 37-5

SGA Sizing Parameters: Configuration Overview 37-6

Dynamic Transfer Modes for System Global Area (SGA) Allocation 37-7

Architecture of the Memory Broker Mechanism 37-8

Manual Resizing Procedures for Dynamic SGA Parameters 37-9

Behavioral Characteristics of Auto-Tuned SGA Parameters 37-10

Behavioral Characteristics of Manually Tuned SGA Components 37-11

Utilization of the V$SYSTEM_PARAMETER View for Parameter Inspection 37-12

Procedures for Resizing the SGA_TARGET Parameter 37-13

Protocols for Disabling Automatic Shared Memory Management 37-14

Utilization of the SGA Advisor Utility 37-15

Monitoring Procedures for ASMM Effectiveness 37-16

Management Strategies for SGA Allocation in Pluggable Databases (PDBs) 37-17

Chapter Summary 37-18

Practical Application Overview 37-19

38 Introduction to In-Memory Column Store

Objectives 38-2

Feature Set of the Database In-Memory Component 38-3

Performance Goals Associated with the In-Memory Column Store 38-5

Strategic Benefits of Implementing the In-Memory Column Store 38-7

Conceptual Overview of the In-Memory Column Store 38-8

Comparative Analysis: Row Store Versus Column Store Architectures 38-10

Structure and Function of the In-Memory Column Unit 38-11

Comparison: In-Memory Column Store Cache versus Buffer Cache 38-12

Implementation of Dual Format In-Memory Storage 38-13

Resolution of Indexing Challenges in In-Memory Environments 38-14

Operational Process Flow for In-Memory Data Processing 38-15

Dual Format Representation of Segments within the SGA for In-Memory Processing 38-16

Chapter Summary 38-17

39 Configuring the In-Memory Column Store Feature

Objectives 39-2

Deployment Procedures for the IM Column Store 39-3

Configuration of Object-Level Settings for IM Column Store Deployment 39-4

Configuration of Column-Level Settings for IM Column Store Deployment 39-5

Definition of Compression Parameters for the IM Column Store 39-6

Utilization of the In-Memory Advisor Utility 39-7

Differentiation Between the IM Advisor and Compression Advisor Utilities 39-8

Calculation Methodologies for Compression Ratios 39-9

Functionality of the IM FastStart Feature 39-10

Overview of Automatic In-Memory Management Capabilities 39-11

Mechanisms and Actions of Automatic In-Memory (AIM) Processes 39-12

Configuration Procedures for Automatic In-Memory Management 39-13

Diagnostic Views for Monitoring the In-Memory Column Store 39-14

Chapter Summary 39-15

Practical Application Overview 39-16

40 Using the In-Memory Column Store Feature to Improve SQL Performance

Objectives 40-2

Query Performance Benefits Associated with the In-Memory Column Store 40-3

Methodologies for Testing and Comparing Query Performance Outcomes 40-4

Execution of Queries on In-Memory Tables Utilizing Simple Predicates 40-5

Analysis of MINMAX Pruning Statistics for In-Memory Optimization 40-6

Interpretation of IM Column Store Statistical Data 40-7

Execution Plan Analysis: TABLE ACCESS IN MEMORY FULL Operation 40-8

Execution of Queries on In-Memory Tables Utilizing Join Operations 40-9

Execution Plan Analysis: JOIN FILTER CREATE / USE Operations 40-10

Execution of Queries on In-Memory Tables Utilizing Join Groups 40-11

Population Methodologies for Expressions and Virtual Column Results 40-12

Architecture and Function of the In-Memory Expression Unit (IMEU) 40-14

Procedures for Populating In-Memory Expression Result Sets 40-15

Execution of In-Memory Expression Population Within Defined Time Windows 40-17

Management of Latency Associated with the Population of In-Memory Segments 40-18

Relevant Dictionary Views for Monitoring In-Memory Data 40-19

Chapter Summary 40-20

Practical Application Overview 40-21

41 Using In-Memory Column Store with Oracle Database Features

Objectives 41-2

Integration and Interaction with Associated Oracle Products 41-3

Interaction with the Query Optimizer Engine 41-4

Implementation of the IM Column Store in Real Application Clusters (RAC) Environments 41-6

Integration of the IM Column Store with Data Pump Utilities 41-7

Configuration of Data Pump TRANSFORM Parameters for In-Memory Support 41-8

Interaction Mechanisms Between Automatic Data Optimization (ADO) and the In-Memory Feature 41-9

Management Strategies for Heat Map and Automatic Data Optimization Policies 41-10

Creation Procedures for ADO In-Memory Policy Definitions 41-12

Chapter Summary 41-13

Practical Application Overview 41-14