Oracle Tuning: The Definitive Reference

Alexey B. Danchenkov, Donald Burleson

  • 出版商: Rampant Tech Press
  • 出版日期: 2006-08-01
  • 售價: $2,040
  • 貴賓價: 9.5$1,938
  • 語言: 英文
  • 頁數: 640
  • 裝訂: Hardcover
  • ISBN: 0974448621
  • ISBN-13: 9780974448626
  • 相關分類: Oracle





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