Preface ....................................................... vii
Part 1. Methodology ............................................. 1
1. Database Application Development ............................. 3
Outline ...................................................... 3
Overview of the Chapter ...................................... 3
1.1. 1970s Software Technology Era ........................... 4
1.2. Role of the Database in SDLC and SMLC ................... 6
1.3. Enterprise Modeling ..................................... 7
1.4. Logical Database Design ................................ 11
1.5. Physical Database Design ............................... 15
1.6. Database Implementation ................................ 18
1.7. Database Maintenance ................................... 20
1.8. Naming Guidelines for Database Objects ................. 20
Keywords .................................................... 25
References and Further Reading .............................. 26
Exercises ................................................... 27
2. Performance Tuning Methodology .............................. 29
Outline ..................................................... 29
Overview of the Chapter ..................................... 29
2.1. Three Levels of a Database ............................. 29
2.2. Optimization at Each Level ............................. 31
2.3. Process and Metric for Performance Tuning .............. 33
Keywords .................................................... 38
References and Further Reading .............................. 38
Exercises ................................................... 39
3. Tuning the Conceptual Level of a Database ................... 41
Outline ..................................................... 41
Overview of the Chapter ..................................... 41
3.1 Three Versions of the Conceptual Level .................. 42
3.2. Performance Issues at the Conceptual Level ............. 43
3.3. Denormalization of the Conceptual Level ................ 43
3.4. Optimal Indexing of Tables ............................. 47
3.5. Integration of Views into Queries ...................... 48
3.6. Partitioning of Tables and Indices ..................... 48
3.7. Data Replication ....................................... 52
Keywords .................................................... 52
References and Further Reading .............................. 53
Exercises ................................................... 54
Part 2. Oracle Tools for Tuning and Optimization ............... 55
4. Internal Level of an Oracle Database ........................ 57
Outline ..................................................... 57
Overview of the Chapter ..................................... 57
4.1. Components of the Internal Level ....................... 58
4.2. Oracle Instance ........................................ 59
4.3. Oracle Database ........................................ 67
4.4. Internal Level Tuning Methodology ...................... 70
4.5. Oracle Data Dictionary ................................. 70
4.6. V$ Views and X$ Tables ................................. 74
4.7. Initialization Parameters for an Oracle Database ....... 77
Keywords .................................................... 79
References and Further Reading .............................. 80
Exercises ................................................... 80
5. Tuning of Disk Resident Data Structures ..................... 81
Outline ..................................................... 81
Overview of the Chapter ..................................... 81
5.1. Disk-Resident Data Structures .......................... 82
5.2. Performance Tuning of Disk-Resident Data Structures .... 82
5.3. Baseline of Disk-Resident Data Structures .............. 82
5.3.3 Segments with Information on Extents ............. 86
5.4. Changes to Database Schema ............................. 95
5.5. Data Block Structure ................................... 96
5.6. Used Space Fragmentation at the Segment Level .......... 96
5.7. Severity of Free Space Shortage ....................... 101
5.8. Free Space Fragmentation atTablespace Level ........... 105
5.9. Row Chaining and Row Migration in Tables .............. 110
5.10.Performance Tuning of Rollback Segments ............... 115
Keywords ................................................... 134
References and Further Reading ............................. 135
Exercises .................................................. 135
6. Tuning of Memory-Resident Data Structures .................. 137
Outline .................................................... 137
Overview of the Chapter .................................... 137
6.1. Memory Resident Data Structures ....................... 138
6.2. Performance Tuning .................................... 138
6.3. Data Block Buffers .................................... 139
6.4. Redo Log Buffer ....................................... 146
6.5. Shared SQL Pool ....................................... 148
6.6. Background Processes .................................. 156
6.7. Tuning the Memory ..................................... 166
6.8. Tuning the CPU ........................................ 175
6.9. Pinning Packages in Memory ............................ 176
6.10.Latching Mechanism for Access Control ................. 178
Keywords ................................................... 184
References and Further Reading ............................. 185
Exercises .................................................. 186
7. Oracle Utility for Tuning and Optimization ................. 189
Outline .................................................... 189
Overview of the Chapter .................................... 189
7.1. Scope of Oracle Utilities ............................. 189
7.2. UTLBSTAT and UTLESTAT Utilities ....................... 190
7.3. Location and Function of the Utility Scripts .......... 190
7.4. Procedure for Running the Utilities ................... 203
7.5. UTLBSTAT/ESTAT Performance Report Analysis ............ 204
7.6. Comprehensive Tuning Plan for Internal Level .......... 224
7.7. Performance Tracking .................................. 224
7.8. Tuning Activities ..................................... 225
Keywords ................................................... 226
References and Further Reading ............................. 226
Exercises .................................................. 227
8. Optimization of the External Level of a Database ........... 229
Outline .................................................... 229
Overview of the Chapter .................................... 229
8.1. Contents of the External Level ........................ 230
8.2. Principles of Query Optimization ...................... 230
8.3. Query Optimization in Oracle .......................... 232
8.4. Optimal Indexing Guidelines ........................... 235
8.5. Methodology for Optimal Indexing ...................... 239
8.6. Implementation of Indices in Oracle ................... 245
8.7 Tools for Tracking Query Performance ................... 263
Keywords ................................................... 279
References and Further Reading ............................. 280
Exercises .................................................. 281
9. Query Tuning and Optimization Under Oracle 8i .............. 283
Outline .................................................... 283
Overview of the Chapter .................................... 283
9.1. Oracle Query Performance .............................. 283
9.2. Query Tuning in Oracle: General Principles ............ 284
9.3. Query Tuning in Oracle: Cost-based Optimizer .......... 292
9.4. Query Tuning in Oracle: Rule-based Optimizer .......... 299
9.5. Tuning of Join Queries ................................ 301
9.6. Statistical Forecasting for Tracking Performance ...... 310
Keywords ................................................... 312
References and Further Reading ............................. 312
Exercises .................................................. 313
10.Special Features of Oracle 8i and a Glimpse into
Oracle 9i .................................................. 315
Outline .................................................... 315
Overview of the Chapter .................................... 315
10.1.Scope of the Chapter .................................. 316
10.2.Evolution of Oracle Through Versions 8 and 8i ......... 316
10.3.Partitioning of Tables and Indices .................... 317
10.4.Materialized Views .................................... 323
10.5.Defragmentation via Local Tablespace Management ....... 325
10.6.LOB Data Type Versus LONG Data Type ................... 332
10.7.Multiple Buffer Pools in the SGA ...................... 333
10.8.Query Execution Plan Stability via Stored Outlines .... 338
10.9.Index Enhancements .................................... 342
10.10.Query Rewrite for Materialized Views ................. 346
10.11.Online Index Creation, Rebuild, Defragmentation ...... 347
10.12.ANALYZE versus DBMS_STATS ............................ 348
10.13.Optimization of Top_N Queries ........................ 351
10.14.Glimpse into Oracle 9i ............................... 352
Keywords ................................................... 357
References and Further Reading ............................. 357
Part 3. Contemporary Issues ................................... 359
11.Tuning the Data Warehouse at All Levels .................... 361
Outline .................................................... 361
Overview of the Chapter .................................... 361
11.1.Advent of Data Warehouse .............................. 362
11.2.Features of Data Warehouse ............................ 362
11.3.Design Issues of Data Warehouse ....................... 363
11.4.Structure of Data Warehouse ........................... 365
11.5.Proliferation from Data Warehouse ..................... 366
11.6.Metadata .............................................. 368
11.7.Implementation and Internal Level ..................... 369
11.8.Data Loading in Warehouse ............................. 371
11.9.Query Processing and Optimization ..................... 374
Keywords ................................................... 382
References and Further Reading ............................. 382
Exercises .................................................. 383
12.Web-Based Database Applications ............................ 385
Outline .................................................... 385
Overview of the Chapter .................................... 385
12.1.Advent of Web-Based Applications ...................... 386
12.2.Components of Web-Based Applications .................. 387
12.3.Oracle Application Server (OAS) ....................... 389
12.4.Database Transaction Management Under OAS ............. 393
12.5.Oracle Internet Application Server (iAS) .............. 394
12.6.Performance Tuning of Web-Based Databases ............. 394
12.7.Tuning of Internal Level .............................. 395
12.8.Tuning of External Level .............................. 397
Keywords ................................................... 397
References and Further Reading ............................. 397
Exercises .................................................. 398
Appendices .................................................... 399
Appendix A. Sizing Methodology in Oracle 8i ................... 401
Outline .................................................... 401
Overview of the Appendix ................................... 401
Al.Transition from Logical to Physical Database Design ..... 401
A2.Space Usage via Extents ................................. 402
A3.Algorithms for Sizing Tables, Indices, Tablespaces ...... 403
A4.STORAGE Clause Inclusion: Table and Index Levels ........ 407
A5.Sizing Methodology ...................................... 407
A6.RBS, SYSTEM, TEMP, and TOOLS Tablespace Sizing .......... 408
Keywords ................................................... 411
References and Further Reading ............................. 411
Appendix В. Instance and Database Creation .................... 413
Outline .................................................... 413
Overview of the Appendix ................................... 413
Bl.Preparation ............................................. 413
B2.Instance Startup ........................................ 416
В3.Database Creation ....................................... 417
B4.Creation of Users, Roles, Privileges, Schema ............ 417
B5.Miscellaneous Informational Items ....................... 418
Attachments ................................................ 418
Appendix С. Instance and Database Removal ..................... 433
Outline .................................................... 433
Overview of the Appendix ................................... 433
CI.Preparation ............................................. 433
C2.Locating the Components ................................. 434
C3.Removing the Components ................................. 436
C4.Verification ............................................ 437
Appendix D. Database Refresh with Transportable
Tablespaces ................................................... 439
Outline .................................................... 439
Overview of the Appendix ................................... 439
Dl.Database Refresh Process ................................ 439
D2.Detailed Methodology with Scripts ....................... 440
D3.Time Estimates .......................................... 444
D4.Internal Consistency .................................... 445
Keywords ................................................... 447
Appendix E. Mathematical Foundation of Relational
Databases ..................................................... 449
Outline .................................................... 449
Overview of the Appendix ................................... 449
El.Relational Database Systems Foundation Pillars .......... 450
E2.Relation ................................................ 450
E3.Functional Dependency ................................... 451
E4.Query Languages ......................................... 453
E5.Relational Algebra: Prescriptive Query Languages ........ 454
E6.Primitive and Derived Operations ........................ 458
E7.Closure Property for Relational Algebra ................. 459
E8.Relational Calculus: Descriptive Query Languages ........ 460
E9.Tuple Relational Calculus ............................... 462
Е10.Domain Relational Calculus ............................. 464
Ell.Equivalence Theorem for Algebra and Calculus ........... 465
E12.Data Structures for Search Algorithms .................. 467
E13.Linear Linked List ..................................... 467
E14.Search Tree ............................................ 469
E15.Hash Table ............................................. 472
E16.Performance Metrics .................................... 477
Keywords ................................................... 478
References and Further Reading ............................. 479
Index ...................................................... 481
|
