Performance Tuning - Question 114

Which two statements about database blocks are true? (Choose two)

A. DSS environment prefer a large block size.

A. Poor application design that does not make good use of indexes.

B. Applications not written to use bind variable to take advantage of shared SQL statements.

C. Poor application design that require too many tables to be joined together to resolve queries.

D. Applications not closing cursors in a timely fashion because the init.ora parameter OPEN_CURSORS was set too high.

Answer: A, B, D

Explanation:

All these reasons can explain poor database performance.

Incorrect Answers

C: Number of tables that need to be joined should not affect performance in good tuned database.

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

Chapter 3: SQL Application Tuning and Design

Performance Tuning - Question 113

The NOLOGGING mode in SQL statements is a tool used to reduce redo operations, but NOLOGGING does not apply to every operation for which the attribute is set. Which three SQL statements can use the NOLOGGING mode to reduce redo operations? (Choose three)

A. UPDATE

B. CREATE INDEX

C. ALTER INDEX.. REBUILD

D. Conventional Path INSERT

E. CREATE TABLE…. AS SELECT

Answer B C E

Explanation:

You can use the NOLOGGING mode to create index, to rebuild index and to create table as select from the other table.

Incorrect Answers

A: It cannot be used for DML operations itself. Only table can be switched to this mode and than UPDATE command can be performed for the table.

D: Conventional Path INSERT cannot use it. Once the NOLOGGING attribute is set on a table, redo entry generation will be suppressed for all subsequent DML on the table only when that DML is of the following types: Direct Path loads using SQL*Loader, direct load inserts using the /*+ APPEND*/ hint.

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

Chapter 7: Tuning Redo Mechanisms

Performance Tuning - Question 112

Which initialization parameter specifies the location of the alert log file?

A. UTL_FILE_DIR

B. USER_DUMP_DEST

C. LOG_ARCHIVE_DEST

D. BACKGROUND_DUMP_DEST

Answer: D

Explanation:

BACKGROUND_DUMP_DEST initialization parameter specifies the location of the alert log file.

Incorrect Answers

A: UTL_FILE_DIR lets you specify one or more directories that Oracle should use for PL/SQL file I/O.

B: USER_DUMP_DEST is used to specify the location of user trace files.

C: LOG_ARCHIVE_DEST shows the directory for the archived redo logs.

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

Chapter 2: Sources of Tuning Information

Performance Tuning - Question 111

Which type of table is the best candidate to be cached?

A. Small table rarely retrieved with a full table scan.

B. Large table rarely retrieved with a full table scan.

C. Small table frequently retrieved with a full table scan.

D. Large table frequently retrieved with a full table scan.

Answer: C

Explanation:

Small tables frequently retrieved with a full table scan can be cached.

Incorrect Answers

A: If table is rarely retrieved there is no sense to cache it.

B: Large table cannot be easy cached because CACHE option works mostly for the small tables that can be loaded in the memory. Also if table is rarely retrieved there is no sense to cache it.

D: Large table is not good candidate to be cached.

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

Chapter 5: Tuning the Database Buffer Cache

Performance Tuning - Question 110

What is the main reason to create a reverse key index on a column?

A. The column is populates using a sequence.

B. The column contains many different values.

C. The column is mainly used for value range scans.

D. The column implementing an inverted list attribute.

Answer: A

Explanation:

The Reverse Key Index (RKI) is a special type of B-Tree index. The RKI is useful when an index is built on a column that contains sequential numbers.

Incorrect Answers

B: B-Tree index will be appropriate choice for the column with many different values.

C: The column is mainly used for value range scans is not good candidate for the Reverse Key Index. Reverse Key indexes are only useful for equality and non-equality searches. Queries that perform range scans (e.g., using BETWEEN, >, <) on columns that are Reverse Key indexed will not be able to use the index and will cause full table scans.

D: The RKI does not work for the column implementing an inverted list attribute.

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

Chapter 3: SQL Application Tuning and Design

Performance Tuning - Question 109

Which three actions will cause queries to place a table’s blocks at the most-recently-used end of the LRU list? (Choose three)

A. Creating a table with the CACHE option.

B. Querying the table by using a CACHE hint.

C. Ensuring the query performs a full table scan.

D. Defining the table without the option for caching.

E. Altering an existing table to set the CACHE option.

F. Ensuring the query does not retrieve data through index lookup.

G. Creating a separate database buffer cache to hold cached table.

Answer A C E

Explanation:

If you are creating a table with the CACHE option or altering an existing table to set the CACHE option will place a table’s blocks at the most-recently-used end of the LRU list. Blocks will be placed at the most-recently-used end of the LRU list if the query performs a full table scan.

Incorrect Answers

B: Querying the table by using a CACHE hint will not cause queries to place a table’s blocks at the most-recently-used end of the LRU list.

D: Defining the table without the option for caching will not place blocks in the LRU list.

F: This statement will not cause queries to place a table’s blocks at the most-recently-used end of the LRU list.

G: You don’t need to create a separate database buffer cache to hold cached table.

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

Chapter 5: Tuning the Database Buffer Cache

Performance Tuning - Question 108

Database Resource Manager uses resource plans to determine resource limits for the set of users. Which statement is true in reference to resource plans?

A. Resource plans are set using profiles.

B. Only one resource plan can be stored in the database at one time.

C. The database can have many resources plans, but only one can be active at any one time.

D. The database can have many resources plans, and each user chooses which plan to belong to.

Answer: C

Explanation:

Oracle8i introduced a new feature, Resource Manager, which was designed to improve the allocation and management of server resources needed by application users. In Oracle 9i, it is possible to control numerous aspects of application processing via Resource Manager. The database can have many resources plans, but only one can be active at any one time.

Incorrect Answers

A: Resource plans are not set using profiles.

B: The database can have many resources plans.

D: Each user cannot choose which plan to belong to because only one plan can be active at any one time.

OCP: Oracle 9i Performance Tuning Study Guide, Jaseph C. Johnson, p. 517-547

Chapter 10: Operating System Tuning

Performance Tuning - Question 107

Which component will NEVER allocate memory from the large pool?

A. Oracle Library Cache.

B. Oracle Parallel Query.

C. Oracle Recovery Manager.

D. Oracle Multithreaded Server.

Answer: A

Explanation:

Oracle Library Cache will NEVER allocate memory from the Large Pool.

Incorrect Answers

B: Oracle Parallel Query (PQ) can cause the Shared Pool to cache additional items not related to application SQL. That’s why it can allocate memory from the Large Pool.

C: Oracle Recovery Manager (RMAN) utility can use the Large Pool to process requests for optional features.

D: UGA information for the Shared Sever option is cached in the Large Pool if it’s defined. Otherwise, it uses the Shared Pool.

OCP: Oracle 9i Performance Tuning Study Guide, Jaseph C. Johnson, p. 308-310

Chapter 8: Tuning Other SGA Areas

Performance Tuning - Question 106

What are two possible causes of lock contention? (Choose two)

A. Uncommitted changes.

B. Too many rollback segments.

C. Improperly sized redo logs.

D. Shared pool is sized too large.

E. Other protocols imposing unnecessarily high locking levels.

Answer: A, E

Explanation:

Lock contention can arise because of uncommitted changes and unnecessarily table level locks.

Incorrect Answers

B: Large amount of rollback segments decrease possibility of lock contention.

C: Lock contention is not related with the size of redo logs.

D: Lock contention is not related with shared pool size.

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

Chapter 9: Tuning the Contention

Performance Tuning - Question 105

When should you recommend changing the application in order to reuse more SQL?

A. When the GETHITRATIO in the V$LIBRARYCACHE view is above 0.99.

B. When the misses in the dictionary cache are greater than 1% of the hits.

C. When the ratio of GETHITS to GETS in the V$LIBRARYCACHE view is less then 0.9.

D. When the ratio of RELOADS to PINS in the V$LIBRARYCACHE view is less than 0.01.

Answer: C

Explanation:

The ratio of parsed statements (GETS) to those that did not require parsing (GETHITS) is calculated in the GETHITRATIO column of V$LIBRARYCACHE. The higher this number is, the better the application is performing.

Incorrect Answers

A: When the GETHITRATIO in the V$LIBRARYCACHE view is above 0.99, application performance is good.

B: The dictionary cache has nothing to do with SQL statements: it stores the data dictionary information in the memory.

D: The RELOADS column in the V$LIBRARYCACHE view shows the number of times that an executed statement had to be re-parsed because the Library Cache had aged out or invalidated the parsed version of the statement. Reload activity can be monitored by comparing the number of statements that have been executed (PINS) to the number of those statements that required a reload (RELOADS). The less this number is, the better the application is performing.

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

Chapter 4: Tuning the Shared Pool

Performance Tuning - Question 104

What should one be your tuning goals?

A. Use as much memory as possible.

B. Use multiple copies of the code in memory.

C. Access the most possible number of blocks from disk.

D. Access the least possible number of blocks from disk.

Answer: D

Explanation:

The main goal is to access the least possible number of blocks from disk because I/O operations are significantly more expensive as memory operations.

Incorrect Answers

A: You need to use as less memory as possible.

B: You need to share the same code in the memory to reduce the memory usage.

C: The main goals is to access the least, not the most, possible number of blocks from disk

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

Chapter 1: Introduction to Performance Tuning

Performance Tuning - Question 103

Which statement about improving the performance of the database buffer cache by creating multiple buffer pools is true?

A. The KEEP buffer pool must also be deferred if the RECYCLE pool is defined.

B. The buffer pool for an object can be set explicitly only at object creation time.

C. The blocks from an object without an explicitly set buffer pool go into the RECYCLE pool.

D. Buffer pools are assigned to a segment, so option with multiple segments can have blocks in multiple buffer pools.

Answer: D

Explanation:

Oracle provides you with the ability to divide the Database Buffer Cache into as many as three separate areas called Buffer Pools. Segments are then explicitly assigned to use the appropriate Buffer Pool as determined by the DBA. Option with multiple segments can have blocks in multiple buffer pools.

Incorrect Answers

A: There is no such relation with the KEEP buffer pool and the RECYCLE buffer pool: they can be defined independently.

B: The buffer pool for an object can be changed after an object creation.

C: The blocks from an object without an explicitly set buffer pool go into the DEFAULT pool.

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

Chapter 5: Tuning the Database Buffer Cache

Performance Tuning - Question 102

What should be two goals in tuning rollback segments? (Choose two)

A. Transactions should never wait for access to rollback segment.

B. No transaction, however large or exceptional, should ever run out of rollback space.

C. Rollback segments should be configured to extend continually during normal processing.

D. The ratio of waits to the rollback segment header blocks should be less than 5% of the sum of access.

Answer: A, B

Explanation:

There are two main goals for rollback segments: transactions should never wait for access to rollback segment; no transaction should ever run out of rollback space because it used to keep the read-consistent view of the changed data.

Incorrect Answers

C: Rollback should not extend continually during normal processing. It is possible only as exception to keep data for batch jobs performing DML operations with many rows.

D: Transactions should never wait for access to rollback segment.

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

Chapter 8: Tuning Disk I/O

Performance Tuning - Question 101

Where can you find the nondefault parameters when the instance is started?

A. Alert log

B. Online redo log

C. Archiver redo log

D. SYSTEM user’s trace file

Answer: A

Explanation:

Alert log file shows the nondefault parameters when the instance is started.

Incorrect Answers

B: Online redo log is not used to store this information.

C: Archive redo log is just archived copy of online redo log. It does not have any information about nondefault parameters.

D: SYSTEM’s user’s trace file is not used to provide this information.

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

Chapter 2: Sources of Tuning Information

Performance Tuning - Question 100

Which three statements about rebuilding indexes are true? (Choose three)

A. The ALTER INDEX REBUILD command is used to change the storage characteristics of an index.

B. Using the ALTER INDEX REBUILD is usually faster than dropping and recreating an index because it uses the fast full scan feature.

C. Oracle8i allows for the creation of an index or re-creation of an existing index while allowing concurrent operations on the base table.

D. When building an index, the NOLOGGING and UNRECOVERABLE keywords can be used concurrently to reduce the time it takes to rebuild.

Answer: A, B, C

Explanation:

You can use the ALTER INDEX REBUILD command to optimize the storage characteristics of an index. Using the ALTER INDEX command with the REBUILD option is an effective way to quickly rebuild an index because the existing index entries are used to create the new index. The ONLINE option of the ALTER INDEX REBUILD command should be used to minimize any locking issues that occur when an index is rebuilt while users continue to perform DML on the index’s underlying table.

Incorrect Answers

D: When building an index, the NOLOGGING and UNRECOVERABLE keywords cannot be used concurrently to reduce the time it takes to rebuild.

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

Chapter 3: SQL Application Tuning and Design

Performance Tuning - Question 099

What is the least number of buffers an LRU latch must cover in the database buffer cache?

A. 5

B. 10

C. 30

D. 50

E. 100

Answer: D

Explanation:

There are at least 50 blocks per LRU latch for each pool.

Incorrect Answers

A: There are at least 50, not 5, blocks per LRU latch for each pool.

B: There are at least 50, not 10, blocks per LRU latch for each pool.

C: There are at least 50, not 30, blocks per LRU latch for each pool.

E: There are at least 50, not 100, blocks per LRU latch for each pool.

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

Chapter 9: Tuning Contention

Performance Tuning - Question 098

Which type of transaction should you assign to a specific large rollback segment?

A. Batch jobs that modify many rows.

B. Long running serializable transactions.

C. Long running reports, to avoid ‘snapshot too old’ errors.

D. Discrete transactions that modify many rows in the same block.

Answer: A

Explanation:

You need to assign a batch jobs modifying many rows to specific large rollback segment using SET TRANSACTION command.

Incorrect Answers

B: Long running serializable transactions do not require large rollback segments.

C: Reports do not modify any table records. So they do not need specific rollback segment.

D: Discrete transactions that modify many rows in the same block will not cause rollback segment contention.

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

Chapter 3: SQL Application Tuning and Design

Performance Tuning - Question 097

What will this statement do?

CREATE TABLESPACE temp
DATAFILE ‘C:\database\temp.dbf’ SIZE 10m
Temporary;

A. Create a tablespace that will be dropped on instance shutdown.

B. Create a tablespace in which the user can create segments for usage during sorts.

C. Create a tablespace in which Oracle can create segments for usage during sorts.

D. Create a tablespace in which a user can create tables that will be automatically dropped after a week.

Answer: C

Explanation:

This command creates a tablespace in which Oracle can create segments for usage during sorts.

Incorrect Answers

A: You cannot drop the default temporary tablespace until another has been created because doing so would leave the database with nowhere to perform to-disk sorts. Unlike a sort segment stored in a permanent tablespace, the sort segment in the temporary tablespace is not dropped when the user’s sort completes.

Instead, the first sort operation following instance startup creates a sort segment that remains in the temporary tablespace for reuse by subsequent users who also perform sorts to disk. This sort segment will remain in the temporary tablespace until instance shutdown. So only a sort segment will be dropped, not entire tablespace on instance shutdown.

B: User itself cannot create any objects in the temporary tablespace: it is used exclusively by Oracle.

D: User itself cannot create any objects in the temporary tablespace: it is used exclusively by Oracle.

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

Chapter 8: Tuning Disk I/O

Performance Tuning - Question 096

In which two ways can you reduce the amount of sorting that is performed? (Choose two)

A. By using UNION instead of UNION ALL.

B. By using NOSORT when creating tables.

C. By using NOSORT when creating indexes.

D. By using COMPUTE instead of ESTIMATE when analyzing objects.

E. By reducing the number of users that have the sort privilege.

F. By creating appropriate indexes on tables that are joined often.

Answer: B, F

Explanation:

You can avoid a sorting operation by using NOSORT clause when creating table or by creating appropriate indexes on tables that are joined often.

Incorrect Answers

A: The UNION type SQL statement will cause sort operation.

C: You cannot avoid sorting by using NOSORT when creating indexes.

D: By using COMPUTE instead of ESTIMATE when analyzing objects you will process ALL records in the table. It will cause sorting also.

E: There is no sort privilege in Oracle. All users can sort data in Oracle tables.

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

Chapter 8: Tuning Disk I/O

Performance Tuning - Question 095

Which three statements about improving the performance of the database buffer cache by creating multiple buffer pools are true? (Choose three)

A. One, two, or three pools may be defined.

B. There are at least 50 blocks per LRU latch for each pool.

C. Each buffer pool is assigned latches taken from DB_BLOCK_LRU_LATCHES.

D. The size if the DEFAULT pool is obtained by adding all the pools to the value of the DB_BLOCK_BUFFERS parameter.

Answer: A, B, C

Explanation:

Unless you specify otherwise in the init.ora, only the Default Pool is created at instance startup. But Oracle provides you also with the ability to divide the Database Buffer Cache into as many as three separate areas called Buffer Pools: Keep Pool (optional), Recycle Pool (optional) and Default Pool (mandatory). There are at least 50 blocks per LRU latch for each pool. Also each buffer pool is assigned latches taken from DB_BLOCK_LRU_LATCHES.

Incorrect Answers

D: Default Pool is used to cache segments that are not designated for either the Keep or Recycle pools. The size of this pool is designated in bytes, kilobytes, megabytes, or gigabytes, by the init.ora parameter DB_CACHE_SIZE. Unlike Oracle8i, where the memory for the Keep and Recycle pools was taken from the memory allocated to the Default Pool, Oracle9i independently assigns the memory to each of the three Buffer Pool types.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 265-266, 468-476

Chapter 5: Tuning the Database Buffer Cache, Chapter 9: Tuning Contention

Performance Tuning - Question 094

Which dynamic view is most useful for determining the current number of blocks allocated to a buffer pool?

A. V$CACHE

B. V$SESS_IO

C. V$SYSSTAT

D. V$BUFFER_POOL

Answer: D

Explanation:

The V$BYUFFER_POOL dynamic performance view contains information about the configuration of the multiple Buffer Pools themselves. You can monitor the performance of the Buffer Pools using the V$BUFFER_POOL and V$BUFFER_POOL_STATISTICS dynamic performance views.

Incorrect Answers

A: V$CACHE dynamic view is an Oracle9i Real Application Clusters view. This view contains information from the block header of each block in the SGA of the current instance as related to particular database objects.

B: This view lists I/O statistics for each user session.

C: Sort activity can be monitored using the V$SYSSTAT and V$SORT_SEGMENT dynamic data dictionary views, using the output from STATPACK and REPORT.TXT, and using the output from the OEM Performance Manager.

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

Chapter 5: Tuning the Database Buffer Cache

Performance Tuning - Question 093

Which tablespace is used as the temporary tablespace if ‘TEMPORARY TABLESPACE’ is not specified for a user?

A. TEMP

B. DATA

C. SYSTEM

D. ROLLBACK

Answer: C

Explanation:

When a user’s Server Process writes a sort chunk to disk, it writes the data to the user’s temporary tablespace. This tablespace, although it is referred to as the user’s temporary tablespace, can have the tablespace attribute of being either permanent or temporary. If theer is no TEMPORARY TABLESPACE specified for the user, SYSTEM tablespace will be considered as temporary tablespace for the user.

Incorrect Answers

A: There is no TEMP tablespace in Oracle by default.

B: There is no DATA tablespace in Oracle by default.

D: ROLLBACK tablespace cannot be used as temporary tablespace.

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

Chapter 8: Tuning Disk I/O

Performance Tuning - Question 092

What are two main OLTP requirements? (Choose two)

A. Use bind variables rather than literals in your SQL code.

B. Analyze your tables regularly to refresh optimizer statistics.

C. Create multiple small rollback segments as opposed to a few big ones.

D. Create indexes on all columns that are regularly used in query predicates.

E. Set up appropriate default storage parameter values for dynamic (implicit) space allocation.

Answer: C, E

Explanation:

Due to high level of DML activity on the OLTP system you need to use more small rollback segments rather than few large rollback segments. To avoid the performance load of dynamic space allocation, you need to allocate space explicitly so tables, clusters and indexes.

Incorrect Answers

A: This one is not main requirement of the OLTP system.

B: Table and index statistics should be gathered regularly if the CBO is used because of data volumes tend to change quickly in OLTP systems. But this requirement cannot be considered as main OLTP requirement.

D: It is not recommended to create indexes on ALL columns that are regularly used in query predicates because of multiple INSERT and UPDATE operations will cause overload on the database trying to rebuild all indexes after each transaction.

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

Chapter 3: SQL Application Tuning and Design

Performance Tuning - Question 091

Which three types of statistics are reported in report.txt after running UTLESTAT SQL? (Choose three)

A. Locking statistics.

B. Memory usage statistics.

C. Explain plan statistics.

D. Library cache statistics.

E. Buffer busy wait statistics.

F. Rollback contention statistics.

Answer: D, E, F

Explanation:

There are three types of statistics reported in the REPORT.TXT file after running UTLESTAT.SQL: library cache statistics, buffer busy wait statistics, rollback contention statistics.

Incorrect Answers

A: Locking statistics are not reported in REPORT.TXT file.

B: There is no memory usage statistics in REPORT.TXT after running UTLESTAT.SQL.

C: Explain plan statistics are not reported in REPORT.TXT file.

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

Chapter 2: Sources of Tuning Information

Performance Tuning - Question 090

What does this statement do?

SQL> ANALYZE INDEX index_name VALIDITY STRUCTURE;

A. It places information into the INDEX_STATS view and allows for the monitoring of space used by an index.

B. It provides information in the INDEX_HISTOGRAM view to indicate whether an index is invalid or valid.

C. It provides information in the DBA_INDEXES view for the COST BASED Optimizer when choosing an execution plan.

Answer: A

Explanation:

The VALIDATE STRUCTURE option for the ANALYZE command populates the data dictionary view INDEX_STATS with values.

Incorrect Answers

B: This command does not provide information in the INDEX_HISTOGRAM view.

C: This command does not provide information in the DBA_INDEX data dictionary view.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 150

Chapter 3: SQL Application Tuning and Design

Performance Tuning - Question 089

Which two statements about plan stability and stored outlines are true? (Choose two)

A. You can group outlines in categories.

B. You can only have one stored outline per SQL statement.

C. Plan stability only wants when SQL statements match textually.

D. Stored outlines are saved in the data dictionary (SYS schema).

E. Stored outlines become invalid when you analyze the associated objects.

Answer: A, C

Explanation:

Oracle9i maintains predefined execution plans in the data dictionary in the form of stored outlines. Ascertain whether the statement you have identified can be lumped into two or more broad categories of statements. Through a process known as plan equivalence, a SQL statement must exactly match the original statement used to generate an outline in order for the stored outline to be used. Even an identical statement with the addition of a hint or comment will not be considered equivalent for stored outline usage.

Incorrect Answers

B: You can have as many stored outline per SQL statement as you need.

D: Stored outlines are saved in the OUTLN schema.

E: Stored outlines do not become invalid when you analyze the associated objects. That’s why they are used to preserve predefined execution plans.

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

Chapter 3: SQL Application Tuning and Design

Performance Tuning - Question 088

For which reason would you query V$SYSSTAT?

A. Name of the sort segment.

B. Free space available for a sort segment.

C. Number of disk sorts performed since startup.

D. Number of users active on individual sort segments.

Answer: C

Explanation:

Sort activity can be monitored using the V$SYSSTAT and V$SORT_SEGMENT dynamic data dictionary views, using the output from STATPACK and REPORT.TXT, and using the output from the OEM Performance Manager.

Incorrect Answers

A: This view does not show a name of the sort segment.

B: It is not used to show a free space available for a sort segment.

D: V$SYSSTAT does not provide information about number of users active on individual sort segments.

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

Chapter 8: Tuning Disk I/O

Performance Tuning - Question 087

If a willing-to-wait latch request is satisfied on the first attempt, which statistic gets incremented?

A. GETS

B. SLEEPS

C. MISSES

D. IMMEDIATE_GETS

E. IMMEDIATE_MISSES

Answer: A

Explanation:

GETS is number of times a Willing-to_Wait latch was acquired without waiting. So GETS statistic will be incremented.

Incorrect Answers

B: SLLEPS statistic provides the number of times a process had to wait before obtaining a Willing-toWait latch.

C: MISSES shows the number of times a Willing-to_Wait latch was not acquired and a wait resulted.

D: IMMEDIATE_GETS is statistic of numbers of times an Immediate latch was acquired without waiting.

E: IMMEDIATE_MISSES is the number of times an Immediate latch was not acquired and a retry resulted.

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

Chapter 9: Tuning Contention

Performance Tuning - Question 086

When a deadlock shutdown is detected by Oracle, where is the trace file generated?

A. SQL_TRACE

B. TRACE_DEST

C. USER_DUMP_DEST

D. CORE_DUMP_DEST

E. BACKGROUND_DUMP_DEST

Answer: C

Explanation:

Oracle creates a user trace file when processing errors in a user’s Server Process. A deadlock error will be shown in a user trace file which is located in the USER_DUMP_DEST. While events such as deadlocks automatically generate trace files, full scale tracing of user sessions does not occur unless the user or DBA requests it.

Incorrect Answers

A: SQL_TRACE is the main method for collecting SQL Execution information in Oracle collecting a wide range of information and statistics that can be used to tune SQL operations.

B: The TRACE_DEST parameter does not specify the location of this file.

D: CORE_DUMP_DEST should point to the directory where core dumps from the Oracle server will be placed. A core dump is a memory image of the Oracle shadow process produced when an unexpected, unrecoverable or invalid condition occurs. Note that Oracle should always try to write a trace file before producing a core dump.

E: BACKGROUND_DUMP_DEST specifies the pathname (directory or disc) where debugging trace files for the background processes (LGWR, DBWn, and so on) are written during Oracle operations.

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

Chapter 2: Sources of Tuning Information

Performance Tuning - Question 085

Which action could potentially cause checkpoints to take longer?

A. Increasing the number of redo log groups.

B. Increasing the size of rollback segments.

C. Decreasing the value of the REDO_LOG_BUFFERS parameter.

D. Increasing the value of the FAST_START_IO_TARGET parameter.

Answer: D

Explanation:

By increasing the FAST_START_IO_TARGET parameter you tell Oracle that it can use more time to perform recovery after instance crash. So it will cause that checkpoints will take longer. This parameter supersedes the FAST_START_MTTR_TARGET parameter if configured. The FAST_START_MTTR_TARGET init.ora parameter is used to specify a mean time (in seconds) to recover the instance following an instance failure.

Incorrect Answers

A: By increasing the number of redo log groups you will not decrease a time for checkpoints.

B: The size of rollback segments has nothing to do with checkpoints duration.

C: There is no REDO_LOG_BUFFERS parameter in Oracle.

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

Chapter 7: Tuning Redo Mechanisms

Performance Tuning - Question 084

The database includes tables with static data, which are used for queries only. To which size should you set PCTFREE for this type of table?

A. 0

B. 50

C. 20

D. 10

Answer: A

Explanation:

The parameter PCTFREE tells Oracle how much space to set aside in each block to store row updates.

Since we have a database with static data, PCTFREE can be set to 0 to use the disk space more effectively.

Incorrect Answers

B: By setting this parameter to 50 you will preserve 50% of data block for the future inserts. It will be just space wasting because of static nature of data inside the tables.

C: This size still cannot be considered as appropriate for the database with static data.

D: You can use this size for the database with minimal level of update activity.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 401

Chapter 8: Tuning Disk I/O