E-Book, Englisch, 592 Seiten
Finnigan / Wilton / Gorbachev 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
This book is an anthology of effective database management techniques representing the collective wisdom of the OakTable Network. With an emphasis upon performance-but also branching into security, national language, and other issues-the book helps you deliver the most value for your company's investment in Oracle Database technologies. You'll learn to effectively plan for and monitor performance, to troubleshoot systematically when things go wrong, and to manage your database rather than letting it manage you.
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.
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
