Tuning Tips and Techniques

ORACLG

Oracle

Press14

Oracle

Database

12c

Release

2

Performance

Tuning Tips

and

Techniques

Richard

J.

Niemiec

Mc Graw Hill Education

New York

_Chicago

San Francisco Athens London Madrid Mexico

_City

Acknowledgments

xxix

Introduction xxxvii

IntroductiontoOracle Database 12c R1 &R2New Features

(DBA

and

_Developer)

₁

Oracle Database12cR1 (12.1.0.1) 5 IncreasedSize Limitto32K forVARCHAR2 and NVARCHAR2 5

Partial Indexes ₅ InvisibleColumns 6

Multiple

Indexesonthe Same Column List 7

Fetch Firstx Rows ₈

Pluggable

Databases(PDBs) 9

Oracle Database Cloud Service(Databaseas a Service) 11 PDBLevel: MEMORYJJMITand MEMORY_MINIMUM (12cR2) 17

Change Compression

at _ImportTime ₁₈

Adaptive Query Optimization

18

PGA_AGGREGATE_LIMIT 19 ConcurrentExecution for UNION/UNION ALL 20 Invoker

_Rights

FunctionCan Be Results Cached 20 New DBMS_UTILITY.EXPAND_SQL_TEXT 20 Default for Columns Based on

Sequence

₂₀

Multiple

SSD Devicesfor Smart Flash Cache 20

ConcurrentCost-Based

_Optimizer

Statistics

_Gathering

21

Enhanced

_System

Statistics 21

Resource

_Manager

for

_{Runaway Queries}

22

Automatic Data

_Optimization

(ADO) 22 Global Index Maintenance:

_Drop

andTruncate Partition

_Operations

₂₃

ASM Disk

_Scrubbing

₂₃

Xli

Oracle Database 12c Release2 Performance

_{Tuning Tips}

and

_Techniques

Online

_Capability

Improvements 23

Data Guard Improvements 24

RMAN

_Improvements

24

Oracle Database 12cR1 _{(12.1.0.2) 25}

In-Memory

Database 25

Advanced Index

_Compression

28 Automatic

_Big

Table

_Caching

28

FDA

_Support

for Container Databases 28

Full Database

_Caching

28

JSON

_Support

28

FlPS 140 Parameterfor

_Encryption

28

PDB Subset

_Cloning

29

Rapid

Home

_{Provisioning—Creating}

"GoldImages" 29

Oracle Database 12cR2 (12.2) 29

Application Development

29

Enhanced Featuresto ReduceCostsand Issuesfor

_Migration

toOracle 30

Availability

30

BigData 33

Compression and

_Archiving

33

Oracle RAC and Grid Infrastructure 34

Security

34

New

_Background

Processes in 12c 34 Exadata—New with Exadata X6! 35 Version

_Comparison

Chart 36 New Features Review 41 References 42 2 Basic Index

_{Principles (Beginner Developer}

and

_Beginner

DBA) 43

Basic Index

_Concepts

45

Invisible Indexes 47

Multiple Types

of Indexeson the SameColumn(s) 50

Concatenated Indexes 53

Suppressing

Indexes 54

UsingtheNOT_EQUAL

_Operators:

_{<>, !=} 55

Using

IS NULLor IS NOT NULL 55

Using

LIKE 57

Using

Functions 58

Comparing

Mismatched Data

_Types

58

Selectivity

59

The

_Clustering

Factor 59

The

_{Binary Height}

60

Additional Details

_Concerning

BLEVEL and Index

_Height

62

Using Histograms

64

Fast Full Scans 65

Contents

_xiii

Types

of Indexes 67 B-Tree Indexes 68

Bitmap

Indexes 69 Hash Indexes 71

Index-Organized

Tables 72

Reverse

_Key

Indexes 73

Function-Based Indexes 73 Partitioned Indexes 74

New 12cR2 Featuresfor Partitioned Indexes 78

BitmapJoin Indexes 78

FastIndex

_Rebuilding

79

Rebuilding

Indexes Online 79

Tips

Review 80

References 82

3

_Pluggable

Databases,

Disk

_{Implementation}

_Methodology,

andASM(DBA) ... 83

Pluggable

Databases (New in Oracle 12c) 85

CDB or PDB Created

Objects

86

Creating

aPDB: _{Many Ways}to Do It 87

Great

_Pluggable

Database Commands 88

ALTERSYSTEM While in aPDB and Other NiceCommands 92

Using In-Memory(IM) with

_Pluggable

Databases 93 Other12cR2 Featureswith

_Pluggable

Databases 93 Subset

_Standby

(New in Oracle 12cR2) 94

Disk

_Arrays

94

UseDisk

_Arrays

to

_Improve

Performanceand

_Availability

94 How

_Many

DisksDo You Need? 95

What Are Some of the RAID Levels Available? 95

The Newer RAID 5 96

Solid-State Disks 97

ASM

_{Storage Management (Striping/Mirroring)}

97

Setup

and Maintenance of the Traditional

_Filesystem

98

WhatIsthe Cost? 98

Storing

Data and Index Files in

_Separate

Locations 99

Avoiding

I/O Disk Contention 99

The12c Heat_MapandAutomatic Data_Optimization(ADO) 101

12c I/O Performance

_Tracking

Views(Outliers) 102 Oracle

_{BigfileTablespaces}

103

ASM Introduction 104

Communication Across IT Roles 105

ASM Instances 106

ASM Initialization Parameters 107 ASM Installation in 12c 107

Srvctl Enhancements 115

xiv

Oracle Database 12c Release 2 Performance

_Tuning

_Tips

and

_Techniques

ASM Rebalance Enhancements 121 ASM FastMirror_Resync 123 ASM Filter Driver 124 ASMand

_Privileges

132 ASMand

_Multipathing

136

BigfileandASM

137

Avoiding

Disk Contention

_{by Using}

Partitions 137

Getting

More Information AboutPartitions 139

Other

_Types

of

_Partitioning

140 Partitioned Indexes (Local) 143 Partial Indexes 143

Global Index Maintenance:

_Drop

and Truncate Partition

_Operations

146

Other

_{Partitioning Options}

146

Index

_Partitioning

149

Exporting

Partitions 150

Eliminating

Fragmentation

(Only

IfNeeded—Careful!) 150

Using

the CorrectExtent Size 151

Avoiding

Chaining

by Setting

PCTFREE

_Correctly

151

Using

Automatic

_{Segment Space Management}

(ASSM) 152

Increasing

the

_Log

FileSizeand LOG_CHECKPOINTJNTERVAL for

_Speed

153

Determining

IfRedo

_Log

File Size IsaProblem 154

Determining

the Size of Your

_Log

Files and

_Checkpoint

Interval 155

Other

_Helpful

Redo

_Log

Commands 155

Storing Multiple

Control Files onDifferent Disks and Controllers 157 Other Disk I/OPrecautionsandTips 157

Issuesto Considerinthe

_Planning

_Stages

158

Tips

Review 159

References 161

4

_Tuning

the Database with Initialization Parameters (DBA) 163

When

_Upgrading

toOracle Database12c 165

Using

SEC_CASE_SENSITIVE_LOGON 166

Crucial Memory InitializationParametersfor Performance 166

PDB Level: MEMORY_LIMIT and MEMORY_MINlMUM 170

In-Memory

Database (INMEMORY_SIZE) 170

Changing

the Initialization Parameters Withouta Restart 176

Modifying

an Initialization Parameteratthe PDB Level 180

Insight

into the Initialization Parameters from Oracle Utilities 181

Viewing

the Initialization Parameters with

_{Enterprise Manager}

181

IncreasingPerformance

_by

_Tuningthe DB_CACHE_SIZE 182

Using V$DB_CACHE_ADVICEin

_Tuning

_{DB_CACHE_SIZE} 185

Monitoring

the

_V$SQLAREA

ViewtoFind Bad Queries 186

Setting

DB_BLOCK_SIZEtoReflectthe SizeofYour Data Reads 189

Contents _XV

Tuning

theSHARED_POOL_SIZEfor

_Optimal

Performance 191

Using

Stored Procedures for

_Optimal

Use of the Shared

_SQL

Area 191

Setting

the SHARED_POOL_SIZE

High Enough

to

_Fully

Use

theDB_CACHE_SIZE 193

Keeping

the Data

_Dictionary

Cache

_Objects

Cached 193

Keeping

the

_Library

Cache Reload Ratio at 0and theHit Ratio

Above95Percent 195

Using

AvailableMemorytoDetermineIf the SHARED_POOL_SIZE

IsSet

_Correctly

197

Using

theX$KSMSPTableto GetaDetailed Lookatthe Shared Pool 198 PointstoRemember About Cache Size 199 Waits Relatedto Initialization Parameters 200

Using

Oracle

_Multiple

BufferPools 201

Pools Relatedto DB_CACHE_SIZE and

_{Allocating Memory}

for Data 201

Modifying

the LRU

_Algorithm

202

Pools RelatedtoSHARED_POOL_SIZEand

Allocating Memory

for Statements 202

Tuning

PGA_AGGREGATE_TARGETand PGA_AGGREGATE_LIMIT 203

Modifying

the Size of Your SGAtoAvoid

_Paging

and

_Swapping

204

Understanding

the Oracle_Optimizer 204

How

_Optimization

Looksatthe Data 204

Creating

Enough

Dispatchers

205

Have

_Enough

_Open

Cursors(OPEN_CURSORS) 206

Don't Let Your DDL Statements Fail (DDL_LOCK_TIMEOUT) 206

Two

_Important

Exadata Initialization Parameters(Exadata

Only)

207

Top

25Initialization Parameters 207

Initialization Parametersoverthe Years 210

Finding

Undocumented Initialization Parameters 210

Understanding

the

_Typical

Server 211

Modeling

a

Typical

Server 212

Sizing

the Oracle

_Applications

Database 213

Tips

Review 218

References 220 5

_Tuning

with

_Enterprise

_Manager

Cloud Control (DBA and

_Developer)

221 Oracle_Enterprise

_Manager

Basics and

_Accessing

OEM via Oracle Cloud 223

Starting

with All

_Targets

and Other

_Groupings

227

Monitoring

and_Tuning

_Using

theOEMPerformanceMenu 229

Performance Tab:_Top

_Activity

229

Performance Tab:_SQL

_|

_{SQL Performance}

_Analyzer

230

Performance Tab: Real-TimeADDM 233

Performance Tab:_SQL

_|

AccessAdvisor 237

Performance Tab:

_{Manage Optimizer}

Statistics 237

Performance Tab:AWR

_|

AWR Administration 238 Performance Tab: ASH

_Analytics

241

XVI Oracle Database 12c Release2 Performance

_Tuning

_Tips

and

_Techniques

Monitoring

and

_{Tuning Using}

theOEMAdministration Menu 241 Database AdministrationTab:

_{Storage|Tablespaces}

242

DatabaseAdministrationTab:

_In-Memory

Central and

Initialization Parameters 244

Database Administration Tab: All Initialization Parameters 244

Database Administration Tab: Resource

_Manager

(Consumer

_Groups)

246

Monitoring

andTuning

Using

theOEM DatabaseorCluster DatabaseMenu 247

Database Tab:Job

_Activity

247 Cluster Database Tab:

_{Configuration!}

Database

_Topology

248

Monitoring

theHosts 248

Monitoring

the

_Application

Servers and Web

_Applications

250

Real

_{Application Testing}

(Database

Replay)

252

Summary

253

Tips

Review 253

References 254

6

_Using

_{EXPLAIN, TRACE,}

and

SQL

Plan

_{Management (Developer}

and

DBA)

255 TheOracleSQLTRACE

_Utility

256

Simple Steps

for SQLTRACE with a

Simple Query

257

The Sections ofaTRACE

Output

262

Digging

into theTKPROF

_Output

263

Using

DBMS_MONITOR 266

Setting

Trace Basedon Session IDand Serial Number 267

Setting

Trace Basedon Client Identifier 267

Setting

Traceforthe ServiceName/Module Name/Action Name 268 Enabled

_Tracing

Views 269 TRCSESS

_Multiple

Trace Files into One File 269

Using

EXPLAIN PLAN Alone 271

AnAdditional EXPLAIN

_Example

fora

Simple Query

273

EXPLAIN PLAN—Read It

_Top

to BottomorBottom to

_Top?

274

Tracing/Explaining

Problem Queriesin

_Developer

Products 279

Important

Columns inthe PLAN_TABLETable 280

Using

DBMS_XPLAN 282

Initialization Parameters for Undocumented TRACE 283

Using

Stored Outlines 284

Dropping

Stored Outlines 285

Using

SQL Plan_Management(SPM) and SPM

Example

285

SPM Terms 286

Using

SPM 287

Using

Fixed SQL Plan Baselines 291

Dropping

a Plan 292

Converting

from Stored OutlinestoSQLPlan

_Management

292

Adaptive

Plans(12cNewFeature)and SPM 294

TipsReview 301

Contents

_xvii

7 Basic Hint

_{Syntax (Developer}

and DBA) 305

Top Hints Used 307

Use Hints

_Sparingly

308

Fixthe

_Design

First 308

AvailableHintsand

_Groupings

309

ExecutionPath 309

AccessMethods 310

Query

Transformation Hints 310

Join

Operations

311

Parallel Execution 311

Other Hints 311

Specifying

a Hint 312

Specifying Multiple

Hints 313

When

_Using

an Alias, Hint the

Alias,

Notthe Table 314

TheHints 314

The Oracle Demo

_Sample

HRSchema 314 The FIRST_ROWSHint 315 The ALL_ROWS Hint 316

The FULL Hint 316

The INDEX Hint 317

The NOJNDEX Hint 319

The INDEXJOIN Hint 320

TheINDEX_COMBINEHint 320

TheINDEX_ASC Hint 321

TheINDEX_DESCHint 322

TheINDEX_FFS Hint 322 The ORDERED Hint 323 The LEADING Hint 324 The NO_EXPAND Hint 325 TheDRIVING_SITE Hint 325 TheUSE_MERGEHint 326

The USE_NL Hint 327

The USE_HASH Hint 328

The_{QB_NAME} Hint 329

The_{PUSH_SUBQ} Hint 330

The PARALLEL Hint 331

The_{NO_PARALLEL} Hint 332

ThePARALLELJNDEX Hint 333

TheAPPEND Hint 333 TheNOAPPEND Hint 334 The CACHE Hint 334 The NOCACHE Hint 335 TheRESULT_CACHEHint 335 The CURSOR_SHARING_EXACTHint 336

wiii

Oracle Database 12c Release2 Performance

_{Tuning Tips}

and

_Techniques

The INMEMORY and NOJNMEMORYandOther IM Hints 337

The _{USEJNVISIBLEJNDEXES}Hint 338

The CONTAINERS Hint 339

The

_{WITH_PLSQL}

Hint 339

Some Miscellaneous Hints and Notes 339

Undocumented Hints 341

Using

HintswithViews 342

Noteson HintsandStored Outlines (or SQL Plan Baselines) 343

Why

Isn't

_My

Hint

_Working?

343 HintsataGlance 343

Tips

Review 345

References 347

8

_Query

_{Tuning (Developer}

and

_Beginner

DBA) 349 Which Queries Do I Tune?

_{Querying V$SQLAREA}

and

_V$SQL

Views 351

Selecting

fromtheV$SQLAREAView toFind the WorstQueries 351

Selecting

from theV$SQLViewto Find the WorstQueries 353 Oracle 12c Viewsfor

_Locating

Resource-Intensive Sessions and

_Queries

354

Selecting

fromV$SESSMETRICto Find Current Resource-Intensive Sessions .. . 354

Viewing

AvailableAWR

_Snapshots

355

Selecting

from the_{DBA_HIST_SQLSTAT}Viewto Find the Worst_Queries 355 When Should I UseanIndex? 356

Selecting

Query

Textfromthe_{DBA_HIST_SQLTEXT}View 357

Selecting Query

EXPLAIN PLAN from the DBA_HIST_SQL_PLANView 358 What If I

_Forget

the Index? 359

Creating

an Index 360

Invisible Index 360

Checking

the Indexon aTable 360 Isthe Column

_Properly

Indexed? 361

WhatIfI Createa Bad Index? 362

Exercising

CautionWhen

_Dropping

anIndex 364

Indexing

the Columns Used in the SELECT and WHERE 365

Using

the Fast Full Scan 367

Making

the_Query

_"Magically"

Faster 368

Caching

aTablein Memory 369

Using

the Result Cache 371

Choosing

Among

Multiple

Indexes(Use theMostSelective) 372

TheIndex

_Merge

373

Indexes That Can Get

_Suppressed

375 Function-Based Indexes 377 Virtual Columns 378 The "Curious" OR 379

Using

the EXISTS Function and the Nested

_Subquery

380

ThatTable Is

_Actually

aView! 381

Contents _XIX

Tuning Changes

inOracle Database 12c 382

Oracle12c

_Adaptive

_{Query Optimization} 383

Adaptive

Statistics 389

Oracle 12c

_Changes

in Statistics

_Gathering

andTwo New

_Histograms

394

Oracle12c

_Changes

in _SQLPlan

_Management

395

Oracle AutomaticSQL

Tuning

397

Ensuring

the

_Tuning

User Has Accesstothe APIs 398

Creating

the

_Tuning

Task 398

Making

Sure the Task Can Be Seen in the Advisor

_Log

399

Executing

the

_{SQL Tuning}

Task 399

Checking

Status of the

_Tuning

Task 399

Displaying

the

_{SQL Tuning}

Advisor Report 399

Reviewing

theReport Output 400

Tuning SQL

Statements

_{Automatically Using}

_{SQL Tuning}Advisor 402

Enabling

Automatic _SQL

_Tuning

Advisor 402

Configuring

Automatic _SQL

_Tuning

Advisor 402

Viewing

Automatic_SQL

_Tuning

Results 403

Using

SQLPerformance

_Analyzer

(SPA) 407

Tips

Review 412 References 414 9 Table

Joins

and Other Advanced

_Tuning

(Advanced

DBAand

_Developer)

... 415

Database

_{Replay (capture/replay)}

417

Set

_Up

Source Database for Database

_{Replay Capture}

418

Prepare

to

_Capture

Workload 418

Capturethe Workload 418

Preparethe Workload for

_Replay

419

Processthe Workload for

_Replay

420

Prepare

to

Replay

the Workload 420

ExecutetheWorkload

_Replay

421

SQLPerformance

_Analyzer

423

Createa SQL

Tuning

Set 423

Createan

Analysis

Task 424

Execute

_Analysis

Task 424

Query

SQL Performance

Analyzer

Advisor Tasks 425

Cancel an

Executing

SQLPerformance

Analyzer Analysis

Task 425

RemoveSQL Performance

Analyzer

Analysis

Task 426

Determine ActiveSQL

Tuning

Sets 426

RemoveSQL

Tuning

Set 426

Drop

SQL

Tuning

Set 427

JoinMethods 427

NESTED LOOPSJoins 428

SORT-MERGEJoins 428 CLUSTER Joins 429 HASH Joins 430

XX Oracle Database 12c Release 2 Performance

Tuning Tips

and

Techniques

TableJoinInitialization Parameters 434

SORT-MERGE andHASHJoin Parameters 434 ATwo-Table Join:

Equal-Sized

Tables(Cost-Based) 435 ATwo-Table INDEXED_Join:

_Equal-Sized

Tables(Cost-Based) 439

Forcing

a

Specific

Join Method 443

Eliminating

Join Records (Candidate Rows) in MultitableJoins 445 A Two-TableJoin Betweena

Large

and Small Table 447

Three-TableJoins: Notas Much Fun 450

BitmapJoin Indexes 452

Bitmap

Indexes 452

Bitmap

Join Index 454

BestUsesforthe

_Bitmap

_Join Index 455

Third-Party

Product

_Tuning

458

Example

1 458

Example

2 459

Example

3 460

TuningDistributed

_Queries

462

WhenYou Have

_Everything

Tuned 464

Miscellaneous

_{Tuning Snippets}

464

ExternalTables 465

Snapshot

Too Old:

_{Developer Coding}

Issue 469

Set Eventto

_{Dump Every}

Wait 469

14 Hours to 30Seconds with the EXISTS

_Operator

471

Tuningatthe Block Level (Advanced) 473

Key

Sections ofa Block

Dump

476

ABrief Look atan Index Block

Dump

483

Tuning Using

Simple

Mathematical

_Techniques

485

Traditional Mathematical

_Analysis

486

Seven-Step Methodology

486

DerivingPerformance

_Equations

487

Pattern

_{Interpretation}

493

Mathematical

_Techniques

Conclusions 498

Tips

Review 499 References 500

10

_Using

_PL/SQL

to Enhance Performance

_(Developer

and

DBA)

503

Leveragethe

_PL/SQL

Function Result Cacheto

_Improve

Performance

(Improved

in 12c) 505

DefinePL/SQL

Subprograms

in a

SQL

Statement(Newin 12c) 515

Reference Sequences

Directly

in _PL/SQL

_Expressions

517

Identity

Columns (New in 12c) 519

Max Size Increaseto 32K forVARCHAR2,NVARCHAR2, and RAW Data

_Types

(Newin 12c) 521 Allow

_{Binding PL/SQL-Only}

Data

_Types

toSQLStatements (New in 12c) 522

Contents

xxi

Simplify

Loopswith the CONTINUE Statement 525

Leverage

Compile-Time

Warnings

toCatch

_Programming

Mistakes

_(Improved

in12c) ... 528

IncreasePerformancewith Native

_Compilation

530

Maximize Performancewiththe

_{Optimizing Compiler}

533

UseDBMS_APPLICATION_INFOfor Real-Time

_Monitoring

539

Log Timing

Information in aDatabase Table 541

Reduce PL/SQL

Program

Unit Iterations and IterationTime 544

Use ROWID for Iterative

_Processing

546

StandardizeonData_Types, IF Statement

_Order,

and PLSJNTEGER 548 Ensure theSame Data_Types in

_{Comparison Operations}

548 OrderIFConditions Based onthe

Frequency

of the Condition 550

Usethe PLSJNTEGERPL/SQLData

_Type

for

_{Integer Operations}

551

Reduce the Calls toSYSDATE 552

Reduce theUseof the MOD Function 553

Improve

Shared Pool Use

_{by Pinning}

PL/SQL

Objects

555

Pinning

(Caching)

PL/SQL

Object

Statements into

_Memory

555

Pinning

All

_Packages

556

Identify

PL/SQL

Objects

That Needto Be Pinned 557

Useand

_Modify

_{DBMS_SHARED_POOLSIZES} 557

Find

_{Large Objects}

558

Get Detailed

_Object

Information from DBA_OBJECT_SIZE 558 Get

_{Contiguous Space Currently}

in the Shared Pool 559 Find Invalid

_Objects

559 Find Disabled

_Triggers

561

Use

_PL/SQL

Associative_Arraysfor FastReference Table

_Lookups

562

Find andTunethe_SQLWhen

_Objects

AreUsed 565

ConsiderTime _ComponentWhen

_Working

with DATE Data

_Types

568

Use

_PL/SQL

to Tune

_PL/SQL

570

Understand the

_Implications

of_PL/SQL

_Object

Location 571 UseRollback

_Segments

to

_{Open Large}

Cursors 572

Active Transaction

_Management:

Process

_{Large Quantities}

ofData 573

Use

_Temporary

DatabaseTables for Increased Performance 574

LimittheUseof

_Dynamic

_{SQL 574}

Use

_Pipelined

Table FunctionstoBuild

_Complex

ResultSets 575 Leave Those

_Debugging

Commands Alone! 580

The"Look and Feel":Justfor the

_Beginners

586

PL/SQL

Example

587

CreateaProcedure

Example

587

Execute the Procedure from

_{PL/SQL Example}

588

CreateaFunction

Example

588

Execute the GET_CUST_NAME FunctionfromSQL

Example

588

Createa

Package Example

588

DatabaseTrigger

Example Using

PL/SQL 589

Tips

Review ₅₈₉ References ₅₉₂

XXH Oracle

Database

12c Release2 Performance

_{Tuning Tips}

and

_Techniques

11 Oracle

_Cloud,

Exadata,

_Tuning

RAC,

and

_Using

Parallel Features 593 The March tothe Cloud (PastandPresent) 596 The Oracle Cloud 599 Exadata Database Machine 609

Exadata

_Terminology

and the Basics 609

Exadata Statistics 610

Exadata

_{Storage Expansion}

Rack

_Briefly

612

Smart Scans 614

Flash Cache 614

Storage

Indexes 617

Hybrid

Columnar

_Compression

618

I/O Resource

_Management

621

Use All Oracle

_{Security Advantages}

with Exadata 622

Best Practices 622

Summary:

Exadata =

Paradigm

Shift! 623

Oracle Database

_Appliance

(ODA) 624

SuperCluster

Usingthe M7 SPARC

_Chip

624

Other OracleHardwaretoConsider 625 Oracle

_Big

Data

_Appliance

X6-2 625

ZFS

_Storage

Servers 625

StorageTek

Modular

_{Library System}

625

Parallel Databases 626

Real

_Application

Clusters (RAC) 626

Oracle RAC Architecture 627

Internal

_Workings

of theOracle RAC

_System

629

RAC Performance

_Tuning

Overview 632

RAC Cluster Interconnect Performance 633

Finding

RAC Wait Events—Sessions

_Waiting

633

RAC Wait Events and Interconnect Statistics 635

Cluster Interconnect

_{Tuning—Hardware}

Tier 641

Basic_Conceptsof Parallel Operations 645

Basic

_Concepts

ofParallel

_Operations

645

Parallel DMLandDDLStatements and

_Operations

647

Managing

Parallel Server Resources and Parallel Statement

_Queuing

648 Parallelism and Partitions 649

Inter- and

_{Intra-operation}

Parallelization 649

Examples

of

_Using

Inter- and

_{Intra-operations}

(PARALLEL and NO_PARALLEL Hints) 650

Creating

Table and Index

_Examples

_Using

Parallel

_Operations

652

Monitoring

Parallel

_Operations

viathe V$Views 653

Using

EXPLAIN PLAN andAUTOTRACEonParallel

Operations

655

Using

the SET AUTOTRACE ON/OFF Command 658

Contents

_xxiii

Parallel

_Loading

662

Optimizing

Parallel

_Operations

in RAC 664

Objectives

of Parallel

_Operations

664

RAC Parallel

_Usage

Models 664

Parallel InitializationParameters 665

V$Viewsfor

_Viewing

ParallelStatistics 665

CreateTableAs 665

Parallel Index Builds 665

Performance Considerations and

_Summary

666

Other Parallel Notes 666 Oracle Documentation Is Online 666

Tips

Review 667 References 668 12 The

V$

Views

_(Developer

and

_DBA)

671

Creating

and

_Granting

AccesstoV$Views 673

Obtaining

aCountand

Listing

ofAllV$Views 676

Getting

a

Listing

for theX$

Scripts

ThatMake

Up

theV$Views 677

Examining

the

_Underlying

_Objects

That Make

_Up

the DBA_Views 678

Using

Helpful

V$

_Scripts

680

Basic Database Information 681 Basic Automatic Workload

_Repository

(AWR) Information 682 Basic

_Licensing

Information 683 Database

_Options

Installed in Your Database 683

Summary

of

_Memory

Allocated (V$SGA) 684

Querying

V$IM_SEGMENTSAfter

_Setting

the_{INMEMORY_SIZE} 685

Automatic

_{Memory Management}

and MEMORY_TARGET 687

Detailed _MemoryAllocated(V$SGASTAT) 688

Detailed _MemoryAllocated(V$SGASTAT) foraPDBvs. Root CDB 689

Finding spfile.ora/init.ora

SettingsinV$PARAMETER 690

Modifying

anInitialization ParameteratPDBLevel 691

Determining

IfData Is in

_Memory

(V$SYSSTAT &V$SYSMETRIC) 692

Determining Memory

fortheData

_Dictionary

(V$ROWCACHE) 693

Determining Memory

for_{the Shared SQL}and

_PL/SQL

(V$LIBRARYCACHE) 694

Querying

V$CONTAINERSandV$PDBSfor Container Information 696

Querying

V$CONTAINERSWhen

_{Using Pluggable}

Databases 696

Querying

V$PDBSfor

_Pluggable

Database Information 697

Using

the Result Cache 698

Identifying

PL/SQL

Objects

That Needto BeKept (Pinned) 700

Finding

ProblemQueries

by Monitoring

V$SESSION_LONGOPS 701

Finding

ProblemQueries

by Querying V$SQLAREA

703

Finding

Out What Users Are

_Doing

and Which Resources

_They

Are

_Using

704

Finding

Out Which

_Objects

aUser Is

_Accessing

₇₀₅

XXIV Oracle Database 12c Release2 Performance

_{Tuning Tips}

and

_Techniques

Using

Indexes 706

Identifying Locking

Issues 708

Killing

the Problem Session 710

Finding

Userswith

_Multiple

Sessions 711

Querying

forCurrentProfiles 712

Finding

DiskI/O Issues 713

Checking

for

_Privileges

and Roles 715

Wait Events V$ Views 718

Some of the

_Major

V$ View

_Categories

721

Tips

Review 728 References 730 13 The X$ Tables and Internals

_Topics

(Advanced

DBA) 731

Introducing

theX$Tables 732

Misconceptions

About theX$Tables 734

Granting

AccesstoViewthe X$ Tables 734

Creating

V$Views and X$ Tables in 12c 735

TheX$Tables

_Comprising

theV$Views 737

Obtaining

aListof All the X$Tables in 12c 738

Obtaining

aListof All theX$ Indexes in12c 740

Using

Hints with X$Tables and Indexes 741

Monitoring Space

Allocationsinthe Shared Pool 742

Creating

Queriesto Monitorthe Shared Pool 743

ORA-04031 Errors 744

Large

Allocations

_Causing

Contention 745

Shared Pool

_{Fragmentation}

745

Low Free _Memory in Shared and Java Pools 747

Library

CacheMemory Use 747

High

Numberof Hard Parses 750

Mutex/LatchWaitsand/or

_Sleeps

751

MiscellaneousX$ TableNotes 752

Obtaining

Information About Redo

_Log

Files 753

Setting

Initialization Parameters 753

Case 1 754

Case 2 755

Case3 755

Case4 755

Case 5 755

Exploring

Buffer Cache/Data Block Details 757

Buffer Statuses 758

Segments

Occupying

Block Buffers 760

Hot Data Blocks and the Causes of Latch Contention and Wait Events 761

Obtaining

Database- and

_{Instance-Specific}

Information 766

Contents _XXV Oracle Internals

_Topics

768

Traces 768 DBMS_TRACE

Package

772 Events 773

Dumps

774 ORADEBUG 775 trcsess

Utility

777

Reading

theTraceFile 778

Wait Information and

_Response

Time 781 RecursiveCalls 782 Module Info 782 Commit 783

Unmap

783 Bind Variables 783 Errors 784

Some Common X$Table

_Groups

784 SomeCommonX$Table andNon-V$ Fixed View Associations 801

CommonX$Table Joins 803

X$Table

_Naming

Conventions_(MyFavorite Section of This Book!) 805 X$Table

_Naming

Conventionswith CONJD,and INMEMORY 812 FutureVersion _Impactin 12cR2 813

Tips

Review 813

References 814

Using Statspack

and

the AWR

_Report

toTune

_{Waits, Latches,}

andMutexes ... 815

What's New in 12cR2(12.2)

Statspack

and theAWR

_Report

817

Installing

Statspack

818

Security

of the PERFSTAT Account 818

Post-Installation 819

Gathering

Statistics 820

Running

the Statistics

_Report

822

The Automatic Workload

_Repository

(AWR) and the AWR

_Report

824

Manually Managing

the AWR 825

AWRAutomated

_Snapshots

826

AWR

_{Snapshot Reports}

826

Run theAWR_Reportin Oracle

_{Enterprise Manager}

Cloud Control 828

Interpretingthe

_Statspack

andAWR _Report

_Output

831

The Header Information and Cache Sizes 832

The Load Profile 833

Instance

_Efficiency

835

Shared Pool Statistics 838

Top

Wait Events 838

Oracle

_Bugs

851 The Life ofanOracle ShadowProcess 852

XXvi

Oracle Database 12c Release2 Performance

_{Tuning Tips}

and

_Techniques

RAC Wait Events and Interconnect Statistics 852

Top

SQLStatements 853 Instance

_Activity

Statistics 856

Tablespace

and File I/O Statistics 861

SegmentStatistics 864

Additional

_Memory

Statistics 865 UNDO Statistics 871 Latch andMutex Statistics 872

Tuning

and

_Viewing

atthe Block Level (Advanced) 883

Dictionary

and

_Library

Cache Statistics 886

SGA

_Memory

Statistics 888

Nondefault Initialization Parameters 889

Top

15

_Things

toLook forin AWR

_Report

and

_Statspack

_Output

890

Managing

the

_Statspack

Data 892

Upgrading Statspack

892

Deinstalling Statspack

893

Quick

Notesonthe New ADDM

Report

893

Scripts

in 12cR2 898

Tips

Review 900

References 902

15

_Performing

a

Quick

_System

Review

(DBA)

905

Total Performance Index (TPI) 906

Education Performance Index (EPI) 907

System

Performance Index(SPI) 909

Memory Performance Index (MPI) 912 Top25 _"MemoryAbuser"StatementsTuned 912

Top

10

_"Memory

Abusers"asa Percentof AllStatements 914

Buffer Cache HitRatio 914

Dictionary

CacheHit Ratio 916

Library

Cache Hit Ratio 91 7

PGA

_Memory

Sort Ratio 918

Percentage

of Data Buffers Still Free 919

Using

the Result Cache

_Effectively

920

Pinning/Caching

Objects

921 Disk PerformanceIndex(DPI) 922

Top

25 "Disk-Read Abuser" Statements Tuned 922

Top

10 Disk-Read Abusersas

Percentage

of AllStatements 923

Tables/Indexes

_Separated

or

Using

ASM 925

Mission-Critical Table

_Management

925

Key

Oracle Files

_Separated

926

Automatic Undo

_Management

926

Contents _XXVII Total Performance Index(TPI) 930

Overall

_System

Review

_Example

930

Rating System

931

Example System

Review

_{Rating Categories}

931

Items

_Requiring

Immediate Action 933

Other Items

_Requiring

Action 934

System

Information List 934

Memory-Related

Values 934

Disk-Related Values 935 CPU-Related Values 935

Backup-

and

_{Recovery-Related}

Information 936

Naming

Conventionsand/or Standards and Security Information

_Questions

.. . 937

DBA

_Knowledge

_Rating

937

Other ItemstoConsiderin Your TPI and

_System

Review 938

Tips

Review 939

References 940

16 Monitor the

_{System Using}

Unix Utilities

(DBA)

941 Unix/Linux Utilities 942

Using

thesarCommandtoMonitor CPU

_Usage

943

sar -u(Checkfor CPU

Bogged

Down) 943

Thesar-d Command(Find I/O Problems) 944

Thesar-b Command(Checkthe BufferCache) 947

Thesar_{-q Command}(Checkthe Run_Queue and

_{Swap Queue Lengths)}

948

Usingthesarand vmstatCommandstoMonitor

_{Paging/Swapping}

948

Using

sar_-pto _{Report Paging}Activities 949

Using

sar -wto Report Swappingand

Switching

Activities 949

Using

sar -rto ReportFree_Memory and Free_Swap 950

Using

sar_-gto

_{Report Paging}

Activities 950

Using

sar_-wpgrto

_Report

on

Memory

Resources 951

Finding

theWorst Useron the

System Using

the_topCommand 954

Monitoring

Tools 955

Using

the

_uptime

CommandtoMonitor CPU Load 955

Using

the_mpstatCommandto

Identify

CPU Bottlenecks 956

Combining

ps with Selected V$ Views 957

CPU/Memory Monitoring

Tool (Task

Manager)

onWindows 959

Using

the iostat Commandto

Identify

Disk I/O Bottlenecks 959

Using

iostat-d for Disk Drives sd15, sd16, sd17, and sd18 960

Using

iostat-D 960

Using

iostat-x 960

Combining

iostat-xwith

Logic

ina Shell

Script

961

Using

the

_ipcs

CommandtoDetermine Shared

_Memory

962

XXVlii

Oracle Database 12c Release2 Performance

_{Tuning Tips}

and

_Techniques

Monitoring

Disk Free

_Space

₉₆₄

The dt Command ₉₆₅ TheduCommand 966

Monitoring

Network Performance with netstat 966

Modifying

the

_{Configuration}

Information File 967

Other Factors That Affect Performance ₉₆₇ Other Sources to_Improve Performance ₉₆₉

Tips

Review ₉₆₉

References ₉₇₀ A

_Key

_{Initialization Parameters (DBA) 971}

Obsoleted/Desupported

Initialization Parameters 972

Deprecated

Initialization Parameters 973

Top

25 Initialization Parameters 974

Top

20 Initialization Parameters Notto

_Forget

₉₇₇

Top 13 Undocumented Initialization Parameters (As I See It) 978

Bonus 11 Undocumented Initialization Parameters 981

Listing

of Documented Initialization Parameters

_{(V$PARAMETER)}

982

Listing

of Undocumented Initialization Parameters

_{(X$KSPPI/X$KSPPCV)}

1009

Additional Oracle

_Applications

Notes 1009

Concurrent

_Managers

1009

Applications—Finding Module-Specific

Patches 1011

Diagnostics

DataCollection: EBS

_Analyzers

1011

WebServer

_Tuning

₁₀₁₃

Timeouts ₁₀₁₄

Database Initialization ParameterSizing 1016

Top

10 Reasons NottoWriteaBook 1017

Tips

Review 1017

References 1018

B The

V$

Views (DBAand

_Developer)

1019

CreationofV$ and GV$ Views and X$Tables 1020

A Listof Oracle 12c(12.2.0.0.1) GV$Views 1021

A ListofOracle 12c(12.2.0.0.1) V$Views 1021

Oracle 12c

_Scripts

for theX$Tables Used to CreatetheV$Views 1029 C The

X$

Tables (DBA) 1039 Oracle 12cR2 X$Tables Ordered

_by

Name 1040

Oracle 12cR2 X$ Indexes 1048

Oracle 12cR2V$Views Cross-ReferencedtotheX$Tables 1048