Oracle Server - Performances e Tuning del Database

Livello - durata - Modalità di erogazione - Prezzo

Avanzato - 5 giorni - Aula - (Euro) 1400

Destinatari

Amministratori di Dabatase, Consulenti

Prerequisiti

Avere competenze assimilabili al corso "amministrazione avanzata"

Obiettivi

Il tuning delle performance del database è un'operazione di importanza critica in qualsiasi sistema informatico efficace. La riduzione dei tempi di attesa, l'aumento della produttività  degli utenti e l'ottimizzazione delle performance del database Oracle sono fattori cruciali per il successo.

In Questo corso viene illustrato il valore di una progettazione accurata del database e vengono indicati i metodi per il tuning di un database Oracle in modo da ottimizzare le performance; viene spiegata l'importanza di progettare in modo ottimale il database fin dall'inizio e viene descritto il metodo utilizzato per eseguire il tuning di un database di produzione Oracle9i. Viene descritto il tuning del database e delle istanze anzichè soffermarsi sui problemi di performance specifici del sistema operativo.

Contenuti

Overview of Oracle 9i Performance TuningJob Roles in Tuning; Tuning phases; Tuning goals and Service Level Agreements; Common performance problems; Tuning Methodology

Diagnostic and Tuning ToolsAlert log file; Background process trace files; User trace files

- Dictionary views providing statistics; Dynamic performance views; TIMED_STATISTICS parameter to collect statistics; Statistics Package; STATSPACK procedures

Sizing the Shared PoolOverview of the shared pool; Library cache tuning; Reuse statements; Using Reserved Space; Keeping Large Objects; Related tuning issues; Data Dictionary Cache (DDC) Tuning

Sizing the Buffer CacheOverview of tuning the buffer cache; Buffer Cache Sizing Parameters in Oracle9i; Depreciated Buffer Cache Parameters; Buffer Cache Advisory Parameter; Dynamically resizing SGA components; Granules of Allocation; Increase the size of a SGA component; Resolving Techniques

Sizing other SGA StructuresSizing the redo log buffer; Detecting contention; Resolving contention; Sizing the Java Pool; Monitoring Java Pool Memory; Sizing the SGA for Java; Sizing Java Pool Memory; Limiting Java Session Memory Usage

Database Configuration and I/O IssuesDistributing files across devices; Tablespace usage; Detecting improper tablespace usage; Moving datafiles; Oracle File Striping; Tuning Full Table Scans; Tuning checkpoints; Redo log file configuration

Optimize Sort OperationsWhat is the sorting process; Sort Area Parameters (New & Old); Tuning sort operations (DB Configuration); Tuning Temporary segments; Reducing sort operations (application tuning); Identify sort operations; Avoiding sort operations; Diagnostic Tools

Diagnosing Contention For LatchesOracle's Implementation of latches; Latch request types - willing-to-wait, immediate; Reducing Latch Contention; Significant Latches; Shared Pool and Library Cache Latches

Tuning Rollback (or UNDO) SegmentsDescribing Rollback segment usage; Usage and configuration; Detecting Problems with Rollback Segments; Resolving Problems with Rollback Segments; Creating Rollback Segments and bringing them online; Allocating transactions to a rollback segment; Resolving RBS problems; Describing System Managed Undo

Monitoring and Detecting Lock ContentionConcepts of Locking; Overview of Locking issues; Types of DML locks

- Levels of locks; Types of DDL lock modes; Detecting Blocking Locks; Monitoring locking activity; Deadlocks

Tuning Oracle Shared ServerIntroducing Oracle Shared Server; When are Shared Servers required in Oracle9i; Monitoring dispatcher processes; Monitoring shared server processes; Increasing or decreasing the number of Dispatchers; Increasing or decreasing the number of Shared Servers; Monitoring Process Usage; Monitoring Memory Usage

Application TuningOracle Data Structures; Selecting the physical structure; Data storage structures; Clusters; Indexes; B-Tree; Compressed

Using Oracle Blocks EfficientlyDatabase Storage hierarchy; Allocating extents; Monitoring Space Usage in Data Segments; Recovering space from sparsely populated segments; Database Blocks; Chaining and migration; Detecting row chaining and migration; Resolving row chaining and migration

SQL Statement TuningCost-based, Rule-based Optimiser Modes; New Cost Based Optimiser option; Plan Stability; Plan Equivalence; Stored Outlines; Creating Stored Outlines; Maintaining Stored Outlines; Hints

Tuning the Operating System System Architecture; Virtual and Physical Memory; Paging and Swapping; CPU Tuning Guidelines; Process Vs Thread; Resource Manager Concepts