Oracle 9i performance tuning for DBAs

Concentrika poppy

	About the course 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. Expects to get lots of hands-on practice, to generate and see problems and finally to solve them. The training is strongly lab based and takes a real world view of everyday business computing problems. Audience Database administrators Developers Data warehouse support team members Prerequisites You should be knowledgeable in Oracle 8i or Oracle 9i DBA to get the best out of this course. Duration 3 days
Overview of Oracle 9i 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 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 IO Disk performance Assessing physical reads Segment statistics Segment wait events Longops facility Disk I/O DBWR slaves
Optimize Sort Operations	Fundamentals of sorts Recognizing 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