Oracle Tuning: The Definitive Reference

Description

Incorporating the principles of artificial intelligence, Oracle10g has developed a sophisticated mechanism for capturing and tracking database performance over time periods. This new complexity has introduced dozens of new v$ and DBA views, plus dozens of Automatic Workload Repository (AWR) tables.

The AWR and its interaction with the Automatic Database Diagnostic Monitor (ADDM) is a revolution in database tuning. By understanding the internal workings of the AWR tables, the senior DBA can develop time-series tuning models to predict upcoming outages and dynamically change the instance to accommodate the impending resource changes.

This is not a book for beginners. Targeted at the senior Oracle DBA, this book dives deep into the internals of the v$ views, the AWR table structures and the new DBA history views. Packed with ready-to-run scripts, you can quickly monitor and identify the most challenging performance issues.

 

Table of Contents

Chapter 1:  Introduction to Oracle Tuning       7

Oracle Tuning 7

Reactive Oracle Tuning          8

Proactive Oracle Tuning         9

Inside AWR    10

Poor Design and Poor System Performance    11

The Proactive Oracle Tuning Hierarchy         12

External Hardware Performance Review       14

Finding Database Bottlenecks 15

Disk Constrained Database     16

CPU Constrained Database     17

Network Constrained Database           17

Using Hardware to Correct a Sub-Optimal Oracle Database  19

Oracle Instance Tuning            19

Dynamic Instance Parameters  20

Static Instance Parameters       20

Statistics for the Oracle SQL Optimizer          21

Oracle Object Tuning  22

Oracle SQL Tuning     23

Emergency Oracle Tuning Support      24

Fix Missing CBO Statistics     25

Repair CBO Statistics 25

Set Missing Initialization Parameters  26

Adding Missing Indexes          27

Change CBO Parameters         27

Employ cursor_sharing=force 28

Implement the KEEP Pool       29

Employ Materialized Views   30

Implement Bitmap Indexes      31

Adding Freelists          32

Summary of Silver Bullet Tuning Techniques 33

Conclusion      34
 

Chapter 2:  Time-Series Oracle Tuning          35
 

Introduction to Time Series Analysis  35

Time-Series Tuning Guidelines          36

Measuring Behavior over Short Periods         37

Rules for adjusting shared_pool_size 63

Sizing the Shared Pool with the New Advisory Utility           66

Rules for adjusting pga_aggregate_target        67

Rules for Adjusting the Data Buffer Sizes       68

Scheduling an SGA Reconfiguration   69

Trend-based Oracle Reconfiguration  70

When to Trigger a Dynamic Reconfiguration  71

Approaches to Self-tuning Oracle Databases  73

Tuning a constantly changing database            74

Can Oracle possess psychic abilities?            75

Capturing time-series metrics 75

AWR Licensing Options         76

Tracking Oracle Option Usage            77

Customized AWR Tuning Reports      79

Exception Reporting with OEM          81

Exception Reporting with the AWR    82

Exception reporting with dba_hist_filestatxs  83

Trend identification with the AWR     86

Correlation analysis reports with the AWR and ASH views  91

Conclusion      99

References      101
 

Chapter 3:  Oracle10g Automated Workload Structures         102

The Many Faces of Oracle10g            102

The AWR data collection mechanism 104

Customizing AWR Scripts for Proactive Tuning         105

The Mysterious AWR Performance Tables     106

AWR vs. STATSPACK          107

Inside the AWR Tables           110

The Oracle10g Wait Event Tables      111

A Kick in the ASH      112

Inside the dba_hist Views       114

Conclusion      116
 

Chapter 4:  Investigating the dba_hist Views  117

Know the History        117

Access Paths to AWR data      118

Inside the dba_hist Data Dictionary Views     120

Database Wait Events in the dba_hist Views  121

dba_hist_system_event            124

dba_hist_event_name  127

dba_hist_bg_event_summary  127

dba_hist_waitstat        130

dba_hist_enqueue_stat 132

Metric dba_hist Views            135

dba_hist_filemetric_history    136

dba_hist_sessmetric_history   137

dba_hist_sysmetric_history     138

dba_hist_sysmetric_summary  139

dba_hist_waitclassmet_history           141

Time Model Statistics dba_hist Views           141

dba_hist_sys_time_model       142

System statistics          145

dba_hist_sysstat          145

dba_hist_latch 147

dba_hist_latch_misses_summary        149

dba_hist_librarycache 152

dba_hist_rowcache_summary 154

dba_hist_buffer_pool_stat       157

Operating System Statistics in AWR   161

SQL Statistics 163

The dba_hist_sqlstat View      165

Segment Statistics       170

Datafile I/O Statistics  173

Conclusion      179

References      181
 

Chapter 5:  AWR vs. STATSPACK   182

Why Such a Difference?          182

Files Delivered with STATSPACK    183

STATSPACK and AWR Statistics Comparison         185

Statistic Management in AWR and STATSPACK      187

The snap_level Parameter       190

session_id       191

num_sql           191

STATSPACK Collection Thresholds 191

Conclusion      200

Chapter 6:  Inside Oracle10g v$ Views          201

The Secret World of the v$ Views      201

Changes in Wait Event v$ Views        202

New Active Session History v$ View            207

New Time Model v$ Views    213

New Database Metric v$ Views         215

Changes to SQL Related v$ Views     221

Tips for v$data buffer contents           223

Conclusion      223
 

Chapter 7:  Understanding the Oracle 10g Metrics     225

Inside Oracle10g Metrics        225

The v$metric Tables   227

Database Workload Metrics   229

Database Wait Metrics            233

Datafile Metrics          236

Database Service Metrics       237

Conclusion      238
 

Chapter 8:  Oracle10g DBMS Tuning Packages         240

Packaging Oracle10g Tuning   240

The dbms_workload_repository Package       241

Creating an AWR Report        245

The dbms_advisor Package     247

The ADDM Advisor   248

Working with the SQLAccess Advisor           257

Using the quick_tune option    264

Inside the dbms_sqltune Package        265

Conclusion      272

Chapter 9:  The AWR Time Model Approach            274

Time Model Tuning for Oracle           274

Finding the Cause of Buffer Busy Waits          284

Conclusion      291

Chapter 10:  Reading an AWR or STATSPACK Report        293

Listening to the Database        293

Generating the AWR Report   294

Reading the AWR Report        295

Report Summary          299

Cache Sizes     300

Load Profile    300

Instance Efficiency Percentage            302

Top 5 Timed Events Section   305

Wait Events     306

Time Model Statistics 308

Operating System Statistics     309

Service Statistics        309

Top SQL         310

Instance Activity Section         312

I/O Reports Section     313

Advisory Section        316

Buffer Pool Advisory  316

Buffer Wait Statistics Section 317

Enqueue Activity Section        318

Undo Segment Summary Section         318

Undo Segment Stats Section    319

Latch Statistics Section           319

Segment Statistics Section       320

Dictionary Cache Stats Section           321

Library Cache Activity Section           321

SGA Memory Summary Section          322

init.ora Parameters Section     323

Conclusion      324
 

Chapter 11:  Predictive Models with AWR    325

Predicting the Future with AWR         325

Exception Reporting with the AWR    329

Exception reporting with dba_hist_filestatxs  331

General trend identification with the AWR     334

Correlation analysis with AWR and ASH       337

Conclusion      341
 

Chapter 12:  Server & Network Tuning with AWR    343

Oracle Server Tuning  343

Outside the Oracle Instance     344

Oracle Server Bottlenecks      345

Disk I/O and Oracle    346

Moore’s Law  348

Server RAM and Oracle         352

Tracking External Server Metrics with AWR 356

Oracle and the 64-bit server technology         356

The New Age of Oracle Server Consolidation           359

Enterprise Manager for Server & Environment           364

Server Metrics and SQL Execution     371

CPU Based Optimizer Costing            372

I/O Costing      372

Network Tuning          373

The tcp.nodelay parameter      376

The automatic_ipc parameter  376

The SDU and TDU parameters            377

Conclusion      378
 

Chapter 13:  Disk Tuning with Oracle 379

Monitoring Disk Performance 379

Inside Oracle Disk Architecture          380

Disk Architectures of the 21st Century            382

RAID Technology       385

Oracle and Direct I/O 388

Checking the Server Direct I/O Option           389

Enabling Direct I/O with Kernel Parameters  389

Direct I/O for Windows          389

Direct I/O for IBM AIX          389

Direct I/O for Linux    390

Direct I/O for Sun Solaris       390

Direct I/O for Veritas  391

Oracle Blocksize and Disk I/O           391

Oracle Blocksize & Index I/O 392

Not all Indexes are used in Range Scans         394

Using Oracle Multiple Blocksizes      400

Improving SQL execution plans          403

Real World Applications of multiple blocksizes        404

Reducing disk I/O with SSD   408

Oracle Disk Monitoring          409

Examining Real-time Disk Statistics   411

Examining Global I/O 414

Locating Hot I/O Objects        423

Tracking I/O for specific Tables         424

Find the Current I/O Session Bandits  436

Measuring Disk I/O Speed      443

Analyzing real time I/O waits 445

Time series I/O Wait Analysis            449

Time Series Monitoring of the Data Buffers   454

Monitoring Disk I/O with AWR          455

Conclusion      464

 

Chapter 14:  Oracle Instance Tuning   466

Semper Vigilans          466

Instance Tuning comes first!    467

Instance Configuration for High Performance 468

OS kernel parameters  469

Server Settings for Windows Servers 469

Kernel setting for UNIX and Linux servers     470

Oracle Parameter Tuning        471

SQL Optimizer Parameters     474

Data Buffer Cache Hidden Parameters            474

Instance Wait Tuning   476

Tuning the Oracle10g Data Buffer Pools         480

The Problem of Duplicitous RAM Caches      481

Why is Oracle Logical I/O so Slow?  482

Data Block Caching in the SGA          484

Full Table Caching in Oracle10g        486

Oracle Data Buffer Metrics     487

Using AWR for buffer pool statistics  489

Oracle’s Seven Data Buffer Hit Ratios           495

Viewing Information about SGA Performance            497

AMM and Oracle Instance Tuning      501

Plotting the Data Buffer Hit Ratio by Day of the Week           507

Internals of the Oracle Data Buffers    511

Finding Hot Blocks inside the Oracle Data Buffers    512

Viewing the Data Buffer Contents       513

The Downside of Mega Data Buffers  522

Allocating Oracle Objects into Multiple RAM data Buffers   524

Sizing the KEEP Pool 529

Automating KEEP Pool Assignment    532

Tuning the RECYCLE Pool     537

Small block size          543

Larger block size         543

Finding Baselines        545

Learning Instance Tuning from Performance Benchmarks       547

Conclusion      549

Chapter 15:  SQL Tuning        551

Understanding SQL Tuning     551

Optimizing Oracle SQL Execution      552

Goals of SQL Tuning  554

Remove unnecessary large-table full table scans        555

Cache small-table full table scans       556

Verify optimal index usage      556

Verify optimal JOIN techniques          557

Tuning by Simplifying SQL Syntax     557

Using the WITH clause to simplify complex SQL       559

Tuning SQL with Temporary Tables   564

Oracle SQL Performance Parameters 564

Using optimizer_index_cost_adj         566

Setting the SQL Optimizer Cost Model           568

Turning on CPU Costing          570

Turning on I/O Costing            570

Notes on Bug 2820066:           571

Bi-modal system configuration            571

Statistics and SQL Optimization         572

Managing Schema Statistics with dbms_stats  573

Schema Statistics Management            578

External Costing with the Optimizer   579

Tuning SQL with Histograms  580

Optimal table join order          580

Index skew      581

Inside Oracle10g Dynamic Sampling  584

How is join cardinality estimated?     590

Enabling Dynamic Sampling   591

Sampling Table Scans 594

Tuning SQL access with clustering_factor      596

Rules for Oracle Indexing       596

Faster SQL with Database Reorganizations    600

Oracle Indexes – Is Maintenance Required?   602

When Should Indexes be rebuilt?        604

Locating Tables and Indexes for the KEEP Pool         608

Interrogating SQL execution Plans      609

Identifying Problem SQL         621

Find the Problem Sessions      622

Identify the Resource-Intensive SQL   627

Oracle tuning with hints          632

AWR and SQL Tuning 636

The dba_hist_sqlstat Table     639

The dba_hist_sql_plan Table  644

Viewing table and index access with AWR    649

Tracking SQL nested loop joins          650

Counting index usage inside SQL        657

Tracking full scan access with AWR  663

Interrogating table join methods          675

Supersizing the PGA for large sorts and hash joins     676

Hidden parameters for Oracle PGA regions   678

Super-sizing the PGA  679

Important caveats in PGA management           681

Oracle10g SQL Tuning           681

The SQL Tuning Advisor        682

Using SQL Tuning Advisor Session    683

Oracle10g Automatic Database Diagnostics Management      685

Oracle SQL Tuning Silver Bullets      688

Using Function-based Indexes (FBI)   688

Using Temporary Tables         690

Fixing CBO Statistics  691

Changing CBO SQL Optimizer Parameters     692

Repairing Obsolete CBO Statistics Gathering 693

Removing full-table scans with Oracle Text   694

Oracle Text Index Re-synchronization            695

Conclusion      696
 

Chapter 16:  Oracle10g Wait Event Tuning with AWR and ASH       698

The Oracle Wait Event Model&n