Certifying Performance Tuning

By Jim DiIanni

Here are more concepts and sample questions for OCP certification.

The Oracle9i Performance Tuning exam is the second exam you must take to achieve the Oracle Certified Professional (OCP) level of certification. This is the first of two columns regarding the topics you will likely encounter on the Oracle9i Performance Tuning exam, and it includes a discussion about diagnostic and tuning tools, the shared pool, and the buffer cache.

A fundamental goal of performance tuning is to ensure that users have acceptable responses from queries to their data. Think of how frustrating it is to enter search criteria to generate a report only to wait hours for the report to complete. A DBA can employ Oracle9i diagnostic and tuning tools to ensure that an acceptable level of performance is consistently maintained, regardless of application or database changes. Knowledge of these tools, combined with a thorough understanding of Oracle9i Database architecture, will enable the DBA to effectively diagnose and resolve performance-related problems.

Diagnostic and Tuning Tools

A key component of effective performance tuning is an established baseline against which to measure diagnostic and tuning results. A DBA can establish a performance baseline by collecting statistics during periods that are representative of database operations across various load situations. Once this baseline is established, a DBA can determine if something indicates a performance problem or is consistent with the baseline.

One source of diagnostic information a DBA can use to establish a baseline is the alert.log file.

What initialization parameter is used to specify the location of the alert.log file?

A. CORE_DUMP_DEST
B. USER_DUMP_DEST
C. ALERT_DUMP_DEST
D. BACKGROUND_DUMP_DEST

The correct answer is D. The alert.log file contains information such as checkpoint intervals, internal ORA-600, deadlock, and time-out errors. The DBA can also use the alert.log file to proactively monitor the health of the database by checking it regularly. Note that checkpoint start and end times will be recorded in the alert.log file if the parameter LOG_CHECKPOINTS_TO_ALERT is set to TRUE.

A DBA can use the STATSPACK utility to collect performance and diagnostic information.

Match the STATSPACK activity (from the first set of answers, uppercase A-D) with the correct statement to perform the activity (from the second set of answers, lowercase a-d).

A. Install STATSPACK
B. Collect statistics
C. Automatically collect statistics
D. Produce a report
a. $ORACLE_HOME/rdbms/admin/ spauto.sql
b. $ORACLE_HOME/rdbms/admin/ spreport.sql
c. $ORACLE_HOME/rdbms/admin/ spcreate.sql
d. execute STATSPACK.snap

The correct matches are A and c, B and d, C and a, and D and b. Using wait events is an effective approach to diagnosing performance problems, because they allow a DBA to zero in on specific problems. Once again, it is best to analyze all diagnostics against an established baseline.

Shared Pool

DBAs can perform diagnostic and tuning activities on Oracle9i Database memory structures. Potential problem areas can be identified with available diagnostic tools. For example, a DBA can find the diagnostic information associated with the shared pool and buffer cache in the report.txt output generated with the UTLBSTAT/UTLESTAT utilities and also in the report output generated by STATSPACK. The DBA can also query dynamic performance views to determine hit ratios and wait statistics. Performance Manager and Oracle Expert provide GUI tools for diagnostics and tuning.

What two memory areas are located within the shared pool?

A. Library cache
B. Large pool
C. Keep buffer pool
D. Dictionary cache
E. Default buffer pool

The correct answers are A and D. It is important to remember that objects stored in the library and dictionary caches are shared objects. A user global area in the shared pool keeps information about shared server connections when the large pool is not configured.

One goal for managing performance is to ensure that objects are shared across multiple server processes and that the number of times objects are reloaded in the library cache is minimized. When a SQL statement is executed, one step in the parsing process checks if the statement exists in the shared SQL area of the library cache. Because Oracle9i Database uses a hashing algorithm that determines the ASCII value of statements for purposes of matching, the statements must have the same exact syntax and case.

Therefore, the following two statements are different:

select first_name from employee 
  where first_name = 'james';
select first_name from employee 

  where first_name = 'JAMES';

Note that a constant value for first_name is used in each statement: 'james' in the first statement and 'JAMES' in the second statement. The difference in lowercase versus uppercase first_name values produces a different ASCII value for each statement. A "hit" occurs when the SQL statement executes and the same SQL statement is found in memory. A "miss" occurs when the executed SQL statement is not found in memory. The previous example will result in a "miss" that a DBA can avoid by using a bind variable for the value of first_name. Multiple users can then share one statement by passing in the value of first_name, thereby reducing the number of misses. The statement may look something like this:

select first_name from employee
  where first_name = '&first_name';

Users could pass the value 'james' or 'JAMES' to the statement.

What activities will help reduce SELECT statement misses by keeping parsing to a minimum in an OLTP application?

A. Use constant values in the search criteria of a SELECT statement.
B. Prevent statements from being aged out by allocating sufficient space.
C. Use bind variables in the search criteria of a SELECT statement.
D. Avoid invalidations that induce parsing.

The correct answers are B, C, and D. Answer A is incorrect because, as shown in the example above, using a constant value actually reduces the chances of sharing a SQL statement.

GETHITRATIO determines the percentage of parse calls that find a cursor to share (GETHITS/GETS). You often see recommendations stating that this value should be in the high 90s for OLTP applications. Although this may be generally true, always remember to compare values against an acceptable baseline.

What dynamic performance view will provide the value of the GETHITRATIO for SQL statements?

A. V$SQLAREA
B. V$SQL
C. V$SGASTATD
D. V$LIBRARYCACHE
E. V$DB_OBJECT_CACHE

The correct answer is D, because the shared SQL area is stored in the library cache.

I have only touched on the diagnostic and tuning activities associated with the shared pool in this column. Other topic areas you will want to study when preparing for the exam are shared pool sizing, stored execution plans, invalidations, reloads, and the dictionary cache.

Next Steps GET Certified oracle.com/education/certification You must pass a total of four exams to achieve your Oracle9i DBA Certified Professional credential: Oracle9i: SQL, Oracle9i Database: Fundamentals I, Oracle9i Database: Fundamentals II, and Oracle9i Database: Performance Tuning (discussed in this column). Please visit oracle.com/education/certification for more information about Oracle Certification and to download a free exam candidate guide.

Buffer Cache

Another memory structure DBAs will want to manage for acceptable performance is the buffer cache. Two of the primary tuning goals for the buffer cache are: (1) make servers find data in memory and (2) create no waits on the buffer cache. If the buffer cache is not adequately sized, blocks that were previously read into the cache may be aged out to make room for new blocks that are being read in. This may result in increased I/O and overall performance degradation.

Two tuning techniques a DBA can use for the buffer cache are (1) monitor wait events associated with the buffer cache and (2) calculate the cache hit ratio from Oracle database statistics.

The database collects statistics on data access and stores them in the dynamic performance table V$SYSSTAT. You can determine the cache hit ratio by using system statistics derived from V$SYSSTAT.

Match the V$SYSSTAT statistic (from the first set of answers, uppercase A-D) with its description (from the second set of answers, lowercase a-d).

A. Physical reads
B. Physical reads direct
C. Physical reads direct (lob)
D. Session logical reads
a. Number of blocks read from disk
b. Number of logical read requests
c. Number of direct reads of large binary objects
d. Number of direct reads, does not require the cache

The correct matches are A and a, B and d, C and c, and D and b. You can calculate the cache hit ratio by using the following formula:

Hit Ratio = 1 - 
  (physical reads 
   - physical reads direct 
   - physical reads direct (lob) ) 
  / session logical reads

You are likely to see varying versions of this formula, but if you encounter this topic on the exam, the above methodology is the one you want to be familiar with. Remember to multiply the hit ratio by 100 to derive a percentage. The performance guideline is that if the percentage is less than 90 percent, you will want to look at tuning the cache. However, this is dependent on the nature of your application and your baseline performance statistics. Because the cache hit ratio is only one performance indicator, you will want to familiarize yourself with the various wait events associated with the buffer cache.

Jim DiIanni (ocpexam_ww@oracle.com) is the certification director for Oracle Certification Programs.