Q. 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.
Q. 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.
Q. 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.
Q. 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.
Q. 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.
Q. 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.
Q. 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.
Q . 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.
Q. 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.
Q. 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.
Q. 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
Q. 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.
Q. What is the least number of buffers an LRU latch must cover in the database buffer cache?
Q. 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.
Q. What will this statement do? CREATE TABLESPACE temp DATAFILE ‘C:databasetemp.dbf’ SIZE 10n 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.
Q. 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.
Q. 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.
Q. Which dynamic view is most useful for determining the current number of blocks allocated to a buffer pool?
Q. Which tablespace is used as the temporary tablespace if ‘TEMPORARY TABLESPACE’ is not specified for a user?
Q. 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.