Expert Oracle Practices: Oracle Database Administration from the Oak Table

Prelim......Page 1
Contents at a Glance......Page 5
Contents......Page 7
Foreword......Page 21
Randolf Geist......Page 25
Connie Green......Page 26
Niall Litchfield......Page 27
Jože Senega......Page 28
Jeremiah Wilton......Page 29
Graham Wood......Page 30
Peter Sharman......Page 31
Guess Hunting......Page 33
Why Do We Guess?......Page 35
Understanding a Problem......Page 37
Logical Conclusions vs. Historical Observations......Page 38
Knowledge Is Power......Page 40
RTFM......Page 41
Paradigm Shifts......Page 43
Experience Is Danger......Page 44
Fixing the Root Cause?......Page 45
Best Practices and Myths......Page 46
BattleAgainstAnyGuess.com......Page 47
What Is Cloud Computing?......Page 49
Infrastructure as a Service (IAAS)......Page 50
Google......Page 51
Running Oracle on Amazon’s Cloud......Page 52
Making the Cloud Concrete......Page 53
AWS Management Console......Page 55
Elasticfox......Page 56
Perl/Java/Ruby Libraries......Page 57
Starting Out: Getting a Suitable Operating System Running......Page 58
Simple Storage Service (S3)......Page 60
EBS Performance for Oracle......Page 61
Attaching and Configuring EBS Storage......Page 63
Method A: Generic AMI and EBS File System......Page 64
Oracle Backup on EC2: The OSB Cloud Module......Page 65
Summary......Page 66
The Early Days......Page 67
Performance Strategy......Page 68
Design and Development......Page 69
Common Design Pitfalls......Page 70
Testing for Performance......Page 71
Set Up and Verify Monitoring......Page 72
Know Your Hardware......Page 73
Verify Statistics Collection......Page 74
Post Installation or Upgrade......Page 75
Reactive Tuning......Page 76
Step 1: Define the Problem......Page 77
The User Experience......Page 78
An Agreed-Upon Problem Definition......Page 79
Hardware and Database Data......Page 80
Sanity Check......Page 81
Scope: Instance-Wide......Page 82
Step 3: Formulate a Theory......Page 84
Step 4: Implement and Verify the Solution......Page 85
Diagnostic Tools......Page 86
Using and Interpreting ADDM......Page 87
Using and Interpreting the ASH Report......Page 89
Top Event P1/P2/P3 Values......Page 90
Top SQL with Top Events......Page 91
The Report Layout......Page 92
Elapsed Time......Page 93
Timed Events and Time Model......Page 94
Load Profile......Page 96
Sanity Check OSStat......Page 97
Drill into Relevant Details......Page 98
Time-Based Statistics......Page 99
% Activity and Average Active Sessions......Page 100
Wait Classes......Page 101
Execution Plan Stability and Profiles......Page 102
Summary......Page 103
The DBA as Designer......Page 105
Be Approachable......Page 106
Attend Code Reviews......Page 107
Partnership Between DBAs and Developers......Page 108
Be Open to New Ideas......Page 109
Design-First Methodologies vs. Agile Techniques......Page 110
Design-First Approach......Page 111
Do You Like Waterfalls?......Page 112
Does Agility Equate to Flexibility?......Page 114
Choose Your Datatypes Carefully......Page 115
Take Care in Sizing VARCHAR2 Strings......Page 119
Watch Your Comparison Semantics......Page 120
Don’t Make the Database Guess......Page 122
When Bigger Is Not Better......Page 123
Heaps of Trouble......Page 124
Faster, Not Harder......Page 132
Middle Tier vs. Database......Page 134
The Importance of Having Integrity......Page 135
The DBA as Database Evangelist......Page 137
Reading the Documentation and Keeping Current......Page 139
Knowing, Testing, and Teaching Your Software’s Features......Page 140
Constant and Iterative Knowledge Sharing......Page 142
Architecture......Page 143
Using the Right Tools for the Job......Page 144
Setting Up the Example......Page 145
Drilling Down to a Problem Thread......Page 146
Drilling into the Thread......Page 148
Memory......Page 149
The Registry......Page 151
Services......Page 154
Anatomy of a Service......Page 155
Control of Services......Page 156
Scripting......Page 158
Summary......Page 162
Adopting a Performance Mindset......Page 163
EXPLAIN PLAN......Page 165
DBMS_XPLAN......Page 167
Extended SQL Trace Data......Page 170
Case 1: The Lack of a Good Index......Page 173
Case 2: The Presence of Unidentified Data Skew......Page 174
Case 3: SQL That Should Be Rewritten......Page 176
Case 4: SQL That Unnecessarily Invokes PL/SQL......Page 180
Summary......Page 183
Further Reading......Page 184
Parsing Phase......Page 185
Execution Plan Preparation......Page 186
Cost......Page 187
User-Defined Statistics......Page 188
Creating Some Example Objects......Page 189
Running an Example Query......Page 192
Understanding How It Works......Page 194
Indicating Default Selectivity and Default Cost......Page 196
Breaking the Association......Page 197
Example 1: Influence of the Increased Cost......Page 198
Example 2: Influence on the Order of Operations with Default Statistics......Page 200
Example 3: Influence on the Order of Operations......Page 202
Summary......Page 204
Understanding Performance Optimization Methods......Page 205
Monitoring and Reacting to the BCHR......Page 206
Monitoring Delta Values of System/Session Stats......Page 214
Monitoring File Activity......Page 216
Monitoring the Delta Values of System/Session Waits......Page 222
Monitoring CPU Utilization......Page 228
CPU Load Generators......Page 229
Determining the CPU Run Queue......Page 230
Determining CPU Utilization......Page 233
Capturing Some Statistics......Page 235
Decision Tree for Quickly Interpreting the Statistics......Page 241
Creating Statspack or AWR Reports......Page 244
Monitoring the Delta Values for SQL Statements......Page 247
Examining Execution Plans and Plan Statistics......Page 251
Examining Optimizer Parameters Affecting Plans......Page 259
Activating and Deactivating the Optimizer Trace......Page 262
Peeked Bind Variables......Page 263
Transformations......Page 265
System Statistics......Page 267
Base Statistical Information......Page 268
Dynamic Sampling......Page 270
Single Table Access Path......Page 273
General Plans......Page 274
Plan Table......Page 275
Query Block Registry......Page 276
The Query......Page 277
Generating 10046 Extended Traces......Page 278
Brief Summary of a Raw 10046 Extended Trace File’s Contents......Page 279
Logon Trigger That Enables 10046 Tracing on Logon for a Program......Page 281
Enabling 10046 Tracing—Application Source Code May Be Modified......Page 282
Enabling 10046 Tracing—Application Source Code Cannot Be Modified......Page 283
Sample Trace File Analysis with Oracle 11.1.0.7......Page 285
Generating a Trace File on Error......Page 292
Initiating a Trace with SQL*Plus ORADEBUG......Page 293
Listing Events Set in Another Session......Page 294
HANGANALYZE Dump......Page 296
HEAPDUMP Dumps......Page 298
Process State Dump......Page 304
SHORT_STACK Dump......Page 305
SYSTEMSTATE Dump......Page 306
Reviewing the Enterprise Manager ADDM Findings......Page 307
Examining Network Packets......Page 311
SQL*Net Tracing......Page 315
Process Monitor Tracing......Page 317
Investigating Enqueue Waits......Page 318
Summary......Page 323
Decision Tree for Performance Monitoring......Page 329
Specific to a Single User or Job Function......Page 330
Problems Reported by IT Staff......Page 331
Quick Checkup......Page 332
Problem After Upgrading the Oracle Release Version......Page 338
Problem After Upgrading the ERP Version......Page 345
Verify the Inefficiency......Page 353
Verify That the Trace File Covers Only One Test......Page 354
Verify That the Trace File Is Complete......Page 355
Verify That the Issue Is a Database Issue......Page 357
Determine Whether It Is a Parse or Execution Problem......Page 359
Parse Performance Issues......Page 360
Majority of Parse Time Spent on the CPU......Page 361
General Optimizer Settings and Object Statistics......Page 363
Histogram Issues......Page 364
Common Parameters Influencing Optimizer......Page 365
Statement and Physical Design Issues......Page 366
Data Access Issues......Page 367
Optimizer Not Using (Correct) Index......Page 369
Pagination (Top N) Queries......Page 370
Processing Large Result Sets......Page 371
Join Issues......Page 372
Parallel Processing Issues......Page 373
Shared Pool Abuse......Page 374
Resolving Shared Pool Abuse......Page 375
General Guidelines for Investigating Shared Pool Abuse......Page 376
Managing the Very Large Database......Page 379
Designing (or Retrofitting) a VLDB......Page 380
Infinity Is So Imprecise…......Page 381
Partitioning......Page 383
Data Manipulation with Partitioning......Page 385
Deleting or Updating Millions of Rows......Page 386
Loading Millions of Rows......Page 388
Partition Pruning......Page 389
Partition Configuration......Page 390
Information Life Cycle Management......Page 392
Backup Optimization and Guaranteed Recovery......Page 394
Further Notes on Storage......Page 396
Number of Files in a Database......Page 397
Storage That Can Migrate......Page 398
Summary......Page 399
It Can’t Be Done!......Page 401
Subquery Anomaly......Page 402
Partition Elimination......Page 406
Lack of Knowledge......Page 410
Problems with Statistics......Page 417
Partitioning......Page 419
Batch Jobs......Page 420
Creating Statistics......Page 421
Other Stats......Page 425
Baseline......Page 428
Summary......Page 429
Latches and Why We Need Them......Page 431
Solitaire, Parent, and Child Latches......Page 432
Immediate Mode......Page 433
Willing-to-Wait Mode......Page 435
Step 1: Identify Latches Causing Contention......Page 436
Step 3: Check the Code Path......Page 438
Cache Buffers Chains Latch Contention......Page 439
Common Causes of CBC Latch Contention......Page 441
Step 2: Identify the SQL Statements Involved......Page 442
Step 3: Identify Objects Causing the Contention......Page 445
Step 4: Review Execution Plan of Problem Statements......Page 446
Eliminate Full Index Scan on Small Indices......Page 447
Eliminate Leaf Block Contention with Partitioning......Page 448
Favor Hash Joins Over Tightly-Nested Loop Joins......Page 449
Tune Inefficient Indices......Page 450
Structures in the Shared Pool......Page 451
Shared Pool Free Lists......Page 452
Common Causes of Shared Pool Latch Contention......Page 453
Step 1: Review Distribution Among Child Latches......Page 454
Step 2: Inspect Shared Pool Fragmentation......Page 456
Step 4: Identify SQL Statements Using Literal Values......Page 458
Avoid Setting _kghdsidx_count to 1......Page 460
Library Cache Latch Contention......Page 461
Step 2: Understand the Reason for Unsharable Child Cursors......Page 463
Avoid Flushing the Shared Pool......Page 464
Control and Limit Histograms......Page 465
Enqueue Hash Chains Latch Contention......Page 466
Common Causes of Enqueue Hash Chains Latch Contention......Page 467
Step 2: Identify the Enqueue Causing Latch Contention......Page 468
Step 3: Identify Lock Types with High Gets......Page 469
Disable Table Level Locks......Page 470
The _latch_classes and _latch_class_N Parameters......Page 471
Summary......Page 472
Measuring for Robust Performance......Page 473
Finding the Red Rocks......Page 474
Throughput Counts......Page 475
All Together Now.........Page 476
Initial Tuning......Page 477
Repeating the Analysis......Page 479
Exploring What Variance Can Tell Us About a Process......Page 480
Distribution Analysis......Page 481
Variance......Page 484
What About Standard Deviation?......Page 485
Elapsed Time Data Sources......Page 486
Achieving Robust Performance......Page 487
Designing an Experiment......Page 488
Using Instrumentation......Page 489
Measuring the Results......Page 491
Tolerance Ranges and Process Capability......Page 495
Sampling from Samples......Page 496
Summary......Page 497
User Security......Page 499
Securing User Accounts......Page 500
User Enumeration......Page 501
Splitting the Task in Two......Page 502
Dealing with Oracle Database 10g and Prior......Page 504
Feature Analysis......Page 507
Accounts That Can Definitely Be Removed......Page 509
Accounts That Definitely Have to Remain in the Database......Page 510
Accounts to Analyze Individually......Page 511
Reduction of Accounts......Page 514
Account Password Strength......Page 519
Cracking Passwords with a PL/SQL Password Cracker......Page 520
Cracking Passwords with a “Real” Password Cracker......Page 522
Fixing Weak Passwords......Page 529
Have Accounts Been Used?......Page 530
Have Accounts Been Shared?......Page 531
Password Management......Page 532
Audit Settings......Page 535
Summary......Page 536
Quiz Answer......Page 537
Securing Data......Page 539
Identifying Key Data......Page 540
Locating the Database Table......Page 541
Direct Table Privileges......Page 542
Understand the Hierarchy......Page 545
Other Methods to Read Data......Page 551
Access to Access......Page 557
Duplicating Data......Page 559
Generalizing Across the Database......Page 562
Summary......Page 563
Quiz Answer......Page 564
¦Numbers ¦A......Page 565
¦B......Page 567
¦C......Page 568
¦D......Page 570
¦E......Page 573
¦G......Page 574
¦H......Page 575
¦I......Page 576
¦L......Page 577
¦M......Page 578
¦N......Page 579
¦P......Page 581
¦R......Page 584
¦S......Page 585
¦T......Page 589
¦......Page 590
¦V......Page 591
¦W......Page 592
¦......Page 593