Oracle9i Database: Performance Tuning™

Exam Number:	1Z0-033
Associated Certifications:	Oracle9i DBA OCP
Exam Price:	US$125
Exam Registration:	Register

Duration:

120 minutes

Number of Questions:

Passing Score:

72%

Passing score subject to change. View Details

Recommended Training and Preparation

The exam is based on Oracle9i Release 2

Oracle9i Database: Performance Tuning

Oracle9i Database Performance Tuning R2 Self-Study CD Course (NETg Oracle)
Self-Study CD-ROMs are an excellent tool to help you prepare for your exam. SSCDs reinforce the course material and allow you to focus on sections that you need to review. Note: the SSCDs do not count towards your course requirements for certification and should be used as a study aid only.

Additional Information & Resources

Practice Exams

Oracle authorized practice exam from Self Test Software. 1Z0-033 Oracle9i Database: Performance Tuning
Oracle authorized practice exam from Transcender: Cert-1Z0-033 : DBCert: 9i Database: Performance Tuning

Oracle9i DBA Certification Exam Candidate Guide
Exam Retake Policy
Sample Questions

Exam Topics

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

Oracle Certification Program Fraudulent Activity Policy

Oracle reserves the right to take action against any candidate involved in fraudulent activities, including, but not limited to, fraudulent use of vouchers, promotional codes, reselling exam discounts and vouchers, cheating on an exam, alteration of score reports, alteration of completion certificates, violation of exam retake policies or other activities deemed fraudulent by Oracle.

If Oracle determines, in its sole discretion, that fraudulent activity has taken place, it reserves the right to take action up to and including, but not limited to, decertification of a candidate's Oracle Certified Associate, Oracle Certified Professional and/or OCM credentials, temporary, indefinite or permanent ban of a candidate from Oracle certification programs, notification to a candidate's employer, and notification to law enforcement agencies. Candidates found committing fraudulent activities forfeit all fees previously paid to Oracle, or to Oracle's authorized vendors, and may be required to pay additional fees for services rendered.

View the Oracle Certification Program Candidate Agreement which requires your agreement before the start of each exam.