Search BC Sites  Phone  919-335-6342 Oracle Books Oracle tuning Oracle training Oracle support Remote Oracle STATSPACK Viewer Privacy Policy 2023

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"