Skip Ribbon Commands
Skip to main content

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

Price:

Duration: 4 Days

Audience:

Level:

Technology:

Delivery Method:

Software Assurance Value:

Microsoft CPE:

Course Information

Course Description

​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.

Course Objectives

​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.

Course 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.

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

Course 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
Course Schedule
This course is not scheduled yet.