Oracle PL/SQL Performance Tuning Training Course

This course is taught at your Company site with up to 20 students.

Click here for on-site course prices

Optional supplemental mentoring

* Learn the basics of PL/SQL tuning.

* See how to tune calls to the Oracle database.

* Use PL/SQL arrays and internal tables to speed-up processing.

* Understand PL/SQL tracing and debugging.

* Use ref cursors and bulk collect for fast performance.

* See how to improve PL/SQL performance by replacing complex SQL with intermediate arrays.

* Learn how to compile PL/SQL.

This BC Oracle PL/SQL programming & Tuning Training class is an intense course that is designed to give the student maximum exposure to Oracle PL/SQL tuning and performance optimization. The student learns by example, studying actual performance case studies.

The PL/SQL tuning class topics start with the basic PL/SQL performance tuning and progress into increasingly complex tuning techniques, including bulking, optimizing calls to Oracle and using ref cursors. We guarantee your success via our optional PL/SQL mentoring and the students will gradually masters PL/SQL through increasingly challenging classroom exercises.

Oracle PL/SQL Tuning
Expert Secrets for High Performance Programming

Dr. Tim Hall

ISBN 0-9761573-9-X

This PL/SQL tuning training course is designed for practicing Oracle PL/SQL professionals who have basic experience with PL/SQL and the use of a procedural language. Prior experience with PL/SQL is not required, but experience with a relational database is highly desirable.

This course was designed by Donald K. Burleson, an acknowledged leader in Oracle database administration. Burleson has two decades of real-world DBA experience in Oracle PL/SQL features and shares his Oracle secrets in this intense Oracle PL/SQL training. Burleson Corporation instructors offer decades of real world DBA experience in Oracle features, and they will share their Oracle secrets in this intense Oracle PL/SQL training.

Learning Objectives

By the end of this course the student will understand advanced PL/SQL tuning procedures, and will be able to use advanced tuning features such as ref cursors and bulk fetches. The student will also become familiar with the internals of Oracle PL/SQL and will be able to compile PL/SQL for super-fast performance.

Note: Every shop has unique PL/SQL training needs, and this course is offered as a 3, 4 or 5 day course. The students choose from a variety of training class topics to maximize their learning experiences.

Oracle PL/SQL Performance Tuning Training Class
Syllabus

Section 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

Section 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

Section 3 - Arrays and Bulk Binds

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

Section 4 - Caching Session Data

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

Section 5 - PL/SQL 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

Section 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

Section 7 - Table Functions and Pipelining

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

Section 8 - Monitoring and Profiling PL/SQL

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

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail:

and include the URL for the page.

Burleson Consulting

The Oracle of Database Support
Oracle Performance Tuning
Remote DBA Services