Expert PL/SQL Practices · for Oracle Developers and DBAs by John Beresniewicz, Adrian Billington, Martin Büchi, Melanie Caffrey, Ron Crisco, Lewis Cunningham, Dominic Delmolino, Sue Harper, Torben Holm, Connor McDonald, Arup Nanda, Stephan Petit, Michael Rosenblum, Robyn Sands, Riyaj Shamsudeen -- Read -- Imperial Library of Trantor

Index

Cover Title Copyright Contents at a Glance Contents About the Authors About the Technical Reviewer Introduction Chapter 1: Do Not Use

Row-by-Row Processing Nested Row-by-Row Processing Lookup Queries Excessive Access to DUAL

Arithmetics with Date Access to Sequences Populating Master-Detail Rows

Excessive Function Calls

Unnecessary Function Execution Costly Function Calls

Database Link Calls Excessive Use of Triggers Excessive Commits Excessive Parsing Summary

Chapter 2: Dynamic SQL: Handling the Unknown

The Hero

Native Dynamic SQL Dynamic Cursors DBMS_SQL

Sample of Dynamic Thinking Security Issues Performance and Resource Utilization

Anti-Patterns Comparing Implementations of Dynamic SQL

Object Dependencies

Negative Effects Positive Effects

Summary

Chapter 3: PL/SQL and Parallel Processing

Why Parallel Processing? Laws Affecting Parallel Processing The Rise of Big Data Parallel vs. Distributed Processing Parallel Hardware Architectures Identify Your Goals

Speedup Scaling Up Degree of Parallelism

Candidate Workloads for Parallel Processing

Parallelism and OLTP Parallelism and Non-OLTP Workloads

The MapReduce Programming Model Before Looking to PL/SQL Processes Available for Parallel Activities Using Parallel Execution Servers for MapReduce

Pipelined Table Functions Guidance Parallel Pipelined Table Functions Summary

Summary

Chapter 4: Warnings and Conditional Compilation

PL/SQL Warnings

Basics Using Warnings Promoting Warnings to Errors Ignoring Warnings Compilation and Warnings Final Words on Warnings

Conditional Compilation

Basics What Part of the Code is Running? Benefits of Preprocessing Code Invalidations Controlling Compilation Inquiry Variables Final Words on Conditional Compilation

Summary

Chapter 5: PL/SQL Unit Testing

Why Test Your Code? What Is Unit Testing?

Debugging or Testing? When Should You Build Tests?

Tools for Building Unit Tests

utPLSQL: Working with Command Line Code Quest Code Tester for Oracle Oracle SQL Developer

Preparing and Maintaining the Unit Test Environment

Creating the Unit Test Repository Maintaining the Unit Test Repository Importing Tests

Building Unit Tests

Using the Unit Test Wizard Creating the First Implementation Adding Startup and Teardown Processes Collecting Code Coverage Statistics Specifying Parameters Adding Process Validation Saving Tests Debugging and Running Tests

Broadening the Scope of the Tests

Creating Lookup Values Seeding Test Implementations Creating a Dynamic Query

Supporting Unit Test Features

Running Reports Creating a Library of Components Exporting, Importing, and Synchronizing Tests Building Suites

Running Tests from the Command Line Summary

Chapter 6: Bulk SQL Operations

The Hardware Store Setting for the Examples in this Chapter Bulk Operations in PL/SQL

Getting Started with BULK Fetch Three Collection-Style Datatypes Why should I bother? Monitoring Bulk Collect Overheads Refactoring Code to Use Bulk Collect

Bulk Binding

Getting Started with Bulk Bind Measuring Bulk Binding Performance Monitoring Memory Usage Improvements in 11g

Error Handling with Bulk Bind

SAVE EXCEPTIONS with Batches LOG ERRORS Clause Robust Bulk Bind

A Justification for Massive Collections The Real Benefit: Client Bulk Processing Summary

Chapter 7: Know Your Code

What This Chapter Will (and Will Not) Cover Automated Code Analysis

Static Analysis Dynamic Analysis

When to Analyze? Performing Static Analysis

The Data Dictionary PL/SCOPE

Performing Dynamic Analysis

DBMS_PROFILER and DBMS_TRACE DBMS_HPROF

Summary

Chapter 8: Contract-Oriented Programming

Design by Contract

Software Contracts Basic Contract Elements Assertions References

Implementing PL/SQL Contracts

Basic ASSERT Procedure Standard Package-Local ASSERT Enforcing Contracts Using ASSERT An Additional Improvement Contract-Oriented Function Prototype

Example: Testing Odd and Even Integers Useful Contract Patterns

Not-NULL IN / Not-NULL OUT FUNCTION RETURN Not-NULL FUNCTION RETURN BOOLEAN Not-NULL Check Functions: RETURN TRUE OR ASSERTFAIL

Principles For Bug-Free Code

Assert Preconditions Rigorously Modularize Ruthlessly Adopt Function-Based Interfaces Crash on ASSERTFAIL Regression Test Your Postconditions Avoid Correctness-Performance Tradeoffs Oracle 11g Optimized Compilation

Summary

Chapter 9: PL/SQL from SQL

The Cost of Using PL/SQL Functions in SQL

Context-Switching Executions Suboptimal Data Access Optimizer Difficulties The Read-Consistency Trap Other Issues

Reducing the Cost of PL/SQL Functions

A Sense of Perspective Using SQL Alternatives Reducing Executions Assisting the CBO Tuning PL/SQL

Summary

Chapter 10: Choosing the Right Cursor

Explicit Cursors

The Anatomy of an Explicit Cursor Explicit Cursors and Bulk Processing REF Cursors in Brief

Implicit Cursors

The Anatomy of an Implicit Cursor The Implicit Cursor and the Extra Fetch Theory

Static REF Cursors

Cursor Variable Restrictions Laundry List Your Client and REF Cursors A Few Words about Parsing

Dynamic REF Cursors

Example and Best Use The Threat of SQL Injection Describing REF Cursor Columns

Summary

Chapter 11: PL/SQL Programming in the Large

The Database as PL/SQL-Based Application Server

Case Study: The Avaloq Banking System Strengths of Business Logic in the Database with PL/SQL Limits of the Database as PL/SQL-Based Application Server Soft Factors

Requirements of Programming in the Large Uniformity through Conventions

Abbreviations Pre- and Suffixes for PL/SQL Identifiers

Modularization of Code and Data

Packages and Associated Tables as Modules Modules Containing Multiple Packages or Submodules Schemas as Modules Modularization within Schemas Modularization with Schemas vs. within Schemas

Object-Oriented Programming with PL/SQL

Object-Oriented Programming with User-Defined Types Object-Oriented Programming with PL/SQL Records Assessment

Memory Management

Measuring Memory Usage Collections

Summary

Chapter 12: Evolutionary Data Modeling

Lessons from Two Decades of System Development The Database and Agile Development Evolutionary Data Modeling Refactoring the Database Creating an Access Layer via PL/SQL The Agile Manifesto Using PL/SQL with Evolutionary Data Modeling

Define the Interface Think Extensible Test Driven Development Use Schemas and Users Wisely

Summary

Chapter 13: Profiling for Performance

What Is Performance?

Functional Requirements Response Time Throughput Resource Utilization Performance Is a Feature

What Is Profiling?

Sequence Diagrams The Magic of Profiles Benefits of Profiling

Instrumentation

Why is This Program Slow? Measurement Intrusion Identification Conditional Compilation Built-in Profilers Extended SQL Trace Data (Event 10046) Instrumentation Library for Oracle (ILO)

Problem Diagnosis

Method R ILO Example Profiling Example

Summary

Chapter 14: Coding Conventions and Error Handling

Why coding conventions? Formatting

Case Comments Indentation

Dynamic Code Packages Stored Procedures

Naming Parameters Calls Local Variables Constants Types Global Variables Local Procedures and Functions Procedure Metadata

Functions Error Handling

Error Trapping Error Reporting Error Recovery Test First. Display Second.

Summary

Chapter 15: Dependencies and Invalidations

Dependency Chains Shortening Dependency Chains Datatype Reference View for Table Alterations Adding Components into Packages Synonyms in Dependency Chains Resource Locking Forcing Dependency in Triggers Creating Triggers Disabled Initially Summary

Index

← Prev
Back
Next →

← Prev
Back
Next →