Robert Diekmann's Dev Demo Platform
Supporting Services
Welcome
PL/SQL Is My Super Power
My account and access
Table of contents
PL/SQL Is My Super Power
By Robert Diekmann
PL/SQL is my superpower.
1 Introduction
2 Cursors
3 Least Privilege
4 ETL
4.1 JASON Example
4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
4.2.1 Parallel MERGE Processing Using Hash-Based Partitioning of CRM Change Data
4.2.1.1 How Hash-Based Partitioning Works
4.2.1.2 Why Hash Partitioning Eliminates Hot Block Contention
4.2.1.2.1 Updates Are Targeting Different Rows
4.2.1.2.2 Inserts Avoid “Right-Most Index Hot Block” Problems
4.2.1.2.3 Direct-Path INSERT Removes Further Contention
4.2.1.3 Why MERGE Still Works Efficiently in Parallel
4.2.1.3.1 UPDATE path
4.2.1.3.2 INSERT path
4.2.1.4 How this Strategy Avoids the Classic Oracle Contention Patterns
4.2.1.5 Why Hash Partitioning Is Superior to Range Partitioning for MERGE
4.2.2 Parallel MERGE with a Parallel Manager Package
4.2.2.1 The Parallel Manager’s Role
4.2.2.2 Can the Parallel Manager Auto-Sense Load?
5 Logging Manager and Autonomous Error Logging
5.1 Why a Logging Manager?
5.2 Logging Manager Concepts
5.3 Real-World Batch Example
5.4 The check_point Pattern
5.5 Autonomous Error Logging (DEBUG_PKG)
6 Vectors Are My Superpower
6.1 Ultra-High-Speed Record Matching with PL/SQL
6.2 Why Identity Matching Is Harder Than It Looks
6.3 The Core Idea: Identity as a Vector, Not a Row
6.4 The Person Identity Vector (Canonical Example)
6.4.1 Step 1: Canonicalization (Stability First)
6.4.2 Step 2: Numeric Encoding
6.4.3 Step 3: Matching Becomes Trivial
6.5 Why This Is Fast (Really Fast)
6.5.1 Integer comparisons beat string comparisons
6.5.2 Fixed-length, predictable execution
6.5.3 Precomputation eliminates rework
6.5.4 Indexing actually works
6.6 Generalizing the Pattern Across Domains
6.6.1 Person ↔ Prescription (Pharmacy Systems)
6.6.2 Person ↔ Weapon (Regulated Systems)
6.6.3 Person ↔ Customer (CRM Systems)
6.7 Handling Long Strings (Addresses, Free Text)
6.8 Privacy-Sensitive Attributes (SSN, National IDs)
6.9 Near-Match Detection Without Guessing
6.10 Why This Belongs in PL/SQL
6.11 Performance Benchmarks (Practical, Not Theoretical)
6.11.1 Baseline Scenario
6.11.2 Comparison Summary
6.11.3 Why the Difference Is So Large
6.11.3.1 CPU Cost per Comparison
6.11.3.2 Predictable Execution Paths
6.11.3.3 Index Efficiency
6.11.3.4 Elimination of Rework
6.11.4 Observed Throughput (Order of Magnitude)
6.11.5 Impact on System Design
6.11.6 Benchmarking Guidance for Your Own System
6.11.7 Key Takeaway
6.11.8 Final Thought
7 Database Links Are My Superpower
7.1 Why Database Links Are Controversial (and Why They Shouldn’t Be)
7.2 What a Database Link Actually Is
7.3 Using Analytic Views as the Boundary for Database Links
7.3.1 What Is Exposed Across the Link
7.3.2 Why Views Simplify Database Link Usage
7.3.3 Relationship to Distributed Query Execution
7.3.4 Governance and Security Implications
7.3.5 Summary
7.4 How a Database Link Works (Conceptually)
7.5 Creating a Database Link (Mechanics, Not Magic)
7.6 Why DB Links Work So Well in Large Enterprises
7.6.1 They Preserve System Ownership
7.6.2 They Avoid Unnecessary Data Movement
7.6.3 They Integrate Natively With SQL
7.7 Performance: The Real Story (Not the Myths)
7.7.1 The Myth
7.7.2 The Reality
7.7.3 Where Performance Is Won or Lost
7.7.3.1 Predicate Pushdown
7.7.3.2 Join Location
7.7.3.3 Bulk Operations
7.8 Network Tuning: The Hidden Half of Database Link Performance
7.8.1 Transparency Versus Indirection
7.8.2 Why the Network Matters So Much
7.8.3 The Hard Truth: Database Links Reveal Problems Others Hide
7.8.4 Practical Design Implications
7.8.5 Why This Is Difficult to Negotiate
7.8.6 The Architectural Payoff
7.9 Security: The Most Misunderstood Aspect
7.9.1 Authentication & Credential Control
7.9.2 Authorization & Least Privilege
7.9.3 Auditing & Traceability
7.9.4 Data-in-Transit Security
7.9.5 DB Links vs APIs: A False Dichotomy
7.10 When NOT to Use DB Links
7.11 The Scientific Argument for DB Links
7.12 Database Link Performance Tuning Checklist
7.12.1 SQL & Query Design (Highest Impact)
7.12.2 Join Strategy & Execution Location
7.12.3 Indexing & Data Shape
7.12.4 Network & Infrastructure (Often Ignored, Often the Root Cause)
7.12.5 Session Management & Resource Control
7.12.6 PL/SQL Usage Patterns
7.12.7 Monitoring & Diagnostics
7.12.8 Anti-Patterns to Avoid (Red Flags)
7.12.9 Final Rule of Thumb
7.12.10 Final Thought
Appendices: Cursor and Parallel Processing Examples
Appendix A: Cursors and Logging Examples
A.1 Example person table definition
A.2 One possible SQL to query this table would be below
A.3 The view would be as shown and it is needed to define the cursor next
A.4 creating a cursor on the view v_persons (defined above in 2.3)
A.5 old style cursors
A.6 Better style cursor, bulk collect
A.7 Better style cursor, bulk collect with Limit and FORALL
A.8 How to monitor memory consumption with cursors
A.9 How to monitor memory consumption using PGA memory size
A.10 Collection exceptions across multiple rows
A.11 cursor exceptions, don’t kill me now, report later
A.12 Log Manager Code
A.12.1 EVENT_LOG_OBJ Type
A.12.2 EVENT_LOG Table
A.12.3 LOG_MANAGER Package Specification
A.12.4 LOG_MANAGER Package Body
Appendix B: Parallel Processing and Scheduled Jobs
B.1 New Interface: parallel_manager.runjob
B.1.1 Semantics
B.2 “AUTO” Concurrency: How the Manager Chooses a Limit
B.3 Letting the Job Ask: “What Parallel Slice Am I?”
B.3.1 How does this work?
B.4 Specification of the Called Job
B.4.1 Job Entry Point
B.4.2 Job Responsibilities
B.4.3 Invariants (Rules)
B.5 How This Fits the Earlier Story
B.6 What can we measure to approximate “system effort”?
B.6.1 PGA usage – how much private memory is in play?
B.6.2 Session and process limits – how crowded is the instance?
B.6.3 CPU pressure – how busy are we?
B.6.4 How many of our jobs are already running?
B.7 Tying this back to the earlier PGA / LIMIT discussion
B.8 A realistic AUTO algorithm for parallel_manager
B.8.1 Step 1 – Base capacity from static limits
B.8.2 Step 2 – Check current database load at decision time
B.8.3 Step 3 – Decision rule for starting another job
B.8.4 Step 4 – “Always below a percentage of acceptable consumption”
B.9 Caveats you probably want to mention in the book
B.10 How to describe this in your book
Appendices: Vector and Network Examples
Appendix C: Vector Creation Logic
C.1 SQL Types (Vector + optional element list)
C.2 Package Spec
C.3 Package Body (canonicalization + hash → vector)
C.4 Quick usage examples
Appendix D: Working with Network Engineers to Optimize Database Link Performance
D.1 Why Network Engagement Changes Everything
D.2 How to Describe the Problem in Network Terms (Written or Spoken)
D.3 The Changes Network Engineers Commonly Make (That Actually Matter)
D.3.1 Path and Routing Optimization
D.3.1.1 What changes
D.3.1.2 How to ask (ticket language)
D.3.2 Firewall and Inspection Policy Adjustments
D.3.2.1 What changes
D.3.2.2 How to ask
D.3.3 MTU Alignment and Fragmentation Elimination
D.3.3.1 What changes
D.3.3.2 How to ask
D.3.4 Listener and Port Segmentation
D.3.4.1 What changes
D.3.4.2 How to ask
D.3.5 Batch Window Coordination
D.3.5.1 What changes
D.3.5.2 How to ask
D.3.6 Encryption Cost Awareness: Performance, Placement, and Capacity
D.4 Where the Encryption Cost Actually Occurs
D.4.1 Why Database Links Surface Encryption Cost So Clearly
D.4.2 CPU Placement Awareness
D.4.3 Capacity Planning for Encrypted Workloads
D.4.4 How to Engage Network and Security Teams
D.4.5 Why This Often Leads to Significant Improvements
D.4.6 Key Takeaway
D.5 Encryption Placement and Dollar Cost
D.6 Where the Cost Shows Up Financially
D.6.1 Database Host CPU Cost
D.7 Network Infrastructure Cost
D.8 Why Database Links Make Cost Visible
D.9 A Practical Cost Comparison (Conceptual)
D.10 Why This Argument Often Changes the Conversation
D.11 Key Takeaway: Deliberate Placement Beats Accidental Cost
D.12 Why This Often Requires Written Support
D.13 The Architectural Reality
D.14 Closing Thought
Index
Robert Diekmann's Dev Demo Platform
Robert Diekmann's Dev Demo Platform