Oracle9i Advanced PL/SQL Programming

This 4-day course will allow PL/SQL developers to write efficient and tuned programs. Performance techniques, problem areas and troubleshooting tools are all presented

Target audience

This course has been developed for Oracle application developers. Students should have attended the following course or have equivalent knowledge and experience:

• Introduction to Oracle 9i SQL
• Introduction to Oracle 9i Advanced SQL
• Introduction to Oracle 9i PL/SQL language
• Oracle 9i Architecture for Developers
• Oracle 9i SQL Tuning

Course content

Understanding the tuning issues

Using TKPROF and AUTOTRACE

Using the STATSPACK utility Setting Snampshot levels and SQL threholds • Maintaining the SQL performance table

Exploiting and managing cursor sharing Bind variables and cursor peeking • Using the cursor_sharing parameter

Using cursor variables Using sys_refcursor

Invoking external procedures Registering with the database • Oracle net configuration

Advanced program units Using invoker rights • Native dynamic SQL • Autonomous transactions

Using collections Bulk bind using collections • Collection methods

PL/SQL tuning with DBMS_profiler() Installing DBMS_profiler() • Using DBMS_profiler() to manage runs

Evaluating the PL/SQL profiler data User queries of the cross run tables • System-supplied cross run tables queries

PL/SQL tuning techniques Using nocopy for parameters • Using the returning clause • Choosing the optimum datatype • Useful PL/SQL coding techniques

PL/SQL debugging with DBMS_trace() Installing DBMS_trace() • Using DBMS_trace() to manage runs

Examining the PLSQL_TRACE_…tables

About Courseware Company :: Instructor-led training courseware :: Self-study solutions :: Buy online today