Question 1.
Which single dynamic view is the most useful for determining buffer cache performance when using multiple buffer pools?

A. V$SYSSTAT
B. V$BUFFER_POOL
C. V$SYSTEM_EVENT
D. V$BUFFER_POOL_STATISTICS

Answer:   D

Explanation:
V$BUFFER_POOL_STATISTICS view give information about multiple buffer pools in detail.

Question 2.
What is the main reason for a row overflow area when creating index-organized tables?

A. Avoid row chaining and migration.
B. Keep the b-tree structure densely clustered.
C. Speed up full table scans and fast full index scans.
D. Improve performance when the index-organized table is clustered.

Answer:   B

Explanation:
Row overflow area is an area where you can store non key columns. For this you specify overflow tablespace and the column name where the row is split. The row over flow is preserve dense clustering of a B-Tree index.

Question 3.
Data dictionary information is held in memory longer than library cache data. Which is most likely to be true as a consequence of this?

A.	You do no not need to monitor library cache usage.
B.	You have to tune the database buffer cache regularly.
C.	You have to tune the library cache and dictionary cache independently.
D.	Good hit ratios in the library cache imply acceptable hit ratios in the dictionary cache.
E.	Good hit ratios in the dictionary cache imply acceptable hit ratios on the database buffer cache.

Answer:   D

Explanation:
Library cache contains SQL statements and PL/SQL blocks. It uses the LRU mechanism to manage the cache. Data Dictionary cache holds definitions of dictionary objects in memory. If hit ratio in the library cache is good it imply acceptable hit ratios on the database buffer cache.

Question 4.
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, B & E

Explanation:
When a table is small and is used very frequently by many users then you can place this table in the LRU list to improve performance. Use CACHE clause with the table creation command. Also a table can be altered to set the CACHE option. When using SQL command to query the table then use the CACHE hint in the SQL statement to place the table in the LRU list

Question 5.
When performing a sort operation, you notice that there are a large number of sorts requiring I/O 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:
When a large sort operations needs to be perform and the memory area become short then you need to increase the SORT_AREA_SIZE to allow more sort operations to be performed in the memory. SORT_AREA_SIZE parameter specifies an area in the memory for sort operation 
It can be changed dynamically and all large sort operations are done with in this memory area.

Question 6.
Which procedure of the DBMS_RESOURCE_MANAGER package would first need to be performed when creating a new resource object?

A. CREATE_PLAN
B. CREATE_PENDING_AREA
C. CREATE_CONSUMER_GROUP
D. CREATE_PLAN_DIRECTIVE

Answer:   B

Explanation:
CREATE_PENDING_AREA is use with the DBMS_RESOURCE_MANAGER package to create pending area. This area is used to store new resource manager plans, consumer groups and resource plan directives and changes before they are committed. This area is used to facilitate validations before the changes are accepted. 

Question 7.
When a deadlock occurs, what should be used to diagnose the problem and determine how to prevent the deadlock from occurring again?

A. Performance manager.
B. ORA 00060 error message.
C. The resulting trace file.

Answer:   C

Explanation:
A dead lock situation will occur when two or more users wait for data locked by each other. The oracle server automatically detect and resolve dead locks by rollbacking the transaction. Monitor trace file to detect dead lock error to determine if there are problems with the applications. Dead lock situation will be recorded in the trace file in the USER_DUMP_DEST directory with an ORA 00060 error message.

Question 8.
Which statement about the amount of undo generated is true?

A. The amount is the same for any DML operation.
B. Deletes are inexpensive, because only the ROWID must be stored.
C. Inserts are inexpensive, because only the ROWID must be stored.
D. Update are inexpensive, because only the new column value must be stored.

Answer:   C

Explanation:
Inserts are less expensive than update or delete because only the ROWID of the new inserted rows is stored in the rollback segment while for update the whole old data and the new data is stored and will remove when the transaction commit or rollback. Similarly for delete more space is required to stored undo generated than insert.

Question 9.
Sometimes the LGWR process must wait because DBWn has not compressed checkpointing a file. How do you identify this situation?

A. Check the V$SESSION_WAIT view for the ‘log buffer space’ event.
B. Check the alert.log file for the message “CHECKPOINT NOT COMPLETE”
C. Check the ‘redo buffer allocation retries’ statistic in the V$SYSSTAT view
D. Check the ‘log file switch (checkpoint complete)’ event in the V$SYSTEM_EVENT view

Answer:   B

Explanation:
Some time ‘CHECKPOINT NOT COMPLETE’ message will be found in the alert log file it occur by the non completion of checkpoints due to spinning the redo log files so rapidly. A redo file on which checkpoint is occurring is required by LGWR and therefore LGWR has to wait for the checkpoint to be completed on the redo log file.

Question 10.
Which two parameters could result in problems when starting more shared servers? (Choose two).

A. PROCESSES
B. MTS_MAX_SERVERS
C. MTS_MAX_PROCESSES
D. MTS_MAX_DISPATCHERS
E. PARALLEL_MAX_SERVERS

Answer:   B & D

Explanation:
In multi threaded server environment a maximum number of shared servers cannot be more than the value set in the MTS_MAX_SERVERS parameter starting more server then this limit would cause a problem and similarly a maximum number of dispatchers cannot be more than MTS_MAX_DISPATCHERS parameter value.
 MTS_SERVERS and MTS_DISPATCHERS gives the initial number of servers and dispatchers started.

	Web www.certsbraindumps.com