ORA10g102 - Oracle Database 10g: Advanced PL/SQL Programming and Tuning

Course ORA10g102

Table of Contents

Introduction
Audience
At Course Completion
Prerequisites
Certified Professional Exams
Student Materials
Course Outline

Introduction

The objective of this 4-day course is to allow experienced PL/SQL developers to write efficient programs. Advanced techniques that not only improve execution speed, but also enhance the functionality of the programs are also discussed.

Return to Top

Audience

The target audience for this course is senior Oracle application developers. Developers who will be building, debugging and tuning PL/SQL program units will benefit from this course.

Return to Top

At Course Completion

At the end of this course, students will be able to:

• Exploit and manage cursor sharing.
• Utilize cursor variables.
• Invoke external procedures.
• Include dynamic SQL and other advanced programming techniques.
• Efficiently execute dynamic SQL using bulk bind techniques.
• Tune with the DBMS_PROFILER() system-supplied package.
• Debug with the DBMS_TRACE() system-supplied package.
• Perform tuning and analysis of PL/SQL applications.

Return to Top

Prerequisites

Before attending this course, students must have:

• Completed ora10g008, Oracle Database 10g: Introduction to SQL I, or equivalent.
• Completed ora10g009, Oracle Database 10g: Introduction to SQL II, or equivalent.
• Completed ora10g101, Oracle Database 10g: Program With PL/SQL, or equivalent.
• Completed ora10g002, Oracle Database 10g: Architecture For Developers, or equivalent.
• Completed ora10g003, Oracle Database 10g: SQL Tuning

Return to Top

Certified Professional Exams

No exam currently exists for this course.

Return to Top

Student Materials

The student kit includes a comprehensive workbook and other necessary materials for this class.

Return to Top

Course Outline

PL/SQL DEVELOPMENT SUPPORT FROM EM

• ABOUT EM & PL/SQL
• MANAGING INVALID OBJECTS

EXPLOITING & MANAGING CURSOR SHARING

• ABOUT CURSOR SHARING
• BIND VARIABLES & CURSOR PEEKING
• USING THE CURSOR_SHARING PARAMETER

DYNAMIC SQL

• NATIVE DYNAMIC SQL

USING COLLECTIONS

• ABOUT COLLECTIONS
• BULK BIND USING COLLECTIONS
• COLLECTION METHODS

RETRIEVING DDL WITH DBMS_METADATA()

• WHY RETRIEVE OBJECT DEFINITIONS?
• RETRIEVING DEFAULT METADATA
• RETRIEVING CUSTOMIZED METADATA

ADVANCED DBMS_METADATA() PROCESSING

• SET_TRANSFORM_PARAM()
• GET_QUERY()

PROCESSING DEPENDENT OBJECTS WITH DBMS_METADATA()

REDEFINING TABLES WITH DBMS_REDEFINITION()

• ABOUT TABLE REDEFINITION
• USING THE DBMS_REDEFINITION() PACKAGE

USING OTHER SYSTEM-SUPPLIED PACKAGES

• DBMS_DESCRIBE()
• UTL_MAIL
• DBMS_SCHEDULER()

USING LARGE OBJECTS (LOBS)

ADVANCED INTERFACE METHODS

• ABOUT EXTERNAL PROCEDURES
• REGISTERING WITH THE DATABASE
• ORACLE NET CONFIGURATION
• CALLING EXTERNAL PROCEDURES

ABOUT PL/SQL PERFORMANCE TUNING

• ABOUT THE TUNING EFFORT
• THE DIFFICULTY OF TUNING
• THE SQL & PL/SQL EXECUTION ENVIRONMENT
• THE IMPACT OF THE SYSTEMS INFRASTRUCTURE

USING THE TUNING UTILITIES

INFLUENCING ORACLE10G PL/SQL COMPILATION

• PLSQL_WARNINGS
• PLSQL_OPTIMIZE_LEVEL
• PL/SQL NATIVE EXECUTION

PL/SQL TUNING WITH DBMS_PROFILER()

• ABOUT DBMS_PROFILER()
• INSTALLING DBMS_PROFILER()
• USING DBMS_PROFILER() TO MANAGE RUNS
• EXAMINING THE PROFILER TABLES

EVALUATING THE PL/SQL PROFILER DATA

• ABOUT THE PROFILER DATA
• USER QUERIES OF THE CROSS RUN TABLES
• SYSTEM-SUPPLIED CROSS RUN TABLES QUERIES

PL/SQL ADVANCED PROGRAMMING & TUNING TECHNIQUES

• AUTONOMOUS TRANSACTIONS
• USING NOCOPY FOR PARAMETERS
• CHOOSING THE OPTIMUM DATATYPE
• USEFUL PL/SQL CODING TECHNIQUES

PL/SQL DEBUGGING WITH DBMS_TRACE()

• ABOUT THE TRACE FACILITY
• INSTALLING DBMS_TRACE()
• USING DBMS_TRACE() TO MANAGE RUNS
• EXAMINING THE PLSQL_TRACE_... TABLES

Return to Top