Oracle 1Z0-033 Oracle9i:Performance Tuning

About Exam

• Overview of Oracle9i Performance Tuning
• Sizing the Buffer Cache
• Sizing other SGA Structures
• Monitoring and Detecting Lock Contention
• Using Oracle Blocks Efficiently
• Diagnostic and Tuning Tools
• Database Configuration and I/O Issues
• Optimize Sort Operations  
• Tuning Oracle Shared Server
• Application Tuning
• SQL Statement Tuning
• Sizing the Shared Pool
• Diagnosing Contention For Latches
• Tuning the Operating System and Using Resource Manager

Exam Objectives

• Describe the roles associated with the database tuning process
• Describe the dependency between tuning in different development phases
• Describe service level agreements
• Describe appropriate tuning goals
• Describe the most common tuning problems
• Describe the tuning considerations during development and production
• Describe performance and safety tradeoffs

• Describe how the buffer cache is used by different Oracle processes
• Describe the tuning issues related to the buffer cache
• Monitor the use of the buffer cache, also the different pools within the buffer cache
• Implement dynamic SGA allocation
• Set the DB_CACHE_ADVICE parameter
• Create and size multiple buffer pools
• Detect and resolve free list contention

• Monitor and size the redo log buffer
• Monitor and size the java pool
• Control the amount of Java session memory used by a session
• Configure the instance to use I/O Slaves
• Configure and use multiple DBW processors

• Define levels of locking
• Describe possible causes of contention
• Use Oracle utilities to detect lock contention
• Resolve contention in an emergency
• Prevent locking problems
• Recognize Oracle errors arising from deadlocks

• Describe the correct usage of extents and Oracle blocks
• Explain space usage and the high water mark
• Determine the high water mark
• Recover space from sparsely populated segments
• Describe and detect chaining and migration of Oracle blocks
• Perform index reorganization
• Monitor indexes to determine usage

• Explain how the alert.log file is used
• Explain how background trace files are used
• Explain how user trace files are used
• Describe the statistics kept in the dynamic performance views
• Explain how StatsPack collects statisticsd
• Collect statistics using StatsPack
• Collect statistics using Enterprise Manager
• Use other tuning tools

• Explain the advantages of distributing different Oracle file types
• Describe reasons for partitioning data in tablespaces
• Diagnose tablespace usage problems
• Describe how checkpoints work
• Monitor and tune checkpoints
• Monitor and tune redo logs

• Describe how sorts are performed
• Identify the SQL operations which require sorts
• Differentiate between disk and memory sorts
• Create and monitor temporary tablespaces
• Reduce total sorts and disk sorts
• Determine the number of sorts performed in memory
• Set old and new sort parameters

• Identify issues associated with managing users in a Shared Server environment
• Diagnose and resolve performance issues with Oracle Shared Server processes
• Configure the Oracle Shared Server environment to optimize performance

• Describe the role of the DBA in tuning Applications
• Explain different storage structures, and why one storage structure may be preferred over another
<