Expert Oracle Practices (eBook)

Pete Finnigan works as an independent Oracle security consultant for his own company PeteFinnigan.com Limited. Pete specializes in performing detailed Oracle security Health checks against Oracle databases using a detailed methodology developed by Pete from many years of experience in securing databases. Pete performs this service for many clients worldwide, but also offers other specialist consulting services in many areas related to Oracle security. Pete's company PeteFinnigan.com Limited also develops and delivers a number of Oracle security training courses that are well-regarded and comprehensive.

Contents at a Glance 4
Table of Contents 5
Foreword 18
About the Authors 22
Melanie Caffrey 22
Pete Finnigan 22
Randolf Geist 22
Alex Gorbachev 23
Tim Gorman 23
Connie Green 23
Charles Hooper 24
Jonathan Lewis 24
Niall Litchfield 24
Karen Morton 25
Robyn Sands 25
Jože Senegacnik 25
Uri Shaft 26
Riyaj Shamsudeen 26
Jeremiah Wilton 26
Graham Wood 27
About the Technical Reviewers 28
Melanie Caffrey 28
Arup Nanda 28
Peter Sharman 28
Chapter 1: Battle Against Any Guess 29
Guess Hunting 29
Why Do We Guess? 31
Understanding a Problem 33
Logical Conclusions vs. Historical Observations 34
Knowledge Is Power 36
RTFM 37
Facing the Unknown 39
Paradigm Shifts 39
Experience Is Danger 40
Fixing the Root Cause? 41
Best Practices and Myths 42
BattleAgainstAnyGuess.com 43
Chapter 2: A Partly Cloudy Future 44
What Is Cloud Computing? 44
Software as a Service (SAAS) 45
Platform as a Service (PAAS) 45
Infrastructure as a Service (IAAS) 45
Who Are the Cloud Providers? 46
Sun 46
Salesforce.com 46
Google 46
Microsoft 47
Amazon.com 47
Running Oracle on Amazon’s Cloud 47
But Is It Supported? 48
Making the Cloud Concrete 48
Prerequisites 50
How Do You Work This Thing? 50
AWS Management Console 50
Elasticfox 51
Command-Line Tools 52
Perl/Java/Ruby Libraries 52
Starting Out: Getting a Suitable Operating System Running 53
Persistent Storage 55
Simple Storage Service (S3) 55
Elastic Block Storage (EBS) 56
EBS Performance for Oracle 56
Attaching and Configuring EBS Storage 58
Persistence Approaches 59
Method A: Generic AMI and EBS File System 59
Method B: Custom AMI 60
Method C: Boot from EBS 60
Oracle Backup on EC2: The OSB Cloud Module 60
Summary 61
Chapter 3: Developing a Performance Methodology 62
What Is Performance? 62
The Early Days 62
Time-Based Performance Analysis 63
Performance Strategy 63
Design and Development 64
Common Design Pitfalls 65
Lightweight Performance Measures 66
Quality Assurance 66
Testing for Performance 66
Capturing Resource Utilization and Outlines 67
Set Up and Verify Monitoring 67
New Software and Upgrades 68
Know Your Hardware 68
Verify Statistics Collection 69
Back Up the Optimizer Statistics 70
Implement Change Incrementally 70
Post Installation or Upgrade 70
Reactive Tuning 71
Step 1: Define the Problem 72
The User Experience 73
An Agreed-Upon Problem Definition 74
Hardware and Database Data 75
Step 2: Examine the Performance Data 76
Sanity Check 76
Analysis 77
Scope: Local Issue 77
Scope: Instance-Wide 77
Step 3: Formulate a Theory 79
Step 4: Implement and Verify the Solution 80
Diagnostic Tools 81
Using and Interpreting ADDM 82
Using and Interpreting the ASH Report 84
Top Event P1/P2/P3 Values 85
Top Client IDs 86
Top SQL with Top Events 86
Using and Interpreting the AWR and Statspack Instance Reports 87
The Report Layout 87
Analysis Strategy 88
Elapsed Time 88
Timed Events and Time Model 89
Load Profile 91
Sanity Check OSStat 92
Drill into Relevant Details 93
Concluding Data Analysis 94
Meaning of Key Statistics 94
Time-Based Statistics 94
% Activity and Average Active Sessions 95
ASH-Estimated DB time 96
V$OSSTAT 96
Wait Classes 96
The Optimizer 97
Managing Statistics 97
Locking Statistics 97
Execution Plan Stability and Profiles 97
Summary 98
Chapter 4: The DBA as Designer 99
When to Get Involved in Application Design 100
Be Approachable 100
Ask for Periodic Sign-off on Design and Application Milestones 101
Attend Code Reviews 101
Hold Postmortems 102
Partnership Between DBAs and Developers 102
Hold Brown Bag Sessions 103
Sit Near Each Other 103
Be Open to New Ideas 103
Be on the Same Side 104
Design-First Methodologies vs. Agile Techniques 104
Design-First Approach 105
Agile Software Development 106
Pros and Cons of Each Methodology 106
Do You Like Waterfalls? 106
Does Agility Equate to Flexibility? 108
Schema Design 109
Choose Your Datatypes Carefully 109
Take Care in Sizing VARCHAR2 Strings 113
Watch Your Comparison Semantics 114
Don’t Make the Database Guess 116
When Bigger Is Not Better 117
Heaps of Trouble 118
Faster, Not Harder 126
Other Design Considerations 128
Middle Tier vs. Database 128
Flexibility, Security, Speed 129
The Importance of Having Integrity 129
Don’t Be High Maintenance 131
The DBA as Database Evangelist 131
Reading the Documentation and Keeping Current 133
Knowing, Testing, and Teaching Your Software’s Features 134
Learning from Your Mistakes and Experiences 136
Triages and Postmortems 136
Constant and Iterative Knowledge Sharing 136
Chapter 5: Running Oracle on Windows 137
Architecture 137
CPU Resources 138
Using the Right Tools for the Job 138
Setting Up the Example 139
Drilling Down to a Problem Thread 140
Drilling into the Thread 142
Memory 143
Disk 145
Management 145
The Registry 145
Services 148
Anatomy of a Service 149
Control of Services 150
The Database Console and Other Services 152
Scripting 152
Summary 156
Chapter 6: Managing SQL Performance 157
Adopting a Performance Mindset 157
Defining and Measuring Performance 159
EXPLAIN PLAN 159
DBMS_XPLAN 161
Extended SQL Trace Data 164
Interpreting Performance Data 167
Case 1: The Lack of a Good Index 167
Case 2: The Presence of Unidentified Data Skew 168
Case 3: SQL That Should Be Rewritten 170
Case 4: SQL That Unnecessarily Invokes PL/SQL 174
Summary 177
Further Reading 178
Chapter 7: PL/SQL and the CBO 179
Reviewing the Basics 179
Parsing Phase 179
Execution Plan Preparation 180
Selectivity 181
Cardinality 181
Cost 181
Using the Extensible Optimizer 182
User-Defined Statistics 182
User-Defined Selectivity 183
User-Defined Cost 183
Creating an Example 183
Creating Some Example Objects 183
Running an Example Query 186
Giving the CBO Better Information 188
Understanding How It Works 188
Indicating Default Selectivity and Default Cost 190
Specifying Defaults (Syntax) 191
Determining a Default Cost 191
Breaking the Association 191
Influencing the Execution Plans 192
Example 1: Influence of the Increased Cost 192
Example 2: Influence on the Order of Operations with Default Statistics 194
Example 3: Influence on the Order of Operations 196
Summary 198
Chapter 8: Understanding Performance Optimization Methods 199
Blindly Changing Parameters 200
Monitoring and Reacting to the BCHR 200
Monitoring Delta Values of System/Session Stats 208
Monitoring File Activity 210
Monitoring the Delta Values of System/Session Waits 216
Monitoring CPU Utilization 222
CPU Load Generators 223
Determining the CPU Run Queue 224
Determining CPU Utilization 227
Sampling Performance with Low Overhead 229
Capturing Some Statistics 229
Decision Tree for Quickly Interpreting the Statistics 235
Creating Statspack or AWR Reports 238
Monitoring the Delta Values for SQL Statements 241
Examining Execution Plans and Plan Statistics 245
Examining Optimizer Parameters Affecting Plans 253
Generating 10053 Cost-Based Optimizer Traces 256
Activating and Deactivating the Optimizer Trace 256
Query Blocks 257
Peeked Bind Variables 257
Optimizer Parameters Used 259
Transformations 259
System Statistics 261
Base Statistical Information 262
Dynamic Sampling 264
Single Table Access Path 267
General Plans 268
Plan Table 269
Query Block Registry 270
Hints 271
The Query 271
Generating 10046 Extended Traces 272
Brief Summary of a Raw 10046 Extended Trace File’s Contents 273
Enabling a 10046 Extended Trace 275
Logon Trigger That Enables 10046 Tracing on Logon for a Program 275
Enabling 10046 Tracing—Application Source Code May Be Modified 276
Enabling 10046 Tracing—Application Source Code Cannot Be Modified 277
Disabling 10046 Tracing 279
Sample Trace File Analysis with Oracle 11.1.0.7 279
Examining Server Stack Traces 286
Generating a Trace File on Error 286
Initiating a Trace with SQL*Plus ORADEBUG 287
Listing Events Set in Another Session 288
HANGANALYZE Dump 290
HEAPDUMP Dumps 292
Java Pool Memory Allocation 292
Large Pool Memory Allocation 293
PGA and UGA Memory Allocation 294
Shared Pool Memory Allocation 295
Process State Dump 298
SHORT_STACK Dump 299
SYSTEMSTATE Dump 300
Operating-System-Generated Stack Traces 301
Reviewing the Enterprise Manager ADDM Findings 301
Examining Network Packets 305
Examining Client-Side Traces 309
SQL*Net Tracing 309
Process Monitor Tracing 311
Spy++ Tracing 312
Investigating Enqueue Waits 312
Summary 317
Chapter 9: Choosing a Performance Optimization Method 322
Decision Tree for Performance Monitoring 322
Performance Problems Not Yet Reported 323
Problems Reported by End Users 323
Specific to a Single User or Job Function 323
Not Specific to a Single User or Job Function 324
Problems Reported by IT Staff 324
Sample Investigations 325
Quick Checkup 325
Problem After Upgrading the Oracle Release Version 331
Problem After Upgrading the ERP Version 338
Performance Optimization Issues 346
Inefficient SQL 346
Verify the Inefficiency 346
Collect Additional Data 347
Verify That the Trace File Covers Only One Test 347
Verify That the Trace File Is Complete 348
Verify That the Issue Is a Database Issue 350
Determine Whether It Is a Parse or Execution Problem 352
Parse Performance Issues 353
Majority of Parse Time Spent on the CPU 354
Majority of Parse Time Spent on Wait Events 356
High Execution Time or Fetch Time Issues 356
General Optimizer Settings and Object Statistics 356
Histogram Issues 357
Common Parameters Influencing Optimizer 358
Statement and Physical Design Issues 359
Data Access Issues 360
Optimizer Not Using (Correct) Index 362
Pagination (Top N) Queries 363
Processing Large Result Sets 364
Join Issues 365
Parallel Processing Issues 366
Shared Pool Abuse 367
Resolving Shared Pool Abuse 368
General Guidelines for Investigating Shared Pool Abuse 369
Chapter 10: Managing the Very Large Database 371
Designing (or Retrofitting) a VLDB 372
Infinity Is So Imprecise… 373
Partitioning 375
Everything Is a Segment 377
Data Manipulation with Partitioning 377
Deleting or Updating Millions of Rows 378
Loading Millions of Rows 380
Partition Pruning 381
Partition Configuration 382
Information Life Cycle Management 384
Backup Optimization and Guaranteed Recovery 386
Further Notes on Storage 388
Limits of Which to Be Aware 389
Database Block Size 389
Number of Files in a Database 389
Storage That Can Migrate 390
Parameter READ_ONLY_OPEN_DELAYED 391
Summary 391
Chapter 11: Statistics 392
It Can’t Be Done! 392
Subquery Anomaly 393
Partition Elimination 397
Lack of Knowledge 401
Problems with Statistics 408
Timing 410
Multinationals 410
Partitioning 410
Batch Jobs 411
Creating Statistics 412
Other Stats 416
Baseline 419
Summary 420
Chapter 12: Troubleshooting Latch Contention 422
Latches and Why We Need Them 422
Solitaire, Parent, and Child Latches 423
Operational Specifics 424
Immediate Mode 424
Willing-to-Wait Mode 426
Latch-Wait Posting Mode 427
Identifying and Analyzing Latch Contention 427
Step 1: Identify Latches Causing Contention 427
Step 2: Review Distribution of Gets 429
Step 3: Check the Code Path 429
Cache Buffers Chains Latch Contention 430
Common Causes of CBC Latch Contention 432
Analyzing CBC Latch Contention 433
Step 1: Review the Distribution of Gets 433
Step 2: Identify the SQL Statements Involved 433
Step 3: Identify Objects Causing the Contention 436
Step 4: Review Execution Plan of Problem Statements 437
Resolving CBC Latch Contention 438
Eliminate Full Table Scan on Small Tables 438
Eliminate Full Index Scan on Small Indices 438
Eliminate Leaf Block Contention with Partitioning 439
Favor Hash Joins Over Tightly-Nested Loop Joins 440
Tune Inefficient Indices 441
Reduce CPU Usage 442
Shared Pool Latch Contention 442
Structures in the Shared Pool 442
Shared Pool Free Lists 443
Shared Pool Reserved Free Lists 444
Common Causes of Shared Pool Latch Contention 444
Analyzing Shared pool Latch Contention 445
Step 1: Review Distribution Among Child Latches 445
Step 2: Inspect Shared Pool Fragmentation 447
Step 3: Review Objects Causing Flushing 449
Step 4: Identify SQL Statements Using Literal Values 449
Resolving Shared Pool Latch Contention 451
Avoid Unnecessary Heaps 451
Avoid and Reduce Fragmentation by Sharing SQL Statements 451
Avoid Setting _kghdsidx_count to 1 451
Avoid Flushing the Shared Pool 452
Avoid Shared Pool Reserved Free List Fragmentation 452
Library Cache Latch Contention 452
Common Causes of Library Cache Latch Contention 454
Analyzing Library Cache Latch Contention 454
Step 1: Identify Cursors Using Literal Values 454
Step 2: Understand the Reason for Unsharable Child Cursors 454
Library Cache Latches and Mutexes 455
Resolving Library Cache Latch Contention 455
Use Bind Variables 455
Avoid Flushing the Shared Pool 455
Adjust the session_cached_cursors Parameter 456
Adjust the cursor_space_for_time Parameter to True 456
Control and Limit Histograms 456
Enqueue Hash Chains Latch Contention 457
Common Causes of Enqueue Hash Chains Latch Contention 458
Analyzing Enqueue Hash Chains Latch Contention 459
Step 1: Review Distribution Among Child Latches. 459
Step 2: Identify the Enqueue Causing Latch Contention 459
Step 3: Identify Lock Types with High Gets 460
Resolving Enqueue Hash Chains Latch Contention 461
Avoid Excessive Short Transactions 461
Disable Table Level Locks 461
Reduce or Avoid Activity Against the Lock Type Causing Contention 462
Advanced Help for Latch Contention Problems 462
The v$latch_parent View 462
The spin_count Parameter 462
The _latch_classes and _latch_class_N Parameters 462
The _latch_wait_posting and _enable_reliable_latch_waits Parameters 463
Summary 463
Chapter 13: Measuring for Robust Performance 464
Finding the Red Rocks 465
Understanding the Properties of Performance 466
Response Time Is Key 466
Throughput Counts 466
Meeting Expectations Matters 467
All Together Now... 467
“Tuning” a Data Warehouse 468
Initial Tuning 468
Repeating the Analysis 470
Exploring What Variance Can Tell Us About a Process 471
Distribution Analysis 472
Distribution of Elapsed Time Data 475
Variance 475
The Index of Dispersion 476
What About Standard Deviation? 476
Elapsed Time Data Sources 477
Achieving Robust Performance 478
Designing an Experiment 479
Using Instrumentation 480
Measuring the Results 482
Tolerance Ranges and Process Capability 486
What is “Too Much” Variation? 487
Measuring Variance Within Oracle Sample Sets 487
Sampling from Samples 487
Summary 488
Chapter 14: User Security 490
Securing User Accounts 491
User Enumeration 492
Splitting the Task in Two 493
Dealing with Oracle Database 10g and Prior 495
Feature Analysis 498
Accounts That Can Definitely Be Removed 500
Accounts That Definitely Have to Remain in the Database 501
Accounts to Analyze Individually 502
Reduction of Accounts 505
Account Password Strength 510
Cracking Passwords with a PL/SQL Password Cracker 511
Cracking Passwords with a “Real” Password Cracker 513
Fixing Weak Passwords 520
Roles and Privilege Assessment 521
Have Accounts Been Used? 521
Have Accounts Been Shared? 522
Password Management 523
Audit Settings 526
Summary 527
Quiz Answer 528
Chapter 15: Securing Data 529
Identifying Key Data 530
Locating the Database Table 531
Direct Table Privileges 532
Understand the Hierarchy 535
Other Methods to Read Data 541
Access to Access 547
Duplicating Data 549
Generalizing Across the Database 552
Summary 553
Quiz Answer 554
Index 555