Muutke küpsiste eelistusi

E-raamat: Oracle Database Performance and Scalability - A Quantitative Approach: A Quantitative Approach [Wiley Online]

Teised raamatud teemal:
  • Wiley Online
  • Hind: 116,25 €*
  • * hind, mis tagab piiramatu üheaegsete kasutajate arvuga ligipääsu piiramatuks ajaks
Oracle Database Performance and Scalability - A Quantitative Approach: A Quantitative ApproachSuurem pilt
Teised raamatud teemal:
Wiley Online e-raamatudRohkem infot Wiley Online kohta
Raamatu kodulehekülg: https://onlinelibrary.wiley.com/doi/book/10.1002/9781118135532
The innovative performance and scalability features with each newer edition of the Oracle database system can present challenges for users. This book teaches software developers and students how to effectively deal with Oracle performance and scalability issues throughout the entire life cycle of developing Oracle-based applications. Using real-world case studies to deliver key theories and concepts, the book introduces highly dependable and ready-to-apply performance and scalability optimization techniques, augmented with Top 10 Oracle Performance and Scalability Features as well as a supplementary support website.
Preface xxv
Why This Book xxv
Who This Book Is For xxvi
How This Book Is Organized xxvii
Software And Hardware xxviii
How To Use This Book xxix
How To Reach The Author xxxi
Acknowledgments xxxiii
Introduction 1(6)
Features of Oracle
2(2)
Objectives
4(1)
Conventions
5(1)
Performance versus Scalability
6(1)
Part 1 Getting Started With Oracle 7(68)
1 Basic Concepts
9(5)
1.1 Standard versus Flavored SQLS
10(1)
1.2 Relational versus Object-Oriented Databases
11(1)
1.3 An Instance versus a Database
11(1)
1.4 Summary
12(1)
Recommended Reading
12(1)
Exercises
12(2)
2 Installing Oracle Software
14(20)
2.1 Installing Oracle 11g Server Software
15(3)
2.2 Configuring a Listener
18(1)
2.3 Creating an Oracle Database
18(10)
2.4 Installing Oracle 11g Client Software
28(3)
2.5 Oracle Grid Control versus DB Control
31(2)
2.6 Summary
33(1)
Recommended Reading
33(1)
Exercises
33(1)
3 Options for Accessing an Oracle Server
34(18)
3.1 A Command Line Interface (CLI) versus a GUI-Based Console
35(2)
3.2 The Oracle Enterprise Manager Java Console (OEMJC)
37(3)
3.3 Using the SQL*Plus Tool
40(2)
3.4 Oracle Enterprise Manager DBConsole
42(1)
3.5 Other Tools for Developers
43(1)
3.6 Case Study: Creating ER Diagrams with Visio via ODBC
44(3)
3.7 Case Study: Accessing Oracle in Java via JDBC
47(2)
3.8 Summary
49(1)
Recommended Reading
50(1)
Exercises
50(2)
4 A Quick Tour of an Oracle Server
52(23)
4.1 New Oracle Schemas Beyond "Scott"
53(1)
4.2 Oracle Users versus Schemas
54(2)
4.3 Tablespaces, Segments, Extents, and Data Blocks
56(1)
4.4 Tables, Indexes and Index Types for Structured Data
57(8)
4.5 Domain and LOB Index Types for Unstructured Data
65(3)
4.6 Views, Materialized Views, and Synonyms
68(1)
4.7 Stored Procedures, Functions, and Triggers
68(3)
4.8 Referential Integrity with Foreign Keys
71(2)
4.9 Summary
73(1)
Recommended Reading
73(1)
Exercises
74(1)
Part 2 Oracle Architecture From Performance And Scalability Perspectives 75(340)
5 Understanding Oracle Architecture
79(22)
5.1 The Version History of Oracle
80(2)
5.2 Oracle Processes
82(5)
5.3 Oracle Memory Areas
87(2)
5.4 Dedicated versus Shared Oracle Server Architecture
89(2)
5.5 Performance Sensitive Initialization Parameters
91(3)
5.6 Oracle Static Data Dictionary Views
94(1)
5.7 Oracle Dynamic Performance (V$) Views
95(3)
5.8 Summary
98(1)
Recommended Reading
98(1)
Exercises
99(2)
6 Oracle 10g Memory Management
101(10)
6.1 SGA Sub-Areas
102(2)
6.2 SGA Sizing: Automatic Shared Memory Management (ASMM)
104(2)
6.3 PGA Sizing: PGA_AGGREGATE_TARGET
106(2)
6.4 Summary
108(1)
Recommended Reading
109(1)
Exercises
110(1)
7 Oracle 11g Memory Management
111(5)
7.1 Automatic Memory Management (AMM)
112(1)
7.2 Memory Sizing Options Configurable at Database Creation Time
112(1)
7.3 Checking Memory Management and Usage Distribution at Run Time
113(2)
7.4 Summary
115(1)
Recommended Reading
115(1)
Exercises
115(1)
8 Oracle Storage Structure
116(11)
8.1 Overview
117(2)
8.2 Managing Tablespaces
119(3)
8.3 Managing Data Files
122(2)
8.4 Managing Redo Logs
124(1)
8.5 Summary
125(1)
Recommended Reading
125(1)
Exercises
126(1)
9 Oracle Wait Interface (OWI)
127(12)
9.1 Ratio-based versus OWI-based Oracle Performance Tuning Methodologies
128(2)
9.2 Wait Event-The Core Concept of OWI
130(1)
9.3 Classification of Wait Events from OWI
131(3)
9.4 The Other Part (CPU Time) of the Equation Elapsed Time = CPU Time + Wait Time
134(2)
9.5 AWR as a Compass to Tuning Oracle Performance and Scalability
136(1)
9.6 Summary
137(1)
Recommended Reading
137(1)
Exercises
138(1)
10 Oracle Data Consistency and Concurrency
139(22)
10.1 Select...for Update Statement
140(1)
10.2 ACID Properties of Transactions
141(2)
10.3 Read Phenomena and Data Inconsistencies
143(2)
10.4 Oracle Isolation Levels
145(1)
10.5 Multi-Version Concurrency Control (MVCC) and Read Consistency
145(1)
10.6 Oracle Locks
146(3)
10.7 Lock Escalations versus Conversions
149(1)
10.8 Oracle Latches
149(1)
10.9 Oracle Enqueues
150(1)
10.10 Deadlocks
150(1)
10.11 Taking Advantage of Oracle's Scalable Concurrency Model
151(1)
10.12 Case Study: A JDBC Example
152(6)
10.13 Summary
158(1)
Recommended Reading
159(1)
Exercises
159(2)
11 Anatomy of an Oracle Automatic Workload Repository (AWR) Report
161(66)
11.1 Importance of Performance Statistics
162(3)
11.2 AWR Report Header
165(1)
11.3 Report Summary
166(5)
11.3.1 Cache Sizes
166(1)
11.3.2 Load Profile
167(2)
11.3.3 Instance Efficiency Percentages (Target 100%)
169(1)
11.3.4 Shared Pool Statistics
170(1)
11.3.5 Top Five Timed Events
170(1)
11.4 Main Report
171(1)
11.5 Wait Events Statistics
172(6)
11.5.1 Time Model Statistics
173(1)
11.5.2 Wait Class
174(1)
11.5.3 Wait Events
174(2)
11.5.4 Background Wait Events
176(1)
11.5.5 Operating System Statistics
176(1)
11.5.6 Service Statistics
177(1)
11.5.7 Service Wait Class Stats
178(1)
11.6 SQL Statistics
178(7)
11.6.1 SQL ordered by Elapsed Time
179(1)
11.6.2 SQL ordered by CPU Time
180(1)
11.6.3 SQL ordered by Gets
180(1)
11.6.4 SQL ordered by Reads
181(1)
11.6.5 SQL ordered by Executions
182(1)
11.6.6 SQL ordered by Parse Calls
183(1)
11.6.7 SQL ordered by Sharable Memory
183(1)
11.6.8 SQL ordered by Version Count
183(1)
11.6.9 Complete List of SQL Text
184(1)
11.7 Instance Activity Statistics
185(12)
11.7.1 Instance Activity Stats
185(11)
11.7.2 Instance Activity Stats-Absolute Values
196(1)
11.7.3 Instance Activity Stats-Thread Activity
197(1)
11.8 IO Stats
197(2)
11.8.1 Tablespace IO Stats
198(1)
11.8.2 File IO Stats
198(1)
11.9 Buffer Pool Statistics
199(1)
11.10 Advisory Statistics
199(7)
11.10.1 Instance Recovery Stats
200(1)
11.10.2 Buffer Pool Advisory
200(1)
11.10.3 PGA Aggr Summary
201(1)
11.10.4 PGA Aggr Target Stats
202(1)
11.10.5 PGA Aggr Target Histogram
202(1)
11.10.6 PGA Memory Advisory
203(1)
11.10.7 Shared Pool Advisory
204(1)
11.10.8 SGA Target Advisory
204(1)
11.10.9 Streams Pool Advisory
205(1)
11.10.10 Java Pool Advisory
205(1)
11.11 Wait Statistics
206(1)
11.12 Undo Statistics
207(1)
11.13 Latch Statistics
208(7)
11.13.1 Latch Activity
208(5)
11.13.2 Latch Sleep Breakdown
213(1)
11.13.3 Latch Miss Sources
214(1)
11.13.4 Parent and Child Latch Statistics
215(1)
11.14 Segment Statistics
215(3)
11.14.1 Segments by Logical Reads
215(1)
11.14.2 Segments by Physical Reads
216(1)
11.14.3 Segments by Row Lock Waits
217(1)
11.14.4 Segments by ITL Waits
217(1)
11.14.5 Segments by Buffer Busy Waits
217(1)
11.15 Dictionary Cache Stats
218(1)
11.16 Library Cache Activity
219(1)
11.17 Memory Statistics
219(3)
11.17.1 Process Memory Summary
219(1)
11.17.2 SGA Memory Summary
220(1)
11.17.3 SGA Breakdown Difference
221(1)
11.18 Streams Statistics
222(2)
11.19 Resource Limit Stats
224(1)
11.20 init.ora Parameters
224(1)
11.21 Summary
225(1)
Recommended Reading
225(1)
Exercises
226(1)
12 Oracle Advanced Features and Options
227(30)
12.1 Oracle 8i New Features
227(6)
12.1.1 Java
228(1)
12.1.2 Oracle interMedia, Spatial, Time Series, and Visual Image Retrieval
229(1)
12.1.3 Oracle Parallel Server
230(1)
12.1.4 Optimizer Plan Stability
230(1)
12.1.5 Locally Managed Tablespaces
230(1)
12.1.6 Online Index Creation and Rebuild
231(1)
12.1.7 Online Read-Only Tablespaces
231(1)
12.1.8 Temporary Tables
231(1)
12.1.9 Non-Blocking OCI (Oracle Call Interface)
231(1)
12.1.10 Function-Based Indexes
232(1)
12.1.11 Logical ROWIDs
232(1)
12.1.12 Enhanced Partitioning
232(1)
12.1.13 Connection Load Balancing
233(1)
12.1.14 Client Load Balancing
233(1)
12.1.15 Oracle Enterprise Manager
233(1)
12.2 Oracle 9i New Features
233(8)
12.2.1 Real Application Clusters (RAC)
234(2)
12.2.2 Data Guard
236(3)
12.2.3 Performance Tuning Intelligent Advisors
239(1)
12.2.4 Actual Operation-Level Query Statistics
239(1)
12.2.5 Dynamic Sampling of Optimizer Statistics
239(1)
12.2.6 Cloning Production Database with Oracle Enterprise Manager
240(1)
12.2.7 Renaming Columns and Constraints
241(1)
12.2.8 Dynamic Memory Pools
241(1)
12.2.9 Flashback Query
241(1)
12.2.10 List Partitioning
241(1)
12.3 Oracle 10g New Features
241(7)
12.3.1 Automatic Storage Management (ASM)
242(2)
12.3.2 Asynchronous Commit
244(1)
12.3.3 Database Replay
244(1)
12.3.4 Read Performance Statistics Directly from the SGA
245(1)
12.3.5 Automatic Workload Repository (AWR)
245(1)
12.3.6 Automatic Database Diagnostic Monitor (ADDM)
245(1)
12.3.7 Automatic Shared Memory Tuning
245(1)
12.3.8 Automatic Optimizer Statistics Gathering
245(2)
12.3.9 SQL Tuning Features
247(1)
12.3.10 Grid Computing
247(1)
12.4 Oracle 11g New Features
248(7)
12.4.1 Automatic Memory Management
249(1)
12.4.2 Intelligent Cursor Sharing
249(1)
12.4.3 Database Resident Connection Pool (DRCP)
249(1)
12.4.4 Server Result Cache
250(1)
12.4.5 Database Smart Flash Cache
251(1)
12.4.6 Database Replay SQL Performance Analyzer (SPA) Integration
252(1)
12.4.7 I/O Calibration
252(1)
12.4.8 Partitioning Enhancements
252(1)
12.4.9 SQL Plan Management
253(1)
12.4.10 Zero-Size Unusable Indexes and Index Partitions
254(1)
12.4.11 Invisible Indexes
254(1)
12.4.12 Virtual Columns
254(1)
12.5 Summary
255(1)
Recommended Reading
255(1)
Exercises
255(2)
13 Top 10 Oracle Performance and Scalability Features
257(9)
13.1 Real Application Clustering (RAC)
258(2)
13.2 Dedicated versus Shared Server Models
260(1)
13.3 Proven Transaction and Concurrency Models
260(1)
13.4 A Highly Efficient SQL Optimization Engine
261(1)
13.5 Efficient Parallel Processing with Modern Multi-Core CPUs
261(1)
13.6 Partitioning
262(1)
13.7 An All-Encompassing, Powerful Performance, and Scalability Troubleshooting Tool-AWR
262(1)
13.8 The Most Comprehensive Set of Internal Performance Metrics
263(1)
13.9 Database Resident Connection Pool
263(1)
13.10 In-Memory Database Cache (IMDB)
263(1)
13.11 Summary
263(1)
Recommended Reading
264(1)
Exercises
264(2)
14 Oracle-Based Application Performance and Scalability by Design
266(60)
14.1 Rapid Development Methodologies
268(1)
14.2 Planning
269(3)
14.2.1 Vision
269(1)
14.2.2 Objectives
270(1)
14.2.3 ROI Analysis
270(1)
14.2.4 Feasibility Study
271(1)
14.2.5 Project Team Formation
271(1)
14.3 Requirements Gathering
272(3)
14.3.1 Use Cases
273(1)
14.3.2 User Views
274(1)
14.3.3 Business Processes, Entities, and Business Rules
274(1)
14.4 Conceptual Design via Data Modeling
275(5)
14.4.1 Entity-Relationship Diagramming
276(2)
14.4.2 The Information Engineering (IE) Format for ERDs
278(1)
14.4.3 UML Format for ERDs
279(1)
14.4.4 Relational Format for ERDs
279(1)
14.5 Logical Design via Normalization
280(15)
14.5.1 Operational Anomalies
281(1)
14.5.2 Review of Relation Theory
282(3)
14.5.3 Functional Dependencies and Lossless-Join Decompositions
285(2)
14.5.4 First Normal Form (1NF): Avoiding Multi-Valued Columns
287(1)
14.5.5 Second Normal Form (2NF): Eliminating Partial Dependencies
288(1)
14.5.6 Third Normal Form (3NF): Eliminating Transitive Dependencies:
288(1)
14.5.7 Boyce-Codd Normal Form (BCNF): Eliminating Key-Non-Key Dependencies
289(1)
14.5.8 Fourth Normal Form (4NF): Trivializing or Keying Multi-Valued Dependencies
290(2)
14.5.9 Fifth Normal Form (5NF): Trivializing or Keying Join Dependencies
292(2)
14.5.10 Which Level of Normalization to Settle Down?
294(1)
14.5.11 Denormalization?
294(1)
14.6 Physical Design
295(20)
14.6.1 Naming Conventions
297(1)
14.6.2 Creating Tablespaces
298(1)
14.6.3 Creating a Schema User with Proper Privileges
299(1)
14.6.4 Creating Application Schema Objects
299(9)
14.6.5 Changing Schema Objects
308(1)
14.6.6 Enforcing Business Rules and Data Integrity
309(3)
14.6.7 Adding Views
312(1)
14.6.8 Creating Sequences and Synonyms
312(1)
14.6.9 Adding Indexes
313(1)
14.6.10 Security
314(1)
14.7 Implementation
315(7)
14.7.1 Choosing an Effective and Efficient Coding Path
315(1)
14.7.2 Leveraging Proven Oracle Database Design Principles
316(2)
14.7.3 Leveraging Proven Application Design Patterns
318(1)
14.7.4 Enforcing with an Effective and Efficient Testing Process
319(3)
14.8 Release To Market (RTM)
322(1)
14.9 Continuous Improvements
322(1)
14.10 Summary
323(1)
Recommended Reading
324(1)
Exercises
325(1)
15 Project: Soba-A Secure Online Banking Application on Oracle
326(89)
15.1 Getting SOBA Up and Running
328(5)
15.1.1 Prerequisite Software
328(1)
15.1.2 Initial Software Stack Setup
329(1)
15.1.3 Creating SOBA Database on Oracle
330(1)
15.1.4 Installing SOBA on Eclipse IDE
330(1)
15.1.5 Configuring SOBA to Work with Oracle
331(2)
15.1.6 Configuring SOBA to Work with Hibernate
333(1)
15.1.7 Building SOBA and Deploying SOBA with Ant to Run on Tomcat
333(1)
15.2 Overview of Spring Framework
333(4)
15.2.1 Background
333(1)
15.2.2 Spring for Building Flexible Applications Faster
334(1)
15.2.3 Spring Inversion of Control (IoC) and Dependency Injection
335(1)
15.2.4 Features of Spring 3.0
336(1)
15.3 MVC Architecture
337(5)
15.3.1 MVC Architecture in General
338(2)
15.3.2 Spring MVC in Action with SOBA
340(2)
15.4 Spring MVC Framework Applied to SOBA
342(26)
15.4.1 Spring DispatcherServlet and WebApplicationContext
343(4)
15.4.2 Logic Flow of SOBA Defined in Spring MVC Framework
347(1)
15.4.3 A Web Entry Point Defined in a Spring MVC Web Form
348(2)
15.4.4 Handler Mapping
350(3)
15.4.5 Implementing Spring Controllers
353(5)
15.4.6 A Typical View Defined in a Spring MVC Web Form
358(4)
15.4.7 A Typical Form Success Controller and its Resultant View
362(2)
15.4.8 POJOs Referenced in the CreateCustomerFormController
364(4)
15.5 Hibernate Object-Relational Mapping (ORM) Applied to SOBA
368(8)
15.5.1 Benefits of Using Hibernate
369(1)
15.5.2 Metadata Mapping with Hibernate
370(1)
15.5.3 Configuring Hibernate to Work with Oracle
371(2)
15.5.4 Hibernate DAO
373(3)
15.6 RESTful Web Services Applied to SOBA
376(10)
15.6.1 Introduction to RESTful Web Services
376(1)
15.6.2 RESTful Constraints
377(1)
15.6.3 RESTful Interface Design Principles
378(1)
15.6.4 Spring's Support for RESTful Web Services
379(1)
15.6.5 Server Code
380(3)
15.6.6 Client Code
383(3)
15.7 Spring Security Applied to SOBA
386(8)
15.7.1 Basic Concepts
387(1)
15.7.2 Security Configured in web.xml
387(1)
15.7.3 Security Configured in soba-security.xml
388(6)
15.7.4 Implementing Spring Security in Views
394(1)
15.8 Spring ACL Applied to SOBA
394(19)
15.8.1 Creating ACL Tables in Oracle
395(1)
15.8.2 Configuring Spring ACL
395(3)
15.8.3 Maintaining ACLs for SOBA Domain Objects
398(6)
15.8.4 Applying ACLs to Business Operations
404(2)
15.8.5 Testing ACLs with SOBA
406(7)
15.9 Summary
413(1)
Recommended Reading
414(1)
Exercises
414(1)
Part 3 Optimizing Oracle Performance And Scalability 415(58)
16 Logistics of the Oracle Cost-Based Optimizer (CBO)
417(14)
16.1 Life of a SQL Statement in Oracle
418(2)
16.2 Oracle SQL Optimizer: Rule-Based versus Cost-Based
420(1)
16.3 CBO Statistics
421(1)
16.4 Pivot Role of Gathering Database Statistics to CBO
422(2)
16.5 Methods of Gathering CBO Statistics
424(1)
16.6 Locking and Unlocking CBO Statistics
425(1)
16.7 Explain Plan-A Handle to CBO
425(1)
16.8 Data Access Methods-CBO's Footprints
426(1)
16.9 Looking Up CBO's Plan Hidden in V$SQL_PLAN
427(1)
16.10 When CBO may Generate Suboptimum Execution Plans
428(1)
16.11 Summary
429(1)
Recommended Reading
429(1)
Exercises
430(1)
17 Oracle SQL Tuning
431(18)
17.1 Tuning Joins
432(5)
17.2 Tuning Subqueries
437(2)
17.3 Case Study: Performance of SUBQUERY versus JOIN
439(4)
17.4 Case Study: Performance of IN versus EXISTS
443(1)
17.5 Case Study: A SQL Tuning Yielded a 12x Performance Gain
444(3)
17.6 Summary
447(1)
Recommended Reading
447(1)
Exercises
448(1)
18 Oracle Indexing
449(10)
18.1 Rules of Thumb on Indexing
450(1)
18.2 Creating and Using Ubiquitous b-Tree Indexes
451(1)
18.3 Advanced Indexing Scheme I: Covering Indexes versus Index-Organized Tables
452(1)
18.4 Advanced Indexing Scheme II: Function-Based Indexes (FBIs)
453(1)
18.5 Unusual Indexing Scheme I: BITMAP Indexes
454(1)
18.6 Unusual Indexing Scheme II: Reverse Key Indexes
455(1)
18.7 Unusual Indexing Scheme III: Compressed Composite Indexes
455(1)
18.8 How To Create Oracle Indexes
456(1)
18.9 Summary
457(1)
Recommended Reading
458(1)
Exercises
458(1)
19 Auto_Tune Features
459(14)
19.1 Oracle Automatic Database Diagnostic Monitor (ADDM)
460(2)
19.2 Automatic Undo Management
462(1)
19.3 Data Recovery Advisor
462(1)
19.4 Memory Advisors
462(4)
19.5 MTTR Advisor
466(1)
19.6 Segment Advisor
466(1)
19.7 SQL Advisors
467(2)
19.8 SQL Performance Analyzer
469(1)
19.9 Summary
470(1)
Recommended Reading
471(1)
Exercises
471(2)
Part 4 Case Studies: Oracle Meeting Real World Performance And Scalability Challenges 473(160)
20 Case Study: Achieving High Throughput with Array Processing
477(8)
20.1 Context
478(1)
20.2 Performance Model
479(1)
20.3 Tests
480(1)
20.4 Solution
480(2)
20.5 Effects of Array Processing
482(2)
20.6 Summary
484(1)
Recommended Reading
484(1)
Exercises
484(1)
21 Case Study: Performance Comparison of Heap-Organized versus Index-Organized Tables
485(7)
21.1 Context
486(1)
21.2 Conversion from Heap-Organized to Index-Organized
487(1)
21.3 Creating Indexes
487(1)
21.4 Creating Constraints
488(1)
21.5 EXPLAIN PLANs
488(1)
21.6 Oracle SQL Traces
489(1)
21.7 Summary
490(1)
Recommended Reading
491(1)
Exercises
491(1)
22 Case Study: SQL Tuning: "IN" versus "OR" versus Global Temporary Table
492(12)
22.1 Context
493(1)
22.2 Test Program
494(1)
22.3 Observation 1: IN_CreateStatement is the Best Performer
495(2)
22.4 Observation 2: Batch Insert Saves Time
497(1)
22.5 Temptable Performed Better without an Index Hint than with an Index Hint
498(1)
22.6 Effects of APPEND Hint for Populating Temptable
499(1)
22.7 Effects of Number of Iterations
499(1)
22.8 OR and IN without the Index Hint
499(2)
22.9 Limitation on the Number of Literal Values and the Size of OR Statement
501(1)
22.10 Dealing with More Than 1000 Literal Values for an IN Based SQL Query
501(1)
22.11 A Recommendation for Dealing with 1000 Literal Value Limit in an IN Statement
501(1)
22.12 Summary
502(1)
Recommended Reading
503(1)
Exercises
503(1)
23 Case Study: Data Access Paths (Double Buffering)
504(27)
23.1 Data Access Paths in General
505(6)
23.1.1 Data Buffering
507(2)
23.1.2 Inode Locking
509(1)
23.1.3 Write-Sync Daemon
510(1)
23.2 Test Environments
511(3)
23.2.1 Solaris on Veritas
511(1)
23.2.2 Solaris on UFS
511(1)
23.2.3 Windows on NTFS
512(2)
23.3 Test Results with Solaris on Veritas
514(8)
23.3.1 Test Run #1-145 ms Average Read Time
514(2)
23.3.2 Test Run #2-401 ms Average Read Time
516(2)
23.3.3 Test Run #3-261 ms Average Read Time
518(1)
23.3.4 Test Run #4-0.98 ms Average Read Time
519(2)
23.3.5 Analysis
521(1)
23.4 Test Results with Solaris on UFS
522(4)
23.4.1 Test Run #1-447 ms Average Read Time
522(2)
23.4.2 Test Run #2-10ms Average Read Time
524(1)
23.4.3 Analysis
525(1)
23.5 Test Results with Windows on NTFS
526(2)
23.5.1 Test Run-8 ms Average Read Time
526(2)
23.5.2 Analysis
528(1)
23.6 Moral of the Case Study
528(1)
Recommended Reading
529(1)
Exercises
530(1)
24 Case Study: Covering Index
531(16)
24.1 Getting to Know the Application Architecture
533(1)
24.2 Quantifying the Problems
533(1)
24.3 Analyzing Bottlenecks
533(2)
24.4 Applying Optimizations Tunings
535(1)
24.5 Verifying the Fixes
535(10)
24.5.1 Report Summary
537(1)
24.5.2 Wait Events Statistics
538(3)
24.5.3 SQL Statistics
541(3)
24.5.4 IO Stats
544(1)
24.5.5 Buffer Pool Statistics
544(1)
24.5.6 Wait Statistics
544(1)
24.5.7 init.ora Parameters
545(1)
24.6 Moral of the Case Study
545(1)
Recommended Reading
546(1)
Exercises
546(1)
25 Case Study: CURSOR_SHARING
547(24)
25.1 The Concept of a Bind Variable
548(1)
25.2 Oracle CURSOR_SHARING Parameter
549(1)
25.3 Getting to Know the Application Architecture
550(1)
25.4 Quantifying Problems
550(1)
25.5 Analyzing Bottlenecks
551(9)
25.5.1 Report Summary
552(4)
25.5.2 SQL Statistics
556(1)
25.5.3 ID Stats
557(1)
25.5.4 Wait Statistics
558(1)
25.5.5 init.ora Parameters
558(2)
25.6 Applying Tuning: CURSOR_SHARING = FORCE
560(4)
25.6.1 Report Summary
561(2)
25.6.2 Wait Events Statistics
563(1)
25.7 Applying Tuning: CURSOR_SHARING = SIMILAR
564(5)
25.7.1 Report Summary
564(2)
25.7.2 Wait Events Statistics
566(3)
25.8 Moral of the Case Study
569(1)
Recommended Reading
569(1)
Exercises
570(1)
26 Case Study: Bulk Transactions
571(23)
26.1 Application Architecture
572(1)
26.2 Quantifying Problems
572(1)
26.3 Identifying Performance and Scalability Optimization Opportunities
573(8)
26.3.1 Report Summary
573(2)
26.3.2 Wait Events Statistics
575(2)
26.3.3 SQL Statistics
577(2)
26.3.4 Wait Statistics
579(2)
26.4 Effects of Bulk Transactions on Performance
581(11)
26.4.1 Report Summary
581(2)
26.4.2 Wait Events Statistics
583(2)
26.4.3 SQL Statistics
585(2)
26.4.4 Wait Statistics
587(5)
26.5 Moral of the Case Study
592(1)
Recommended Reading
593(1)
Exercises
593(1)
27 Case Study: Missing Statistics
594(26)
27.1 Decaying Performance due to Missing Statistics
595(2)
27.2 First Run with no Statistics
597(7)
27.2.1 Report Summary
598(1)
27.2.2 Wait Events Statistics
599(2)
27.2.3 SQL Statistics
601(1)
27.2.4 IO Stats
602(1)
27.2.5 Wait Statistics
602(1)
27.2.6 init.ora Parameters
603(1)
27.3 Second Run with Missing Statistics
604(7)
27.3.1 Report Summary
605(1)
27.3.2 Wait Events Statistics
606(1)
27.3.3 SQL Statistics
607(2)
27.3.4 IO Stats
609(1)
27.3.5 Wait Statistics
609(2)
27.4 Third Run with Updated Statistics
611(7)
27.4.1 Report Summary
611(2)
27.4.2 Wait Events Statistics
613(1)
27.4.3 Operating System Statistics
614(1)
27.4.4 SQL Statistics
614(2)
27.4.5 Wait Statistics
616(2)
27.5 Moral of the Case Study
618(1)
Recommended Reading
618(1)
Exercises
618(2)
28 Case Study: Misconfigured SAN Storage
620(13)
28.1 Architecture of the Apple's Xserve RAID
621(1)
28.2 Problem Analysis
622(4)
28.2.1 Report Summary
622(2)
28.2.2 Wait Events Statistics
624(1)
28.2.3 IO Stats
625(1)
28.2.4 init.ora Parameters
625(1)
28.3 Reconfiguring the RAID and Verifying
626(3)
28.3.1 Report Summary
626(2)
28.3.2 Wait Events Statistics
628(1)
28.3.3 IO Stats
629(1)
28.4 Moral of the Case Study
629(1)
Recommended Reading
630(1)
Exercises
630(3)
Appendix A Oracle Product Documentations 633(3)
A.1 Oracle Database Concepts
633(1)
A.2 Oracle Database Administrator's Guide
633(1)
A.3 Oracle Database Reference
634(1)
A.4 Oracle Database Performance Tuning Guide
634(1)
A.5 Oracle Database 2 Day + Performance Tuning Guide
634(1)
A.6 Oracle Database 2 Day DBA
634(1)
A.7 Oracle Database SQL Language Reference
634(1)
A.8 Oracle Database Sample Schemas
635(1)
A.9 Oracle Database PL/SQL Packages and Types Reference
635(1)
A.10 Oracle Database PL/SQL Language Reference
635(1)
A.11 Oracle Database JDBC Developer's Guide and References
635(1)
Appendix B Using SQL*PLUS With Oracle 636(12)
B.1 Installation
636(1)
B.2 SQL*Plus and tnsnames.ora File
637(1)
B.3 Basics of SQL*Plus
638(1)
B.4 Common SQL*Plus Commands
638(1)
B.5 Using SQL*Plus to Execute SQL Statements
639(1)
B.6 Using SQL*Plus to Execute PL SQL Blocks
640(1)
B.7 Using SQL*Plus Autotrace to Obtain EXECUTION PLANs and Optimizer Statistics
640(1)
B.8 Using SQL*Plus Timing Command
641(1)
B.9 Exporting/Importing Oracle Databases with SQL*Plus
642(1)
B.10 Creating AWR Reports with SQL*Plus
643(1)
B.11 Checking Tablespace Usage with SQL*Plus
644(2)
B.12 Creating EM DBConsole with SQL*Plus
646(2)
Appendix C A Complete List Of All Wait Events In Oracle 11g 648(8)
Appendix D A Complete List Of All Metrics With The V$Statname View 656(11)
Appendix E A Complete List Of All Statistics With The V$Sysstat View 667(14)
Index 681
HENRY H. LIU, PHD, is a Software Developer at BMC Software, Inc., with a physicist background from his prior career. His primary responsibilities at BMC include helping build performance and scalability into BMC's cloud computing and enterprise service management software products. Dr. Liu is the author of the highly acclaimed Software Performance and Scalability: A Quantitative Approach (Wiley).
Püsilink: https://www.kriso.ee/db/9781118135532_pe.html
Märksõnad: