ISBN 0-9744486-2-1 ISBN 13 978-0974448626 Library of Congress Number:2005901261 980 pages: Hardcover -  9x7 Publication Date: April 2006 Shelving: Database/Oracle Oracle in-Focus Series # 24

Oracle Tuning The Definitive Reference Donald K. Burleson Retail Price $59.95 /  £37.95  Order now for 30% off and get online access to the code depot! Only $41.95 (30% off)         Key Features   About the Authors Reader Comments Table of Contents   Errata            Oracle 10g has become the most flexible and robust database ever created and Oracle tuning has become increasingly complex as the result of this massive power.  This book provides a complete step-by-step approach for holistic Oracle tuning and it is the accumulated knowledge from tuning thousands of Oracle databases.

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.

* See how Oracle captures time-series performance data. * Learn techniques for visualizing performance signatures over time. * Use the online code depot to quickly find performance bottlenecks. * Use historical data to predict impending performance problems. * Understand how to interpret complex Oracle tuning metrics. * Create customized performance alerts using AWR information. * See how AWR allows intelligent Oracle performance optimization. * Learn why the Oracle wait interface and 10046 dumps may become obsolete. * Understand proactive time-series Oracle tuning techniques.

About the Author:

Donald Burleson

Donald K. Burleson is one of the world’s top Oracle Database experts with more than 20 years of full-time DBA experience.  He specializes in creating database architectures for very large online databases and he has worked with some of the world’s most powerful and complex systems.   A former Adjunct Professor, Don Burleson has written 30 books, published more than 100 articles in National Magazines, and serves as Editor-in-Chief of Rampant TechPress. Don is a popular lecturer and teacher and is a frequent speaker at Oracle Openworld and other international database conferences. Donald Burleson's books   Donald Burleson articles   Donald Burleson comments   Don Burleson blog   Free Oracle Tips HTML Text

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            698 Collecting ASH Wait Information       701 Why Wait Event Tuning for Oracle?   703 Active Session History in Enterprise Manager           704 Active Session History in WISE         709 How Does a Wait Bottleneck Get Fixed?       710 System-wide Wait Event Tuning         712 Not All Events Are Created Equal      714 Inside the Real-time v$ Wait Events   716 Inside v$session_wait 717 Inside v$session_event           719 Using ASH for Time-series Wait Tuning        721 Display SQL Wait Details      724 Tip - wait_time vs. time_waited         726 Event Wait Analysis with ASH           726 Understanding Session Wait History   728 Signature Analysis of Wait Events      734 Conclusion      750 Chapter 17:  Tablespace & Object Tuning      751 Introduction to Oracle Segment Management  751 Inside Oracle Tablespace Management           752 The Issue of pctfree     753 The Issue of pctused    754 A Summary of Object Tuning Rules    754 Reducing Segment Header Contention and Buffer Busy Waits            756 Internal freelist Management   757 Characteristics of Bitmap Segment Management         758 Oracle Bitmap freelist Internals          759 New High Water Mark Pointers          760 Extent Control Header Block  760 Potential Performance Issues with ASSM       762 Proactive Tablespace Management     764 Reclaiming Segment Space     765 Online Segment Reorganization          767 Segment Space Growth Prediction      773 ASSM and RAC Advantages  775 Conclusion      777 References      777   Chapter 18:  Oracle Data Warehouse Tuning  778 Oracle Data Warehouse Tuning          778 What Does a Data Warehouse Need?  779 Oracle STAR Transformations and SQL         784 Bad Start Transformation Plan            785 Good Star Transformation Plan           786 Why Oracle 10g for the Data Warehouse?      787 Scaling the Oracle10g data warehouse           793 Conclusion      795   Chapter 19:  Oracle 10g Tuning with OEM    796 Introduction to OEM   796 The New OEM            797 Tuning with Metrics and Exceptions   800 Active Session History in Enterprise Manager           802 Easy Customization of OEM Alerts    803 Instance Efficiency Metrics     805 Alerts Notification and Setup  805 Overview of dbms_scheduler Functions         809 Throughput Metrics in OEM   829 OEM Outside the Instance       831 Exception Tuning Inside Enterprise Manager  837 Advisor Central in OEM         840 ADDM Main Screen   843 ADDM Recommendations      845 Understanding SQL Advisor Recommendations         853 The SQL Tuning Advisor Links          855 The Top SQL Screen   859 Viewing SQL Details in OEM 860 The Execution Plan Tab          861 Current Statistics Tab  863 Execution History Tab 864 Tuning History Tab     865 Oracle SQL Tuning Sets          866 Creating an SQL Tuning Set    866 Viewing SQL Tuning Set Details        867 Using the SQL Access Advisor           868 New Features of the SQL Advisors    869 Inside the SQL Access Advisor          870 The SQL Access Advisor Workload Definition          871 The SQL Access Advisor Recommendation Options  872 The SQL Access Advisor Schedule Advisor  874 The SQL Access Advisor Review      875 SQL Access Advisor Recommendations         876 Using the Memory Advisor through OEM       878 Persistence of Automatically Tuned Values    884 Automated Maintenance Tasks            884 Resource Management 884 Introduction to Online Oracle Tuning Tools    885 Oracle Dictionary Scripts for Tuning  886 Oracle Time Series Tuning Tools       888 Third-party Wait Event Tuning Tools 889 External Bottlenecks   892 Internal Bottlenecks     893 Oracle10g OEM Review        898 New Features of the SQL Advisors    900 Comprehensive Collection      900 OEM Wait Event Metrics        903 Automated Diagnostics in OEM          907 SQL Access Advisor  911 Shortcomings of OEM 913 Conclusion      918 Chapter 20:  Oracle RAC and Grid Tuning     921 Introduction to Tuning with RAC        921 Oracle RAC in a Nutshell       922 Oracle Scalability and Grid Technology         926 First Scale Up with SMP Servers       926 Next Scale Out with Multiple SMP Servers    927 Oracle10g Grid in a Nutshell  928 Blade Servers and Oracle RAC Tuning          930 Blade Servers and Oracle App Servers          931 The Revolution of Cache Fusion         932 Overview of RAC and Grid Tuning    935 RAC Load Balancing  936 Managing Inter-instance Data Block Transfers           939 Parallel Processing and RAC Performance     943