Oracle Tuning Power Scripts: With 100+ High Performance SQL Scripts

Description

Written by the world's most widely-read Oracle script developers and authors of over 50 best-selling Oracle books, Mike Ault, Don Burleson and Harry Conway shares their personal arsenal of Oracle data dictionary scripts in this comprehensive book. With over 50 years of combined experience using Oracle monitoring scripts and Oracle tuning scripts, this is the most comprehensive Oracle script collection ever assembled.

Packed with over 100 ready-to-user Oracle scripts, this is the definitive collection for every Oracle professional DBA. It would take man-years to develop these scripts from scratch, making this book the best value in the Oracle industry.

Caution - These are extremely powerful undocumented Oracle data dictionary scripts. They should only be used by a certified Oracle DBA, and after a careful review of the Oracle data dictionary script functionality. These Oracle scripts are not for beginners and knowledge of the Oracle data dictionary is required to fully utilize these scripts. USE AT YOUR OWN RISK.

 

Table of Contents

Using the Online Code Depot 1
Conventions Used in this Book 2
Acknowledgements 4
Preface 6
Chapter 1: CPU, Enqueue, and Wait Event Monitoring 7
CPU Usage Statistics 7
SQL by CPU Usage 9
Enqueues 11
Monitoring Events 14
System Events by Percent 16
System Events by Percent - Sample Report 18
SYS_EVENTS 19
Events Related to ORA-4031 Error 21
Snap_delta_sys_events_pct90 23
Oracle10g Wait Events 26
Oracle10g Enqueues 28
dba_hist_enqueue_stat 28
10g Time Model Statistics dba_hist Views 31
dba_hist_sys_time_model 32
Conclusion 37
Chapter 2: File I/O Monitoring 38
Monitoring I/O 38
Global Basic Queries 39
DATAFILE I/O 44
PL/SQL to Calculate I/O per Second Data 48
I/O Timing Analysis 50
Calculate I/O Timing Values for Datafiles 50
SNAP FILE I/O 52
Find the Current I/O Session Bandits 54
Report on 9i DBWR Statistics 60
Oracle 10g FILE I/O 62
New Analysis Techniques for Oracle10g and Above 62
Oracle 10g Segment Statistics 65
Oracle 10g Datafile I/O Statistics 69
Conclusion 94
Chapter 3: Monitoring Locks, Latches, and Waits 95
Latches 95
Locks 99
Monitoring Sessions Causing Blocked Locks 100
Monitoring DDL and DML Locks 102
Monitoring Internal Locks 104
Monitoring Waits 107
Data Block Waits 109
Monitoring Oracle 10g 117
dba_hist_system_event 124
dba_hist_event_name 127
dba_hist_waitstat 128
dba_hist_waitclassmet_history 130
dba_hist_latch 132
dba_hist_latch_misses_summary 135
Oracle 10g Instance Wait Tuning 139
Oracle10g Wait Event Tuning 141
Not all Events are Created Equal 141
Event Wait Analysis with ASH 143
Inside the Active Session History Tables 145
Signature Analysis of Wait Events 149
Conclusion 165
Chapter 4: Monitoring Users and Processes 167
Monitoring Currently Logged-in User Processes 167
Locating Top Resource Sessions 170
Session Bottleneck Analysis 176
Investigating Session Memory Usage 183
Examining Background Processes 185
Monitoring Rollback Activity 188
Oracle 10g 190
Session Wait Analysis in Oracle10g 190
dba_hist_sessmetric_history 195
dba_hist_bg_event_summary 196
Conclusion 199
Chapter 5: Objects 200
Determine Global Object Access Patterns 200
Removing Storage-Related Performance Vampires 208
Detecting Space-Related Object Performance Problems 208
Locating Hot I/O Objects 216
Oracle10g 220
Viewing Table and Index Access with AWR 225
Tracking SQL Nested Loop Joins 225
Counting Index Usage Inside SQL 233
Tracking Full Scan Access 240
Conclusion 254
Chapter 6: SGA Monitoring 255
Importance of Proper Configuration 255
Getting a Handle on Memory Usage 257
Understanding the SGA 260
Gaining Insight into Memory Use 262
More on Memory Ratios 266
How to Keep Data Where It Belongs 269
Structure of the Shared Pool 274
Monitoring and Tuning the Shared Pool 276
Looking into the Shared Pool 277
When Does Less Become More? 278
Conclusions about the Shared Pool 295
Examining the Log Buffer 306
Miscellaneous Memory Issues 306
Buffer Busy Waits 306
Oracle 10g 308
Oracle10g Automatic Memory Management 308
Ratio Coverage in Oracle10g 308
dba_hist_librarycache 310
dba_hist_rowcache_summary 312
dba_hist_buffer_pool_stat 315
Plotting the Data Buffer Usage by Hour of the Day 320
Plotting the DBHR by Day of the Week 323
Automating KEEP Pool Assignment 325
Conclusion 328
Chapter 7: SQL 329
Sorting in Oracle9i and Above 329
Pinpointing Sessions with Problem SQL 334
What is Bad SQL? 338
Pinpointing Bad SQL 340
Historical SQL Analysis 340
Current SQL Analysis 345
New Techniques for Analyzing SQL Execution 346
Interrogating SQL Execution Plans 351
SQL Tuning Roadmap 356
Understand the Query and Dependent Objects 356
Look for SQL Rewrite Possibilities 362
Look for Object-Based Solutions 364
Oracle 10g 368
SQL Statistics 368
dba_hist_sqlstat 369
Oracle10g SQL Tuning Scripts 376
dba_hist_sqlstat 384
dba_hist_sql_plan 390
Interrogating Table Join Methods 393
Conclusion 404
Chapter 8: Storage and Space Management 405
Avoiding Database Downtime 405
Automatic Growth 406Unlimited Object Extents 410
Correcting Space-Related Object Performance Problems 421
Oracle 10g 424
Oracle10g Storage Diagnostics 424
Oracle 10g ASSM 425
Segment Space Growth Prediction 432
Conclusion 434
Chapter 9: Miscellaneous Scripts 435
Workspaces in Oracle9i 435
pga_aggregate_target in Oracle9i 437
Uncovering Security Holes 438
Finding Storage Hogs 443
Oracle10g 447
Metric dba_hist Views 447
dba_hist_sysmetric_history 448
dba_hist_sysmetric_summary 449
System Statistics 451
dba_hist_sysstat 452
Operating System Statistics 454
Conclusion 457
Index 458
About Harry Conway 458
About Mike Ault 465
About Don Burleson 466
About Mike Reed 467