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
• Storage management issues

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 Scheduler
• Starting jobs based on system or application events

Enhancing application capabilities

• Controlling the application schema with invoker's rights
• 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
• Tuning with automated tools

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
• Exploring B-Trees
• Parallel DML

PL/SQL tuning techniques

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