Oracle 10g Architecture for Developers (ORL410)

Course Description

• What memory areas and processes make up an Oracle "instance" (and what an "instance" is)
• What files are part of the Oracle database
• How the Oracle cost-based optimizer parses and executes SQL statements
• Oracle locking mechanism and rules
• Oracle transaction statements, rules and isolation levels
• What types of storage structures Oracle 10g supports (e.g. heap tables, index clusters) and guidelines on choosing
• What types of indexes Oracle 10g supports (e.g. b-tree, bitmap) and guidelines on choosing
• The data types Oracle offers with 10g and guidelines on choosing

Course Objectives

• Describe the purpose of the major components within an Oracle Database and Oracle instance
• Make effective and efficient use of the architecture, for example, incorporate effective shared pool reuse into your applications
• Code scalable applications, because you will understand the concurrency rules Oracle employs.
• Make educated schema design decisions, for example, when to use a heap table or an index-organized table or when to choose a bitmap index over a B-tree index.

Course Audience

Course Topics

• Database and Instance
• The Big Picture
• Database Files
• Segments and Extents
• DBA_DATA_FILES0
• DBA_SEGMENTS
• Redo Log Files
• Control Files
• Temp Files
• Parameter Files
• Password Files

• The Big Picture
• The Processes
• V$BGPROCESS
• Database Block Writer
• Log Writer - LGWR
• System Monitor - SMON
• Process Monitor - PMON
• Checkpoint - CKPT
• Archiver ARCn
• Miscellaneous Processes
• New 10g Processes

• The Big Picture
• PGA and UGA
• The Oracle SGA
• Automatic Shared Memory Management
• Size of SGA Areas
• Shared Pool
• Library Cache
• Library Cache Retention
• SQL Statement Processing
• Data Dictionary Cache
• Buffer Cache
• Database Block Size
• Non-Standard DB Size
• Keep and Recycle Caches
• Redo Log Buffer
• Other Pools

• Oracle10g Architecture
• The SGA
• SQL Statement Processing Overview
• Soft Parse
• Hard Parse: Optimization
• Row Source Generation
• Execute Step
• Fetch Step

• Bind Variables
• Querying V$SQL
• PL/SQL Bind Variables
• Cursor Sharing
• Bind Variable Peeking

• Introduction to Locks
• Row Lock Architecture
• Basic Locking Rules
• DML Locks
• DDL Locks
• Locking Issues: Lost Update
• Locking Issues: Blocking
• Locking Issues: Deadlocks
• Deadlocks: Cause and Fix

• Transaction Review
• Supported Statements
• Statement Level Read Consistency
• Transaction Level Read Consistency
• Oracle Isolation Levels
• Phantom and Non-Repeatable Reads
• Read Committed
• Serializable
• Read Only
• Savepoints
• Implicit Commits

• Schema (Data Storage) Options
• Heap Tables
• Index Organized Tables
• Index Clustered Tables
• Hash Clustered Tables
• Sorted Hash Clusters (10g)
• Materialized Views
• Temporary Tables
• Nested Tables
• Object Tables
• Partitioned Tables

• Index Concepts
• B-Tree Index Concepts
• B-Tree Effect on Query Performance
• Bitmap Indexes
• Reverse Key Indexes
• Function-Based Indexes
• Bitmap Join Indexes

• Character and Binary Types
• Number Types
• Datetime and Timestamp Types
• Long Types
• XML Type
• Miscellaneous Data Types

Prerequisite Knowledge/Experience

Delivery & Duration