Q. In the shared server environment, the UGA components user session data and cursor state information are stored in which memory structures?
A*. Shared pool
B . Log buffer
C . VBA
D . PGA
E . Buffer cache
Explanation:
In the Shared Server environment, some of the UGA components are moved into the shared pool. These components reside in the PGA when dedicated servers are used. The session stack space remains in the PGA regardless of server configuration.
Q. RMAN will use which pools to cache buffers for input/output (I/O) slaves during backup and restore operations? (Choose two.)
A . The Program Global Area (PGA).
B*. The shared pool if the large pool is not configured.
C . The large pool if the shared pool is not configured.
D*. The large pool when it is configured.
E . The shared pool exclusively, in all cases.
Explanation:
RMAN will attempt to use the large pool, if it is present, to buffer slave process I/O. If the large pool is not present, RMAN will attempt to use the shared pool to buffer the I/O. If neither can be used, RMAN will use local process memory.
Q. Which of the following best describes a buffer cache miss?
A . When buffers are moved to the dirty list before they are written to disk.
B . An Oracle user process is searching for a free buffer and reaches the threshold limit.
C . Changed data that has not been written to disk.
D*. Data is not in the cache and the block of data must be read into the cache from a datafile on disk.
Explanation:
A buffer cache miss indicates that the data block required for an operation was not found in the buffer cache. The server process then has to perform an I/O to retrieve the block from a data file.
Q. The free buffer waits system wait event is an indicator of which type of problem?
A . There is a wait for the current copy of a data block in memory due to unselective indexes.
B . There are too many DBWn processes.
C*. The DBWn processes are not keeping up with the number of dirty buffers that must be written to disk.
D . System page faulting is taking place.
Explanation:
If there's a row in V$SYSTEM_EVENT where the EVENT column equals free buffer wait, this indicates that the DBWn processes aren't writing dirty blocks fast enough to make room for new blocks. It's possible that the I/O system isn't keeping up. Also, a high number may indicate that the buffer cache is too small.
Q. Which of these steps are taken by the server process as it searches for a free buffer in the buffer cache? (Choose two.)
A*. The server process signals DBWn to flush the dirty buffers from the buffer cache.
B . The buffer is moved up to the most recently used (MRU) MRU end of the least recently used (LRU)LRU list and a logical read occurs.
C . The server process signals DBWn to read dirty buffers from the data files.
D*. The server process moves dirty buffers to the dirty list as it searches the LRU list for a free buffer.
Explanation:
These two steps are part of the search operation when a server process is looking for a free buffer in the buffer cache. Dirty blocks are moved onto the dirty buffer list and then written to disk. The buffer is moved up to the most recently used (MRU) MRU end of the least recently used (LRU)LRU list and a logical read occurs indicates that the required block was found in the cache. The server process signals DBWn to read dirty buffers from the data files is incorrect; . DBWn doesn't read dirty buffers from disk; it writes them to disk.
Q. Dynamic SGA allocation enables the DBA to increase the size of the SGA components up to?
A . MAX_SGA_SIZE
B*. SGA_MAX_SIZE
C . DB_BLOCK_SIZE
D . SHARED_POOL_SIZE+BUFFER_CACHE_SIZE
Explanation:
By setting the DB_CACHE_ADVICE parameter to READY, the system allocates memory structures in the shared pool. Set it to ON, and the system begins collecting statistics that can be used to size the db DB buffer cache. The parameter is dynamically configurable. The V$DB_CACHE_ADVICE view will show you estimated physical read reductions if the DB_CACHE_SIZE parameter is increased, or read increases if the value is reduced.
Q. You're concerned that the buffer cache hit ratio is too low, and you believe that increasing the size of DB_CACHE_SIZE will improve performance.Which initialization parameter and dynamic performance view can you use to help determine the optimal DB_CACHE_SIZE? (Choose two.)
A*. DB_CACHE_ADVICE parameter
B*. V$DB_CACHE_ADVICE
C . DB_BUFFER_CACHE
D . V$CACHE_ADVICE
E . V$DB_CACHE
Explanation:
By setting the DB_CACHE_ADVICE parameter to READY, the system allocates memory structures in the shared pool. Set it to ON, and the system begins collecting statistics that can be used to size the DB buffer cache. The parameter is dynamically configurable. The V$DB_CACHE_ADVICE view will show you estimated physical read reductions if the DB_CACHE_SIZE parameter is increased, or read increases if the value is reduced.
Q. Which one of these statements is true about the buffer cache in Oracle9i?
A .You assign objects to the KEEP or RECYCLE buffer pool at startup time using the DB_CACHE_KEEP and DB_CACHE_RECYCLE initialization parameters.
B . You assign objects to the KEEP or RECYCLE buffer pools using the DBMS_BUFFER_POOL package.
C*. The KEEP and RECYCLE buffer pools are separately defined memory areas from the DEFAULT buffer pool.
D . The size of the KEEP and RECYCLE buffer pools are subtracted from the DEFAULT buffer pool.
Explanation:
In previous version of Oracle, the KEEP and RECYCLE were subtracted from the total DB buffer cache size. In Oracle9i, the KEEP, RECYCLE, and DEFAULT are all sized separately with the initialization parameters DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_ SIZE, and DB_CACHE_SIZE respectively.
Q. Freelist contention can be eliminated by using automatic segment-space management, which is designated when?
A . At the database create time.
B*. At the tablespace create time.
C . At the table create time.
D . At the query run time.
Explanation:
Automatic segment-space management is designated at the time the tablespace is created by issuing the EXTENT MANAGEMENT LOCAL clause in the CREATE TABLESPACE command.
Q. When a user makes changes to the database with INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP commands, the information required to reconstruct or redo the changes is written from the user's memory space into redo log buffer entries by which Oracle process?
A*. The server process
B . DBWn
C . PMON
D . LGWn
E . SMON
Explanation:
The server process is responsible for writing changes to the redo log buffer. LGWR reads the redo log buffer and writes to the redo log files. DBWn is responsible for writing dirty blocks from the DB buffer cache to the data files.
Q. Which of these environmental influences would lead you to increase the size of the java pool? (Choose two.)
A*. The application uses large Java stored procedures.
B . Large client-side applications are written in Java.
C . Application users are spread across multiple continents.
D . Dedicated servers are used.
E*. Shared servers are used for the Java components.
Explanation:
If the application uses large Java stored procedures, then a large java_pool setting will contribute to system performance. Also, if Java components are used in the database and the database uses shared servers, some of the Java process memory structures are kept in the Java pool. Large client-side applications are written in Java, Dedicated servers are used, and Application users are spread across multiple continents do not directly affect the Java pool requirements.
Q . Multiple DBW processes are preferred over DBWR I/O slaves because?
A . I/O slaves gather, batch, and write dirty buffers.
B*. DBWn processes gather, batch, and write dirty buffers independently.
C . I/O slaves are more efficient because they focus only on reads.
D . The DBW0 process is freed to gather dirty buffers when I/O slaves are used.
Explanation:
As opposed to I/O slaves, which only write the dirty buffers to data files, multiple DBWn processes perform their tasks in parallel with each other. If one DBW0 process is used with multiple I/O slaves, then the DBW0 process still must gather, batch, and then hand off the batch to an available I/O slave for writing.
Q. Which of the following are good techniques to reduce I/O contention?
A . Place all heavily used tables in the same tablespace.
B*. Separate data and indexes into different tablespaces on different physical devices.
C . Place REDO logs and ROLLBACK segment tablespaces on the same device.
D . Place all REDO logs on the same RAID-5 device.
Explanation:
This is a good practice because generally a table and its associated indexes are usually read in the same time frame by the same server process or processes. Also, if large amounts of DML are on a table, I/O contention can be a major performance problem if the data files and index files are stored on the same physical device.
Q. Which of these statements is true about assigning a default temporary tablespace to a user?
A . If you do not specify a TEMPORARY TABLESPACE when you create the database, the TEMPORARY tablespace will be defined as the temporary tablespace for each new user.
B . If you do not specify a DEFAULT TEMPORARY TABLESPACE when you create the database, the TEMP tablespace will be defined as the temporary tablespace for each new user.
C . If you do not specify a SYSTEM TEMPORARY TABLESPACE when you create the database, the SYSTEM tablespace will be defined as the temporary tablespace for each new user.
D*. If you do not specify a DEFAULT TEMPORARY TABLESPACE when you create the database, the SYSTEM tablespace will be defined as the temporary tablespace for each new user.
Explanation:
The good news is that if you do specify a DEFAULT TEMPORARY TABLESPACE when you create the database, you don't have to worry about applications building temporary segments in your SYSTEM tablespace, or assigning a TEMPORARY tablespace to each user that you create.
Q. The purpose of a checkpoint is to? (Choose two.)
A*. Ensure database consistency.
B . Ensure that data blocks are read into memory as quickly as possible.
C . Keep track of redo entries.
D*. Synchronize the modified data blocks in memory with the datafiles on disk.
E . Write the current redo log to the archived redo log.
Explanation:
Checkpoints are used to make sure that we have a consistent point to which we can recover in case of a system crash. To get there, we need to write the dirty blocks from the DB buffer cache to disk, and make sure that redo is available in the redo logs.
Q. Which of these factors would lead you to increase the value of LOG_CHECKPOINT_TIMEOUT? (Choose two.)
A . Checkpoints occur infrequently, lengthening the time to recover the database.
B . Checkpoints occur too frequently, lengthening the time to recover the database.
C*. Checkpoints occur too frequently, causing performance problems.
D . Checkpointing is not a performance problem, but you need to recover the database more quickly.
E*. Checkpointing is a performance problem, and database recovery time is not an issue.
Explanation:
Frequent checkpointing reduces the recovery window in case of a failure, but can also impact performance because of the I/O. If the frequency is too often, and fast recovery time is not as important as good performance while the system is functioning normally, then consider increasing the time between checkpoints.
Q. Which of these tuning approaches can be used to improve the performance of redo logging? (Choose two.)
A . Smaller redo logs improve performance by reducing checkpoint frequency.
B . Place redo logs on RAID-5 disks.
C*. Place redo logs on separate RAID-0 disks.
D*. Larger redo logs improve performance by reducing checkpoint frequency.
Explanation:
Since LGWR writes continuously and sequentially to the redo logs, and they're only read in case of recovery (or by LogMiner), it makes sense to put them on the fastest write devices available. RAID-0 provides the fastest write performance of the RAID options. Also, if checkpoint processes are a performance problem, consider reducing the frequency by increasing the size of the redo logs. There is a tradeoff, as longer times between checkpoints can increase the time required to recover.
Q. To reduce unnecessary sorting, you can employ which of the following SQL techniques? (Choose two.)
A*. Use the UNION ALL operator instead of UNION.
B . Use the UNION operator instead of UNION ALL.
C . Use DISTINCT when selecting the primary key.
D*. Don't use DISTINCT when selecting the primary key.
E . Use the NOSORT option when building indexes on unsorted table data.
Explanation:
There's no need to use the DISTINCT keyword if you know you're selecting on a primary key; the DISTINCT causes an unnecessary sort. Also, the UNION operator sorts the return set and removes duplicates; if you know there are no duplicates between the queries, then use UNION ALL instead.
Q. To monitor current session sort operations, you should join which two dynamic performance views?
A . V$SORT_SEGMENT and DBA_SEGMENTS
B . V$SORT_USAGE and V$PROCESS
C . V$SORT and V$PROCESS
D . V$SORT_USAGE and V$SQL
E*. V$SORT_USAGE and V$SESSION
Explanation:
The V$SORT_USAGE view describes user sorting activities, the extents allocated, and the type of segment created. Join the SESSION_ADDR column with the ADDR column of V$SESSION to gather session information.
Q. Which of the following does not require a sort?
A . Select using the DISTINCT keyword
B . Truncating a table
C . Building an index
D*. No Answer is Correct
E . Analyzing a table
Explanation:
Truncating a table does generate a sort. Each of the others, analyzing a table, building an index, using the DISTINCT keyword, and performing a sort-merge join, requires at least one sort operation.
Continue.....