E-Book, Englisch, 592 Seiten
Finnigan / Wilton / Wood Expert Oracle Practices
1. ed
ISBN: 978-1-4302-2669-7
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark
Oracle Database Administration from the Oak Table
E-Book, Englisch, 592 Seiten
ISBN: 978-1-4302-2669-7
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark
Autoren/Hrsg.
Weitere Infos & Material
1;Contents at a Glance;4
2;Table of Contents;5
3;Foreword;18
4;About the Authors;22
4.1;Melanie Caffrey;22
4.2;Pete Finnigan;22
4.3;Randolf Geist;22
4.4;Alex Gorbachev;23
4.5;Tim Gorman;23
4.6;Connie Green;23
4.7;Charles Hooper;24
4.8;Jonathan Lewis;24
4.9;Niall Litchfield;24
4.10;Karen Morton;25
4.11;Robyn Sands;25
4.12;Jože Senegacnik;25
4.13;Uri Shaft;26
4.14;Riyaj Shamsudeen;26
4.15;Jeremiah Wilton;26
4.16;Graham Wood;27
5;About the Technical Reviewers;28
5.1;Melanie Caffrey;28
5.2;Arup Nanda;28
5.3;Peter Sharman;28
6;Chapter 1: Battle Against Any Guess;29
6.1;Guess Hunting;29
6.2;Why Do We Guess?;31
6.3;Understanding a Problem;33
6.3.1;Logical Conclusions vs. Historical Observations;34
6.3.2;Knowledge Is Power;36
6.3.3;RTFM;37
6.4;Facing the Unknown;39
6.4.1;Paradigm Shifts;39
6.4.2;Experience Is Danger;40
6.5;Fixing the Root Cause?;41
6.6;Best Practices and Myths;42
6.7;BattleAgainstAnyGuess.com;43
7;Chapter 2: A Partly Cloudy Future;44
7.1;What Is Cloud Computing?;44
7.1.1;Software as a Service (SAAS);45
7.1.2;Platform as a Service (PAAS);45
7.1.3;Infrastructure as a Service (IAAS);45
7.2;Who Are the Cloud Providers?;46
7.2.1;Sun;46
7.2.2;Salesforce.com;46
7.2.3;Google;46
7.2.4;Microsoft;47
7.2.5;Amazon.com;47
7.3;Running Oracle on Amazon’s Cloud;47
7.3.1;But Is It Supported?;48
7.3.2;Making the Cloud Concrete;48
7.3.3;Prerequisites;50
7.3.4;How Do You Work This Thing?;50
7.3.4.1;AWS Management Console;50
7.3.4.2;Elasticfox;51
7.3.4.3;Command-Line Tools;52
7.3.4.4;Perl/Java/Ruby Libraries;52
7.4;Starting Out: Getting a Suitable Operating System Running;53
7.5;Persistent Storage;55
7.5.1;Simple Storage Service (S3);55
7.5.2;Elastic Block Storage (EBS);56
7.5.3;EBS Performance for Oracle;56
7.5.4;Attaching and Configuring EBS Storage;58
7.6;Persistence Approaches;59
7.6.1;Method A: Generic AMI and EBS File System;59
7.6.2;Method B: Custom AMI;60
7.6.3;Method C: Boot from EBS;60
7.7;Oracle Backup on EC2: The OSB Cloud Module;60
7.8;Summary;61
8;Chapter 3: Developing a Performance Methodology;62
8.1;What Is Performance?;62
8.1.1;The Early Days;62
8.1.2;Time-Based Performance Analysis;63
8.1.3;Performance Strategy;63
8.2;Design and Development;64
8.2.1;Common Design Pitfalls;65
8.2.2;Lightweight Performance Measures;66
8.3;Quality Assurance;66
8.3.1;Testing for Performance;66
8.3.2;Capturing Resource Utilization and Outlines;67
8.3.2.1;Set Up and Verify Monitoring;67
8.4;New Software and Upgrades;68
8.4.1;Know Your Hardware;68
8.4.2;Verify Statistics Collection;69
8.4.3;Back Up the Optimizer Statistics;70
8.4.4;Implement Change Incrementally;70
8.5;Post Installation or Upgrade;70
8.6;Reactive Tuning;71
8.6.1;Step 1: Define the Problem;72
8.6.1.1;The User Experience;73
8.6.1.2;An Agreed-Upon Problem Definition;74
8.6.1.3;Hardware and Database Data;75
8.6.2;Step 2: Examine the Performance Data;76
8.6.2.1;Sanity Check;76
8.6.2.2;Analysis;77
8.6.2.3;Scope: Local Issue;77
8.6.2.4;Scope: Instance-Wide;77
8.6.3;Step 3: Formulate a Theory;79
8.6.4;Step 4: Implement and Verify the Solution;80
8.7;Diagnostic Tools;81
8.7.1;Using and Interpreting ADDM;82
8.7.2;Using and Interpreting the ASH Report;84
8.7.2.1;Top Event P1/P2/P3 Values;85
8.7.2.2;Top Client IDs;86
8.7.2.3;Top SQL with Top Events;86
8.7.3;Using and Interpreting the AWR and Statspack Instance Reports;87
8.7.3.1;The Report Layout;87
8.7.3.2;Analysis Strategy;88
8.7.3.3;Elapsed Time;88
8.7.3.4;Timed Events and Time Model;89
8.7.3.5;Load Profile;91
8.7.3.6;Sanity Check OSStat;92
8.7.3.7;Drill into Relevant Details;93
8.7.3.8;Concluding Data Analysis;94
8.8;Meaning of Key Statistics;94
8.8.1;Time-Based Statistics;94
8.8.2;% Activity and Average Active Sessions;95
8.8.3;ASH-Estimated DB time;96
8.8.4;V$OSSTAT;96
8.8.5;Wait Classes;96
8.9;The Optimizer;97
8.9.1;Managing Statistics;97
8.9.2;Locking Statistics;97
8.9.3;Execution Plan Stability and Profiles;97
8.10;Summary;98
9;Chapter 4: The DBA as Designer;99
9.1;When to Get Involved in Application Design;100
9.1.1;Be Approachable;100
9.1.2;Ask for Periodic Sign-off on Design and Application Milestones;101
9.1.3;Attend Code Reviews;101
9.1.4;Hold Postmortems;102
9.2;Partnership Between DBAs and Developers;102
9.2.1;Hold Brown Bag Sessions;103
9.2.2;Sit Near Each Other;103
9.2.3;Be Open to New Ideas;103
9.2.4;Be on the Same Side;104
9.3;Design-First Methodologies vs. Agile Techniques;104
9.3.1;Design-First Approach;105
9.3.2;Agile Software Development;106
9.3.3;Pros and Cons of Each Methodology;106
9.3.3.1;Do You Like Waterfalls?;106
9.3.3.2;Does Agility Equate to Flexibility?;108
9.4;Schema Design;109
9.4.1;Choose Your Datatypes Carefully;109
9.4.1.1;Take Care in Sizing VARCHAR2 Strings;113
9.4.1.2;Watch Your Comparison Semantics;114
9.4.1.3;Don’t Make the Database Guess;116
9.4.2;When Bigger Is Not Better;117
9.4.3;Heaps of Trouble;118
9.4.4;Faster, Not Harder;126
9.5;Other Design Considerations;128
9.5.1;Middle Tier vs. Database;128
9.5.2;Flexibility, Security, Speed;129
9.5.3;The Importance of Having Integrity;129
9.5.4;Don’t Be High Maintenance;131
9.6;The DBA as Database Evangelist;131
9.6.1;Reading the Documentation and Keeping Current;133
9.6.2;Knowing, Testing, and Teaching Your Software’s Features;134
9.7;Learning from Your Mistakes and Experiences;136
9.7.1;Triages and Postmortems;136
9.7.2;Constant and Iterative Knowledge Sharing;136
10;Chapter 5: Running Oracle on Windows;137
10.1;Architecture;137
10.1.1;CPU Resources;138
10.1.1.1;Using the Right Tools for the Job;138
10.1.1.2;Setting Up the Example;139
10.1.1.3;Drilling Down to a Problem Thread;140
10.1.1.4;Drilling into the Thread;142
10.1.2;Memory;143
10.1.3;Disk;145
10.2;Management;145
10.2.1;The Registry;145
10.2.2;Services;148
10.2.2.1;Anatomy of a Service;149
10.2.2.2;Control of Services;150
10.2.2.3;The Database Console and Other Services;152
10.2.3;Scripting;152
10.3;Summary;156
11;Chapter 6: Managing SQL Performance;157
11.1;Adopting a Performance Mindset;157
11.2;Defining and Measuring Performance;159
11.2.1;EXPLAIN PLAN;159
11.2.2;DBMS_XPLAN;161
11.2.3;Extended SQL Trace Data;164
11.3;Interpreting Performance Data;167
11.3.1;Case 1: The Lack of a Good Index;167
11.3.2;Case 2: The Presence of Unidentified Data Skew;168
11.3.3;Case 3: SQL That Should Be Rewritten;170
11.3.4;Case 4: SQL That Unnecessarily Invokes PL/SQL;174
11.4;Summary;177
11.5;Further Reading;178
12;Chapter 7: PL/SQL and the CBO;179
12.1;Reviewing the Basics;179
12.1.1;Parsing Phase;179
12.1.2;Execution Plan Preparation;180
12.1.2.1;Selectivity;181
12.1.2.2;Cardinality;181
12.1.2.3;Cost;181
12.2;Using the Extensible Optimizer;182
12.2.1;User-Defined Statistics;182
12.2.2;User-Defined Selectivity;183
12.2.3;User-Defined Cost;183
12.3;Creating an Example;183
12.3.1;Creating Some Example Objects;183
12.3.2;Running an Example Query;186
12.3.3;Giving the CBO Better Information;188
12.4;Understanding How It Works;188
12.5;Indicating Default Selectivity and Default Cost;190
12.5.1;Specifying Defaults (Syntax);191
12.5.2;Determining a Default Cost;191
12.5.3;Breaking the Association;191
12.6;Influencing the Execution Plans;192
12.6.1;Example 1: Influence of the Increased Cost;192
12.6.2;Example 2: Influence on the Order of Operations with Default Statistics;194
12.6.3;Example 3: Influence on the Order of Operations;196
12.7;Summary;198
13;Chapter 8: Understanding Performance Optimization Methods;199
13.1;Blindly Changing Parameters;200
13.2;Monitoring and Reacting to the BCHR;200
13.3;Monitoring Delta Values of System/Session Stats;208
13.4;Monitoring File Activity;210
13.5;Monitoring the Delta Values of System/Session Waits;216
13.6;Monitoring CPU Utilization;222
13.6.1;CPU Load Generators;223
13.6.2;Determining the CPU Run Queue;224
13.6.3;Determining CPU Utilization;227
13.7;Sampling Performance with Low Overhead;229
13.7.1;Capturing Some Statistics;229
13.7.2;Decision Tree for Quickly Interpreting the Statistics;235
13.8;Creating Statspack or AWR Reports;238
13.9;Monitoring the Delta Values for SQL Statements;241
13.10;Examining Execution Plans and Plan Statistics;245
13.11;Examining Optimizer Parameters Affecting Plans;253
13.12;Generating 10053 Cost-Based Optimizer Traces;256
13.12.1;Activating and Deactivating the Optimizer Trace;256
13.12.2;Query Blocks;257
13.12.3;Peeked Bind Variables;257
13.12.4;Optimizer Parameters Used;259
13.12.5;Transformations;259
13.12.6;System Statistics;261
13.12.7;Base Statistical Information;262
13.12.8;Dynamic Sampling;264
13.12.9;Single Table Access Path;267
13.12.10;General Plans;268
13.12.11;Plan Table;269
13.12.12;Query Block Registry;270
13.12.13;Hints;271
13.12.14;The Query;271
13.13;Generating 10046 Extended Traces;272
13.13.1;Brief Summary of a Raw 10046 Extended Trace File’s Contents;273
13.13.2;Enabling a 10046 Extended Trace;275
13.13.2.1;Logon Trigger That Enables 10046 Tracing on Logon for a Program;275
13.13.2.2;Enabling 10046 Tracing—Application Source Code May Be Modified;276
13.13.2.3;Enabling 10046 Tracing—Application Source Code Cannot Be Modified;277
13.13.3;Disabling 10046 Tracing;279
13.13.4;Sample Trace File Analysis with Oracle 11.1.0.7;279
13.14;Examining Server Stack Traces;286
13.14.1;Generating a Trace File on Error;286
13.14.2;Initiating a Trace with SQL*Plus ORADEBUG;287
13.14.2.1;Listing Events Set in Another Session;288
13.14.2.2;HANGANALYZE Dump;290
13.14.2.3;HEAPDUMP Dumps;292
13.14.2.3.1;Java Pool Memory Allocation;292
13.14.2.3.2;Large Pool Memory Allocation;293
13.14.2.3.3;PGA and UGA Memory Allocation;294
13.14.2.3.4;Shared Pool Memory Allocation;295
13.14.2.4;Process State Dump;298
13.14.2.5;SHORT_STACK Dump;299
13.14.2.6;SYSTEMSTATE Dump;300
13.14.3;Operating-System-Generated Stack Traces;301
13.15;Reviewing the Enterprise Manager ADDM Findings;301
13.16;Examining Network Packets;305
13.17;Examining Client-Side Traces;309
13.17.1;SQL*Net Tracing;309
13.17.2;Process Monitor Tracing;311
13.17.3;Spy++ Tracing;312
13.18;Investigating Enqueue Waits;312
13.19;Summary;317
14;Chapter 9: Choosing a Performance Optimization Method;322
14.1;Decision Tree for Performance Monitoring;322
14.1.1;Performance Problems Not Yet Reported;323
14.1.2;Problems Reported by End Users;323
14.1.2.1;Specific to a Single User or Job Function;323
14.1.2.2;Not Specific to a Single User or Job Function;324
14.1.3;Problems Reported by IT Staff;324
14.2;Sample Investigations;325
14.2.1;Quick Checkup;325
14.2.2;Problem After Upgrading the Oracle Release Version;331
14.2.3;Problem After Upgrading the ERP Version;338
14.3;Performance Optimization Issues;346
14.4;Inefficient SQL;346
14.4.1;Verify the Inefficiency;346
14.4.2;Collect Additional Data;347
14.4.3;Verify That the Trace File Covers Only One Test;347
14.4.4;Verify That the Trace File Is Complete;348
14.4.5;Verify That the Issue Is a Database Issue;350
14.4.6;Determine Whether It Is a Parse or Execution Problem;352
14.5;Parse Performance Issues;353
14.5.1;Majority of Parse Time Spent on the CPU;354
14.5.2;Majority of Parse Time Spent on Wait Events;356
14.6;High Execution Time or Fetch Time Issues;356
14.6.1;General Optimizer Settings and Object Statistics;356
14.6.2;Histogram Issues;357
14.6.3;Common Parameters Influencing Optimizer;358
14.6.4;Statement and Physical Design Issues;359
14.6.5;Data Access Issues;360
14.6.6;Optimizer Not Using (Correct) Index;362
14.6.7;Pagination (Top N) Queries;363
14.6.8;Processing Large Result Sets;364
14.6.9;Join Issues;365
14.6.10;Parallel Processing Issues;366
14.6.11;Shared Pool Abuse;367
14.6.12;Resolving Shared Pool Abuse;368
14.6.13;General Guidelines for Investigating Shared Pool Abuse;369
15;Chapter 10: Managing the Very Large Database;371
15.1;Designing (or Retrofitting) a VLDB;372
15.2;Infinity Is So Imprecise…;373
15.3;Partitioning;375
15.3.1;Everything Is a Segment;377
15.3.2;Data Manipulation with Partitioning;377
15.3.2.1;Deleting or Updating Millions of Rows;378
15.3.2.2;Loading Millions of Rows;380
15.3.3;Partition Pruning;381
15.3.4;Partition Configuration;382
15.4;Information Life Cycle Management;384
15.5;Backup Optimization and Guaranteed Recovery;386
15.6;Further Notes on Storage;388
15.7;Limits of Which to Be Aware;389
15.7.1;Database Block Size;389
15.7.2;Number of Files in a Database;389
15.7.3;Storage That Can Migrate;390
15.7.4;Parameter READ_ONLY_OPEN_DELAYED;391
15.8;Summary;391
16;Chapter 11: Statistics;392
16.1;It Can’t Be Done!;392
16.1.1;Subquery Anomaly;393
16.1.2;Partition Elimination;397
16.2;Lack of Knowledge;401
16.3;Problems with Statistics;408
16.4;Timing;410
16.4.1;Multinationals;410
16.4.2;Partitioning;410
16.4.3;Batch Jobs;411
16.5;Creating Statistics;412
16.5.1;Other Stats;416
16.5.2;Baseline;419
16.6;Summary;420
17;Chapter 12: Troubleshooting Latch Contention;422
17.1;Latches and Why We Need Them;422
17.2;Solitaire, Parent, and Child Latches;423
17.3;Operational Specifics;424
17.3.1;Immediate Mode;424
17.3.2;Willing-to-Wait Mode;426
17.3.3;Latch-Wait Posting Mode;427
17.4;Identifying and Analyzing Latch Contention;427
17.4.1;Step 1: Identify Latches Causing Contention;427
17.4.2;Step 2: Review Distribution of Gets;429
17.4.3;Step 3: Check the Code Path;429
17.5;Cache Buffers Chains Latch Contention;430
17.5.1;Common Causes of CBC Latch Contention;432
17.5.2;Analyzing CBC Latch Contention;433
17.5.2.1;Step 1: Review the Distribution of Gets;433
17.5.2.2;Step 2: Identify the SQL Statements Involved;433
17.5.2.3;Step 3: Identify Objects Causing the Contention;436
17.5.2.4;Step 4: Review Execution Plan of Problem Statements;437
17.5.3;Resolving CBC Latch Contention;438
17.5.3.1;Eliminate Full Table Scan on Small Tables;438
17.5.3.2;Eliminate Full Index Scan on Small Indices;438
17.5.3.3;Eliminate Leaf Block Contention with Partitioning;439
17.5.3.4;Favor Hash Joins Over Tightly-Nested Loop Joins;440
17.5.3.5;Tune Inefficient Indices;441
17.5.3.6;Reduce CPU Usage;442
17.6;Shared Pool Latch Contention;442
17.6.1;Structures in the Shared Pool;442
17.6.1.1;Shared Pool Free Lists;443
17.6.1.2;Shared Pool Reserved Free Lists;444
17.6.2;Common Causes of Shared Pool Latch Contention;444
17.6.3;Analyzing Shared pool Latch Contention;445
17.6.3.1;Step 1: Review Distribution Among Child Latches;445
17.6.3.2;Step 2: Inspect Shared Pool Fragmentation;447
17.6.3.3;Step 3: Review Objects Causing Flushing;449
17.6.3.4;Step 4: Identify SQL Statements Using Literal Values;449
17.6.4;Resolving Shared Pool Latch Contention;451
17.6.4.1;Avoid Unnecessary Heaps;451
17.6.4.2;Avoid and Reduce Fragmentation by Sharing SQL Statements;451
17.6.4.3;Avoid Setting _kghdsidx_count to 1;451
17.6.4.4;Avoid Flushing the Shared Pool;452
17.6.4.5;Avoid Shared Pool Reserved Free List Fragmentation;452
17.7;Library Cache Latch Contention;452
17.7.1;Common Causes of Library Cache Latch Contention;454
17.7.2;Analyzing Library Cache Latch Contention;454
17.7.2.1;Step 1: Identify Cursors Using Literal Values;454
17.7.2.2;Step 2: Understand the Reason for Unsharable Child Cursors;454
17.7.3;Library Cache Latches and Mutexes;455
17.7.4;Resolving Library Cache Latch Contention;455
17.7.4.1;Use Bind Variables;455
17.7.4.2;Avoid Flushing the Shared Pool;455
17.7.4.3;Adjust the session_cached_cursors Parameter;456
17.7.4.4;Adjust the cursor_space_for_time Parameter to True;456
17.7.4.5;Control and Limit Histograms;456
17.8;Enqueue Hash Chains Latch Contention;457
17.8.1;Common Causes of Enqueue Hash Chains Latch Contention;458
17.8.2;Analyzing Enqueue Hash Chains Latch Contention;459
17.8.2.1;Step 1: Review Distribution Among Child Latches.;459
17.8.2.2;Step 2: Identify the Enqueue Causing Latch Contention;459
17.8.2.3;Step 3: Identify Lock Types with High Gets;460
17.8.3;Resolving Enqueue Hash Chains Latch Contention;461
17.8.3.1;Avoid Excessive Short Transactions;461
17.8.3.2;Disable Table Level Locks;461
17.8.3.3;Reduce or Avoid Activity Against the Lock Type Causing Contention;462
17.9;Advanced Help for Latch Contention Problems;462
17.9.1;The v$latch_parent View;462
17.9.2;The spin_count Parameter;462
17.9.3;The _latch_classes and _latch_class_N Parameters;462
17.9.4;The _latch_wait_posting and _enable_reliable_latch_waits Parameters;463
17.10;Summary;463
18;Chapter 13: Measuring for Robust Performance;464
18.1;Finding the Red Rocks;465
18.2;Understanding the Properties of Performance;466
18.2.1;Response Time Is Key;466
18.2.2;Throughput Counts;466
18.2.3;Meeting Expectations Matters;467
18.2.4;All Together Now...;467
18.3;“Tuning” a Data Warehouse;468
18.3.1;Initial Tuning;468
18.3.2;Repeating the Analysis;470
18.4;Exploring What Variance Can Tell Us About a Process;471
18.4.1;Distribution Analysis;472
18.4.2;Distribution of Elapsed Time Data;475
18.4.3;Variance;475
18.4.4;The Index of Dispersion;476
18.4.5;What About Standard Deviation?;476
18.4.6;Elapsed Time Data Sources;477
18.5;Achieving Robust Performance;478
18.5.1;Designing an Experiment;479
18.5.2;Using Instrumentation;480
18.5.3;Measuring the Results;482
18.5.4;Tolerance Ranges and Process Capability;486
18.5.5;What is “Too Much” Variation?;487
18.5.6;Measuring Variance Within Oracle Sample Sets;487
18.5.7;Sampling from Samples;487
18.6;Summary;488
19;Chapter 14: User Security;490
19.1;Securing User Accounts;491
19.2;User Enumeration;492
19.2.1;Splitting the Task in Two;493
19.2.2;Dealing with Oracle Database 10g and Prior;495
19.3;Feature Analysis;498
19.3.1;Accounts That Can Definitely Be Removed;500
19.3.2;Accounts That Definitely Have to Remain in the Database;501
19.3.3;Accounts to Analyze Individually;502
19.4;Reduction of Accounts;505
19.5;Account Password Strength;510
19.5.1;Cracking Passwords with a PL/SQL Password Cracker;511
19.5.2;Cracking Passwords with a “Real” Password Cracker;513
19.5.3;Fixing Weak Passwords;520
19.6;Roles and Privilege Assessment;521
19.6.1;Have Accounts Been Used?;521
19.6.2;Have Accounts Been Shared?;522
19.7;Password Management;523
19.8;Audit Settings;526
19.9;Summary;527
19.10;Quiz Answer;528
20;Chapter 15: Securing Data;529
20.1;Identifying Key Data;530
20.2;Locating the Database Table;531
20.2.1;Direct Table Privileges;532
20.2.2;Understand the Hierarchy;535
20.2.3;Other Methods to Read Data;541
20.2.4;Access to Access;547
20.3;Duplicating Data;549
20.4;Generalizing Across the Database;552
20.5;Summary;553
20.6;Quiz Answer;554
21;Index;555
