Oracle Database 19c Performance Management and Tuning Training Course

1 Overview

Objectives 1-2

What Is Performance Management? 1-3

Who Manages Performance? 1-4

What Does the DBA Tune? 1-5

Types of Tuning 1-6

Tuning Methodology 1-7

Effective Tuning Goals 1-9

General Tuning Session 1-11

Tuning a CDB 1-13

Performance Tuning: Diagnostics 1-14

Performance Tuning: Features and Tools 1-15

Tuning Objectives 1-16

Summary 1-17

Practice Overview 1-18

2 Defining the Scope of Performance Issues

Objectives 2-2

Defining the Problem 2-3

Limit the Scope 2-4

Determining Tuning Priorities 2-5

Common Tuning Problems 2-6

Tuning Life Cycle Phases 2-8

Tuning During the Life Cycle 2-9

Application Design and Development 2-10

Testing: Database Configuration 2-11

Deployment 2-12

Production 2-13

Migration, Upgrade, and Environment Changes 2-14

ADDM Tuning Session 2-15

Performance Versus Business Requirements 2-16

Monitoring and Tuning Tools: Overview 2-17

Summary 2-19

Practice Overview 2-20

3 Using the Time Model to Diagnose Performance Issues

Objectives 3-2

Time Model: Overview 3-3

DB Time 3-4

CPU and Wait Time Tuning Dimensions 3-5

Time Model Statistics Hierarchy 3-6

Time Model: Example 3-8

Top Timed Events 3-9

Summary 3-10

Practice Overview 3-11

4 Using Statistics and Wait Events to Diagnose Performance Issues

Objectives 4-2

Dynamic Performance Views 4-3

Dynamic Performance Views: Usage Examples 4-4

Dynamic Performance Views: Considerations 4-5

Statistic Levels 4-6

Instance Activity and Wait Event Statistics 4-8

System Statistic Classes 4-9

Displaying Statistics 4-10

Displaying SGA Statistics 4-11

Wait Events 4-12

Using the V$EVENT_NAME View 4-13

Wait Classes 4-14

Displaying Wait Event Statistics 4-15

Commonly Observed Wait Events 4-17

Using the V$SESSION_WAIT View 4-18

Precision of System Statistics 4-19

Summary 4-20

Practice Overview 4-21

5 Using Log and Trace Files to Monitor Performance

Objectives 5-2

Viewing the Alert Log 5-3

Using Alert Log Information as an Aid in Managing Performance 5-5

Administering the DDL Log File 5-6

Understanding the Debug Log File 5-7

User Trace Files 5-8

Background Processes Trace Files 5-9

Summary 5-10

Practice Overview 5-11

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

Objectives 6-2

Enterprise Manager: Overview 6-3

Configuring Enterprise Manager Database Express 6-4

Oracle Enterprise Manager Cloud Control Components 6-5

Using Features of the Oracle Management Packs and Options 6-6

Oracle SQL Developer 6-7

SQL Developer Command Line (SQLcl) 6-8

Summary 6-9

Practice Overview 6-10

7 Using Statspack to View Performance Data

Objectives 7-2

Introduction to Statspack 7-3

Statspack Scripts 7-4

Installing Statspack 7-6

Capturing Statspack Snapshots 7-7

Configuring Snapshot Data Capture 7-8

Statspack Snapshot Levels 7-9

Statspack Baselines and Purging 7-11

Reporting with Statspack 7-12

Statspack Considerations 7-13

Statspack Reports 7-14

Reading a Statspack Report 7-15

Statspack Report Drilldown Sections 7-16

Report Drilldown Examples 7-18

Load Profile Section 7-19

Time Model Section 7-20

Statspack and AWR 7-21

Summary 7-22

Practice Overview 7-23

8 Using Automatic Workload Repository

Objectives 8-2

Automatic Workload Repository: Overview 8-3

Automatic Workload Repository Data 8-4

Workload Repository 8-5

AWR Administration 8-6

AWR Snapshot Purging Policy 8-7

Managing Snapshots with PL/SQL 8-8

AWR Snapshot Settings 8-9

Manual AWR Snapshots 8-10

Managing AWR Data in a Multitenant Environment 8-11

AWR Snapshots and ADDM in a Multitenant Architecture Database 8-12

Generating AWR Reports 8-13

Reading the AWR Report 8-14

AWR Report: Multitenant Data 8-15

Generating AWR Reports by Using SQL*Plus 8-16

Statspack and AWR Reports 8-17

Reading a Statspack or an AWR Report 8-18

Compare Periods: Benefits 8-19

Snapshots and Periods Comparisons 8-20

Compare Periods: Results 8-21

Compare Periods: Report 8-22

Multitenant AWR Views 8-23

Summary 8-24

Practice Overview 8-25

9 Using Metrics and Alerts

Objectives 9-2

Metrics and Alerts 9-3

Limitation of Base Statistics 9-4

Typical Delta Tools 9-5

Oracle Database Metrics 9-6

Benefits of Metrics 9-7

Viewing Metric History Information 9-8

Viewing Metric Details 9-9

Statistic Histograms 9-10

Histogram Views 9-11

Server-Generated Alerts 9-12

Alert Usage Model 9-13

Metric and Alert Views 9-14

Summary 9-15

Practice Overview 9-16

10 Using Baselines

Objectives 10-2

Comparative Performance Analysis with AWR Baselines 10-3

Automatic Workload Repository Baselines 10-4

AWR Baselines 10-5

Types of Baselines 10-6

Moving Window Baseline 10-7

Baselines in Performance Page Settings 10-8

Baseline Templates 10-9

Creating AWR Baselines 10-10

Creating a Single AWR Baseline 10-11

Creating a Repeating Baseline and Template 10-12

Managing Baselines by Using the DBMS_WORKLOAD_REPOSITORY
Package 10-13

Generating a Baseline Template for a Single Time Period 10-14

Creating a Repeating Baseline Template 10-15

Baseline Views 10-16

Performance Monitoring and Baselines 10-17

Defining Alert Thresholds by Using a Static Baseline 10-19

Configuring a Basic Set of Thresholds 10-20

Summary 10-21

Practice 3 Overview: Using AWR Baselines 10-22

11 Managing Automated Maintenance Tasks

Objectives 11-2

Automated Maintenance Tasks 11-3

Maintenance Windows 11-4

Default Maintenance Plan 11-5

Automated Maintenance Task Priorities 11-6

Configuring Automated Maintenance Tasks 11-7

Summary 11-8

Practice Overview 11-9

12 Using ADDM to Analyze Performance

Objectives 12-2

ADDM Performance Monitoring 12-3

ADDM and Database Time 12-4

DB Time-Graph and ADDM Methodology 12-5

Top Performance Issues Detected 12-7

ADDM Recommendations 12-8

Creating a Manual ADDM Task 12-9

ADDM Tasks in a Multitenant Architecture Database 12-10

Changing ADDM Attributes 12-11

Retrieving ADDM Reports by Using SQL 12-12

Compare Periods ADDM: Analysis 12-13

Workload Compatibility 12-14

Configuring Automatic ADDM Analysis at the PDB Level 12-15

Using the DBMS_ADDM Package to Compare Periods 12-16

Example: Using the DBMS_ADDM Package to Compare Periods 12-17

Summary 12-18

Practice Overview 12-19

13 Using Active Session History Data for First Fault System Analysis

Objectives 13-2

Active Session History: Overview 13-3

Active Session History: Mechanics 13-4

ASH Sampling: Example 13-5

Accessing ASH Data 13-6

Analyzing the ASH Data 13-7

Using Enterprise Manager to Generate ASH Reports 13-8

Using the ASH Report Script to Generate a Report 13-9

ASH Report Structure 13-10

Determining the Source of Data 13-11

Performing Skew Analysis 13-12

Additional Automatic Workload Repository Views 13-13

Summary 13-14

Practice Overview 13-15

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

Objectives 14-2

Emergency Monitoring: Challenges 14-3

Emergency Monitoring: Goals 14-4

Using Real-Time ADDM to Perform a Root-Cause Analysis 14-5

Using Real-Time ADDM 14-6

Real-Time ADDM in the Database 14-7

Using Real-Time ADDM 14-9

Viewing Real-Time ADDM Results 14-10

Summary 14-11

15 Overview of SQL Statement Processing

Objectives 15-2

SQL Statement Processing Phases 15-3

Parsing 15-4

SQL Cursor Storage 15-5

Session Cursor Cache 15-6

Cursor Usage and Parsing 15-7

SQL Statement Processing Phases: Bind 15-8

SQL Statement Processing Phases: Execute and Fetch 15-9

Processing a DML Statement 15-10

Commit Processing 15-12

Identifying Poorly Performing SQL Statements 15-13

Top SQL Reports 15-14

SQL Monitoring 15-15

Monitored SQL Execution Details 15-16

Summary 15-17

16 Maintaining Indexes

Objectives 16-2

Creating Indexes 16-3

Using Invisible and Unusable Indexes 16-4

Dropping Indexes 16-5

Reducing the Cost of SQL Operations 16-6

Index Maintenance 16-7

Using Advanced Index Compression 16-9

Other Index Options 16-10

SQL Access Advisor 16-11

Quiz 16-12

Automatic Indexing Task 16-13

Automatic Index Task Workflow 16-15

Automatic Indexing Task Reporting 16-16

Automatic Indexing Views 16-17

Summary 16-18

Practice Overview 16-19

17 Maintaining Tables

Objectives 17-2

Reducing the Cost of SQL Operations 17-3

Table Maintenance for Performance 17-4

Table Reorganization Methods 17-5

Space Management 17-6

Extent Management 17-7

Locally Managed Extents 17-8

Large Extents: Considerations 17-9

How Table Data Is Stored 17-11

Anatomy of a Database Block 17-12

Minimize Block Visits 17-13

Block Allocation 17-14

Free Lists 17-15

Block Space Management 17-16

Block Space Management with Free Lists 17-17

Automatic Segment Space Management 17-19

Automatic Segment Space Management at Work 17-20

Block Space Management with ASSM 17-21

Creating an Automatic Segment Space Management Segment 17-22

Migration and Chaining 17-23

Guidelines for PCTFREE and PCTUSED 17-25

Detecting Migration and Chaining 17-26

Selecting Migrated Rows 17-27

Eliminating Migrated Rows 17-28

Shrinking Segments: Overview 17-30

Shrinking Segments: Considerations 17-31

Shrinking Segments by Using SQL 17-32

Segment Shrink: Basic Execution 17-33

Segment Shrink: Execution Considerations 17-34

Data Compression 17-35

Advanced Row Compression: Overview 17-37

Advanced Row Compression: Concepts 17-38

Using Advanced Row Compression 17-39

Advanced Row Compression for DML Operations 17-40

Advanced Index Compression 17-41

How Does Hybrid Columnar Compression Work? 17-42

Using the Compression Advisor 17-43

Using the Compression Advisor for Indexes 17-44

Viewing Table Compression Information 17-45

Quiz 17-46

Summary 17-47

Practice Overview 17-48

18 Introduction to Query Optimizer

Objectives 18-2

Role of the Oracle Optimizer 18-3

Functions of the Query Optimizer 18-5

Selectivity 18-7

Cardinality and Cost 18-8

Changing Optimizer Behavior 18-9

Setting and Viewing Optimizer Parameters 18-10

Using Initialization Parameters to Control Optimizer Behavior 18-11

Enabling Query Optimizer Features 18-13

Influencing the Optimizer Approach 18-14

Optimizing SQL Statements 18-15

Access Paths 18-16

Choosing an Access Path 18-17

Summary 18-18

19 Understanding Execution Plans

Objectives 19-2

What Is an Execution Plan? 19-3

Methods for Viewing Execution Plans 19-4

Uses of Execution Plans 19-5

DBMS_XPLAN Package: Overview 19-6

EXPLAIN PLAN Command 19-8

EXPLAIN PLAN Command: Example 19-9

EXPLAIN PLAN Command: Output 19-10

Reading an Execution Plan 19-11

Using the V$SQL_PLAN View 19-12

Querying V$SQL_PLAN 19-13

V$SQL_PLAN_STATISTICS View 19-14

Querying the AWR 19-15

SQL*Plus AUTOTRACE 19-16

Using SQL*Plus AUTOTRACE 19-17

SQL*Plus AUTOTRACE: Statistics 19-18

Quiz 19-19

Adaptive Execution Plans 19-20

Dynamic Plans 19-21

Dynamic Plan: Adaptive Process 19-22

Dynamic Plans: Example 19-23

Continuous Adaptive Query Plans 19-24

Automatic Re-Optimization 19-25

Comparing Execution Plans 19-26

Summary 19-27

Practice Overview 19-28

20 Viewing Execution Plans by Using SQL Trace and TKPROF

Objectives 20-2

SQL Trace Facility 20-3

How to Use the SQL Trace Facility 20-5

Initialization Parameters 20-6

Enabling SQL Trace 20-8

Disabling SQL Trace 20-9

Formatting Your Trace Files 20-10

TKPROF Command Options 20-11

Output of the TKPROF Command 20-13

TKPROF Output with No Index: Example 20-18

TKPROF Output with Index: Example 20-19

Generating an Optimizer Trace 20-20

Summary 20-21

Practice Overview 20-22

21 Managing Optimizer Statistics

Objectives 21-2

Optimizer Statistics 21-3

Types of Optimizer Statistics 21-4

Optimizer Statistics Collection 21-5

Dynamic Statistics 21-7

Gathering Statistics and Setting Optimizer Statistics Preferences 21-8

Setting Statistic Preferences 21-9

Viewing and Managing Optimizer Statistics Preferences 21-11

Extended Statistics 21-12

Maintaining Optimizer Statistics 21-13

Automated Maintenance Tasks 21-14

Optimizer Statistics Advisor 21-15

Optimizer Statistics Advisor Report 21-16

Executing Optimizer Statistics Advisor Tasks 21-17

Restoring Statistics 21-18

Deferred Statistics Publishing: Overview 21-19

Deferred Statistics Publishing: Example 21-21

Managing Real-Time Statistics 21-22

Configuring High-Frequency Automatic Optimizer Statistics Collection 21-23

Summary 21-24

Practice Overview 21-25

22 Using Automatic SQL Tuning

Objectives 22-2

Automatic SQL Tuning: Overview 22-3

SQL Statement Profiling 22-4

Plan Tuning Flow and SQL Profile Creation 22-5

SQL Tuning Loop 22-6

Using SQL Profiles 22-7

Summary 22-8

23 Using the SQL Plan Management Feature

Objectives 23-2

SQL Plan Management: Overview 23-3

SQL Plan Baseline: Architecture 23-4

Loading SQL Plan Baselines 23-5

Loading SQL Plan Baselines from AWR 23-6

Evolving SQL Plan Baselines 23-7

Adaptive SQL Plan Management 23-8

Automatically Evolving SQL Plan Baseline 23-9

Including Alternate Plans in the SPM Evolve Advisor List 23-10

Important Baseline SQL Plan Attributes 23-11

SQL Plan Selection 23-12

Possible SQL Plan Manageability Scenarios 23-13

SQL Performance Analyzer and SQL Plan Baseline Scenario 23-14

Loading a SQL Plan Baseline Automatically 23-15

Purging SQL Management Base Policy 23-16

Enterprise Manager and SQL Plan Baselines 23-17

Quiz 23-18

Summary 23-19

Practice Overview 23-20

24 Overview of the SQL Advisors

Objectives 24-2

SQL Tuning Process 24-3

SQL Tuning Advisor: Overview 24-4

SQL Access Advisor: Overview 24-6

SQL Performance Analyzer: Overview 24-7

Summary 24-9

25 Using the SQL Tuning Advisor

Objectives 25-2

SQL Tuning Advisor: Overview 25-3

SQL Tuning Advisor Architecture 25-6

Automatic Tuning Optimizer 25-7

Using the SQL Tuning Advisor 25-8

SQL Tuning Advisor Options 25-9

SQL Tuning Advisor Recommendations 25-10

Alternative Execution Plans 25-11

Summary 25-13

Practice Overview 25-14

26 Using the SQL Access Advisor

Objectives 26-2

SQL Access Advisor: Overview 26-3

Using the SQL Access Advisor 26-4

Viewing Recommendations 26-5

Viewing Recommendation Details 26-6

Summary 26-7

Practice Overview 26-8

27 Overview of Real Application Testing Components

Objectives 27-2

Real Application Testing: Overview 27-3

Real Application Testing: Use Cases 27-4

Summary 27-5

28 Using SQL Performance Analyzer to Determine the Impact of Changes

Objectives 28-2

SQL Performance Analyzer: Process 28-3

Steps 6-7: Comparing/Analyzing Performance and Tuning Regressed SQL 28-5

Capturing the SQL Workload 28-6

Creating a SQL Performance Analyzer Task 28-7

SQL Performance Analyzer Task Page 28-8

SQL Performance Analyzer: PL/SQL Example 28-9

Tuning Regressed SQL Statements 28-11

SQL Performance Analyzer: Data Dictionary Views 28-12

Quiz 28-13

Summary 28-14

Practice Overview 28-15

29 Using Database Replay to Test System Performance

Objectives 29-2

Using Database Replay 29-3

The Big Picture 29-4

System Architecture: Capture 29-5

System Architecture: Processing the Workload 29-7

System Architecture: Replay 29-8

Database Replay Workflow in Enterprise Manager 29-9

Accessing Database Replay in Enterprise Manager 29-10

Capture Considerations 29-11

Replay Considerations: Preparation 29-13

Replay Considerations 29-14

Replay Customized Options 29-15

Replay Analysis 29-16

Quiz 29-17

Database Replay Packages 29-18

Data Dictionary Views: Database Replay 29-19

Database Replay: PL/SQL Example 29-20

Calibrating Replay Clients 29-22

Capturing and Replaying in a CDB and PDBs 29-23

Reporting 29-24

Quiz 29-25

Summary 29-26

Practice Overview 29-27

30 Implementing Real-Time Database Operation Monitoring

Objectives 30-2

Overview 30-3

Use Cases 30-4

Defining a DB Operation 30-5

Scope of a Composite DB Operation 30-6

Database Operation Concepts 30-7

Identifying a Database Operation 30-8

Enabling Monitoring of Database Operations 30-9

Identifying, Starting, and Completing a Database Operation 30-10

Monitoring Database Operations in Sessions 30-11

Monitoring the Progress of a Database Operation 30-12

Monitoring Load Database Operations 30-13

Monitoring Load Database Operation Details 30-14

Database Operation View: V$SQL_MONITOR 30-15

Database Operation Views 30-16

Reporting Database Operations by Using Functions 30-17

Database Operation Tuning 30-18

Summary 30-19

Practice Overview 30-20

31 Using Services to Monitor Applications

Objectives 31-2

What is a service? 31-3

Service Attributes 31-4

Service Types 31-5

Creating Services 31-6

Using the DBMS_SERVICE Package to Manage Services 31-7

Where are services used? 31-8

Using Services with Client Applications 31-9

Using Services with the Resource Manager 31-10

Using Enterprise Manager to Manage Consumer Group Mappings 31-11

Services and the Resource Manager: Example 31-12

Using Enterprise Manager to Create a Job Class 31-13

Using Enterprise Manager to Create a Job 31-14

Services and the Scheduler: Example 31-15

Using Services with Metric Thresholds 31-16

Using Enterprise Manager to Change Service Thresholds 31-17

Services and Metric Thresholds: Example 31-18

Service Aggregation and Tracing 31-19

Top Services Performance Page 31-20

Service Aggregation Configuration 31-21

Service Aggregation: Example 31-22

Client Identifier Aggregation and Tracing 31-23

Using the TRCSESS Utility 31-24

Service Performance Views 31-25

Summary 31-27

Practice Overview 31-28

32 Overview of Memory Structures

Objectives 32-2

Managing Memory Caches and Structures 32-3

Guidelines for Efficient Memory Usage 32-4

Summary 32-6

Practice Overview 32-7

33 Managing Shared Pool Performance

Objectives 33-2

Shared Pool Architecture 33-3

Shared Pool Operation 33-4

Library Cache 33-5

Latch and Mutex 33-6

Latch and Mutex: Views and Statistics 33-8

Diagnostic Tools for Tuning the Shared Pool 33-10

AWR/Statspack Indicators 33-11

Top Timed Events 33-12

Time Model 33-13

Load Profile 33-14

Instance Efficiencies 33-15

Library Cache Activity 33-16

Avoid Hard Parses 33-17

Are Cursors Being Shared? 33-18

Candidate Cursors for Sharing 33-19

Sharing Cursors 33-20

Adaptive Cursor Sharing: Example 33-21

Adaptive Cursor Sharing Views 33-23

Interacting with Adaptive Cursor Sharing 33-24

Reduce the Cost of Soft Parses 33-25

Quiz 33-26

Sizing the Shared Pool 33-27

Shared Pool Advisory 33-28

Shared Pool Advisory in an AWR Report 33-29

Shared Pool Advisor 33-30

Avoiding Fragmentation 33-31

Large Memory Requirements 33-32

Tuning the Shared Pool Reserved Pool 33-34

Keeping Large Objects 33-36

Data Dictionary Cache 33-38

Dictionary Cache Misses 33-39

SQL Query Result Cache: Overview 33-40

Managing the SQL Query Result Cache 33-41

Using the RESULT_CACHE Hint 33-43

Using Table Annotation to Control Result Caching 33-44

Using the DBMS_RESULT_CACHE Package 33-45

Viewing SQL Result Cache Dictionary Information 33-46

SQL Query Result Cache: Considerations 33-47

Summary 33-48

Practice Overview 33-49

34 Managing Buffer Cache Performance

Objectives 34-2

Buffer Cache: Highlights 34-3

Database Buffers 34-4

Buffer Hash Table for Lookups 34-5

Working Sets 34-6

Tuning Goals and Techniques 34-8

Symptoms of a Buffer Cache Issue 34-10

Cache Buffer Chains Latch Contention 34-11

Finding Hot Segments 34-12

Buffer Busy Waits 34-13

Buffer Cache Hit Ratio 34-14

Buffer Cache Hit Ratio is Not Everything 34-15

Interpreting Buffer Cache Hit Ratio 34-16

Read Waits 34-17

Free Buffer Waits 34-18

Solutions for Buffer Cache Issues 34-19

Sizing the Buffer Cache 34-20

Buffer Cache Size Parameters 34-21

Dynamic Buffer Cache Advisory Parameter 34-22

Buffer Cache Advisory View 34-23

Using the V$DB_CACHE_ADVICE View 34-24

Using the Buffer Cache Advisor 34-25

Caching Tables 34-26

Automatic Big Table Caching 34-27

Configuring Automatic Big Table Caching 34-28

Using Automatic Big Table Caching 34-29

Monitoring Automatic Big Table Caching 34-30

Memoptimized Rowstore 34-31

In-Memory Hash Index 34-32

Multiple Buffer Pools 34-33

Enabling Multiple Buffer Pools 34-34

Calculating the Hit Ratio for Multiple Pools 34-35

Multiple Block Sizes 34-36

Multiple Database Writers 34-37

Multiple I/O Slaves 34-38

Using Multiple Writers and I/O Slaves 34-39

Private Pool for I/O-Intensive Operations 34-40

Automatically Tuned Multiblock Reads 34-41

Database Smart Flash Cache Overview 34-42

Using Database Smart Flash Cache 34-43

Database Smart Flash Cache Architecture Overview 34-44

Configuring Database Smart Flash Cache 34-45

Sizing Database Smart Flash Cache 34-46

Enabling and Disabling Flash Devices 34-47

Specifying Database Smart Flash Cache for a Table 34-48

Full Database In-Memory Caching 34-49

Setting Up Force Full Database Caching 34-50

Monitoring Full Database In-Memory Caching 34-51

Flushing the Buffer Cache (for Testing Only) 34-52

Summary 34-53

Practice Overview 34-54

35 Managing PGA and Temporary Space Performance

Objectives 35-2

SQL Memory Usage 35-3

Performance Impact 35-4

Automatic PGA Memory 35-5

SQL Memory Manager 35-6

Configuring Automatic PGA Memory 35-7

Setting PGA_AGGREGATE_TARGET Initially 35-8

Limiting the Size of the Program Global Area 35-9

Managing the PGA for PDBs 35-10

Monitoring SQL Memory Usage 35-11

Monitoring SQL Memory Usage: Examples 35-12

Tuning SQL Memory Usage 35-13

PGA Target Advice Statistics 35-14

PGA Target Advice Histograms 35-15

Automatic PGA and Enterprise Manager 35-16

Automatic PGA and AWR Reports 35-17

Temporary Tablespace Management: Overview 35-18

Temporary Tablespace: Locally Managed 35-19

Configuring Temporary Tablespace 35-20

Temporary Tablespace Group: Overview 35-22

Temporary Tablespace Group: Benefits 35-23

Creating Temporary Tablespace Groups 35-24

Maintaining Temporary Tablespace Groups 35-25

Viewing Tablespace Groups 35-26

Monitoring Temporary Tablespace 35-27

Shrinking a Temporary Tablespace 35-28

Using the Tablespace Option When Creating a Temporary Table 35-29

Quiz 35-30

Summary 35-31

Practice Overview 35-32

36 Configuring the Large Pool

Objectives 36-2

Large Pool Overview 36-3

Tuning the Large Pool 36-4

Summary 36-5

37 Using Automatic Shared Memory Management

Objectives 37-2

Oracle Database Architecture 37-3

Granules 37-4

Automatic Shared Memory Management: Overview 37-5

SGA Sizing Parameters: Overview 37-6

Dynamic SGA Transfer Modes 37-7

Memory Broker Architecture 37-8

Manually Resizing Dynamic SGA Parameters 37-9

Behavior of Auto-Tuned SGA Parameters 37-10

Behavior of Manually Tuned SGA Components 37-11

Using the V$SYSTEM_PARAMETER View 37-12

Resizing SGA_TARGET 37-13

Disabling Automatic Shared Memory Management 37-14

Using the SGA Advisor 37-15

Monitoring ASMM 37-16

Managing SGA for PDBs 37-17

Summary 37-18

Practice Overview 37-19

38 Introduction to In-Memory Column Store

Objectives 38-2

Database In-Memory Feature Set 38-3

Goals of In-Memory Column Store 38-5

Benefits 38-7

Overview 38-8

Row Store Versus Column Store: 2D Vision 38-10

In-Memory Column Unit 38-11

Compare: In-Memory Column Store Cache and Buffer Cache 38-12

Dual Format In-Memory 38-13

Indexes Issues 38-14

Process 38-15

In-Memory Column Store: Dual Format of Segments in SGA 38-16

Summary 38-17

39 Configuring the In-Memory Column Store Feature

Objectives 39-2

Deploying IM Column Store 39-3

Deploying IM Column Store: Objects Setting 39-4

Deploying IM Column Store: Columns Setting 39-5

Defining IM Column Store Compression 39-6

In-Memory Advisor 39-7

IM Advisor or Compression Advisor? 39-8

Computing Compression Ratio 39-9

IM FastStart 39-10

Automatic In-Memory: Overview 39-11

AIM Action 39-12

Configuring Automatic In-Memory 39-13

Diagnostic Views 39-14

Summary 39-15

Practice Overview 39-16

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

Objectives 40-2

Query Benefits 40-3

Testing and Comparing Query Performance 40-4

Queries on In-Memory Tables: Simple Predicate 40-5

MINMAX Pruning Statistics 40-6

IM Column Store Statistics 40-7

Execution Plan: TABLE ACCESS IN MEMORY FULL 40-8

Queries on In-Memory Tables: Join 40-9

Execution Plan: JOIN FILTER CREATE / USE 40-10

Queries on In-Memory Tables: Join Groups 40-11

Population of Expressions and Virtual Columns Results 40-12

In-Memory Expression Unit (IMEU) 40-14

Populating In-Memory Expression Results 40-15

Populating In-Memory Expression Results Within a Window 40-17

Waiting for In-Memory Segments to be Populated 40-18

Views 40-19

Summary 40-20

Practice Overview 40-21

41 Using In-Memory Column Store with Oracle Database Features

Objectives 41-2

Interaction with Other Products 41-3

Optimizer 41-4

IM Column Store and Real Application Clusters 41-6

IM Column Store and Data Pump 41-7

Data Pump TRANSFORM Names 41-8

Automatic Data Optimization Interaction 41-9

Managing Heat Map and Automatic Data Optimization Policies 41-10

Creating ADO In-Memory Policies 41-12

Summary 41-13

Practice Overview 41-14