Oracle 11g - Application Tuning - Workshop Training Course

Application Tuning Methodology for Government

Architecture of Database and Instance for Government

• Server Processes
• Memory Structure (SGA, PGA)
• Parsing and Shared Cursors
• Data Files, Log Files, Parameter Files

Analysis of Command Execution Plan for Government

• Hypothetical Plan (EXPLAIN PLAN, SQLPlus AutoTrac XPlane)
• Actual Execution Plan (V$SQL_PLAN, XPlane, AWR)

Monitoring Performance and Identifying Bottlenecks in the Process for Government

• Monitoring Current Instance Status Using System Dictionary Views
• Historical Monitoring of Dictionaries
• Application Tracking (SQLTrace, TkProf, TreSess)

The Optimization Process for Government

• Cost-Based Optimization and Regulation
• Determination to Optimize

Controlling Work of Cost-Based Optimizer by:

• Session Parameters and Instance Settings
• Hints (Tips)
• Query Plan Patterns

Statistics and Histograms for Government

• Impact of Statistics and Histograms on Performance
• Methods of Collecting Statistics and Histograms
• Strategies for Counting and Estimating Statistics
• Management of Statistics: Blocking, Copying, Editing, Automation of Collection, Monitoring Changes
• Dynamic Data Sampling (Temporary Tables, Complex Predicates)
• Multi-Column Statistics Based on Expressions
• System Statistics

The Logical and Physical Structure of the Database for Government

• Tablespaces
• Segments
• Extensions (EXTENTS)
• Blocks

Data Storage Methods for Government

• Physical Aspects of Tables
• Temporary Tables
• Index-Organized Tables
• External Tables
• Partitioned Tables (Range, List, Hash, Composite)
• Physical Reorganization of Tables

Materialized Views and the QUERY REWRITE Mechanism for Government

Methods of Data Indexing for Government

• Building B-TREE Indexes
• Index Properties
• Types of Indexes: Unique, Multi-Column, Function-Based, Reverse
• Index Compression
• Rebuilding and Merging Indexes
• Virtual Indexes
• Private and Public Indexes
• Bitmap Indexes and Conjunctions

Case Study - Full-Scan Data for Government

• Impact of Table Placement and Block-Level Read Performance
• Data Loading Methods: Conventional and Direct Path
• Predicate Order

Case Study - Access to Data via the Index for Government

• Index Reading Methods (UNIQUE SCAN, RANGE SCAN, FULL SCAN, FAST FULL SCAN, MIN/MAX SCAN)
• Using Function-Based Indices
• Index Selectivity (Clustering Factor)
• Multi-Column Indexes and SKIP SCAN
• NULL Values and Indexes
• Index-Organized Tables (IOT)
• Impact of Indexes on DML Operations

Case Study - Sorting for Government

• Sorting in Memory
• Sort by Index
• Linguistic Sorting
• Entropy's Effect on Sorting (Clustering Factor)

Case Study - Joins and Subqueries for Government

• Join Methods: MERGE, HASH, NESTED LOOP
• Joins in OLTP and OLAP Systems
• Order of Join Operations
• Outer Joins
• Anti-Joins
• Semi-Joins (Incomplete)
• Simple Subqueries
• Correlated Subqueries
• Views and the WITH Clause

Other Operations of Cost-Based Optimizer for Government

• Buffer Sort
• INLIST
• VIEW
• FILTER
• Count Stop Key
• Result Cache

Distributed Queries for Government

• Reading Query Plans Using DBLinks
• Selecting the Leading Instance

Parallel Processing for Government