Oracle PL/SQL 9i/10g: Application Development and Tuning for Oracle Databases

Scheduled Dates (sort by: location | date)

Course Overview

Developing efficient, secure, high-performance database applications is essential to the success of organisational information systems. Oracle database features, including LOBs, partitioning, PL/SQL built-ins and The Scheduler, enable developers to extend and enhance application development capabilities.

In this hands-on course, you gain the skills to create powerful and secure Oracle database applications, and tune them for optimal performance.

Audience

This course is valuable for developers and others interested in increasing the performance and efficiency of their Oracle database applications. Knowledge of SQL and PL/SQL at the level of Course 590, " Oracle9 i Introduction ", or Course 593, " Oracle Database 10 g Introduction ", is assumed.

Skills Gained

• Design and implement efficient applications while incorporating Oracle9 i and 10 g features
• Load data into Oracle databases using SQL*Loader or external tables
• Maintain and secure the database with advanced application techniques
• Enhance applications with key Oracle-provided packages
• Control the cost-based optimiser
• Improve query performance using the Oracle-provided toolset
• Tune applications for optimal performance

Course Outline

Introduction to the Application Environment

The Oracle database environment

• Reviewing basic architecture concepts
• Examining existing databases using the Data Dictionary

Database design considerations

• Designing for performance
• Managing basic storage structures

Preparing the Database

Maintaining the application schema

• Managing dependencies
• Migrating LONG to LOB datatypes

Populating the database

• Loading data from other sources using SQL*Loader or external tables
• Writing to external tables using Data Pump
• Importing other Oracle database content

Maintaining the Database

Applying advanced programming techniques

• Developing modular code using packages
• Managing default, serialisable and autonomous transactions
• Resolving potential trigger problems

Securing the database

• Enforcing security using PL/SQL
• Implementing row-level security applications
• Controlling access with Oracle Label Security

Locking and read consistency

• Implicit vs. explicit locking
• Avoiding deadlocks

Exploiting Oracle Built-in Packages

Handling other media types

• Accessing operating system files
• Programming with LOBs

Automating routine processing

• Scheduling routines with DBMS_JOB or Oracle 10 g Scheduler
• Starting jobs based on system or application events

Enhancing application capabilities

• Controlling the application schema with invoker's rights
• Interprocess communication with Advanced Queuing
• Generating DDL for existing objects

Improving Query Performance

Identifying basic tuning techniques

• Developing a tuning methodology
• Planning and managing the tuning process

Employing tuning tools

• Running EXPLAIN PLAN and autotrace
• SQL Trace and TKPROF output

Query tuning techniques

• Improving subqueries
• Optimising join operations
• Partitioning data to improve access to frequently used data
• Storing rows in sorted sequence with sorted hash clusters

Deciphering and Controlling the Optimiser

Optimiser concepts

• Fundamentals of access paths
• Gathering object and system statistics with DBMS_STATS
• Managing low-selectivity columns with histograms

Influencing the Optimiser

• Utilising hints and optimiser mode
• Specifying first-rows optimisation
• Determining the driving table

Tuning the Application

Optimising performance

• Partitioned, function-based and other indexing options
• Monitoring index usage
• Reducing heavy I/O operations

PL/SQL tuning techniques

• Generating dynamic SQL
• Binding variables to enable SQL statement reuse

Oracle PL/SQL 9i/10g: Application Development and Tuning for Oracle Databases / 492