Performance Tuning - Question 121

The Accounting department has contacted you concerning their perceived poor performance of the new accounting package that was installed last week. Which three actions would help diagnose the problem? (Choose three)

A. Query the DBA_ROLLBACK_SEGs table to see if there is contention for rollback segment headers while the accounting application is being run.

B. Query DBA_DATA_FILES while the accounting application is being used to determine if any of the data files are being used excessively.

C. Query the V$SQL_PLAN table to determine which, if any, indexes are being used for the accounting tables.

D. Query the V$FILESTAT table while the accounting application is being used to determine if any of the data files are being used excessively.

E. Query the V$LOGFILE table to see if an excessive amount of redo is being generated while the accounting application is being run.

F. Generate performance monitoring reports using UTLBSTAT and UTLESTAT or the STATSPACK while the account application is being used and compare them to your previously generated base line reports from the same tool.

Answer: C, D, F

Explanation:

The V$SQL_PLAN view provides a way of examining the execution plan for cursors that were executed and are still cached. The V$FILESTAT view contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time. Generate performance monitoring reports using UTLBSTAT and UTLESTAT or the STATSPACK will also help to find the reason of poor performance.

Incorrect Answers

A: The DBA_ROLLBACK_SEGS data dictionary view stores information about the rollback segments of a database. But this information cannot be used to check contention for rollback segment headers.

B: The DBA_DATA_FILES data dictionary view provides descriptive information about each datafile, including the tablespace to which it belongs and the file id. The file id can be used to join with other views for detail information, but itself it will not give you information if any of the data files are being used excessively.

E: This view just identifies redo log groups and members and member status.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 40-41

Chapter 2: Sources of Tuning Information