Introduction to the Application Environment

The Oracle database environment

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

Database design considerations

• Managing basic storage structures
• Enhancing performance with optional storage structures

Preparing the Database

Populating the database

• Loading data from other sources using SQL*Loader or external tables
• Writing to external tables using Data Pump
• Upgrading Data Pump with legacy mode

Maintaining the application schema

• Implementing best practices for the PL/SQL development structures
• Managing dependencies
• Generating DDL for existing objects

Exploiting Oracle Built-In Packages

Automating routine processing

• Scheduling routines with DBMS_JOB or Oracle Scheduler
• Starting jobs based on system or application events
• Grouping scheduled jobs for program dependent scheduling

Handling other media types

• Programming with LOBs
• Providing transparent compression, encryption and deduplication with SecureFiles

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
• Waiting for DML and DDL locking

Upgrading the application

• Building a new application version using Edition-Based Redefinition
• Implementing the new version

Exploiting Oracle Built-In Packages

Handling other media types

• Accessing operating system files
• Programming with LOBs

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

• 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
• Determining the driving table

Tuning the Application

Optimising performance

• Exploring B-Trees
• Bitmapped, function-based and other indexing options
• Partitioning indexes with virtual columns and other options
• Monitoring index usage with invisible indexes

Tuning with the automated tools

• Tuning SQL with the SQL Tuning Advisor and the SQL Access ADVISOR
• Identifying real-time performance problems with Real-Time SQL Monitoring