Oracle 10G PL/SQL Performance Tuning

AUDIENCE: This course is ideal for:
Database administrators, Developers, Data warehouse support team members, Application second and third line support.
PREREQUISITES: Attendees must be knowledgeable in Oracle PL/SQL.
DURATION: 2 days. Hands on.
OBJECTIVES: The Oracle RDBMS is typically deployed throughout a business, from the corporate mainframe, to enterprise Unix servers, down to departmental level Intel platforms. For a database of any significant size, performance immediately becomes an issue. Users may resent a query taking 3.5h, for instance. Or on the other hand, an update locks a table for 30 minutes while it modifies data, which denies application access to the table(s) concerned.

There will always be a platform ceiling set by hardware and o/s kernel performance but wouldn''t it be nice to use the gap between what we are getting today, and what we could get from our platform investment?

Often application support identify a section of the product causing performance problems but lack further details; which PL/SQL program is the cause?, who is the executing user?, what is the SQL that is running? The course investigates methods of sleuthing slow running PL/SQL programs.

Essentially there are two main avenues for RDBMS optimisation. Strategies deployed by the DBA, such as cache management and DBWR and LGWR optimisation, is one of them. The other approach is to investigate how the PLSQL and SQL is running, and to optimally change it or its execution environment.

While the core subject of this course is the optimisation of the PLSQL component of RDBMS programs, we do touch on areas of RDBMS optimisation that impact PLSQL performance.

Expect to get lots of hands-on practice, to generate and see problems and finally to solve them. The training is strongly lab based and takes a real world view of everyday business computing problems.
COURSE CONTENT:
Overview of Oracle 10G Performance Tuning
Job Roles in Tuning
Tuning phases
Tuning goals and Service Level Agreements
Common performance problems
Tuning Methodology

The Shared Pool
Overview of the shared pool
Library cache tuning
Reuse statements
Using Reserved Space
Pinning Objects in the shared pool
Data Dictionary Cache (DDC) Tuning
Searching the shared pool
Parameters:-
CURSOR_SHARING, OPEN_CURSORS, CURSOR_SPACE_FOR_TIME, session_cached_cursors

Causes of PL/SQL Performance Problems
Badly Written SQL Statements in a PL/SQL Program
Poor Programming Practices
Duplication of Built-in Functions
Inefficient Conditional Control Statements
Implicit Datatype Conversions
Inappropriate Declarations for Numeric Datatypes
Unnecessary NOT NULL Constraints
Pinned Packages
Serially Reusable Packages

Identifying PL/SQL Performance Problems
DBMS_TRACE
DBMS_PROFILER

PL/SQL Features for Performance Tuning
Tuning PL/SQL Performance with Native Dynamic SQL
Tuning PL/SQL Performance with Bulk Binds
Tuning PL/SQL Performance with the NOCOPY Compiler Hint
Tuning PL/SQL Performance with the RETURNING Clause
Tuning PL/SQL Performance with External Routines
Improving PL/SQL Performance with Object Types and Collections
Compiling PL/SQL Code for Native Execution
Inserting PL/SQL record(s) into a table
Querying data into collections of records
Associative arrays (index by tables)
Multiple inserts
Table functions (pipelined and parallel)
Using ref cursors

Diagnosing contention
Explicit and implicit data locking
Detecting locks as a cause of poor performance
Commit point planning in bulk updates
DISTRIBUTED_LOCK_TIMEOUT

MB07/01