Oracle Database 12c Release 2 Performance Tuning Tips & Techniques | Oracle Press

Home >
Browse >
Oracle Press >
Oracle Database 12c Release 2 Performance Tuning Tips & Techniques

Oracle Database 12c Release 2 Performance Tuning Tips & Techniques

Authors: Richard Niemiec

Published: March 2017 Pages: 848

eISBN: 9781259589690 | ISBN: 9781259589683

Cover

Title Page

Copyright Page

About the Author

Contents at a Glance

Contents

Acknowledgments

Introduction

1 Introduction to Oracle Database 12c R1 & R2 New Features (DBA and Developer)

Oracle Database 12cR1 (12.1.0.1)

Oracle Database 12cR1 (12.1.0.2)

Oracle Database 12cR2 (12.2)

New Background Processes in 12c

Exadata-New with Exadata X6!

Version Comparison Chart

New Features Review

References

2 Basic Index Principles (Beginner Developer and Beginner DBA)

Basic Index Concepts

Invisible Indexes

Multiple Types of Indexes on the Same Column(s)

Concatenated Indexes

Suppressing Indexes

Selectivity

The Clustering Factor

The Binary Height

Using Histograms

Fast Full Scans

Skip-Scans

Types of Indexes

Fast Index Rebuilding

Rebuilding Indexes Online

Tips Review

References

3 Pluggable Databases, Disk Implementation Methodology, and ASM (DBA)

Pluggable Databases (New in Oracle 12c)

Disk Arrays

Setup and Maintenance of the Traditional Filesystem

The 12c Heat Map and Automatic Data Optimization (ADO)

12c I/O Performance Tracking Views (Outliers)

Oracle Bigfile Tablespaces

ASM Introduction

Avoiding Disk Contention by Using Partitions

Eliminating Fragmentation (Only If Needed-Careful!)

Increasing the Log File Size and LOG_CHECKPOINT_INTERVAL for Speed

Determining If Redo Log File Size Is a Problem

Storing Multiple Control Files on Different Disks and Controllers

Other Disk I/O Precautions and Tips

Issues to Consider in the Planning Stages

Tips Review

References

4 Tuning the Database with Initialization Parameters (DBA)

When Upgrading to Oracle Database 12c

Using SEC_CASE_SENSITIVE_LOGON

Crucial Memory Initialization Parameters for Performance

PDB Level: MEMORY_LIMIT and MEMORY_MINIMUM

In-Memory Database (INMEMORY_SIZE)

Changing the Initialization Parameters Without a Restart

Modifying an Initialization Parameter at the PDB Level

Insight into the Initialization Parameters from Oracle Utilities

Viewing the Initialization Parameters with Enterprise Manager

Increasing Performance by Tuning the DB_CACHE_SIZE

Using V$DB_CACHE_ADVICE in Tuning DB_CACHE_SIZE

Setting DB_BLOCK_SIZE to Reflect the Size of Your Data Reads

Setting SGA_MAX_SIZE to 25 to 50 Percent of the Size Allocated to Main Memory

Tuning the SHARED_POOL_SIZE for Optimal Performance

Using Oracle Multiple Buffer Pools

Tuning PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT

Modifying the Size of Your SGA to Avoid Paging and Swapping

Understanding the Oracle Optimizer

Creating Enough Dispatchers

Two Important Exadata Initialization Parameters (Exadata Only)

Top 25 Initialization Parameters

Understanding the Typical Server

Tips Review

References

5 Tuning with Enterprise Manager Cloud Control (DBA and Developer)

Oracle Enterprise Manager Basics and Accessing OEM via Oracle Cloud

Starting with All Targets and Other Groupings

Monitoring and Tuning Using the OEM Performance Menu

Monitoring and Tuning Using the OEM Administration Menu

Monitoring and Tuning Using the OEM Database or Cluster Database Menu

Database Tab: Job Activity

Monitoring the Hosts

Monitoring the Application Servers and Web Applications

Real Application Testing (Database Replay)

Summary

Tips Review

References

6 Using EXPLAIN, TRACE, and SQL Plan Management (Developer and DBA)

The Oracle SQL TRACE Utility

Using DBMS_MONITOR

Using EXPLAIN PLAN Alone

Using Stored Outlines

Using SQL Plan Management (SPM) and SPM Example

Tips Review

References

7 Basic Hint Syntax (Developer and DBA)

Top Hints Used

Available Hints and Groupings

Specifying a Hint

Specifying Multiple Hints

When Using an Alias, Hint the Alias, Not the Table

The Hints

Some Miscellaneous Hints and Notes

Why Isn't My Hint Working?

Hints at a Glance

Tips Review

References

8 Query Tuning (Developer and Beginner DBA)

Which Queries Do I Tune? Querying V$SQLAREA and V$SQL Views

Oracle 12c Views for Locating Resource-Intensive Sessions and Queries

When Should I Use an Index?

What If I Forget the Index?

What If I Create a Bad Index?

Exercising Caution When Dropping an Index

Indexing the Columns Used in the SELECT and WHERE

Using the Fast Full Scan

Making the Query “Magically” Faster

Caching a Table in Memory

Using the Result Cache

Choosing Among Multiple Indexes (Use the Most Selective)

The Index Merge

Indexes That Can Get Suppressed

Function-Based Indexes

Virtual Columns

The “Curious” OR

Using the EXISTS Function and the Nested Subquery

That Table Is Actually a View!

SQL and Grand Unified Theory

Tuning Changes in Oracle Database 12c

Oracle Automatic SQL Tuning

Tuning SQL Statements Automatically Using SQL Tuning Advisor

Using SQL Performance Analyzer (SPA)

Tips Review

References

9 Table Joins and Other Advanced Tuning (Advanced DBA and Developer)

Database Replay (capture/replay)

SQL Performance Analyzer

Join Methods

Table Join Initialization Parameters

A Two-Table Join: Equal-Sized Tables (Cost-Based)

A Two-Table INDEXED Join: Equal-Sized Tables (Cost-Based)

Forcing a Specific Join Method

Eliminating Join Records (Candidate Rows) in Multitable Joins

A Two-Table Join Between a Large and Small Table

Three-Table Joins: Not as Much Fun

Bitmap Join Indexes

Third-Party Product Tuning

Tuning Distributed Queries

When You Have Everything Tuned

Miscellaneous Tuning Snippets

Tuning at the Block Level (Advanced)

Tuning Using Simple Mathematical Techniques

Tips Review

References

10 Using PL/SQL to Enhance Performance (Developer and DBA)

Leverage the PL/SQL Function Result Cache to Improve Performance (Improved in 12c)

Define PL/SQL Subprograms in a SQL Statement (New in 12c)

Reference Sequences Directly in PL/SQL Expressions

Identity Columns (New in 12c)

Max Size Increase to 32K for VARCHAR2, NVARCHAR2, and RAW Data Types (New in 12c)

Allow Binding PL/SQL-Only Data Types to SQL Statements (New in 12c)

Use Named Parameters in SQL Function Calls

Simplify Loops with the CONTINUE Statement

Leverage Compile-Time Warnings to Catch Programming Mistakes (Improved in 12c)

Increase Performance with Native Compilation

Maximize Performance with the Optimizing Compiler

Use DBMS_APPLICATION_INFO for Real-Time Monitoring

Log Timing Information in a Database Table

Reduce PL/SQL Program Unit Iterations and Iteration Time

Use ROWID for Iterative Processing

Standardize on Data Types, IF Statement Order, and PLS_INTEGER

Reduce the Calls to SYSDATE

Reduce the Use of the MOD Function

Improve Shared Pool Use by Pinning PL/SQL Objects

Identify PL/SQL Objects That Need to Be Pinned

Use and Modify DBMS_SHARED_POOL.SIZES

Get Detailed Object Information from DBA_OBJECT_SIZE

Find Invalid Objects

Find Disabled Triggers

Use PL/SQL Associative Arrays for Fast Reference Table Lookups

Find and Tune the SQL When Objects Are Used

Consider Time Component When Working with DATE Data Types

Use PL/SQL to Tune PL/SQL

Understand the Implications of PL/SQL Object Location

Use Rollback Segments to Open Large Cursors

Use Temporary Database Tables for Increased Performance

Limit the Use of Dynamic SQL

Use Pipelined Table Functions to Build Complex Result Sets

Leave Those Debugging Commands Alone!

The “Look and Feel”: Just for the Beginners

Tips Review

References

11 Oracle Cloud, Exadata, Tuning RAC, and Using Parallel Features

The March to the Cloud (Past and Present)

The Oracle Cloud

Exadata Database Machine

Oracle Database Appliance (ODA)

SuperCluster Using the M7 SPARC Chip

Other Oracle Hardware to Consider

Parallel Databases

Real Application Clusters (RAC)

RAC Performance Tuning Overview

Basic Concepts of Parallel Operations

Oracle Documentation Is Online

Tips Review

References

12 The V$ Views (Developer and DBA)

Creating and Granting Access to V$ Views

Getting a Listing for the X$ Scripts That Make Up the V$ Views

Using Helpful V$ Scripts

Summary of Memory Allocated (V$SGA)

Finding spfile.ora/init.ora Settings in V$PARAMETER

Modifying an Initialization Parameter at PDB Level

Determining If Data Is in Memory (V$SYSSTAT & V$SYSMETRIC)

Determining Memory for the Data Dictionary (V$ROWCACHE)

Determining Memory for the Shared SQL and PL/SQL (V$LIBRARYCACHE)

Querying V$CONTAINERS and V$PDBS for Container Information

Identifying PL/SQL Objects That Need to Be Kept (Pinned)

Finding Problem Queries by Monitoring V$SESSION_LONGOPS

Finding Problem Queries by Querying V$SQLAREA

Finding Out What Users Are Doing and Which Resources They Are Using

Using Indexes

Identifying Locking Issues

Finding Disk I/O Issues

Checking for Privileges and Roles

Wait Events V$ Views

Some of the Major V$ View Categories

Tips Review

References

13 The X$ Tables and Internals Topics (Advanced DBA)

Introducing the X$ Tables

Creating V$ Views and X$ Tables in 12c

Obtaining a List of All the X$ Tables in 12c

Obtaining a List of All the X$ Indexes in 12c

Using Hints with X$ Tables and Indexes

Monitoring Space Allocations in the Shared Pool

Creating Queries to Monitor the Shared Pool

Obtaining Information About Redo Log Files

Setting Initialization Parameters

Exploring Buffer Cache/Data Block Details

Obtaining Database- and Instance-Specific Information

Effective X$ Table Use and Strategy

Oracle Internals Topics

Reading the Trace File

Some Common X$ Table Groups

Some Common X$ Table and Non-V$ Fixed View Associations

Common X$ Table Joins

X$ Table Naming Conventions (My Favorite Section of This Book!)

Future Version Impact in 12cR2

Tips Review

References

14 Using Statspack and the AWR Report to Tune Waits, Latches, and Mutexes

What's New in 12cR2 (12.2) Statspack and the AWR Report

Installing Statspack

The Automatic Workload Repository (AWR) and the AWR Report

Interpreting the Statspack and AWR Report Output

Top 15 Things to Look for in AWR Report and Statspack Output

Quick Notes on the New ADDM Report

Scripts in 12cR2

Tips Review

References

15 Performing a Quick System Review (DBA)

Total Performance Index (TPI)

Education Performance Index (EPI)

System Performance Index (SPI)

Memory Performance Index (MPI)

Disk Performance Index (DPI)

Total Performance Index (TPI)

Overall System Review Example

System Information List

Other Items to Consider in Your TPI and System Review

Tips Review

References

16 Monitor the System Using Unix Utilities (DBA)

Unix/Linux Utilities

Using the sar Command to Monitor CPU Usage

Using the sar and vmstat Commands to Monitor Paging/Swapping

Finding the Worst User on the System Using the top Command

Using the uptime Command to Monitor CPU Load

Using the mpstat Command to Identify CPU Bottlenecks

Combining ps with Selected V$ Views

Using the iostat Command to Identify Disk I/O Bottlenecks

Using the ipcs Command to Determine Shared Memory

Using the vmstat Command to Monitor System Load

Monitoring Disk Free Space

Monitoring Network Performance with netstat

Modifying the Configuration Information File

Other Sources to Improve Performance

Tips Review

References

A Key Initialization Parameters (DBA)

Obsoleted/Desupported Initialization Parameters

Deprecated Initialization Parameters

Top 25 Initialization Parameters

Top 20 Initialization Parameters Not to Forget

Top 13 Undocumented Initialization Parameters (As I See It)

Bonus 11 Undocumented Initialization Parameters

Listing of Documented Initialization Parameters (V$PARAMETER)

Listing of Undocumented Initialization Parameters (X$KSPPI/X$KSPPCV)

Additional Oracle Applications Notes

Top 10 Reasons Not to Write a Book

Tips Review

References

B The V$ Views (DBA and Developer)

Creation of V$ and GV$ Views and X$ Tables

A List of Oracle 12c (12.2.0.0.1) GV$ Views

A List of Oracle 12c (12.2.0.0.1) V$ Views

Oracle 12c Scripts for the X$ Tables Used to Create the V$ Views

C The X$ Tables (DBA)

Oracle 12cR2 X$ Tables Ordered by Name

Oracle 12cR2 X$ Indexes

Oracle 12cR2 V$ Views Cross-Referenced to the X$ Tables

Index