Performance Tuning - Question 083

Which two views can be used to detect lock contention? (Choose two)

A. V$LOCK

B. V$LOCKED_OBJECT

C. V$LOCK_CONTENTION

Answer: A, B

Explanation:

When left to its default mechanisms, Oracle generally does a very effective job of managing locking. When it does occur, lock contention can be identified using the V$LOCK and V$LOCKED_OBJECT dynamic performance views, the DBA_WAITERS and DBA_BLOCKS data dictionary views, and the OEM Performance Manager GUI.

Incorrect Answers

C: There is no V$LOCK_CONTENTION data dictionary view in Oracle.

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

Chapter 9: Tuning Contention

Performance Tuning - Question 082

Which statement could require a sort?

A. SELECT DISTINCT dept_id FROM emp:

B. UPDATE emp SET salary=salary*1.1 WHERE id =7722;

C. SELECT emp_id, name FROM emp WHERE emp-id= 7722;

D. SELECT emp_id, name FROM emp WHERE emp_id BETWEEN 7722 and 7100;

Answer: A

Explanation:

The types of SQL statements that can cause database sorts to occur include the following: ORDER BY, GROUP BY, SELECT DISTINCT, UNION, INTERSECT, MINUS, ANALYZE, CREATE INDEX, joins between tables on columns that are not indexed.

Incorrect Answers

B: This UPDATE statement does not require any sort operation.

C: This SELECT statement does not require any sort operation.

D: Usage of BETWEEN clause in the SELECT statement will not cause any sort operation.

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

Chapter 8: Tuning Disk I/O

Performance Tuning - Question 081

When performing a sort operation, you notice that there are a large number of sorts requiring I/0 to the disk. Which parameter could be increased to allow more sorts to be performed in memory?

A. SORT_AREA_SIZE

B. LARGE_POOL_SIZE

C. SORT_AREA_RETAINED_SIZE.

D. SORT_MULTIBLOCK_READ_COUNT

Answer: A

Explanation:

The amount of memory set aside for each user’s Server Process to perform these sort operations is impacted by the following init.ora parameters: SORT_AREA_SIZE, SORT_AREA_RETAINED_SIZE, PGA_AGGREGRATE_TARGET, WORKAREA_SIZE_POLICY. The default value for SORT_AREA_SIZE is OS-dependent. The minimum size for this parameter is equivalent to six Oracle blocks. The maximum size is OS-dependent. SORT_AREA_SIZE specifies how much memory each user’s Server Process should set aside to perform in-memory sort operations.

Incorrect Answers

B: LARGE_POOL_SIZE parameter is used to buffer I/O server processes as well as backup and recovery. It also caches session data when the Shared Server feature is used.

C: Once a sort operation is complete, if the sort area still contains sorted rows that need to be returned to the user, the user’s Server Process reduces the memory set aside for the final fetch to the value specified by SORT_AREA_RETAINED_SIZE.

D: SORT_MULTIBLOCK_READ_COUNT specifies the number of database blocks to read each time a sort performs a read from a temporary segment. Temporary segments are used by a sort when the data does not fit in SORT_ARE_SIZE of memory. In these situations, sort writes out sections of data to temporary segments in the form of sorted runs. Once all the data has been partially sorted to these runs, sort merges the runs by reading pieces of them from the temporary segment into memory to produce the final sorted output. If SORT_AREA_SIZE is not large enough to merge all the runs at once, subsets of the runs are merged in a number of merge passes.

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

Chapter 8: Tuning Disk I/O

Performance Tuning - Question 080

Which two statements regarding OLTP systems are true? (Choose two)

A. Use literals for optimally shared SQL rather than bind variables to keep the overhead of parsing to a minimum.

B. To avoid the performance load of dynamic space allocation, allocate space explicitly so tables,clusters and indexes .

C. B-tree indexing is preferred to bitmap indexing, because of locking issues affecting DML operations.

D. Use hash clusters especially on tables that are heavily inserted into, because of the use of space and the number of blocks that need to be visited.

E. Use application code to enforce rules instead of constraints, because constraints are extremelyexpensive to process.

Answer: B, C

Explanation:

Online Transaction Processing (OLTP) systems tend to be accessed by large numbers of users doing short DML transactions. Users of OLTP systems are primarily concerned with throughput: the total time it takes to place an order, remove an item from inventory, or schedule an appointment. To avoid the performance load of dynamic space allocation, you need to allocate space explicitly so tables, clusters and indexes .

Bitmap indexes will not work good for the OLTP systems because of locking issues affecting DML operations. B-tree indexes can handle this easier and effective ly .

Incorrect Answers

A: You cannot use literals for optimally shared SQL rather than bind variables because of nature of OLTP systems: they work effectively using bind variables because of performing DML operations.

D: Hash clusters work more effective for DSS (Decision Support Systems): high level inserts and updates will eliminate the advantage of hash clusters which require lower level of DML activity as DSS systems provide.

E: Constraints are not expensive to process: they need to be used to avoid additional application code creation to enforce a business rules.

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

Chapter 3: SQL Application Tuning and Design

Performance Tuning - Question 079.

Which of the following Resource Manager rules must be validated with the DBMS_RESOURCE_MANAGER.validate_pending_area procedure? (Choose three.)

A. The plan must have a directive for SYSTEM_GROUP.

B . The plan must have a directive for OTHER_GROUPS.

C . No subplan loops.

D . The plan must have a directive for SYS_GROUP.

E. Each subplan must reference the top-level plan's consumer group.

Explanation:

Answer A and E: Resource Manager consumer groups are user-defined (or DBA-defined) defined groups of schema users that have similar resource requirements. The DBA can assign a user to as many consumer groups as is required. A session logs on with the default consumer group for the user, and the user session can then switch to other allowed consumer groups as needed.

Performance Tuning - Question 078.

Which of the following Resource Manager rules must be validated with the DBMS_RESOURCE_MANAGER.validate_pending_area procedure? (Choose three.)

A. The plan must have a directive for OTHER_GROUPS.

B . Each subplan must reference the top-level plan's consumer group.

C. Referenced subplans and/or consumer groups must exist.

D. No subplan loops.

E . The plan must have a directive for SYSTEM_GROUP.

Explanation:

Answer A, C and D: The validate_pending_area procedure checks the entries in the resource plan against the Resource Manager rules. The plan must specify a directive for OTHER_GROUPS. No subplan loops are allowed; that is, no subplan can have a directive that includes a higher-level plan as a subplan. All referenced subplans and consumer groups are verified.

Performance Tuning - Question 077.

Which of these tools are used to copy statistics between databases? (Choose three.)

A . SQL*Plus copy

B. import

C. DBMS_STATS

D. export

E . DBMS_JOB

Explanation:

Answer B, C and D: DBMS_STATS is used to create and populate the statistics table in a schema. The export and import utilities are used to extract the object from the source database and bring it into the target database. DBMS_STATS is then used on the target database to move the statistics into the data dictionary.

Performance Tuning - Question 076.

In which of these cases should statistics be run on database schema objects? (Choose two.)

A . OPTIMIZER_MODE=COST

B. OPTIMIZER_MODE=CHOOSE

C . The RBO is used exclusively by setting OPTIMIZER_MODE=TRUE.

D. OPTIMIZER_MODE=ALL_ROWS

E . OPTIMIZER_MODE=RULE

Explanation:

Answer B and D: TRUE and COST are not valid OPTIMIZER_MODE settings. When RULE is specified, statistics are not used. ALL_ROWS and CHOOSE are both CBO settings; that is, ALL_ROWS indicates to the CBO that it should optimize for throughput, and CHOOSE indicates to use the CBO if statistics are available on any table in the SQL statement. If any table in the SQL statement has statistics on it, then performance is better under the CHOOSE option; however, performance will be best if all the tables have statistics on them.

Performance Tuning - Question 075.

The EXPLAIN PLAN command, the Autotrace feature in SQL*Plus, and the TKPROF utility each have the following in common?

A . Each requires the explain_table in the schema of the SQL originator.

B . The PLAN_TABLE must exist for each in all conditions.

C. A plan table is required for each, and for TKPROF when explain=username/password.

D . Each requires the DBA or Unix administrator to execute.

E . They each run within a SQL*Plus session.

Explanation:

Answer C: Both the EXPLAIN PLAN command and the Autotrace option require a plan table. The TKPROF command requires the plan table only if the explain parameter is included.

Performance Tuning - Question 074.

To begin using a stored outline for the instance, what should you do?

A . Execute the ALTER DATABASE USE_STORED_OUTLINES=category_name command.

B . Execute the procedure DBMS_OUTLN.USE_SYSTEM_STORED_OUTLINES('category_name').

C. Execute the ALTER SYSTEM USE_STORED_OUTLINES=category_name command.

D . Set init.ora parameter USE_STORED_OUTLINES=category_name.

Explanation:

Answer C: If category_name is DEFAULT or TRUE, then the DEFAULT outline is used. Otherwise, specify a named stored outline. USE_STORED_OUTLINES is not an initialization parameter.

Performance Tuning - Question 073.

The rule-based optimizer (RBO) relies on which of the following? (Choose three.)

A. Data dictionary information about the structure of the referenced objects.

B. The syntax of the SQL statements.

C . Statistics gathered by the DBMS_STATS package.

D. Its set of heuristics to determine which plan will work best.

Explanation:

Answer A, B and D: The RBO does not use statistics to determine execution plans; it uses the syntax of the SQL statement, uses dictionary information about the structure of each object referenced in the statement, and applies a set of rules to determine optimal execution plans.

Performance Tuning - Question 072.

To start and stop monitoring an index to determine usage, use which ALTER INDEX command clause?

A . START MONITORING/STOP MONITORING

B. MONITORING USAGE/NOMONITORING USAGE

C . START MONITORING USAGE/STOP MONITORING USAGE

D . START USAGE/STOP USAGE

Explanation:

Answer B: The MONITORING USAGE clause is used to both start and stop monitoring index usage. The V$OBJECT_USAGE view will indicate if the index was used during the monitoring period.

Performance Tuning - Question 071.

Index reorganization is essential for volatile indexes because? (Choose three.)

A. Deleted row entries are reused based on index key value.

B . Deleted row entries are reused based on the PCTUSED block parameter.

C. Delete activity will degrade the index performance.

D. Leaf blocks with only one entry are still maintained.

E . Modified index keys are moved to less sparse blocks.

Explanation:

Answer A, C and D: Compared to an index with no empty row slots, an index with deleted rows will have an overall reduction in effectiveness because more block reads will be required to process the same number of rows. When a row is deleted, a new entry into the index will only replace it if the index key value is identical; therefore, there is a the potential that deleted row entries will not be reused. Each block in the index is maintained, even if there is only one row in it.

Performance Tuning - Question 070.

Which of the following statements accurately describes the chaining and migration of Oracle blocks? (Choose two.)

A. Chaining occurs when a row is too large to fit into a single data block.

B . Chaining occurs when a row is updated and will not fit back into the block where its physical ROWID points to.

C . Migration occurs when a row is too large to fit into a single data block.

D. Migration occurs when a row is updated and will no longer fit back into the block where its physical ROWID points to, but will fit into another data block.

E . The physical ROWID changes to the new block in both chaining and migration.

Explanation:

Answer A and D: Chaining and migration are two distinctly different events that can occur to rows of data in a data block. An INSERT or UPDATE can cause chaining usually if the row is very large. Migration occurs when a row is updated, and the row will not fit back into the block. If Oracle finds enough free space and has to move the row to another block, a small piece of the row remains in the original block, so the ROWID doesn't change. Neither row chaining nor migration requires index rebuilds.

Performance Tuning - Question 069.

You can recover space from a sparsely populated table by using the ALTER TABLE MOVE command, but what are the negative side effects? (Choose two.)

A . The rows are sorted and coalesced, but no new storage parameters are allowed.

B. The indexes must be rebuilt before they can be used.

C . All indexes are dropped because the physical ROWID no longer exists.

D . The indexes must be dropped before the move statement will succeed.

E. All indexes become invalidated because the physical ROWID of each row in the table changes.

Explanation:

Answer B and E: When the table is moved, indexes are invalidated because the ROWID for each index entry is not automatically converted to the new physical ROWID. Therefore, we must rebuild an index before it can be used. Check the STATUS column in DBA_INDEXES to verify.

Performance Tuning - Question 068.

Which two conditions are necessary for free space in a block to be coalesced automatically? (Choose two.)

A . Free space is not coalesced automatically; it must be done manually.

B . Free space is coalesced automatically on instance startup.

C. An INSERT or UPDATE statement attempts to update a block that has enough free space.

D. The free space in the block is fragmented such that no new rows can fit into contiguous free space.

E . A DELETE statement is issued, and there are noncontiguous rows below the PCTFREE mark.

Explanation:

Answer C and D: Free space within a data block is not coalesced manually, ; it is not coalesced automatically when rows are deleted, or when the instance starts up. Free space within a block is coalesced when there is enough free space (when the block is on the free list), an INSERT or UPDATE occurs, and the free space within the block is fragmented such that the INSERT or UPDATE cannot occur in the block unless coalescing occurs.

Performance Tuning - Question 067.

Which of these are not true about the high watermark of a data segment? (Choose two.)

A . Reset by a TRUNCATE statement.

B. Reset by a DELETE statement.

C . Recorded in the header block of the segment.

D. Recorded in the data dictionary.

Explanation:

Answer B and D: The high- water mark indicates the block in the table for which all blocks below it have been used, and all blocks above it have not been used; it is recorded in the segment header block. The TRUNCATE command resets the high- water mark to the minimum. The DELETE command does not reset the high- water mark.

Performance Tuning - Question 066.

Which of these are performance advantages of using a small block size? (Choose two.)

A . A small number of rows per block.

B. Random access is relatively more efficient.

C. Reduced block contention.

D . Good for sequential access.

Explanation:

Answer B and C: A small block size means that you'll get fewer rows into a block. However, random access improves because they don't have to read as much erroneous information.

Performance Tuning - Question 065.

In a materialized view, which of these refresh modes may be chosen as a performance-improving alternative?

A . AUTOMATIC

B . COMPLETE

C. FAST

D . MANUAL

E . DIFFERENTIAL

Explanation:

Answer C: The FAST refresh mode only sends individual changes to the target materialized view; this is usually significantly faster than a COMPLETE refresh, which resends all rows in the master table. Manual and automatic describe modes used to schedule the refresh jobs. DIFFERENTIAL is something I just made up.

Performance Tuning - Question 064.

Which of the following is true about the physical guess on an IOT?

A . Following a failed or incorrect guess, access is via the UROWID.

B . The physical guess is updated when a block splits.

C. Following a failed or incorrect guess, access is via the primary key.

D . Following a failed or incorrect guess, access is via the secondary key.

E . The physical guess is the same thing as the physical ROWID on heap-organized tables.

Explanation:

Answer C: The physical guess is a ROWID guess of the actual location of a row in the IOT. If, for whatever reason, a row moves in an IOT, the physical guess becomes invalid. The primary key is then used for IOT lookup; the primary key is slower.

Performance Tuning - Question 063.

To use a function-based index on a table, which of the following must occur? (Choose three.)

A. A query rewrite must be enabled for the session or instance.

B. The CBO must be used.

C . You must have a query rewrite system privilege.

D . The function-based index must be on a materialized view.

E. The table must be analyzed.

Explanation:

Answer A, B and E: A function-based index relies on statistics gathered for the CBO. Also, the query will be rewritten by the optimizer to use the function or expression, but only if the query rewrite parameters have been set.

Performance Tuning - Question 062.

An index-organized table (IOT) might be the best storage structure to implement, given which of the following conditions? (Choose two.)

A . Rows are frequently deleted and updated to expand character columns.

B . All queries will use full-table scans on the IOT.

C. All queries will use the primary key for lookup.

D. Rows are frequently added, but not updated or deleted.

Explanation:

Answer C and D: IOT's are best suited for physical guess lookups, based on the primary key value. An IOT is also a good choice when there is minimal maintenance required on the index structure. Too many deletes can cause performance problems due to sparsely populated blocks. An IOT must have a primary key.

Performance Tuning - Question 061.

When determining the optimal configuration for dispatchers by querying the V$DISPATCHER_RATE view, which of the following is not true?

A . If the CUR_ column values are near their AVG_ counterparts and below the MAX_ values, and response time is good, then you probably have a good number of dispatchers allocated.

B . If, under system load, you see that CUR_ is close to or equal to MAX_, then consider increasing the number of dispatchers.

C. If, under system load, you see that CUR_ is close to or equal to MAX_, then consider decreasing the number of dispatchers.

D . If CUR_ and AVG_ are consistently well below MAX_, then consider reducing the number of dispatchers.

Explanation:

Answer C: Each of the other answers is an approach you could use to tune the number of dispatchers. The basic concept that if your dispatcher load is close to MAX_, then you should consider adding more dispatchers.

Performance Tuning - Question 060.

Which of these views can you use to monitor usage of Oracle Shared server processes?

A. V$SHARED_SERVER_MONITOR

B . V$SHARED_SERVERS

C . V$SHARED_PROCESSES

D . V$SS_PROCESSES

Explanation:

Answer A: The V$SHARED_SERVER_MONITOR view is a good starting point for monitoring Shared Server processes. It indicates general statistics for Shared Server processes and high water marks. Columns include: MAXIMUM_CONNECTIONS, MAXIMUM_SERVERS, MAXIMUM_SESSIONS, SERVERS_STARTED, SERVERS_TERMINATED, and SERVERS_HIGHWATER.

Performance Tuning - Question 059.

Which of the following describes features that enable scalability for users in an Oracle Shared server environment? (Choose two.)

A . Oracle Remote Sensing enables DBAs to switch users from a dedicated server to a shared server and back based on Resource Manager connection groups.

B. Oracle Connection Manager enables multiple client network sessions to be multiplexed through a single network connection to a database.

C. Connection pooling enables you to reduce the number of physical network connections to a dispatcher.

D . Remote Dispatcher Management enables web-based users to dynamically allocate additional dispatcher resources based on load balancing algorithms.

E . Dedicated servers enable systems to scale even when memory is constrained.

Explanation:

Answer B and C: 'Remote Dispatcher Management enables web-based users to dynamically allocate additional dispatcher resources based on load balancing algorithms' and 'Oracle Remote Sensing enables DBAs to switch users from a dedicated server to a shared server and back based on Resource Manager connection groups' are not Oracle products, but I think they should consider developing them. 'Dedicated servers enable systems to scale even when memory is constrained' is a direct contradiction to what we know about dedicated servers and shared servers. Both Connection Manager and connection pooling are specified when a dispatcher is defined.

Performance Tuning - Question 058.

Which of these is not a condition that would lead to a deadlock in the current session?

A . The other session is waiting for a resource that is locked in an incompatible mode by the current session.

B . Another session is holding a lock on a resource in an incompatible mode.

C. The other session is waiting for a resource that is also requested by a third session.

D . The resource is required by the current session.

Explanation:

Answer C: The conditions Another session is holding a lock on a resource in an incompatible mode, The resource is required by the current session, and The other session is waiting for a resource that is locked in an incompatible mode by the current session are the prerequisite conditions that make a deadlock happen. Multiple users can wait for the same resource without causing a deadlock. However, multiple users could also be involved in a deadlock if the correct (but unlikely) conditions occur.

Performance Tuning - Question 057.

Querying the DBA_WAITERS view will give us which vital piece of information that we can use to resolve lock contention in an emergency?

A . WAITING_SESSION

B . BLOCKING_SESSION

C. HOLDING_SESSION

D . SESSION_SID

Explanation:

Answer C: The DBA_WAITERS view also indicates the lock mode held, and the mode requested by the WAITING_SESSION. We can use the HOLDING_SESSION along with information from V$SESSION to issue the ALTER SYSTEM KILL SESSION command.

Performance Tuning - Question 056.

What are the results of this query? (Choose all that apply.)
SELECT * FROM v$lockMINUSSELECT * FROM v$enqueue_lock/

A . All the DDL locks on the system

B. All user locks on the system

C. All DML locks on the system

D . Always 0 rows, since the views are synonymous

Explanation:

Answer B and C: The V$LOCK view shows all locks on the system. The V$ENQUEUE_LOCK view has the same columns as V$LOCK, except it displays all locks owned by enqueue state objects only. The difference is user and DML locks.

Performance Tuning - Question 055.

When users inform the DBA that a resource is unavailable or that queries are not responding, which views should the DBA use to determine the locking and waiting sessions? (Choose two.)

A . V$BLOCK_AND_WAIT

B. DBA_BLOCKERS

C . DBA_LOCKS_HELD

D . V$SESSIONS_WAITING

E. DBA_WAITERS

Explanation:

Answer B and E: The DBA_BLOCKERS and DBA_WAITERS views are used to determine who is causing lock contention, and who is waiting on lock requests. DBA_LOCKS can be used, but the DBA_BLOCKERS and DBA_WAITERS views are pre-defined to display the basic information needed for problem resolution. V$SESSIONS_WAITING and V$BLOCK_AND_WAIT are not valid views.

Performance Tuning - Question 054.

The DBA can modify the default locking mechanism by setting which init.ora parameter?

A . ROW_LOCKING=DEFAULT

B . ROW_LOCKING=TABLE

C . ROW_LOCKING=ROW

D. ROW_LOCKING=INTENT

Explanation:

Answer D: The DBA can modify the default locking mechanism by setting the ROW_LOCKING initialization parameter. The default value is ALWAYS; this causes default locking at the row level during DML statements. This is the least restrictive. You can also specify DEFAULT, which is synonymous with ALWAYS. Setting the value to INTENT causes default locking at the table level, except for SELECT...FOR UPDATE statements, which cause row-level locking.