Q.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 extreme l y expensive 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.
A: You cannot use literals for optimally shared SQL rather than bind variables because of nature of OL T P 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.
Q.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.
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.
Q.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 BYSELECT DISTINCT,UNION,INTERSECT,MINUS,ANALYZE, CREATE INDEX, joins between tables on columns that are not indexed.
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.
Q.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.
C: There is no V$LOCK_CONTENTION data dictionary view in Oracle.
Q.The database includes tables with static data, which are used for queries only. To which size should you set PCTFREE for this type of table?
A. 0
B. 50
C. 20
D. 10
Answer: A
Explanation:
The parameter PCTFREE tells Oracle how much space to set aside in each block to store row updates.Since we have a database with static data, PCTFREE can be set to 0 to use the disk space more effectively.
B: By setting this parameter to 50 you will preserve 50% of data block for the future inserts. It will be just space wasting because of static nature of data inside the tables.
C: This size still cannot be considered as appropriate for the database with static data.
D: You can use this size for the database with minimal level of update activity.
Q.Which action could potentially cause checkpoints to take longer?
A. Increasing the number of redo log groups.
B. Increasing the size of rollback segments.
C. Decreasing the value of the REDO_LOG_BUFFERS parameter.
D. Increasing the value of the FAST_START_IO_TARGET parameter.
Answer: D
Explanation:
By increasing the FAST_START_IO_TARGET parameter you tell Oracle that it can use more time to perform recovery after instance crash. So it will cause that checkpoints will take longer. This parameter supersede the FAST_START_MTTR_TARGET parameter if configured. The FAST_START_MTTR_TARGET init.ora parameter is used to specify a mean time (in seconds) to recover the instance following an instance failure.
A: By increasing the number of redo log groups you will not decrease a time for checkpoints.
B: The size of rollback segments has nothing to do with checkpoints duration.
C: There is no REDO_LOG_BUFFERS parameter in Oracle.
Q.When a deadlock shutdown is detected by Oracle, where is the trace file generated?
A. SQL_TRACE
B. TRACE_DEST
C. USER_DUMP_DEST
D. CORE_DUMP_DEST
E. BACKGROUND_DUMP_DEST
Answer: C
Explanation:
Oracle creates a user trace file when processing errors in a user’s Server Process. A deadlock error will be shown in a user trace file which is located in the USER_DUMP_DEST. While events such as deadlocks automatically generate trace files, full scale tracing of user sessions does not occur unless the user or DBA requests it.
A: SQL_TRACE is the main method for collecting SQL Execution information in Oracle collecting a wide range of information and statistics that can be used to tune SQL operations.
B: The TRACE_DEST parameter does not specify the location of this file.
D: CORE_DUMP_DEST should point to the directory where core dumps from the Oracle server will beplaced. A core dump is a memory image of the Oracle shadow process produced when an unexpected ,unrecoverable or invalid condition occurs. Note that Oracle should always try to write a trace file before producing a core dump.
E: BACKGROUND_DUMP_DEST specifies the pathname (directory or disc) where debugging trace files for the background processes (LGWR, DBWn, and so on) are written during Oracle operations.
Q.If a willing-to-wait latch request is satisfied on the first attempt, which statistic gets incremented?
A. GETS
B. SLEEPS
C. MISSES
D. IMMEDIATE_GETS
E. IMMEDIATE_MISSES
Answer: A
Explanation:
GETS is number of times a Willing-to_Wait latch was acquired without waiting. So GETS statistic will be incremented.
B: SLLEPS statistic provides the number of times a process had to wait before obtaining a Willing-toWait latch.
C: MISSES shows the number of times a Willing-to_Wait latch was not acquired and a wait resulted.
D: IMMEDIATE_GETS is statistic of numbers of times an Immediate latch was acquired without waiting.
E: IMMEDIATE_MISSES is the number of times an Immediate latch was not acquired and a retry resulted.
Q.For which reason would you query V$SYSSTAT?
A. Name of the sort segment.
B. Free space available for a sort segment.
C. Number of disk sorts performed since startup.
D. Number of users active on individual sort segments.
Answer: C
Explanation:
Sort activity can be monitored using the V$SYSSTAT and V$SORT_SEGMENT dynamic data dictionary views, using the output from STATPACK and REPORT.TXT, and using the output from the OEM Performance Manager.
A: This view does not show a name of the sort segment.
B: It is not used to show a free space available for a sort segment.
D: V$SYSSTAT does not provide information about number of users active on individual sort segments.
Q.Which two statements about plan stability and stored outlines are true? (Choose two)
A. You can group outlines in categories.
B. You can only have one stored outline per SQL statement.
C. Plan stability only wants when SQL statements match textually.
D. Stored outlines are saved in the data dictionary (SYS schema).
E. Stored outlines become invalid when you analyze the associated objects.
Answer: A, C
Explanation:
Oracle9i maintains predefined execution plans in the data dictionary in the form of stored outlines. Ascertain whether the statement you have identified can be lumped into two or more broad categories of statements.Through a process known as plan equivalence, a SQL statement must exactly match the original statement used to generate an outline in order for the stored outline to be used. Even an identical statement with the addition of a hint or comment will not be considered equivalent for stored outline usage.
B:You can have as many stored outline per SQL statement as you need.
D:Stored outlines are saved in the OUTLN schema.
E:Stored outlines do not become invalid when you analyze the associated objects.That’s why they are used to preserve predefined execution plans.
Q.What does this statement do?
SQL> ANALYZE INDEX index_name VALIDITY STRUCTURE;
A. It places information into the INDEX_STATS view and allows for the monitoring of space used by an index.
B. It provides information in the INDEX_HISTOGRAM view to indicate whether an index is invalid or valid.
C. It provides information in the DBA_INDEXES view for the COST BASED Optimizer when choosing an execution plan.
Answer: A
Explanation:
The VALIDATE STRUCTURE option for the ANALYZE command populates the data dictionary view INDEX_STATS with values.
B: This command does not provide information in the INDEX_HISTOGRAM view.
C: This command does not provide information in the DBA_INDEX data dictionary view.
Q.Which three types of statistics are reported in report.txt after running UTLESTAT SQL? (Choose three)
A. Locking statistics.
B. Memory usage statistics.
C. Explain plan statistics.
D. Library cache statistics.
E. Buffer busy wait statistics.
F. Rollback contention statistics.
Answer: D, E, F
Explanation:
There are three types of statistics reported in the REPORT.TXT file after running UTLESTAT.SQL: library cache statistics, buffer busy wait statistics, rollback contention statistics.
A: Locking statistics are not reported in REPORT.TXT file.
B: There is no memory usage statistics in REPORT.TXT after running UTLESTAT.SQL.
C: Explain plan statistics are not reported in REPORT.TXT file.
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.
Answer: C, E
Explanation:
Due to high level of DML activity on the OLTP system you need to use more small rollback segments rather than few large rollback segments. To avoid the performance load of dynamic space allocation, you need to allocate space explicitly so tables, clusters and indexes.
A: This one is not main requirement of the OLTP system.
B: Table and index statistics should be gathered regularly if the CBO is used because of data volumes tend to change quickly in OLTP systems. But this requirement cannot be considered as main OLTP requirement.
D: It is not recommended to create indexes on ALL columns that are regularly used in query predicates because of multiple INSERT and UPDATE operations will cause overload on the database trying to rebuild all indexes after each transaction.
Q.Which tablespace is used as the temporary tablespace if ‘TEMPORARY TABLESPACE’ is not specified for a user?
A. TEMP
B. DATA
C. SYSTEM
D. ROLLBACK
Answer: C
Explanation:
When a user’s Server Process writes a sort chunk to disk, it writes the data to the user’s temporary tablespace. This tablespace, although it is referred to as the user’s temporary tablespace, can have the tablespace attribute of being either permanent or temporary.If theer is no TEMPORARY TABLESPACE specified for the user, SYSTEM tablespace will be considered as temporary tablespace for the user.
A: There is no TEMP tablespace in Oracle by default.
B: There is no DATA tablespace in Oracle by default.
D: ROLLBACK tablespace cannot be used as temporary tablespace.
Q.Which dynamic view is most useful for determining the current number of blocks allocated to a buffer pool?
A. V$CACHE
B. V$SESS_IO
C. V$SYSSTAT
D. V$BUFFER_POOL
Answer: D
Explanation:
The V$BYUFFER_POOL dynamic performance view contains information about the configuration of the multiple Buffer Pools themselves. You can monitor the performance of the Buffer Pools using the V$BUFFER_POOL and V$BUFFER_POOL_STATISTICS dynamic performance views.
A: V$CACHE dynamic view is an Oracle9i Real Application Clusters view. This view contains information from the block header of each block in the SGA of the current instance as related to particular database objects.
B: This view lists I/O statistics for each user session.
C: Sort activity can be monitored using the V$SYSSTAT and V$SORT_SEGMENT dynamic data dictionary views, using the output from STATPACK and REPORT.TXT, and using the output from the OEM Performance Manager.
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.
Answer: A, B, C
Explanation:
Unless you specify otherwise in the init.ora, only the Default Pool is created at instance startup. But Oracle provides you also with the ability to divide the Database Buffer Cache into as many as three separate areas called Buffer Pools: Keep Pool (optional), Recycle Pool (optional) and Default Pool (mandatory). There are at least 50 blocks per LRU latch for each pool. Also each buffer pool is assigned latches taken from DB_BLOCK_LRU_LATCHES.
D: Default Pool is used to cache segments that are not designated for either the Keep or Recycle pools. The size of this pool is designated in bytes, kilobytes, megabytes, or gigabytes, by the init.ora parameter DB_CACHE_SIZE. Unlike Oracle8i, where the memory for the Keep and Recycle pools was taken from
the memory allocated to the Default Pool, Oracle9i independently assigns the memory to each of the three Buffer Pool types.
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.
Answer: B,F
Explanation:
You can avoid a sorting operation by using NOSORT clause when creating table or by creating appropriate indexes on tables that are joined often.
A: The UNION type SQL statement will cause sort operation.
C: You cannot avoid sorting by using NOSORT when creating indexes.
D: By using COMPUTE instead of ESTIMATE when analyzing objects you will process ALL records in the table. It will cause sorting also.
E: There is no sort privilege in Oracle. All users can sort data in Oracle tables.
Q.What will this statement do?CREATE TABLESPACE temp
DATAFILE ‘C:\database\temp.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.
Answer: C
Explanation:
This command creates a tablespace in which Oracle can create segments for usage during sorts.
A: You cannot drop the default temporary tablespace until another has been created because doing so would leave the database with nowhere to perform to-disk sorts. Unlike a sort segment stored in a permanent tablespace, the sort segment in the temporary tablespace is not dropped when the user’s sort completes.Instead, the first sort operation following instance startup creates a sort segment that remains in the temporary tablespace for reuse by subsequent users who also perform sorts to disk.This sort segment will remain in the temporary tablespace until instance shutdown. So only a sort segment will be dropped,not entire tablespace on instance shutdown.
B: User itself cannot create any objects in the temporary tablespace: it is used exclusively by Oracle.
D: User itself cannot create any objects in the temporary tablespace: it is used exclusively by Oracle.
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.
Answer: A
Explanation:
You need to assign a batch jobs modifying many rows to specific large rollback segment using SET TRANSACTION command.
B: Long running serializable transactions do not require large rollback segments.
C: Reports do not modify any table records. So they do not need specific rollback segment.
D: Discrete transactions that modify many rows in the same block will not cause rollback segment contention.
Q.What is the least number of buffers an LRU latch must cover in the database buffer cache?
A. 5
B. 10
C. 30
D. 50
E. 100
Answer: D
Explanation:
There are at least 50 blocks per LRU latch for each pool.
A: There are at least 50, not 5, blocks per LRU latch for each pool.
B: There are at least 50, not 10, blocks per LRU latch for each pool.
C: There are at least 50, not 30, blocks per LRU latch for each pool.
E: There are at least 50, not 100, blocks per LRU latch for each pool.
Continue.....
