Q. Which of these is not an advantage of Oracle partitioning over manual striping? (Choose two.)
A. Each partition can be assigned to its own tablespace and datafiles.
B. Extent allocation is manual with the Oracle Partition option.
C. The cost-based optimizer can choose better-performing execution plans by pruning partitions that aren’t needed by the query.
D. RAID-5 isn’t compatible with manual striping, but it is compatible with partitioning.
E. The administration of manual striping is labor-intensive.
Answer: B, D
Extent allocation and RAID-5 are not advantages of using the Oracle Partitioning option. Extent allocation is automatic with the Oracle Partitioning option, and the extents will end up in the partition’s tablespace. You do not need to manually allocate an extent. RAID-5 is compatible with both manual striping and the Oracle Partitioning option. Other choices are advantages of partitioning over manual striping.
Q . Which of these SQL statements cause a sort operation? (Choose two.)
A. CREATE TABLE
B. GROUP BY
D. UNION ALL
E. SORT BY
Answer: B, C
The GROUP BY clause explicitly directs Oracle to aggregate the result set, which requires the atomic data to be sorted. The UNION operator requires a sort because duplicate values are eliminated. CREATE TABLE is incorrect choice – the exception would be the CREATE TABLE … AS SELECT when a primary key is specified. UNION ALL does not eliminate duplicates. SORT BY is not a valid SQL statement clause.
Q . You discover contention for the cache buffers LRU chain latch. Which of the following describe a possible solution? (Choose two.)
A. Increase the number of LRU latches on the buffer cache by increasing the LRU_CHAIN_LATCHES parameter value.
B. Decrease the number of LRU latches on the buffer cache.
C. Tune the application SQL to reduce large index range scans and full table scans.
D. Increase the number of DBWn processes.
E. Increase the number of shared server processes.
F. Increase the number of ARCn processes.
Answer: C, D
Contention on the cache buffers LRU chain latch indicates that the buffer cache is very busy and has too much throughput. Investigate user SQL for full-table scans or inefficient index range scans. Also, consider tuning the DBWn processes and buffer cache settings. LRU_CHAIN_LATCHES is not a valid initialization parameter. Also we cannot directly control the number of cache buffers LRU chain latches. Increasing the number of shared server or ARCs processes are not relevant answers.
Q . You’re operating an OLTP database in manual undo management mode and notice that the query SELECT COUNT(*) from V$TRANSACTION is always less than or equal to 160 during peak transaction hours. How many rollback segments should you configure?
The general guideline is to configure the maximum number of concurrent transactions divided by 4. For this case, it’s 40, so we should configure 40 rollback segments. Based on the guideline and the query, each of the other answers is an incorrect value.
Q . You determine that a long-running transaction has used an unnecessarily high-level user-defined lock and is causing contention. Which of these courses of action will eliminate the contention problem without disrupting the transaction?
A. Execute DBMS_LOCK.allocate_unique()
B. Execute DBMS_LOCK.release()
C. Execute DBMS_LOCK.convert()
D. Execute DBMS_LOCK.create()
Q. You noticed that the high watermark for shared server process is continuously at the MAX_SHARED_SERVER limit. Choose a solution to optimize shared server performance:
A. Double the value for SHARED_SERVERS and MAX_SHARED_SERVERS.
B. Increase MAX_SHARED_SERVERS by a few, and check instance performance.
C. Increase SHARED_SERVERS by a few, and check instance performance.
One of the reasons we move to shared servers is to better utilize system resources. If you increase MAX_SHARED_SERVERS by too much, you may cause swapping or CPU starvation. If you increase SHARED_SERVERS to much, you may cause the same problems; remember, Oracle does not drop shared server processes below the number of SHARED_SERVERS. Increasing SHARED_SERVERS alone will not help if the shared server high watermark is the problem.
Q . In a DSS application, the sales summary report queries data from seven different tables and groups the data by region and period. Which one of the following storage structures is a good design choice to improve query performance?
C. Materialized view
Materialized views are designed for the type of situation described in the question. A materialized view could be put in place to speed the queries that would have accessed multiple tables to produce summary information. A cluster would probably not be good structure because the described data is probably volatile and is probably not always queried together. An IOT is not a good way to store data from several tables; however, the materialized view could be stored as an IOT instead of a heap table.
Q . You are building a large fact table for a data warehouse where there will be no updates or deletes. Which of the following settings for PCTFREE and PCTUSED makes the best performance combination?
A. 0, 20
B. 90, 10
C. 10, 95
D. 0, 40
E. 20, 60
F. 100, 0
A large fact table in a data warehouse is usually a table that is not updated. It is usually loaded, and then queried with full-table scans. Setting PCTFREE as slow as possible makes best sense, because there will be no updates into the table, and the table will be read with full-table scans. Combinations PCTFREE=10 and PCTUSED=95 or PCTFREE=100 and PCTUSED=0 are not possible. By setting PCTFREE to 90 and PCTUSED to 10 you will have result opposite of what we would expect, and dangerous because we would keep moving the block on and off the freelist with each inserted or deleted row. PCTFREE=0 and PCTUSED=40 is a acceptable answer, but there’s really no need to set PCTUSED to anything since we won’t drop below that threshold. PCTFREE=20 and PCTUSED=60 is a compromise – but not optimal.
Q . Which of the following reasons to gather statistics with the DBMS_STATS package instead of the ANALYZE command? (Choose two.)
A. The ANALYZE command doesn’t support HISTOGRAMS.
B. DBMS_STATS enables gathering statistics in parallel.
C. The ANALYZE command does not compute statistics on indexes.
D. DBMS_STATS enables block-level sampling.
E. ANALYZE does not enable row-level sampling.
Answer: B, D
There are two advantages that DBMS_STATS has over the ANALYZE command. All other answers are incorrect because each is not true about the ANALYZE command; the ANALYZE command does create histograms, it does compute statistics on indexes, when specified, and it does enable row-level sampling. Although each of these is true, they are also available with DBMS_STATS.
Q . Before a resource plan can become the active top-level plan, which of the following rules must be validated? (Choose all that apply.)
A. All referenced subplans and consumer groups must exist.
B. The sum of percentage for resources that use the emphasis method cannot exceede 100 at any given priority level.
C. The plan must have a directive for OTHER_GROUPS.
D. All plans must contain at least one subplan.
E. A plan has a maximum of 32 consumer groups and 32 subplans.
Answer: A, B, C, E
Each of the rules specified must be met for a plan to be validated. All referenced subplans and consumer groups must exist. CPU percentage for resources in the emphasis method cannot exceed 100 percent at any level, and there must be a directive for OTHER_GROUPS in the plan. There is a maximum of 32 consumer groups allowed in a plan, and a maximum of 32 subplans in a plan. Also no plan is required to have a subplan.
Q . The purpose of a checkpoint is to? (Choose two.)
A. Write the current redo log to the archived redo log.
B. Synchronize the modified data blocks in memory with the datafiles on disk.
C. Ensure database consistency.
D. Ensure that data blocks are read into memory as quickly as possible.
E. Keep track of redo entries.
Answer: B, C
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 . To reduce unnecessary sorting, you can employ which of the following SQL techniques? (Choose two.)
A. Use the UNION operator instead of UNION ALL.
B. Use DISTINCT when selecting the primary key.
C. Don’t use DISTINCT when selecting the primary key.
D. Use the UNION ALL operator instead of UNION.
E. Use the NOSORT option when building indexes on unsorted table data.
Answer: C, D
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 . Oracle uses latches for what purpose?
A. To queue access to database objects such as tables
B. To protect access to memory structures
C. To keep memory doors closed
D. To speed access to database tables
E. To allow two or more process to access and modify the same structures at the same time
Latches are one of the methods used to protect Oracle structures; locks are the other one. Latches have no queueing feature; they do not speed performance, nor do they allow simultaneous or shared access. Latches are exclusive and held briefly.
Q . Which of these views would be used to diagnose tablespace performance problems?
The V$FILESTAT view contains accumulated data about the number of physical reads and writes, blocks read and written, and the total read and write time. These statistics can be used to determine hot spots or disks that are much more active than others, and to evaluate the average read and write time for the files in a tablespace. The information can be used to determine if files need to be moved to different disk drives.
Q . Which of the following will reduce the number of disk sorts?
A. Increasing SORT_AREA_SIZE
B. Decreasing SORT_AREA_RETAINED_SIZE
C. Increasing the SORT_MEMORY_ALLOCATED parameter
D. Altering SORT_AREA_SIZE dynamically, based on available memory within SGA_MAX_SIZE
The value of SORT_AREA_SIZE determines the amount of memory that can be used for sorting operations; a lower value increase the likelihood that sorting will overflow the area and require disk extents. Increasing the size of the sort area will reduce free memory available to the remaining processes in the database server, but will also reduce the likelihood that sort operations will require disk space allocated from the temporary tablespace. SORT_AREA_SIZE, if too large, can contribute to paging and swapping.
Q . The DBA can modify the default locking mechanism by setting which init.ora parameter?
The DBA can modify the default locking mechanism by setting the ROW_LOCKING initialization parameter. The default value is ALWAYS; this causes default locking at the row level during DML statements. This is the least restrictive. You can also specify DEFAULT, which is synonymous with ALWAYS. Setting the value to INTENT causes default locking at the table level, except for SELECT … FOR UPDATE statements, which cause row-level locking.
Q . What is PGA_AGGREGATE_TARGET?
A.Initialization parameter for sort-based operators (for example, ORDER BY, GROUP BY, ROLLUP, window functions)
B. Initialization parameter for hash-join operations
C.Initialization parameter for bitmap merge and bitmap create operations
D.Initialization parameter to enable automatic PGA memory management
E. Initialization parameter to enable automatic PGA memory management if WORKAREA_SIZE_POLICY is set to MANUAL
Prior to Oracle9i, the maximum size of these working areas was controlled using the SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE parameters. Setting these parameters is difficult, because the maximum work area size is ideally selected based on the data input size and the total number of work areas active in the system. These two factors vary a lot from one work area to another and from one point in time to another. Thus, the various *_AREA_SIZE parameters are hard to tune under the best of circumstances. With Oracle9i, you can simplify and improve the way PGA memory is allocated, by enabling automatic PGA memory management. In this mode, Oracle dynamically adjusts the size of the portion of the PGA memory dedicated to work areas, based on an overall PGA memory target explicitly set by the DBA. To enable automatic PGA memory management, you have to set the initialization parameter PGA_AGGREGATE_TARGET, as described in the following section.
Q . What should you do to import data and to avoid migration? (Choose two)
A. Use the CASCADE option during import
B. Use the COMPRESS option during export
C. Use the RECORDLENGTH option during export
D. Coalesce free space
E. Add a new datafile
Answer: D, E
To avoid data migration during import you need coalesce free space or add a new datafile to tablespace. There is no CASCADE option for export/import utilities. Specifies the length, in bytes, of the file record. The RECORDLENGTH parameter is necessary when you must transfer the export file to another operating system that uses a different default value. The COMPRESS option specifies how Export and Import manage the initial extent for table data.
Q . After new version of OLTP applications implementation for testing several QA users have called to state that response time for applications is now very low.You find the following:- The hit ratio in the buffer cache has fallen from its normal level of 88 to 90 percent and is only averaging 75 percent when you look at several different the reports from STATSPACK.- The GETHITRATIO for the library cache is 98%- The disk sorts are rare and there is nothing suspicious in the datafiles read/writes section from STATSPACK- Only one SQL query, specific for only one application, shows a huge amount of physical reads.What should you do?
A. Increase SORT_AREA_SIZE initialization parameter.
B. Set WORKAREA_SIZE_POLICY parameter to AUTO and increase
PGA_AGGREGATE_TARGET initialization parameter.
C. Investigate, why there are so many physical reads for this application.
D. Run DBMS_STATS package to compute tables/indexes statistics.
Since only one specific query shows extensive physical reads and all other wait events are normal you need first to tune this query. After that different SQL statement will be on the top of all time-consuming statements, which will require tuning also. Memory, disks I/O tuning should be considered only after SQL statements response time improvement. DBMS_STATS package can improve application response time, but it does not fix the core of the problem.
Q . STATSPACK report shows a buffer busy waits. Detailed analysis shows that it’s related with freelists contention. You need to fix this issue and add freelists.
A. Search for a data block, causing contention and issue the command ALTER TABLE … FREELIST.
B. Search for a data block, causing contention and issue the command ALTER TABLESPACE … FREELIST.
C. Search for a segment header, causing contention and issue the command ALTER TABLE … FREELIST.
D. Search for a segment header, causing contention and issue the command ALTER TABLESPACE … FREELIST.
Buffer busy wait event indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently. Query V$WAITSTAT for the wait statistics for each class of buffer. Common buffer classes that have buffer busy waits include data block, segment header, undo header, and undo block. If the contention is on the segment header, then this is most likely freelist contention. A freelist is a list of free data blocks that usually includes blocks existing in a number of different extents within the segment. Blocks in freelists contain free space greater than PCTFREE. This is the percentage of a block to be reserved for updates to existing rows. In general, blocks included in process freelists for a database object must satisfy the PCTFREE and PCTUSED constraints. Specify the number of process freelists with the FREELISTS parameter. The default value of FREELISTS is one. The maximum value depends on the data block size. FREELISTS specifies the number of free lists for each of the free list groups for the schema object. Not valid for tablespaces.