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.
Answer: A, B, C
Explanation:
You can use the ALTER INDEX REBUILD command to optimize the storage characteristics of an index.Using the ALTER INDEX command with the REBUILD option is an effective way to quickly rebuild an index because the existing index entries are used to create the new index. The ONLINE option of the ALTER INDEX REBUILD command should be used to minimize any locking issues that occur when an index is rebuilt while users continue to perform DML on the index’s underlying table.
D: When building an index, the NOLOGGING and UNRECOVERABLE keywords cannot be used concurrently to reduce the time it takes to rebuild.
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
Answer: A
Explanation:
Alert log file shows the nondefault parameters when the instance is started.
B: Online redo log is not used to store this information.
C: Archive redo log is just archived copy of online redo log. It does not have any information about nondefault parameters.
D: SYSTEM’s user’s trace file is not used to provide this information.
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.
Answer: A, B
Explanation:
There are two main goals for rollback segments: transactions should never wait for access to rollback segment; no transaction should ever run out of rollback space because it used to keep the read-consistent view of the changed data.
C: Rollback should not extend continually during normal processing. It is possible only as exception to keep data for batch jobs performing DML operations with many rows.
D: Transactions should never wait for access to rollback segment.
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.
Answer: D
Explanation:
Oracle provides you with the ability to divide the Database Buffer Cache into as many as three separate areas called Buffer Pools. Segments are then explicitly assigned to use the appropriate Buffer Pool as determined by the DBA. Option with multiple segments can have blocks in multiple buffer pools.
A: There is no such relation with the KEEP buffer pool and the RECYCLE buffer pool: they can be defined independently.
B: The buffer pool for an object can be changed after an object creation.
C: The blocks from an object without an explicitly set buffer pool go into the DEFAULT pool.
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.
Answer: D
Explanation:
The main goal is to access the least possible number of blocks from disk because I/O operations are significantly more expensive as memory operations.
A: You need to use as less memory as possible.
B: You need to share the same code in the memory to reduce the memory usage.
C: The main goals is to access the least, not the most, possible number of blocks from disk.
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.
Answer: C
Explanation:
The ratio of parsed statements (GETS) to those that did not require parsing (GETHITS) is calculated in the GETHITRATIO column of V$LIBRARYCACHE. The higher this number is, the better the application is performing.
A: When the GETHITRATIO in the V$LIBRARYCACHE view is above 0.99, application performance is good.
B: The dictionary cache has nothing to do with SQL statements: it stores the data dictionary information in the memory.
D: The RELOADS column in the V$LIBRARYCACHE view shows the number of times that an executed statement had to be re-parsed because the Library Cache had aged out or invalidated the parsed version of the statement. Reload activity can be monitored by comparing the number of statements that have been executed (PINS) to the number of those statements that required a reload (RELOADS). The less this number is, the better the application is performing.
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.
Answer: A, E
Explanation:
Lock contention can arise because of uncommitted changes and unnecessarily table level locks.
B: Large amount of rollback segments decrease possibility of lock contention.
C: Lock contention is not related with the size of redo logs.
D: Lock contention is not related with shared pool size.
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.
Answer: A
Explanation:
Oracle Library Cache will NEVER allocate memory from the Large Pool.
B: Oracle Parallel Query (PQ) can cause the Shared Pool to cache additional items not related to application SQL. That’s why it can allocate memory from the Large Pool.
C: Oracle Recovery Manager (RMAN) utility can use the Large Pool to process requests for optional features.
D: UGA information for the Shared Sever option is cached in the Large Pool if it’s defined. Otherwise, it uses the Shared Pool.
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.
Answer: C
Explanation:
Oracle8i introduced a new feature, Resource Manager, which was designed to improve the allocation and management of server resources needed by application users. In Oracle 9i, it is possible to control numerous aspects of application processing via Resource Manager. The database can have many resources plans, but only one can be active at any one time.
A: Resource plans are not set using profiles.
B: The database can have many resources plans.
D: Each user cannot choose which plan to belong to because only one plan can be active at any one time.
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.
Answer: A, C, E
Explanation:
If you are creating a table with the CACHE option or altering an existing table to set the CACHE option will place a table’s blocks at the most-recently-used end of the LRU list. Blocks will be placed at the most-recently-used end of the LRU list if the query performs a full table scan.
B: Querying the table by using a CACHE hint will not cause queries to place a table’s blocks at the most-recently-used end of the LRU list.
D: Defining the table without the option for caching will not place blocks in the LRU list.
F: This statement will not cause queries to place a table’s blocks at the most-recently-used end of the LRU list.
G: You don’t need to create a separate database buffer cache to hold cached table.
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.
Answer: A
Explanation:
The Reverse Key Index (RKI) is a special type of B-Tree index. The RKI is useful when an index is built on a column that contains sequential numbers.
B: B-Tree index will be appropriate choice for the column with many different values.
C: The column is mainly used for value range scans is not good candidate for the Reverse Key Index.Reverse Key indexes are only useful for equality and non-equality searches. Queries that perform range scans (e.g., using BETWEEN, >, <) on columns that are Reverse Key indexed will not be able to use the index and will cause full table scans.
D: The RKI does not work for the column implementing an inverted list attribute.
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.
Answer: C
Explanation:
Small tables frequently retrieved with a full table scan can be cached.
A: If table is rarely retrieved there is no sense to cache it.
B: Large table cannot be easy cached because CACHE option works mostly for the small tables that can be loaded in the memory. Also if table is rarely retrieved there is no sense to cache it.
D: Large table is not good candidate to be cached.
Q.Which initialization parameter specifies the location of the alert log file?
A. UTL_FILE_DIR
B. USER_DUMP_DEST
C. LOG_ARCHIVE_DEST
D. BACKGROUND_DUMP_DEST
Answer: D
Explanation:
BACKGROUND_DUMP_DEST initialization parameter specifies the location of the alert log file.
A: UTL_FILE_DIR lets you specify one or more directories that Oracle should use for PL/SQL file I/O.
B: USER_DUMP_DEST is used to specify the location of user trace files.
C: LOG_ARCHIVE_DEST shows the directory for the archived redo logs.
Q. 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? (Choose three)
A. UPDATE
B. CREATE INDEX
C. ALTER INDEX.. REBUILD
D. Conventional Path INSERT
E. CREATE TABLE…. AS SELECT
Answer: B, C, E
Explanation:
You can use the NOLOGGING mode to create index, to rebuild index and to create table as select from the other table.
A: It cannot be used for DML operations itself. Only table can be switched to this mode and than UPDATE
command can be performed for the table.
D: Conventional Path INSERT cannot use it. Once the NOLOGGING attribute is set on a table, redo entry generation will be suppressed for all subsequent DML on the table only when that DML is of the following types: Direct Path loads using SQL*Loader, direct load inserts using the /*+ APPEND*/ hint.
Q. Which two statements about database blocks are true? (Choose two)
A. DSS environment prefer a large block size.
A. Poor application design that does not make good use of indexes.
B. Applications not written to use bind variable to take advantage of shared SQL statements.
C. Poor application design that require too many tables to be joined together to resolve queries.
D. Applications not closing cursors in a timely fashion because the init.ora parameter OPEN_CURSORS was set too high.
Answer: A, B, D
Explanation:
All these reasons can explain poor database performance.
C: Number of tables that need to be joined should not affect performance in good tuned database.
Q. Which three can you use to monitor locks? (Choose three)
A. V$LOCK
B. DBA_WAITERS
C. V$LOCKED_OBJECT
D. DBA_PENDING_TRANSACTIONS
Answer: A, B, C
Explanation:
All these views can be used to monitor locks. DBA_WAITERS shows all the sessions that are waiting for a lock, but do not hold locks for which another session is waiting.V$LOCKED_OBJECT view lists all locks acquired by every transaction on the system.
D: DBA_PENDING_TRANSACTIONS describes unresolved transactions (either due to failure or if the coordinator has not sent a commit/rollback).
Q. Which two methods can you use to calculate the size of the recycle pool? (Choose two)
A. Total the buffer cache blocks used by the object.
B. Use the direct reads statistic from a tracing tool.
C. Use the physical reads statistic from a tracing tool.
D. Use the indirect reads statistic from a tracing tool.
Answer: B, C
Explanation:
To calculate the size of the recycle pool you can use the direct reads and the physical reads statistic from the tracing tool.
A: Total the buffer cache blocks used by the object cannot be used for this purpose.
D: The indirect reads statistic from a tracing tool will not help you to calculate the size of the recycle pool.
Q. You want to find the high water mark and the number of blocks above the high water mark without analyzing the table. Which method will provide this information?
A. Use DBMS_OUTPUT package.
B. Use the ANALYZE command.
C. Use DBMS_SQL.PARSE procedure.
D. Use DBMS_SPACE.UNUSED_SPACE procedure.
Answer: D
Explanation:
The DBMS_SPACE.UNUSED_SPACE procedure will help you to find the high water mark and the number of blocks above the high water mark without analyzing the table.This procedure returns information about unused space in an object (table, index, or cluster).
A: Package DBMS_OUTPUT enables you to display output from PL/SQL blocks and subprograms, which makes it easier to test and debug them.
B: The task is not to use the ANALYZE command.
C: The PARSE procedure from the DBMS_SQL package can be used to parse DML or DDL statement.
Q. What may happen if you size your rollback segments too small?
A. The long transaction switches to SYSROL rollback.
B. The long transaction fails for lack of rollback space.
C. The long transaction hangs until the DBA alters MINEXTENTS.
D. The long transaction is forces to switch to another rollback segment.
Answer: B
Explanation:
The long transaction can fail for lack of rollback space if you size your rollback segments too small.
A: Transaction cannot be switched to another rollback segment.
C: MINEXTENTS parameter has nothing to do with rollback segments. The long transaction will just fail.
D: Transaction cannot be switched to another rollback segment.
Q. Oracle Shared Server is best used when _____ and _____. (Choose two)
A. The database is experiencing database-intensive work.
B. The database is primary used for batch or DSS operations.
C. CPU usage on your machine is consistently at 90% or higher.
D. An OLTP application is running on a machine approaching memory resource limits.
E. You do not need to scale up the number of concurrent connections to the database.
F. The database is primary used for an interactive application where dedicated servers are mainly idle.
Answer: D, F
Explanation:
Oracle Shared Server configuration will be very helpful when an OLTP application is running on a machine approaching memory resource limits or the database is primary used for an interactive application where dedicated servers are mainly idle. The idle server process is holding system resources. The shared server architecture eliminates the need for a dedicated server process for each connection
A: If the database is experiencing database-intensive work all dedicated servers are busy. But Oracle Shared Server architecture is more useful when dedicated servers are mainly idle.
B: The Oracle Shared Server configuration is more useful for OLTP systems with many DML operations,not for DSS environment with long-running transactions.
C: Oracle Shared Server will be not the best in this situation.
E: It is required when you NEED to scale up the number of concurrent connections to the database.
Continue.....