AUDIENCE:   This course is designed for: Database administrators, Developers, Data warehouse support team members. PREREQUISITES:   Attendees should be knowledgeable in Oracle DBA (preferably at 9i or 10G) to get the best out of this course. DURATION:   3 days. Hands on. OBJECTIVES:   The Oracle RDBMS is typically deployed throughout a business, from the corporate mainframe, to enterprise UNIX servers, down to departmental level Intel platforms. For a database of any significant size, performance immediately becomes an issue. Users may resent a query taking 3.5h, for instance. Or on the other hand, an update locks a table for 30 minutes while it modifies data, which denies application access to the table(s) concerned. There will always be a platform ceiling set by hardware and o/s kernel performance but wouldn’t it be nice to use the gap between what we are getting today, and what we could get from our platform investment. Learn to find out where this performance ceiling is and at what level the current system is running. Essentially there are two main avenues for RDBMS optimisation. Strategies deployed by the DBA, such as cache management and DBWR and LGWR optimisation, is one of them. The other approach is to investigate how the SQL is running, to change either the SQL or its execution environment and see if that made a difference. The subject of this course is the administration of the database. The classical bottlenecks on an Oracle platform are memory, i/o, network and CPU. On a loaded system, one of these areas will cause a bottleneck. Which one is it? This is what we aim to show you how to find out. Remove the bottleneck and we hit another one. Learn how to measure the systems performance in several areas, how to stress test it and examine the effect of changes you may make. COURSE CONTENT:   Overview of Oracle Performance Tuning Job Roles in Tuning Tuning phases Tuning goals and Service Level Agreements Common performance problems Tuning Methodology Diagnostic and Tuning Tools Alert 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 Database events Sizing the Shared Pool Overview 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 Cache Overview of tuning the buffer cache Buffer Cache Sizing Parameters in Oracle9i/10G Buffer Cache Advisory Parameter Dynamically resizing SGA components Granules of Allocation Increase the size of a SGA component Sizing other SGA Structures Sizing 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 Issues Sources of I/O Disk performance Assessing physical reads Segment statistics Segment wait events Longops facility Disk I/O DBWR slaves Optimize Sort Operations Fundamentals of sorts Recognisng sorts have occurred Automated PGA memory management PGA 9i views Tuning considerations Diagnosing contention Latches and internal locks Freelist contention Explicit (manual) data locking Tuning Oracle Shared Server Shared server concepts Setting up the shared server Monitoring shared servers Application Tuning Query optimization EXPLAIN PLAN ANALYZE AUTOTRACE TKPROF Histograms Stored outlines Cached execution plans Automatically gathering stats on tables Materialized views Query rewrite Unused indexes Data storage Tables and indexes Identifying unused indexes Partitioning Clustering Temporary tables Large objects (LOBS) Fragmentation of extents Row management Structure of a block Chained rows problem Appendix 1 Managing the lab environment Appendix 2 Guidelines for monitoring the Unix o/s CPU Monitoring Memory Monitoring Swapping Statistics Process Queuing Statistics Disk Capacity Statistics Disk Performance Statistics MB07/01