Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming

Description

Oracle experts know that PL/SQL tuning makes a huge difference in execution speed. As one of the world’s most popular and respected experts, Dr. Tim Hall shares his secrets for tuning Oracle PL/SQL.

This indispensable book shows how to hypercharge Oracle applications gaining as much as 30x improvement in execution speed using under-documented code tricks.  Packed with working examples, learn how to re-write SQL into PL./SQL and how to use advanced Oracle bulk array processing techniques to achieve super high performance. You can save your company millions of dollars in hardware costs by making your applications run at peak efficiency.

Targeted at the Senior Oracle DBA and developer, this advanced book illustrates powerful techniques that can make PL/SQL run faster than ever before. This book is not for beginners and should only be purchased by seasoned Oracle professionals who must turbocharge their applications. Your time savings from a single script is worth the price of this great book.

 

Table of Contents

Chapter 1 - Introducing PL/SQL

Introduction
What is PL/SQL and Why Should I use It?
PL/SQL Architecture
Overview of PL/SQL Elements
Blocks
Variables and Constants
Using SQL in PL/SQL
Branching and Conditional Control
Looping Statements
Goto
Procedures, Functions and Packages
Records
Object Types
Collections
Associative Arrays (Index-By Tables)
Nested Table Collections
Varrays
Collection Methods
Triggers
Error Handling
My Ideal Environment
Conclusion


Chapter 2 - Writing Efficient PL/SQL

Introduction
Bind Variables
Using Bind Variables
The cursor_sharing Parameter
Dynamic Binds using Contexts
SQL Injection
Bulk Binds
Using Rowids when Updating
Short-circuit Evaluations and Ordering Logic
Implicit vs. Explicit Cursors
Declarations, Blocks, Functions and Procedures in Loops
Duplication of Built-in String Functions
Minimize Datatype Conversions
The Trigger Compilation Myth
Efficient Function Calls
Using the NOCOPY Hint
Using PLS_INTEGER and BINARY_INTEGER Types
Using BINARY_FLOAT and BINARY_DOUBLE Types
Native Compilation of PL/SQL
Decoupling (cheating) for Performance
Conclusion


Chapter3 - Arrays and Bulk Binds

Introduction
Populating Collections Using Bulk Operations
Bulk collect
Bulk Collect from an Explicit Cursor
Chunking Bulk Collections Using the LIMIT Clause
Manually Limiting Bulk Collection Volumes
Bulk Collection of DML Results
FORALL
Bulk INSERT Operations
Bulk UPDATE Operations
Bulk DELETE Operations
Sparse Collections
Host Arrays in Bulk Operations
BULK_ROWCOUNT
Handling Exceptions in Bulk Operations
Unhandled Exceptions
Handled Exceptions
Bulk Operations that Complete
Dynamic SQL and Bulk Operations
Conclusion

Chapter 4 - Caching Session Data

Introduction
Using Arrays for Lookup Tables
Using Package Variables to Store Global Data
Using Contexts to Store Global Data
Conclusion


Chapter 5 - Memory Management

Introduction
Bind Variables and the Shared Pool
The NOCOPY Hint and Memory Usage
Bigger is Better for VARCHAR2 Variables
Using Packages Correctly
Pinning Packages in the Shared Pool
Conclusion


Chapter 6 - Cursor Variables and REF CURSOR Types
 

Introduction
Defining Cursor Variables
Cursor Variables as Parameters
Cursor Attributes and Cursor Variable Usage
Host Variables as Cursor Variables
Dynamic SQL and Variant Resultsets
Restrictions When Using Cursor Variables
Cursor Expressions
Conclusion


Chapter 7 - Table Functions and Pipelining

Introduction
Pipelining Table Functions
Parallel Enabled Table Functions
Creating Transformation Pipelines
Deterministic
Miscellaneous Information
Conclusion


Chapter 8 - Monitoring and Profiling PL/SQL

Introduction
Producing Performance Baselines
Monitoring Specific Code
Code Instrumentation (application tracing)
The DSP Package
dbms_application_info
dbms_session
dbms_system
dbms_profiler
dbms_trace
SQL trace, trcsess and tkprof
Generating SQL Trace Files
trcsess
tkprof
Trace Example
Execution Plans
plan_table
autotrace
Explain Plan
utlxpls.sql
dbms_xplan
Identifying the Impact of Code at the Database Level
Dynamic Performance Views (V$)
sessions.sql
top_sessions.sql
top_sql.sql
longops.sql
session_waits.sql
session_events_by_sid.sql and system_events.sql
session_stats.sql and system_stats.sql
session_io.sql
open_cursors_by_sid.sql
locked_objects.sql
STATSPACK
Automatic Workload Repository (AWR)
ADDM
Using Oracle Enterprise Manager
Conclusion
Book Conclusion

描述

Oracle專家知道PL/SQL調優對執行速度有巨大影響。作為世界上最受歡迎和受尊敬的專家之一，Tim Hall博士分享了他調優Oracle PL/SQL的秘訣。

這本不可或缺的書籍展示了如何通過使用未記錄的代碼技巧，將Oracle應用程序的執行速度提高多達30倍。書中充滿了實用示例，學習如何將SQL重寫為PL/SQL，以及如何使用高級的Oracle批量數組處理技術實現超高性能。通過使應用程序達到最高效率，您可以為公司節省數百萬美元的硬件成本。

這本高級書籍針對高級Oracle DBA和開發人員，展示了可以使PL/SQL運行速度比以往更快的強大技巧。這本書不適合初學者，只應由經驗豐富的Oracle專業人員購買，他們必須將其應用程序提速。從一個腳本中節省的時間價值超過這本優秀書籍的價格。

目錄

第1章 - 介紹PL/SQL
- 什麼是PL/SQL以及為什麼應該使用它？
- PL/SQL架構
- PL/SQL元素概述
- 塊
- 變量和常量
- 在PL/SQL中使用SQL
- 分支和條件控制
- 循環語句
- Goto語句
- 過程、函數和包
- 記錄
- 對象類型
- 集合
- 關聯數組（索引表）
- 嵌套表集合
- Varrays
- 集合方法
- 觸發器
- 錯誤處理
- 我的理想環境
- 結論

第2章 - 編寫高效的PL/SQL
- 引言
- 綁定變量
- 使用綁定變量
- cursor_sharing參數
- 使用上下文進行動態綁定
- SQL注入
- 批量綁定
- 在更新時使用Rowids
- 短路評估和排序邏輯
- 隱式vs.顯式游標
- 循環中的聲明、塊、函數和過程
- 內置字符串函數的重複使用
- 減少數據類型轉換
- 觸發器編譯迷思
- 高效的函數調用
- 使用NOCOPY提示
- 使用PLS_INTEGER和BINARY_INTEGER類型
- 使用BINARY_FLOAT和BINARY_DOUBLE類型
- PL/SQL的本地編譯
- 為了性能而欺騙（解耦）
- 結論

第3章 - 數組和批量綁定
- 引言
- 使用批量操作填充集合
- 批量收集
- 從顯式游標中批量收集
- 使用LIMIT子句分塊批量集合
- 手動限制批量集合容量
- 批量DML結果的集合
- FORALL
- 批量插入操作
- 批量更新操作
- 批量刪除操作
- 稀疏集合
- 批量操作中的主機數組
- BULK_ROWCOUNT
- 批量操作中的異常處理
- 未處理的異常
- 處理的異常
- 完成的批量操作
- 動態SQL和批量操作
- 結論