ISBN: 0-9761573-9-X ISBN 13: 978-0976157397 Library of Congress Number: 2004096473 330 pages Perfect bind - 9x7 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 Get immediate access to working code examples Only $23.95 (30% off) Key Features About the Authors Table of Contents Index Reader Comments Errata - Includes Oracle10g!

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.

* 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:

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:

Errata: