Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

  

 

ISBN: 0-9761573-9-X
ISBN 13: 978-0976157397
Library of Congress Number: 2004096473 
330 pages
Perfect bind - 9x7
PD 906
Shelving: Database/Oracle Oracle In-Focus Series # 8

  Oracle PL/SQL Tuning
Expert Secrets for High Performance Programming

Dr. Timothy Hall   

Retail Price $34.95 /  £27.95 

Buy it for 30% off! Only $23.95
(30% off)
     
Key Features About the Authors Table of Contents
 Index Reader Comments Errata
     

-

Get the Oracle Developer Pack
Six for $79.95 - A $145.00 value
Easy Oracle Application Express (HTML-DB) $39.95
Oracle 10g Reference Poster $8.95
Oracle Backup & Recovery Scripts download $16.95
Oracle PHP: Create web pages with Oracle $27.95
Oracle PL/SQL Programming $16.95
Oracle PL/SQL Tuning $34.95

 


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.

 



                Expert
            PL/SQL
        Programming
              Tips!

Key Features

* Learn undocumented tricks for hypercharging the performance of PL/SQL

* Understand the bulking and direct load techniques to add millions of rows per second

* See how to use the forall, bulk collect and ref cursor commands

* Get an online code depot of working PL/SQL examples

* Learn to re-write SQL into PL/SQL

* Get step-by-step instructions for compiling PL/SQL

* See the secrets for reducing context switching delays

* See the latest Oracle 10g PL/SQL Tuning Features

 

About the Author:
 
 

Dr. Timothy Hall

Dr. Tim Hall is an Oracle Certified Professional DBA (7.3, 8, 8i, 9i, 10g) and has been involved in DBA, design and development work with Oracle databases since graduating from college in 1994 with a PhD in Molecular Genetics.

Tim Hall has gained a wide knowledge of the Oracle software stack and has worked as a consultant for several multi-national companies on projects ranging from real-time control systems to OLTP web applications. Since 2000 he has published over 200 articles on his website www.oracle-base.com covering a wide range of Oracle features.

Tim has been a Karate black belt since 1993 and is a practitioner and qualified teacher of Hatha and Ashtanga yoga. In addition, he enjoys running and completed the 2002 London Marathon.

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

Index Topics:
%ROWTYPE
%TYPE
A
ADDM
APIs
Associative Arrays
Automatic Workload Repository
autotrace
B
BINARY_INTEGER
Bind variables
bind_variable_usage.sql
block_in_loop.sql
Blocks
branch_order.sql
bulk_collect.sql
bulk_collect_i.sql
bulk_collect_from_cursor.sql
bulk_collect_from_cursor_i.sql
bulk_collect_limit.sql
bulk_collect_limit_i.sql
bulk_collect_manual_limit.sql
bulk_collect_manual_limit_i.sql
C
cached_lookup_ap_testi.sql
cached_lookup_api
cached_lookup_api.sql
cached_lookup_api_incorrect.sql
CASE statements
constants
context_api.set_parameter
COUNT
create_bulk_collect_test.sql
create_cached_lookup_tab.sql
create_context.sql
create_dynamic_binds_tab.sql
create_forall_test.sql
create_square_root_functions.sql
create_square_root_schema_objects.sql
create_system_parameters_tab.sql
create_tp_test_schema.sql
create_tp_testfile.sql
cursor_api.sql
cursor_assignments_by_value.sql
cursor_attributes.sql
cursor_comparison.sql
cursor_expression_function_parameter.sql
cursor_expression_parent_child.sql
cursor_expression_tabs.sql
cursor_rowtype_mismatch.sql
cursor_sharing
cursor_variable_assignment.sql
cursor_variable_definitions.sql
cursor_variable_input_param.sql
cursor_variable_test.sql
D
datatype_conversions.sql
dba_advisor_findings
dba_advisor_log
dba_advisor_recommendations
dba_advisor_tasks
dba_hist_active_sess_history
dba_hist_baseline
dba_hist_database_instance
dba_hist_snapshot
dba_hist_sql_plan
dba_hist_wr_control
dba_indexes
dbms_application_info
dbms_ouput
dbms_profiler
dbms_session
dbms_shared_pool
dbms_system
dbms_trace
dbms_xplan
DECLARE
DELETE
delete_forall.sql
dequeue
dsp.pkb
dynamic_binds
dynamic_bulk_collect.sql
dynamic_cursor_variables.sql
dynamic_forall.sql

 
E
efficient_functions.sql
efficient_functions_fb_index.sql
efficient_functions_setup.sql
END LOOP
enqueue
EXACT
exception_test.sql
EXISTS
Explain Plan
explain.sql
explicit cursors
EXTEND
F
FIRST
float_double_test.sql
FORALL
forall_indices_of.sql
forall_values_of.sql
FORCE
FOR-LOOP
FUNCTION
function_in_loop.sql
G
get_resultset.sql
get_row_count.sql
get_row_count_.sql
get_square_roots_tf
global_context.sql
global_context_api_test.sql
global_context_comparison.sql
global_data_api.sql
global_data_api_test.sql
global_data_comparison.sql
GOTO
H
handled_exception.sql
I
identify_trace_file.sql
IF-THEN-ELSE
implicit cursors
insert_forall.sql
insert_forall_i.sql
integer_test.sql
invalid_cursor.sql
K
keep_test_api.sql
L
LAST
LIMIT
locked_objects.sql
longops.sql
M
multiple_params.sql
N
native_comp_test.sql
nested table collections
NEXT
NOCOPY
nocopy.sql
O
OEM
open_cursors_by_sid.sql
open_cursors_full_by_sid.sql
Oracle Forms
P
Packages
parallel_order.sql
PL/SQL
PL/SQL Architecture
plan_table
PLS_INTEGER
PROCEDURE
procedure_in_loop.sql
process_requests_job
profiler_run_details.sql
profiler_runs.sql
profiler_setup.sql
profiler_test.sql
 
Q
query_square_root_functions.sql
R
RAISE
Records
regular_expression.sql
resultset_ado_test.asp
resultset_java_test.java
resultset_plsql_test.sql
return_types.sql
returning_bulk_collect.sql
rowcount_test.sql
rowid_test.sql
ROWNUM
run_profiler.sql
run_trace.sql
S
save_exceptions.sql
secure_global_context_api_body.sql
session_context.sql
session_events_by_sid.sql
session_io.sql
session_stats.sql
session_waits.sql
sessions.sql
SIMILAR
slow_function.sql
slow_function_test.sql
slow_request.sql
slow_request_api.sql
slow_request_api_cleanup.sql
slow_request_api_setup.sql
slow_request_api_test.sql
SQL
sql_injection.sql
STATSPACK
system_events.sql
system_stats.sql
T
test_deterministic.sql
test_dsp.sql
test_parallel_setup.sql
test_parallel_table_function.sql
test_speed.sql
test_speed_.sql
test_table_function_memory_usage.sql
tkprof
top_sql.sql
tp_api.sql
trace_run_events.sql
trace_runs.sql
trace_setup.sql
trace_test.sql
trcsess
trigger_compilation_cleanup.sql
trigger_compilation_setup.sql
trigger_compilation_test.sql
triggers
TRIM
TYPE BODY
TYPEs
U
unhandled_exception.sql
update_forall.sql
update_forall_i.sql
update_global.sql
utlxpls.sql
V
v$active_session_history
v$metric_history
v$metricgroup
v$metricname
V$MYSTAT
v$sql
VARCHAR
varchar_definitions.sql
Variables
variant_resultset.sql
VARRAY
W
WHILE-LOOP
write_to_alert_log.sql

Reviews:

Books on PL/SQL, like books on other topics related to Oracle, range from the just so-so to what can be called bible status. A few books rise to the top and become the de facto definitive reference... 

Chapter 3 of Hall’s book is reason enough on its own to justify adding it to your collection, regardless of your experience level. Ever wanted to see clear examples of using arrays and bulk binds? The chapter includes examples of creating and populating bulk collections, using the limit clause, bulk collection of DML results, FORALL in PL/SQL, bulk insert, bulk update, bulk delete, and many other topics. The examples are instrumented (recording start and end times and reporting on them) and are easy to follow along as they are complete (not just the code snippet for a loop).

What I particularly like about the examples, and there are lots of them, is that they span versions. The old/more cumbersome way in 8i gets about the same amount of attention as the new/more streamlined way in 10g. Examples using more efficient constructs will help you get up to speed in this regard. As an analogy, string manipulation can still be done the old way (substr, instr, etc.), but you’re missing out on the benefits of using REGEXP (regular expression) functions.

If you know you need improvement in certain areas, and you are willing to help yourself get better in those areas, with respect to PL/SQL, Tim Hall’s book is one of those high return on investment purchases that will go a long way towards helping you achieve this goal.

--

Steve C.
Read the full review here.

 

Errata:

Correction 1
============

Page 105-106 - bulk_collect_limit.sql script should read:

SET SERVEROUTPUT ON
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

l_tab t_bulk_collect_test_tab;

CURSOR c_data IS
SELECT *
FROM bulk_collect_test;
BEGIN
OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 10000;
EXIT WHEN l_tab.count = 0;

-- Process contents of collection here.
DBMS_OUTPUT.put_line(l_tab.count || ' rows');
END LOOP;
CLOSE c_data;
END;
/

Correction 2
============


Page 106-107 - bulk_collect_limit_8i.sql script should read:

SET SERVEROUTPUT ON
DECLARE
TYPE t_owner_tab IS TABLE OF bulk_collect_test.owner%TYPE;
TYPE t_object_name_tab IS TABLE OF bulk_collect_test.object_name%TYPE;
TYPE t_object_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;

l_owner_tab t_owner_tab;
l_object_name_tab t_object_name_tab;
l_object_id_tab t_object_id_tab;

CURSOR c_data IS
SELECT owner,
object_name,
object_id
FROM bulk_collect_test;
BEGIN
OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_owner_tab,
l_object_name_tab,
l_object_id_tab
LIMIT 10000;
EXIT WHEN l_owner_tab.count = 0;

-- Process contents of collection here.
DBMS_OUTPUT.put_line(l_owner_tab.count || ' rows');
END LOOP;
CLOSE c_data;
END;
/

Correction 3
============


Page 107-108 - implicit_array_processing.sql script should read:

SET SERVEROUTPUT ON
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

l_tab t_bulk_collect_test_tab;

CURSOR c_data IS
SELECT *
FROM bulk_collect_test;

l_start NUMBER;
BEGIN
-- Time a regular population.
l_start := DBMS_UTILITY.get_time;

FOR cur_rec IN (SELECT *
FROM bulk_collect_test)
LOOP
NULL;
END LOOP;

DBMS_OUTPUT.put_line('Regular : ' ||
(DBMS_UTILITY.get_time - l_start));

-- Time bulk with LIMIT 10.
l_start := DBMS_UTILITY.get_time;

OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 10;
EXIT WHEN l_tab.count = 0;
END LOOP;
CLOSE c_data;

DBMS_OUTPUT.put_line('LIMIT 10 : ' ||
(DBMS_UTILITY.get_time - l_start));

-- Time bulk with LIMIT 100.
l_start := DBMS_UTILITY.get_time;

OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 100;
EXIT WHEN l_tab.count = 0;
END LOOP;
CLOSE c_data;

DBMS_OUTPUT.put_line('LIMIT 100: ' ||
(DBMS_UTILITY.get_time - l_start));
END;
/

 

Correction 4
============

 Page 163:

Currently says:

"Introduction to PL/QL RAM Memory"

I believe it should say something like:

"Introduction to PL/SQL Memory Management"
 

 


 

 

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks