Benefits of Oracle Certification
In the present day competitive world, it has become essential for professionals, especially in the field of Oracle Database Administration, to acquire proper certifications to have an edge over others.
Oracle Certified Professional Exams are a boon for the Oracle DBAs to prove their underlying skills and expertise and earn a formal recognition for the same from Oracle. Oracle 9i Certified Professional Database Administrator Track has been ranked fourth in the Top-Ten Certifications. Read More….
Prerequisites For Oracle 9i Certified Professional DBA Credentials
To successfully become an Oracle 9i Certified Professional Database Administrator, you need to pass the following four exams:
· 1Z0-007 – Introduction to SQL
· 1Z0-031 – Database Fundamentals 1
· 1Z0-032 – Database Fundamentals 2
· 1Z0-033 – Database Performance Tuning
When you successfully complete the first two exams i.e. 1Z0-007 and 1Z0-031, you acquire the Oracle 9i Certified Associate Database Administrator Certification.
When you successfully complete all the four exams listed above, you achieve the Oracle 9i Certified Professional Database Administrator Certification.
In order to achieve your OCP credential you must also complete at least one of the following Oracle University hands-on courses:
· Introduction to Oracle9i: SQL
· Oracle9i Database: Fundamentals I
· Oracle9i Database: Fundamentals II
· Oracle9i Database: Performance Tuning
· Oracle9i New Features for Administrators
· Introduction to Oracle: SQL and PL/SQL
This requirement applies to all those OCP candidates who have taken their first OCA Exam on or after September 1, 2002. If you have earned OCA or even registered for any of the exams before September 1, 2002 then you do not have to complete the course requirement.
Learn More about the latest details about the OCP DBA Exams.
1Z0-033 Exam Details
Exam 1Z0-033 is the fourth exam of the Oracle 9i Certified Professional (OCP) Track. This exam covers the performance tuning issues related to the Oracle database, the instance and the underlying platform.
This Exam contains 59 scored questions, out of which you need to answer at least 38 questions correctly to pass this exam. Thus, the passing percentage is 64%. Total time allotted for the same is 90 minutes.
There may be some questions that are not scored but you cannot differentiate the scored questions from those that are not scored so attempt all the questions.
Types of questions
(1) Multiple Choice Single Response:
In this category, there are multiple choices given for each question; from those choices you have to select ONE correct choice only.
(2) Multiple Choice Multiple Responses:
In this category, there are multiple choices given for each question and you have to select multiple answers, the number of answers that you need to select is specified in the question.
Exam Objective
Oracle Corporation has compiled a list of objectives that are tested in this exam. To view these objectives on the Oracle site click here.
These objectives will be described in the following section. In each objective, I have put NOTES that will specify the areas that you cannot afford to ignore before taking the exam. If you have any doubts about the areas covered in the NOTES, then you need some more preparation before taking the exam.
NOTES are not exhaustive, they are an indicator of what you should study in depth, and therefore will point you towards the right direction for exam preparation.
Objective Credibility 1) Overview of Oracle 9i Performance Tuning * 2) Diagnostic and Tuning Tools ** 3) Sizing the Shared Pool *** 4) Sizing the Buffer Cache *** 5) Sizing Other SGA Structures *** 6) Database Configuration and I/O Issues *** 7) Optimize Sort Operations *** 8) Diagnosing Contention for Latches ** 9) Tuning Rollback (UNDO) Segments *** 10) Monitoring and Detecting Lock Contention ** 11) Tuning Oracle Shared Server ** 12) Application Tuning ** 13) Using Oracle Blocks Efficiently ** 14) SQL Statement Tuning ** 15) Tuning the Operating System and Resource Manager **
Note:
*** Maximum Credits
** Intermediate Credits
* Least Credits
Objective 1: Overview of Oracle9i Performance Tuning
NOTES:
The roles associated with the performance and tuning of the Database and instance are users, management, application developer, application designer, DBA, systems administrator, network engineer, performance engineer – know the responsibility for each role.
Understand how performance and tuning should be handled in the different phases in a developing database. i.e. Design , Application Coding , Memory Tuning ,Tuning Input/Output , Tuning Contention , and Tuning the Operating System. These performance and tuning phases should be handled in this top-down approach for best results.
Understand that Service Level Agreement (SLA) is a document that clearly indicates the user requirements/expectations from the database.
Tuning Goals should fulfill all the requirements mentioned in the SLA.
Objective 2: Diagnostic and Tuning Tools
NOTES:
Know that alert.log file is found in the directory specified by the parameter BACKGROUND_DUMP_DEST in the Oracle database that follows the OFA. It records the error messages and various activities that occur during the operation of a database.
Background trace files are generated in the directory specified by BACKGROUND_DUMP_DEST. They are generated by the background processes: – PMON, SMON, DBW0, LGWR, CKPT, and ARC0.
User trace files are generated in the directory specified by the parameter USER_DUMP_DEST and are generated by user processes. User tracing can be activated by setting init.ora parameter SQL_TRACE=TRUE or DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SID, SERIAL#, TRUE/FALSE) on the DBA level.
Understand very clearly, the common views that are used for dynamic troubleshooting and performance measurement of the database and instance. They are V$SYSSTAT, V$SYSTEM_EVENT, V$WAITSTAT, V$SESSION_WAIT, and V$SESSION_EVENT.
Study in detail about how to install STATSPACK; collecting statistics both manually and automatically, understanding all the sections in the STATSPACK report, and features of STATSPACK.
Gather a working knowledge of Oracle Enterprise Manager tools for measuring performance.
Other tools like UTLBSTAT.SQL / UTLESTAT.SQL, usage of TKPROF for reading the user trace files will assist in the understanding of performance tuning.
Objective 3: Sizing the Shared Pool
NOTES:
The components of the cached SQL statements are the actual text, hash value, P-Code, statistics, and execution plan.
Know the contents of the views V$SQL, V$SQLAREA, V$SQLTEXT, V$SQL_PLAN, V$DB_OBJECT_CACHE, and V$SESSION.
Use of V$LIBRARYCACHE view to monitor the Library Cache especially the columns PINS, PINHITS, RELOADS, INVALIDATIONS.
Use of V$SGASTAT to determine the free memory in shared pool also aids in optimal sizing of the shared pool.
STATSPACK provides the information about library cache in INSTANCE EFFICIENCY PERCENTAGES section (Library Hit %). LIBRARY CACHE ACTIVITY provides the detailed statistics about the library cache.
Use the V$ROWCACHE view for determining the dictionary cache Hit Ratio.
STATSPACK provides detailed information about dictionary cache in DICTIONARY CACHE STATS.
Pinning PL/SQL into the shared pool reserved area (SHARED_POOL_RESERVED_SIZE) can be done using the DBMS_SHARED_POOL.KEEP procedure.
Pinning should be done right after the instance startup.
Understand the use of BIND variables for developing generic code in OLTP applications but not in DSS.
While using bind variables, set the parameter CURSOR_SHARING to use bind variables.
In shared server mode, the UGA is in the SGA – specifically in the shared pool if large pool is not configured. Consider increasing shared pool size accordingly.
Other init parameters that you must know: CURSOR_SPACE_FOR_TIME, OPEN_CURSORS, SESSION_CACHED_CURSORS, and PARALLEL_AUTOMATIC_TUNING.
Objective 4: Sizing the Buffer Cache
NOTES:
The Buffer cache is organized into two lists – Least Recently Used List (LRU) and Dirty List.
LRU list contains the Pinned, Free, and dirty buffers that have not yet been moved to the dirty list.
Dirty list contains the dirty buffers that are yet to be written to the disk.
When a server process reads a buffer from disk, and writes it into the LRU list, it writes it into the Most Recently Used end (MRU) of the LRU List, exceptions being the Full Table Scans (FTS), in which case it writes into the LRU end.
V$SYSSTAT gives the following information:
PHYSICAL READS, PHYSICAL READS DIRECT, PHYSICAL READS DIRECT (LOB) , SESSION LOGICAL READS, and FREE BUFFERS INSPECTED.
V$SYSTEM_EVENT gives FREE BUFFER WAITand BUFFER BUSY WAIT.
STATSPACK sections: INSTANCE EFFICIENCY PERCENTAGES, INSTANCE ACTIVITY STATS, and BUFFER POOL STATISTICS.
To determine the size to which the Database Buffer Pool should be resized to, set the parameter DB_CACHE_ADVICE and then get the info from V$DB_CACHE_ADVICE view.
Database buffers can be of three kinds – DEFAULT POOL (mandatory), RECYCLE POOL, KEEP(optional), and POOL(optional).
For nonstandard block size, you need to create database buffer cache that is for the non-standard block size i.e., DB_nk_CACHE_SIZE. This non-standard cache can only be DEFAULT pool and not KEEP or RECYCLE.
In order to determine which segments to cache in the KEEP pool, use views V$BH, DBA_OBJECTS, V$CACHE.
Consider objects for KEEP pool that are of size 10% or less than the size of the DEFAULT pool
For RECYCLE pool, consider objects that are of size twice or more than the size of the DEFAULT pool.
CACHING tables causes them to be written into the MRU end of the LRU list even if FTS is performed.
Use views V$BUFFER_POOL and V$BUFFER_POOL_STATISTICS.
Objective 5: Sizing other SGA Structures
NOTES:
Size the REDO LOG BUFFER by keeping in mind that it is flushed every 3 seconds OR when it is 1/3 full OR when there is 1MB of data in it whichever occurs first. Default size is 512KB or 128K *number of CPU. This default value is usually enough.
REDO LOG statistics in V$SYSSTAT: REDO BUFFER ALLOCATION RETRIES, REDO ENTRIES, REDO LOG SPACE REQUEST, V$SESSION_WAIT: LOG BUFFER SPACE.
STATSPACK contains info in INSTANCE EFFICIENCY PERCENTAGES and INSTANCE ACTIVITY STATS.
Default size of the JAVA POOL IS 20MB.
Know the parameters JAVA_POOL_SIZE, JAVA_SOFT_SESSIONSPACE_LIMIT, JAVA_MAX_SESSIONSPACE_SIZE.
Factors necessitating the use of Large Pool are parallel operations, backup and recovery operations, and shared server operations. Understand the tuning of large pool and shared pool for the shared servers.
To determine the increase in size due to UGA in the SGA in the shared server mode use the statistic SESSION UGA MEMORY MAX in the V$STATNAME and get the value from V$SESSSTAT view.
Understand the parameter PGA_AGGREGATE_TARGET and its configuration for automatic PGA memory management in the case of dedicated servers.
Know DBWR_IO_SLAVES for creating slaves for the database writer, slaves will help only the I/O activity. DB_WRITER_PROCESSES will create multiple DBWn processes, DBWn process will help in managing the buffers and also I/O. Always, it is more efficient to work with ‘x’ number of DBWR instead of working with 1 DBWR having ‘x’ number of slaves.
If you are getting FREE BUFFER WAITS in the V$SYSTEM_EVENT view then consider database writer processes instead of slaves.
Objective 6: Database Configuration and I/O Issues
NOTES:
Good understanding of the Optimal Flexible Architecture (OFA) to distribute the physical files on multiple disks.
Views for datafile / tempfile info: V$FILESTAT, V$DATAFILE, V$TEMPFILE.
STATSPACK gives detailed information about datafile and tablespaces in their respective sections.
Tricks to distribute the various objects on the datafiles based on assess time and access rate.
Tune the application by SQL tuning and sorts tuning before tuning I/O issues such as separation of files based on I/O.
Understand when does checkpoint occur and what all activities does it do, know parameters related to the checkpoint configuration which are:
FAST_START_MTTR_TARGET
LOG_CHECKPOINT_TO_ALERT
LOG_CHECKPOINT_TIMEOUT
LOG_CHECKPOINT_INTERVAL
CHECKPOINT events found are: V$SYSSTAT: – BACKGROUND CHECKPOINT STARTED, BACKGROUND CHECKPOINT COMPLETED, V$SYSTEM_EVENT: – LOG FILE SWITCH(CHECKPOINT INCOMPLETE) , CHECKPOINT COMPLETED.
Size the REDO LOG FILES such that you eliminate the occurrence of LOG FILE PARALLEL WRITE from V$SYSTEM_EVENT view.
Spread datafiles, redo log files, and archived redo log files over multiple disks based on the I/O demands of the application.
Objective 7: Optimize Sort Operations
NOTES:
SQL statements that use the following cause sorts:
ORDER BY, GROUP BY, DISTINCT, UNION, INTERSECT, MINUS, ANALYZE, CREATE INDEX, and JOIN on non-indexed columns.
Know parameters for dedicated server WORKAREA_SIZE_POLICY & PGA_AGGREGRATE_TARGET, and for shared server SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE.
V$SYSSTAT gives the statistics: – SORT(MEMORY), SORT(DISK)
STATSPACK has info in sections: – LOAD PROFILE, INSTANCE EFFICIENCY PERCENTAGES and INSTANCE ACTIVITY STATS.
Minimize sorts by avoiding the clauses that cause sorts. This can be practiced by using UNION ALL instead of UNION and using ESTIMATE instead of COMPUTE with ANALYZE, create index on the join columns, use NOSORT while creating index on presorted columns.
Create a default temporary tablespace that is locally managed.
Know information contained in views V$SORT_SEGMENT, V$SORT_USAGE.
Understand differences in sort strategy for OLTP systems and DSS systems.
Objective 8: Diagnosing Contention For Latches
NOTES:
Types of latches: – willing to wait and immediate
Behavior of latches on single CPU and multi CPU systems.
V$SYSTEM_EVENT has the event LATCH FREE
STATSPACK sections for latch information: – TOP 5 WAIT EVENTS, LATCH ACTIVITY, LATCH SLEEP BREAKDOWN
Know information in the following views: – V$LATCH, V$LATCH_PARENT, V$LATCH_CHILDREN, V$LATCHNAME, V$LATCHHOLDER, V$LATCH_MISSES.
Six most important and common latches: SHARED POOL LATCH, LIBRARY CACHE LATCH, CACHE BUFFER LRU CHAIN LATCH, CACHE BUFFER CHAIN LATCH, REDO ALLOCATION LATCH, REDO COPY LATCH.
Objective 9: Monitoring and Detecting Lock Contention
NOTES:
Know the DML and DDL locks- implicit and explicit.
Initialization parameters that can be set are ENQUEUE_RESOURCES, ROW_LOCKING, DML_LOCKS.
Views to be used are: V$LOCK, V$LOCKED_OBJECTS, DBA_WAITERS, DBA_BLOCKERS, V$SESSION.
Know tips as a DBA to resolve locking issues.
Following a deadlock, a user trace file is written with the deadlock information and the statistic ENQUEUE DEADLOCK is incremented in the V$SYSSTAT view.
Objective 10: Tuning Oracle Shared Server
NOTES:
Use shared server when there are too many application users, transactions are short and non continuous.
Init parameters that should be set are DISPATCHERS, MAX_DISPATCHERS, SHARED_SERVERS, MAX_SHARED_SERVERS, CIRCUIT and PROCESS.
Views such as V$SHARED_SERVER, V$QUEUE, V$CIRCUIT, V$DISPATCHER, V$DISPATCHER_RATE, V$SHARED_SERVER_MONITOR assist in determining the optimal number and contention of dispatchers and shared servers.
Consider using the large pool while using shared server mode for UGA, determine the size for large pool that should be set for UGA by using the statistic SESSION UGA MEMORY MAX in the V$STATNAME and get the value from V$SESSSTAT view.
Understand how PMON manages workload dynamically by increasing and terminating the shared server processes.
Objective 11: Application Tuning
NOTES:
Various storage structures include heap-organized tables, clusters, Index organized tables (IOT) and materialized views – understand the points to be kept in mind when considering any of them for use.
Indexes to be understood are B-Tree, B-Tree Compression, Reverse-key, function based and bitmap – understand the points to be kept in mind when considering any of them for use. In particular, understand the differences between B-tree and bitmap indexes as they are quite opposite for certain characteristics.
Understand the use of HISTOGRAM in tables that have columns with data spread non-uniformly, histograms are used by CBO for decision making while processing the SQL.
Know how to create materialized views, all the parameters used with the CREATE MATERIALIZED VIEW statement, important being the refreshing options – COMPLETE, FAST, FORCE, NEVER and the refreshing mode- AUTOMATIC and MANUAL.
Query rewrite will occur for materialized views only when the following conditions are met:
· Enable query rewrite clause is specified in the Materialized View CREATE statement.
· QUERY_REWRITE_ENABLED=TRUE
· Any of the Cost Based Optimizer is in use
Objective 12: Using Oracle Blocks Efficiently
NOTES:
Use large block sizes for Decision Support Systems (DSS) and small block sizes for Online Transaction Processing (OLTP) applications, for hybrid systems consider using non-standard block sizes.
Small block sizes are good for small rows and they reduce contention, albeit at the expense of a large space overhead. Large block sizes are good for large rows including the ones with LOB data and have low space overhead, but suffer from block contention and substantial space under-utilization.
The EMPTY_BLOCKS column of DBA_TABLES shows the number of blocks above the HIGH WATER MARK (HWM) and BLOCKS column shows the number of blocks below the HWM.
DBMS_SPACE.UNUSED_SPACE can also be used to determine the unused space above HWM.
To move the HWM you can either (i) export the table, drop or truncate it , then import the table (ii) move the table.
You should know row migration and chaining, migration can be prevented by specifying a proper value for PCTFREE, chaining can be prevented only by increasing the block size.
Determine row chaining and migration by using COMPUTE/ESTIMATE of ANALYZE and then query the DBA_TABLES.CHAINED_ROWS.
V$SYSSTAT contains TABLE FETCH CONTINUED ROW.
STATSPACK has statistics for chained, migrated rows in the section INSTANCE ACTIVITY STATS.
Remove row migration by using (i) Export the table, drop or truncate it, then import the table (ii) Move the table, (iii) Reinsert the rows.
Indexes can be reorganized by COALESCE or REBUILD.
Consider rebuilding an index if it’s BLEVEL > 4, to find the blevel, analyze the index and then see the column DBA_INDEXES.BLEVEL.
To find the space occupied by the deleted rows in the index, analyze the index with validate structure option and the query the following columns from the INDEX_STATS view: DEL_LF_ROW_LEN, LF_ROW_LEN.
Enable/disable index monitoring by alter index MONITORING/ NOMONITORING USAGE and then check the column V$OBJECT_USAGE.USED.
Objective 13: SQL Statement Tuning
NOTES:
Understand the RULE BASED OPTIMIZER (RBO) and the COST BASED OPTIMIZER (CBO), use parameter OPTIMIZER_MODE to set the optimizer. CBO optimizes higher throughput and faster response times.
For CBO to make proper decision while executing SQL you should keep refreshing the statistics for tables and indexes by analyzing them or using the package DBMS_UTILITY (ANALYZE_SCHEMA, ANALYZE_DATABASE) or DBMS_STATS(GATHER_TABLE_STATS, GATHER_INDEX_STATS, GATHER_SYSTEM_STATS, GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS).
Since the CBO of 9i also considers the CPU_COST and TEMP_SPACE, frequently generate the system statistics using the DBMS_STATS.GATHER_SYSTEM_STATS.
Use histograms for columns that have non-uniformly distributed data.
To copy statistics between databases use:
DBMS_STATS.CREATE_STAT_TABLE
DBMS_STATS.EXPORT_SCHEMA_STATS
DBMS_STATS.IMPORT_SCHEMA_STATS
Use TKPROF, EXPLAIN PLAN FOR and AUTOTRACE to obtain the statistics and execution plan for the SQL statements.
Make use of plan stability so that the optimal execution plan can be stored in the database and used later. This can be achieved by using the stored outlines.
OUTLN_PKG package is used to maintain the stored outlines.
Views that contain useful information about outlines are: DBA_OUTLINES and DBA_OUTLINE_HINT
For creating metadata tables to enable the use of private outlines use DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES.
Understand the objects that should be used in OLTP and DSS systems; these two systems usually have opposite requirements.
Objective 14: Tuning the Operating System and Using Resource Manager
NOTES:
Understand the system architectures – Symmetric Multiprocessor System (SMP), Massive Parallel Processor (MPP), and Non-Uniform Memory Access (NUMA).
Paging writes an individual page of memory on the disk temporarily, swapping writes the entire processes memory on the disk (swapping is not done in Windows 2000 systems).
Use LOCK_SGA and intimately shared memory to prevent paging and swapping.
In Windows 2000, each Oracle process runs as a thread of the main process, whereas in Unix based systems, each process runs independently as a process.
Understand the RESOURCE CONSUMER GROUP, RESOURCE PLAN, and RESOURCE PLAN DIRECTIVE.
Understand all the resources that can be restricted and what condition is applied to restrict them.
Know the steps to create the Resource Manager objects.
Have an overview of all the packages, privileges related to the Resource Manager.
Objective 15: Tuning Rollback (UNDO) Segments
NOTES:
Must know views are:
V$ROLLSTAT that has the columns HEADER WAITS, EXTENDS, WRAPS.
V$SYSTEM_EVENT: The events UNDO SEGMENT TX SLOT and UNDO SEGMENT EXTENSION can be used to determine the contention of Rollback header and Dynamic extension of Rollback segments respectively.
V$WAITSTAT: The wait UNDO HEADER, SYSTEM UNDO HEADER, UNDO BLOCK, SYSTEM UNDO BLOCK determine waits for accessing non-System Rollback Segment Header, System Rollback Segment Header, non-System Rollback Segment and System Rollback Segments respectively.
V$SYSSTAT: The statistic CONSISTENT GETS determines the consistent gets (no waits) in accessing the Rollback Blocks.
STATSPACK Report contains the Section “Rollback Segment Stats” that can be used to determine the performance of the Rollback segments.
For automatic undo management, use the init parameters UNDO_MANAGEMENT, UNDO_TABLESPACE, UNDO_RETENTION, and UNDO_SUPRESS_ERRORS.
Undo Retention, expressed in seconds, is an important parameter that is also used during creation of Flashback Queries.
The Undo Tablespace has to be created and defined, otherwise the undo is written into the SYSTEM rollback segment.
You can have multiple Undo Tablespaces defined in the database, but only one can be active at a point in time
Use the V$UNDOSTAT view while using automatic undo management, this gives a 10 minute snapshot of the undo usage.
USEFUL RESOURCES
Books & Magazines
You can get the latest updates on Oracle database by free subscription to the Oracle magazine on the following URL:
http://www.oracle.com/oramag/oracle/index.html
Online Articles
You can read some useful articles on Oracle at:
http://otn.oracle.com/pub/articles/index.html
Online Documentation, e-docs
You can access the complete documentation and online books on Oracle databases at:
http://otn.oracle.com/documentation/oracle9i_arch_901.html
You can access useful notes on DBA activities and performance and tuning at:
http://www.quest-pipelines.com/pipelines/dba/archives.htm
Exam Simulators
There are many ways to prepare for certification exams, one of them being through the use of exam simulators. With these you cannot just identify your weak areas, but also get a feel of the test environment.
Whizlabs has launched the new OCP 9i (1ZO-033) Certification exam simulator that ensures your success in the exam with its high-quality mock tests and quick revision tips for the exam.
Check out the trial version of the Whizlabs OCP 9i (1ZO-033) Certification Exam Simulator.
Ravi Bhateja, a graduate in Physics from Bombay University and an experienced Oracle Database Administrator and Developer (SQL and PL/SQL) has authored the content for Whizlabs OCP 1Z0-033 exam simulator. Ravi is an Oracle9i DBA Certified Professional and also a Sun Certified Programmer for Java 2 Platform. He has acquired great ken and expertise in the field of database administration and programming.
The author can be contacted at authors@whizlabs.com.
About Whizlabs
Whizlabs(www.whizlabs.com), an ISO Certified company, is a leading provider of IT skill assessment and certification exam preparation tools. Whizlabs’ suite of offerings include “IT Certification Exam simulators and Instructor-led, Online Trainings” for various exams by Sun, IBM, Oracle, Microsoft, BEA, Cisco, and other leading IT vendors and “IT skill Assessment Management Solution” for Corporations, Training
Institutes, and Universities.In its first 3 years of inception Whizlabs has helped 300,000 software professionals in realizing their dream of acquiring IT Certifications of their interest. Whizlabs offerings have fuelled the career growth of IT professionals working in 321 Fortune 500 companies spread in 118 countries across the globe.
