09_oracle_perf_tuning_PPT.pdf

? 2007 PTC Module 9: Oracle Performance Tuning Windchill 9.0 System Administration Objectives After completing this module, you will be able to: ! Identify the background processes of the Oracle instance. ! Identify the primary memory structures of the instance. ! Identify the key files of the database. ! Explore the Automatic Shared Memory Management mechanism. ! Manually size various memory caches. ! Use the Automatic Workload Repository. ! Identify and use various tuning advisors. ! Install and use Oracle 10g Tuner for Windchill solutions. Reviewing Instance and Database Instance and database are two terms that seem to cause confusion. ! Instance ? A set of Oracle processes and an SGA (System Global Area). ! Database ? A collection of physical operating system files. SGA database process database process database process database process database process database process database process file filefile file filefile Database Instance 1 2 3 Reviewing Background Processes A number of operating system processes (UNIX) or threads (Windows) manage instance activities in the background. SGA data buffers Redo buffer PMON SMON RECO Remote DB CKPT DBWn LGWR ARCn Datafiles Online Redo Logs Archived Logs shared pool System Global Area (SGA) The System Global Area (SGA) contains three important memory structures. ! Shared pool ! Database buffer cache ! Redo log buffer System Global Area Data Dictionary Cache Shared Pool Database Buffer Cache Redo Log Buffer Library Cache Reviewing the Shared Pool The shared pool contains two memory structures: ! Library cache ! Data dictionary cache Data Dictionary Cache Shared PoolDatabase Buffer Cache Redo Log Buffer Library Cache System Global Area Reviewing the Database Buffer Cache The database buffer cache holds copies of the data blocks from the data files. LRU List Dirty List DB Buffer Cache DBWn Server Data Files System Global Area Reviewing the Redo Log Buffer Redo Log Buffer Archived Log Files Server System Glob