Expert PL/SQL Practices: for Oracle Developers and DBAs

Cover......Page 1
Contents at a Glance......Page 3
Contents......Page 490
About the Authors......Page 502
About the Technical Reviewers......Page 506
Introduction......Page 507
Row-by-Row Processing......Page 4
Nested Row-by-Row Processing......Page 7
Lookup Queries......Page 8
Arithmetics with Date......Page 11
Access to Sequences......Page 12
Excessive Function Calls......Page 13
Unnecessary Function Execution......Page 14
Costly Function Calls......Page 16
Database Link Calls......Page 19
Excessive Parsing......Page 20
Summary......Page 21
Dynamic SQL: Handling the Unknown......Page 22
The Hero......Page 23
Native Dynamic SQL......Page 24
Dynamic Cursors......Page 27
DBMS_SQL......Page 30
Sample of Dynamic Thinking......Page 31
Security Issues......Page 36
Performance and Resource Utilization......Page 40
Anti-Patterns......Page 41
Comparing Implementations of Dynamic SQL......Page 42
Negative Effects......Page 44
Positive Effects......Page 45
Summary......Page 46
Why Parallel Processing?......Page 47
Laws Affecting Parallel Processing......Page 48
Parallel Hardware Architectures......Page 49
Identify Your Goals......Page 50
Degree of Parallelism......Page 51
Parallelism and Non-OLTP Workloads......Page 52
Before Looking to PL/SQL......Page 53
Pipelined Table Functions......Page 54
Summary......Page 71
Basics......Page 73
Using Warnings......Page 75
Promoting Warnings to Errors......Page 78
Ignoring Warnings......Page 80
Compilation and Warnings......Page 82
Final Words on Warnings......Page 84
Basics......Page 85
What Part of the Code is Running?......Page 88
Benefits of Preprocessing Code......Page 89
Invalidations......Page 91
Controlling Compilation......Page 93
Inquiry Variables......Page 95
Final Words on Conditional Compilation......Page 96
Summary......Page 98
Why Test Your Code?......Page 99
When Should You Build Tests?......Page 100
Quest Code Tester for Oracle......Page 101
Preparing and Maintaining the Unit Test Environment......Page 102
Creating the Unit Test Repository......Page 103
Maintaining the Unit Test Repository......Page 104
Building Unit Tests......Page 105
Creating the First Implementation......Page 106
Adding Startup and Teardown Processes......Page 107
Specifying Parameters......Page 108
Adding Process Validation......Page 109
Debugging and Running Tests......Page 110
Creating Lookup Values......Page 111
Seeding Test Implementations......Page 113
Creating a Dynamic Query......Page 114
Running Reports......Page 115
Creating a Library of Components......Page 116
Exporting, Importing, and Synchronizing Tests......Page 119
Running Tests from the Command Line......Page 121
Summary......Page 122
The Hardware Store......Page 123
Setting for the Examples in this Chapter......Page 124
Bulk Operations in PL/SQL......Page 125
Getting Started with BULK Fetch......Page 126
Three Collection-Style Datatypes......Page 129
Why should I bother?......Page 130
Monitoring Bulk Collect Overheads......Page 133
Refactoring Code to Use Bulk Collect......Page 137
Bulk Binding......Page 145
Getting Started with Bulk Bind......Page 146
Measuring Bulk Binding Performance......Page 147
Monitoring Memory Usage......Page 150
Improvements in 11g......Page 152
Error Handling with Bulk Bind......Page 153
SAVE EXCEPTIONS with Batches......Page 157
LOG ERRORS Clause......Page 158
Robust Bulk Bind......Page 159
A Justification for Massive Collections......Page 164
The Real Benefit: Client Bulk Processing......Page 166
Summary......Page 170
What This Chapter Will (and Will Not) Cover......Page 172
Static Analysis......Page 174
When to Analyze?......Page 175
Performing Static Analysis......Page 176
The Data Dictionary......Page 177
PL/SCOPE......Page 184
DBMS_PROFILER and DBMS_TRACE......Page 198
DBMS_HPROF......Page 207
Summary......Page 213
Software Contracts......Page 214
Basic Contract Elements......Page 215
References......Page 216
Basic ASSERT Procedure......Page 217
Standard Package-Local ASSERT......Page 219
Enforcing Contracts Using ASSERT......Page 221
An Additional Improvement......Page 223
Contract-Oriented Function Prototype......Page 224
Example: Testing Odd and Even Integers......Page 225
Useful Contract Patterns......Page 227
FUNCTION RETURN Not-NULL......Page 228
Check Functions: RETURN TRUE OR ASSERTFAIL......Page 229
Assert Preconditions Rigorously......Page 230
Modularize Ruthlessly......Page 231
Regression Test Your Postconditions......Page 232
Avoid Correctness-Performance Tradeoffs......Page 233
Summary......Page 234
The Cost of Using PL/SQL Functions in SQL......Page 235
Context-Switching......Page 236
Executions......Page 242
Suboptimal Data Access......Page 245
Optimizer Difficulties......Page 249
The Read-Consistency Trap......Page 253
Other Issues......Page 255
A Sense of Perspective......Page 256
Using SQL Alternatives......Page 257
Reducing Executions......Page 264
Assisting the CBO......Page 273
Tuning PL/SQL......Page 285
Summary......Page 289
Choosing the Right Cursor......Page 290
Explicit Cursors......Page 291
The Anatomy of an Explicit Cursor......Page 292
Explicit Cursors and Bulk Processing......Page 293
REF Cursors in Brief......Page 294
Implicit Cursors......Page 295
The Anatomy of an Implicit Cursor......Page 296
The Implicit Cursor and the Extra Fetch Theory......Page 298
Static REF Cursors......Page 300
Cursor Variable Restrictions Laundry List......Page 302
Your Client and REF Cursors......Page 303
A Few Words about Parsing......Page 304
Dynamic REF Cursors......Page 306
Example and Best Use......Page 307
The Threat of SQL Injection......Page 308
Describing REF Cursor Columns......Page 310
Summary......Page 311
Case Study: The Avaloq Banking System......Page 312
Strengths of Business Logic in the Database with PL/SQL......Page 314
Soft Factors......Page 317
Uniformity through Conventions......Page 318
Abbreviations......Page 319
Preand Suffixes for PL/SQL Identifiers......Page 322
Modularization of Code and Data......Page 325
Packages and Associated Tables as Modules......Page 326
Modules Containing Multiple Packages or Submodules......Page 331
Schemas as Modules......Page 335
Modularization within Schemas......Page 338
Modularization with Schemas vs. within Schemas......Page 341
Object-Oriented Programming with PL/SQL......Page 342
Object-Oriented Programming with User-Defined Types......Page 343
Object-Oriented Programming with PL/SQL Records......Page 347
Measuring Memory Usage......Page 355
Collections......Page 361
Summary......Page 364
Evolutionary Data Modeling......Page 366
Lessons from Two Decades of System Development......Page 367
The Database and Agile Development......Page 368
Evolutionary Data Modeling......Page 369
Refactoring the Database......Page 371
Creating an Access Layer via PL/SQL......Page 375
The Agile Manifesto......Page 389
Think Extensible......Page 391
Test Driven Development......Page 392
Summary......Page 393
Profiling for Performance......Page 394
Functional Requirements......Page 395
Resource Utilization......Page 396
Benefits of Profiling......Page 0
What Is Profiling?......Page 398
The Magic of Profiles......Page 399
Why is This Program Slow?......Page 401
Identification......Page 403
Built-in Profilers......Page 408
Instrumentation Library for Oracle (ILO)......Page 409
Method R......Page 413
ILO Example......Page 415
Profiling Example......Page 418
Summary......Page 421
Why coding conventions?......Page 423
Comments......Page 425
Indentation......Page 426
Dynamic Code......Page 429
Packages......Page 430
Naming......Page 431
Parameters......Page 432
Constants......Page 433
Procedure Metadata......Page 434
Error Handling......Page 435
Error Reporting......Page 436
Error Recovery......Page 438
Summary......Page 439
Dependency Chains......Page 442
Shortening Dependency Chains......Page 449
Datatype Reference......Page 454
View for Table Alterations......Page 455
Adding Components into Packages......Page 459
Resource Locking......Page 463
Forcing Dependency in Triggers......Page 464
Creating Triggers Disabled Initially......Page 468
Summary......Page 470
B......Page 472
C......Page 473
D......Page 475
E......Page 476
G......Page 477
L......Page 478
P......Page 479
S......Page 484
U, V......Page 486
Y, Z......Page 487