Question 1. Which two are valid modes for a process requesting latches?
A. Scheduled
B. Deferred
C. Immediate
D. Pre- Emptive
E. Willing- to- Wait
Solution C, E.
Question 2. Which three SQL statements can use the NOLOGGING mode to reduce redo operations?
A. UPDATE
B. CREATE INDEX
C. ALTER INDEX …REBUILD
D. Conventional Path INSERT
E. CREATE TABLE …AS SELECT
Solution B, C, E.
Question 3.Which three statements about rebuilding indexes are true?
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.
Solution A, B, C.
Question 4 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. Updates are inexpensive, because only the new column value must be stored.
Solution C.
Question 5 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 users trace file
Solution A.
Question 6 Which action could result in less frequent checkpoints?
A. Increasing the number of redo log groups.
B. Increasing the value of DB_BLOCK_SIZE parameter.
C. Decreasing the value of the REDO_LOG_BUFFERS parameter.
D. Increasing the value of the FAST_START_IO_TARGET parameter.
Solution D.
Question 7. What are two benefits of using locally managed tablespaces with segment space management set to auto?
A. It eliminates the need to set PCTFREE.
B. It eliminates any need to set PCTFREE or FREELISTS.
C. It eliminates any need to set PCTUSED or FREELISTS.
D. It improves the speed of space allocation and deallocation.
E. It allows the DBA to use DB_FILE_MULTIBLOCK_READ_COUNT when setting extent sizes for a table.
Solution C, D.
Question 8 Which three guidelines should you follow regarding the performance of redo logs?
A. Avoid using RAID 5 for redo logs.
B. Place redo logs on disks without any other files.
C. Manually stripe redo log files across several disks.
D. Place redo log files on disks with non- Oracle files.
E. Place members of the same group on different physical disks.
Solution A, B, E.
Question 9 What are two possible causes of lock contention?
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.
Solution A, E.
Question 10 Which view would you query to monitor cumulative total waits for all events and all
sessions?
A. V$SYS_EVENTS
B. V$SYSTEM_EVENT
C. V$SESSION_WAIT
D. V$SYSTEM_STATUS
Solution B.
Question11 The alert log file for a database instance indicated that the checkpoints are frequently failing to complete.
Which action would be a remedy in this situation?
A. Increase the number of archiver ( ARCn) processes.
B. Increasing the number of members for all log groups.
C. Increasing the number of log writer ( LGWR) processes.
D. Increasing the number of database writer ( DBWn) processes.
Solution D.
Question 12 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.
Solution A.
Question 13 Which single dynamic views is the most useful for determining bugger cache performance when using multiple buffer pools?
A. V$ SYSSTAT
B. V$ BUFFER_ POOL
C. V$ SYSTEM_ EVENT
D. V$ BUFFER_ POOL_ STATISTICS
Solution D
Question 14 Which three actions will cause queries to place a tables blocks at the most- recentlyused end of the LRU list?
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.
Solution A, C, E.
Question 15 Which type of change to an application is most likely to improve performance of the library cache?
A. Adding more frequent COMMIT statements.
B. Replacing bind variables with constraints.
C. Reusing as much generic code as possible.
D. Replacing database constraints with triggers.
Solution C.
Question 15 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.
Solution A.
.
Question 16 The cost- based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_ MODE is set to FIRST_ ROWS. Which executation plan will be the result?
A. The sort-merge join
B. The nested loops join
C. This depends on some sort parameter values.
D. This depends on the number of rows in each table.
Solution B.
Question 17 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.
Solution C.
Question 18 What can you use the values in the GETS and GETMISSES columns of V$ROWCACHE to determine the hit ratio for?
A. Library cache.
B. Dictionary cache.
C. Entire shared pool.
D. Large objects such as PL/ SQL packages.
Solution B.
Question 19 User John creates an index with this statement:
CREATE INDEX dept_indx On dept ( empno);
In which tablespace would the index be created?
A. SYSTEM tablespace.
B. John default tablespace.
C. Tablespace will rollback segments.
D. Same tablespace as the EMPLOYEE table.
Solution B.
Question 20 You pinned an object in the shared pool using the DBMS_ SHARED_ POOL package. Which command could you use to unpin this object, assuming you are in a SQL * Plus session?
A. ALTER SYSTEM FLUSH SHARED_ POOL;
B. EXECUTE dbms_shared_ pool.unping;
C. EXECUTE dbms_ shared_ pool.unkeep;
D. EXECUTE dbms_library_cache.unpin;
Solution C.
Question 21 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?
A. UPDATE
B. CREATE INDEX
C. ALTER INDEX.. REBUILD
D. Conventional Path INSERT
E. CREATE TABLE¡-. AS SELECT
Solution B, C, E.
Question 22 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 not need to monitor library cache usage.
B. You have to tune the database buffer cache regularly.
C. You have to rune 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.
Solution D.