Performance Tuning - Question 022

Which of the following columns will you use to calculate the library cache hit ratio? (Choose two.)

A . KEEPS

B. PINS

C. RELOADS

D . GETS

E . GETMISSES

Explanation:

Answer B and C: Here's the formula for the library cache hit ratio, selected from V$LIBRARYCACHE: ((PINS-RELOADS)/PINS)*100. The library cache hit ratio should be greater than 90 percent.

Performance Tuning - Question 021

Which of these is an accurate statement about the performance tuning tool?

A . OEM Oracle Expert is an optional product, but provides real-time monitoring of database performance. OEM Utlestat is an optional product, but can help provide expert advice on configuration parameters.

B . OEM STATSPACK is an optional product, but can help provide expert advice on configuration parameters.

C . OEM Oracle Performance Manager is an optional product, but can help provide expert advice on configuration parameters.

D. OEM Oracle Expert is an optional product, but can help provide expert advice on configuration parameters.

Explanation:

Answer D: OEM Oracle Expert, which is an optional product (not part of the base OEM installation or pricing model), provides expert advice for configuring the database. STATSPACK is not an optional product, UTLESTAT is not optional and is not part of OEM (but can be launched from OEM), OEM Performance Manager does not provide expert configuration advice (Oracle Expert does), and Oracle Expert is not used for real-time monitoring (Performance Manager is).

Performance Tuning - Question 020

Which of these initialization parameters is required for STATSPACK to collect statistics automatically with spauto.sql?

A. JOB_QUEUE_PROCESSES

B . JOB_QUEUE_INTERVAL

C . DB_JOB_QUEUE_PROCESSES

D . STATSPACK_JOB_AUTO

Explanation:

Answer A: The spauto.sql script performs automatic statistics gathering for Statspack. To run, it must use the DBMS_JOB package; for jobs to run, the JOB_QUEUE_PROCESSES value must be 1 or greater. Each of the other answers are invalid parameters.

Performance Tuning - Question 019

A STATSPACK snapshot is a(n) ________ collection of data?

A . dynamic

B. point-in-time

C . ongoing

D . continuously refreshed

Explanation:

Answer B: Each snapshot, executed with the stored procedure STATSPACK.snap, is a point-in-time gathering of cumulative and current statistics in the database. The snapshot is not continuously refreshed, ongoing, or a dynamic collection of data. Each snapshot reads the instance views and populates the PERFSTAT tables with current point-in-time data.

Performance Tuning - Question 018

Which dynamic performance view is the primary place to look for system performance and resource usage information?

A . V$RESOURCE

B . V$PERFORMANCE

C . V$SYSTEM_EVENT

D. V$SYSSTAT

E . V$SYSTEM_STATS

Explanation:

Answer D: System performance and resource usage stats are kept in the V$SYSSTAT view. Hit ratios, cumulative logons, total CPU usage, parse counts, total physical reads and writes, and in-memory and to-disk sorts are just a few of the vital statistics that are available in this view. Wait events are recorded in the V$SYSTEM_EVENT view; V$PERFORMANCE, V$RESOURCE, and V$SYSTEM_STATS are fictitious.

Performance Tuning - Question 017

One way to determine the frequency of checkpoints is to set which of the following parameters and then check the alert.log file for messages?

A. LOG_CHECKPOINTS_TO_ALERT

B . LOG_SIMULTANEOUS_COPIES

C . LOG_CHECKPOINT_TIMEOUT

D . ALERT_LOG_CHECKPOINTING

Explanation:

Answer A: Setting LOG_CHECKPOINTS_TO_ALERT causes the checkpoint process to write a message to the alert log for the instance. You can then compare the timestamp of each message to determine checkpoint frequency. ALERT_LOG_CHECKPOINTING is not a valid parameter. LOG_SIMULTANEOUS_COPIES was used to configure redo copy latches prior to Oracle8i and is no longer supported.. LOG_CHECKPOINT_TIMEOUT specifies the maximum amount of time in seconds a dirty buffer can remain in the database buffer cache before DBWn must write it to disk.

Performance Tuning - Question 016

You notice that all SQL statements that have a join condition on one particular table appear to be running very slowly. Which of these steps should you take first to help identify the performance problem?

A . Check to see if the database is running the CBO or RBO.

B. Verify that there are no missing indexes from the table.

C . Verify that the table blocks are stored in the buffer cache.

D . Check file I/O statistics for the data file and tablespace that the table is in.

Explanation:

Answer B: The problem statement clearly identifies at least one possible problem; that is the missing index on the columns in the join condition. If all queries were experiencing performance problems, then we might first verify that there are recent statistics on tables if the CBO is used. If objects in a specific tablespace were seen as performance constrained, we would check the file I/O statistics for the files in the tablespace.

Performance Tuning - Question 015

Which of these tuning goals are appropriate for a data warehouse system?

A. Improve the performance of specific SQL statements

B . Subsecond response time

C . High data concurrency

D . 24x7 availability

Explanation:

Answer A:In a Data data Warehouse warehouse system, specific SQL statement performance, accuracy, and availability are key tuning issues. Sub-second response times, 24x7 availability, and high data concurrency are usually requirements for an OLTP system.

Performance Tuning - Question 014

Which of the following is not considered an important goal of the database configuration phase?

A. Tuning application code

B . Tuning disk I/O

C . Tuning memory

D . Tuning CPU utilization

Explanation:

Answer A: At the database configuration phase of the application design process, we are mostly concerned with tuning memory, I/O, and CPU. The application code should have already been tuned in the development and test environments. There are usually user acceptance testing and post-implementation opportunities to tune the application SQL code.

Performance Tuning - Question 013

Before you can create a resource plan directive, which of the following steps must you complete? (Choose two.)

A. Create a resource plan.

B . Specify which plan will be used by the instance.

C. Create a pending area.

D . Validate the resource plan.

E . Submit the resource plan.

Explanation:

Answer A and C: If you want to create a resource plan directive, you must first create a resource plan and resource consumer groups. Before you can create a resource plan, you must create a pending area. Validate and submit a resource plan after you create the resource plan directives. Once the plan has been submitted, you can specify that the plan will be used by the instance.

Performance Tuning - Question 012

For what purpose would you copy statistics between databases?

A . So that stored outline execution plans can be modified in the source database.

B. To configure an accurate model of a production environment on a test system.

C . So that the data dictionaries between the two databases can be synchronized.

D . So that the data in the schema tables will be the same.

Explanation:

Answer B: Many times the statistics gathered in a test environment are not equivalent to those gathered in the production environment because data distribution and volume are not the same. By copying statistics from one environment to the next, you can get a more accurate performance analysis on SQL statements.

Performance Tuning - Question 011

The DBA should gather statistics regularly for which reason?

A . If the CBO is specified and there are no statistics on any of the tables referenced in a query, then the RBO is automatically used.

B . If the RBO is used, performance can be adversely affected if statistics are stale.

C . If the RBO is specified and there are no statistics on any of the tables referenced in a query, then the CBO is automatically used.

D. If the CBO is used, performance can be adversely affected if statistics are stale.

Explanation:

Answer D: The CBO relies heavily on the accuracy of the gathered statistics. If the data is modified or turned over rapidly, then the statistics will become stale rapidly. The solution to stale statistics is to gather new statistics.

Performance Tuning - Question 010

When using TKPROF to format a SQL trace file from a user session, which of the following parameters should you specify to remove recursive SQL statements from the output?

A . Explain=no
B . Sys_sql=no
C . Recursive_sql=no
D . Recursive=no
E. Sys=no

Explanation:

Answer E: Recursive SQL statements are not written to the formatted output file if you indicate the sys=no parameter and value pair. Each of the other answers are invalid parameter/value pairs.

Performance Tuning - Question 009

To create a stored outline that has a nonsystem generated outline name, you'll need to perform which of the following?

A . Use the DBMS_OUTLIN.CREATE_OUTLINE procedure.
B . Set the CREATE_STORED_OUTLINES initialization parameter.
C. Use the CREATE or REPLACE OUTLINE command.
D . Use the ALTER SYSTEM SET CREATE_STORED_OUTLINES command.

Explanation:

Answer C: You can create a named stored outline for one query at a time with the CREATE or REPLACE OUTLINE command. Use the DBMS_OUTLIN.CREATE_OUTLINE procedure and Set the CREATE_STORED_OUTLINES initialization parameter are invalid answers; Use the ALTER SYSTEM SET CREATE_STORED_OUTLINES command. will create stored outlines with system-generated names for all executed SQL statements.

Performance Tuning - Question 008

Which of these functions are performed by the optimizer? (Choose four.)

A. Checks the SQL syntax.
B. Chooses an access path for each table.
C. Transforms statements, if needed.
D. Evaluates expressions and conditions.
E. Chooses the optimizer approach (rule- or cost-based).

Explanation:

Answer BCDE: These are the functions of the optimizer. The parser is responsible for validating SQL syntax and database references. The SQL execution engine then executes the query.

Performance Tuning - Question 007

If you have enabled MONITORING for an index, determine if the index can be dropped by querying which dynamic performance view?

A . V$SORT_USAGE
B . V$INDEX_USAGE
C . V$QUERY_USAGE
D . V$TABLE_USAGE
E. V$OBJECT_USAGE

Explanation:

Answer E: The V$OBJECT_USAGE view indicates if an index has been used during the monitoring period. You can enable monitoring for an index by using the ALTER INDEX "el" MONITORING USAGE clause. This is not a valid clause when creating the index.

Performance Tuning - Question 006

Why is it necessary to reorganize an index because of delete activity?

A . Empty blocks are read during index lookups.
B. Index blocks that contain only a single entry still must be maintained.
C . Empty blocks are skipped during an index lookup.
D . Empty branch nodes are still visited during an index scan.

Explanation:

Answer B: Sparsely populated index blocks degrade performance. If you have deleted a significant number of rows from the table that the index is on, you should reorganize the index. The general guideline is 20 percent of the rows deleted should trigger the DBA to reorganize the index. D is not correct because you would not have an empty branch node in a B-Tree index (must be balanced). A and B are not correct because index lookups only apply to populated leaf blocks. However, empty blocks are read during index scans, which is another reason why you should rebuild your index after a large number of rows have been deleted.

Performance Tuning - Question 005

Which of these guidelines will reduce potential row migration?

A . Decrease PCTFREE.
B. Increase PCTFREE.
C . Use the DBMS_STATS.unchain procedure.
D . Use the ANALYZE command on the table regularly.
E . Increase PCTUSED.

Explanation:

Answer B: Row migration occurs because there is not enough free space in the data block where the row was initially placed. Increasing the PCTFREE setting will reduce the likelihood of row migration, but the modification does not change existing data blocks. The new settings apply to all data blocks used by the table, including blocks already allocated and subsequently allocated for the table. However, the blocks already allocated for the table are not immediately reorganized when space usage parameters are altered, but are necessary after the change. Once the PCTFREE is changed, DML will utilize the new setting.

Performance Tuning - Question 004

To recover unused space from a table that has never been used but keeps the data intact, which of these options will cause the least impact on application users?

A . Export the table, truncate the table, and import the table.

B . Truncate the table.

C . Drop the table.

D . Export the table, drop the table, and import the table.

E. Deallocate unused blocks.

Explanation:

Answer E: Execute the command ALTER TABLE table_name DEALLOCATE UNUSED. This will deallocate space from the table that is above the high watermark and return it to the free space in the tablespace. There is no impact to the user because the data blocks that have rows in them will not be affected. Each of the other methods mentioned will affect users that need the data.

Performance Tuning - Question 003

For a data warehouse fact table, which of the following block storage table parameter settings can improve full full-table scan performance on large tables?

A . Set PCTUSED to 50 percent% of PCTFREE.

B . Set PCTFREE to 50 percent% of PCTUSED.

C . Set PCTUSED low.

D . Set PCTUSED high.

E. Minimize PCTFREE.

Explanation:

Answer E: Because we're looking to perform full-table scans against traditionally large tables, we want to make sure that we read as many rows as we can for each I/O. By minimizing PCTFREE, we will probably fit more rows into a block (higher block density) and improve the performance of full-table scans. PCTUSED is irrelevant because we should not have delete activity in a data warehouse fact table.

Performance Tuning - Question 002

Which of these will cause performance degradation on full-table scans?

A. Empty or underused blocks below the high watermark.

B . Empty blocks above the high watermark.

C . Performance indexes on nonpimary key columns.

D . Empty blocks in the region of a segment that have never been used.

Explanation:

Answer A: Empty or underused blocks below the high watermark will be visited by every full-table scan, thus degrading performance. If full-table scans are used frequently, then consider reorganizing the table to eliminate the empty and underused blocks.

Performance Tuning - Question 001

Which of these is not a performance advantage of large table extents?

A . It is possible to read the entire extent map with one I/O.

B . The object has space pre-allocated for future growth.

C . Dynamic extent allocation is reduced with large extents instead of small extents.

D. Some of the space may be wasted until the object grows into the large extents.

Explanation:

Answer D: If you size segments so that they don't extend frequently, then you will have unused space in the extents until the objects become fully populated. This wasted space is not a performance advantage and probably is not a disadvantage either.

Architecture and Administration Q020

Which of the following is not part of the database buffer pool?



1. KEEP
2. RECYCLE
3. LIBRARY
4. DEFAULT



Answer 3 - There is no database buffer cache called LIBRARY. The DBA can configure multiple buffer pools using the appropriate initialization parameters for improved performance. The KEEP buffer pool retains the data blocks in memory, thay are not aged out (as the name implies). The RECYCLE buffer pool removes the buffers from memory as soon as they are not needed. The DEFAULT buffer pool contains the blocks that are not assigned to the other pools.