|
Oracle9i Database: Performance
Tuning
Exam Number: 1Z0-033
Associated Certifications: Oracle9i DBA OCP
Exam Price: $125.00 USD
Duration: 120 minutes
Passing Score: 64%
Overview of Oracle9i Performance Tuning
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
Sizing the Buffer Cache
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
Sizing other SGA Structures
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
Monitoring and Detecting Lock Contention
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
Using Oracle Blocks Efficiently
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
Diagnostic and Tuning Tools
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
Database Configuration and I/O Issues
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
Optimize Sort Operations
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
Tuning Oracle Shared Server
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
Application Tuning
Describe the role of the DBA in tuning Applications
Explain different storage structures, and why one
storage structure may be preferred over another
Explain the different types of indexes
Explain Index Organized Tables
Describe partitioning methods
Explain the use of the DBMS_STATS procedure
Describe Materialized Views and use of Query Rewrites
List requirements for OLTP, DSS and Hybrid Systems
SQL Statement Tuning
Describe how the Optimizer is used
Explain the concept of plan stability
Use stored outlines
Describe how hints are used
Use SQL Trace and TKPROF
Collect statistics on indexes and tables
Describe the use of histograms
Copy statistics between databases
Sizing the Shared Pool
Measure and tune the library cache hit ratio
Measure and tune the dictionary cache hit ratio
Size and pin objects in the shared pool
Tune the shared pool reserve space
Describe the UGA and session memory considerations
Explain other tuning issues related to the shared
pool
Set the large pool
Diagnosing Contention For Latches
Describe the purpose of latches
Describe the different types of latch request
Diagnose contention for latches
Tune the appropriate resources to minimize latch contention
Tuning the Operating System and Using Resource
Manager
Describe different system architectures
Describe the primary steps of OS tuning
Identify similarities between OS and DB tuning
Understand virtual memory and paging
Explain the difference between a process and a thread
Set up Database Resource Manager
Assign users to Resources Manager groups
Create resource plans within groups
|
