Q. From the following, choose the correct sequence of events to configure Resource Manager:
A. Create a pending area, create a plan, create plan directives, and create consumer groups.
B. Create a pending area, create consumer groups, create plan directives, and create a plan.
C. Create a pending area, create a plan, create consumer groups, and create plan directives.
Answer: C
This specifies the correct sequence of events. We did not include validate and submit steps, not did we assign users or roles to consumer groups. However, the steps to create a pending area, create a plan, create consumer groups, and create plan directives are in correct order. Other answers are both incorrect because the sequence of events is incorrect.
Q . The system alert log is written to which directory specified in the init.ora file?
A. USER_DUMP_DEST
B. BACKGROUND_DUMP_DEST
C. CORE_DUMP_DEST
D. ALERT_DUMP_DEST
Answer: B
Specified in the init.ora file, and also dynamically configurable, the BACKGROUNCD_DUMP_DEST specifies the location for dump files from server background processes, and also the alert log. The USER_DUMP_DEST is used for user process trace output, and the CORE_DUMP_DEST directory is used for core dumps.The ALERT_DUMP_DEST is not valid initialization parameter.
Q. The STATSPACK report is similar in nature to which of the following?
A. OEM Oracle Expert
B. OEM Tuning Template
C. SQL Trace and TKPROF
D. UTLSTAT report.txt
Answer: D
The Statspack report is meant to replace UTLBSTAT/UTLESTAT and the resulting report.txt. Statspack enables the retention of multiple snapshots of performance statistics in the database, so you can generate a Statspack report to calculate the difference between parameters taken at two snapshots.
Q . The dictionary cache hit ratio measures the ratio of which two statistics from V$ROWCACHE? (Choose two.)
A. PINS
B. RELOADS
C. GETS
D. GETMISSES
E. KEEPS
Answer: C, D
The actual formula is (sum(gets) – sum(getmisses)) / sum(gets). You should try to keep this ratio in the high 80’s or better. The dictionary cache hit ratio is an indicator of how often the correct data dictionary information is found in memory when needed. GETS indicates the number of data dictionary requests; GETMISSES indicates the number of requests that were not met by reading the cache and required an I/O.
Q . In the shared server environment, the UGA components user session data and cursor state information are stored in which memory structures?
A. PGA
B. VBA
C. Shared pool
D. Buffer cache
E. Log buffer
Answer: C
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 . 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. SHARED_POOL_SIZE+BUFFER_CACHE_SIZE
D. DB_BLOCK_SIZE
Answer: B
The SGA_MAX_SIZE parameter indicates the maximum size that you can dynamically increase the SGA components up to. The buffer cache, redo buffers, the Java pool, large pool, shared pool, and reserved pool all must fit inside the SGA_MAX_SIZE limit.
Q . Freelist contention can be eliminated by using automatic segment-space management, which is designated when?
A. At the table create time
B. At the tablespace create time
C. At the database create time
D. At the query run time
Answer: B
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. Which of these environmental influences would lead you to increase the size of the java pool? (Choose two.)
A. Large client-side applications are written in Java.
B. The application uses large Java stored procedures.
C. Shared servers are used for the Java components.
D. Dedicated servers are used.
E. Application users are spread across multiple contents.
Answer: B, C
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. All other answers do not directly affect the Java pool requirements.
Q . Which of the following is not considered an important goal of the database configuration phase?
A. Tuning disk I/O
B. Tuning memory
C. Tuning application code
D. Tuning CPU utilization
Answer: C
At the database configuration phase of the application design process, we are mostly concerned with tuning memory, I/O, and CPU. The application code should have been already tuned in the development and test environments. There are usually user acceptance testing and post-implementation opportunities to tune the application SQL code.
Q . You noticed that all SQL statements that have a join condition on one particular table appear to be running very slowly. Which of these steps should you take first to help identify the performance problem?
A. Check to see if the database is running the CBO or RBO.
B. Check file I/O statistics for the data file and tablespace that the table is in.
C. Verify that there are no missing indexes from the table.
D. Verify that the table blocks are stored in the buffer cache.
Answer: C
The problem statement clearly identifies at least one possible problem; that is the missing index on the columns in the join condition. If all queries were experiencing performance problems, then we might first verify that there are recent statistics on tables if the CBO is used. If objects in a specific tablespace were seen as performance constrained, we would check the file I/O statistics for the files in the tablespace.
Q . Which dynamic performance view is the primary place to look for system performance and resource usage information?
A. V$SYSTEM_EVENT
B. V$PERFORMANCE
C. V$RESOURCE
D. V$SYSSTAT
E. V$SYSTEM_STATS
Answer: D
System performance and resource usage stats are kept in the V$SYSSTAT view. Hit ratios, cumulative logons, total CPU usage, parse counts, total physical reads and writes, and in-memory and to-disk sorts are just a few of vital statistics that are available in this view.Wait events are recorded in the V$SYSTEM_EVENT view; V$PERFORMANCE, V$RESOURCE, and V$SYSTEM_STATS are fictions.
Q . Which of these are true statements about tuning the dictionary cache? (Choose two.)
A. The algorithm that manages data in the shared pool prefers to keep library cache data over dictionary cache data.
B. The algorithm that manages data in the shared pool prefers to keep dictionary cache data over library cache data.
C. When using sequences, the CACHE option improves dictionary cache performance.
D. The DBA should perform DDL during peak activity hours.
Answer: B, C
It is important to remember that dictionary cache misses are more expensive than library cache misses, so the algorithm that manages the shared pool prefers to keep objects in the dictionary cache. If the library cache statistics look good, that means the library cache is sized appropriately and infers that the dictionary cache is sized appropriately. If you do not use the CACHE option on a sequence, each call to NEXTVAL creates a get in the dc_sequences dictionary cache item. Also, the DBA should perform DDL during off-peal hours.
Q. Which of these accurately describes how the UGA is configured?
A. In both shared server and dedicated server environments, the session stack space is kept in the SGA.
B. The sort areas and private SQL areas are included in the SGA in the dedicated server environment.
C. In both shared server and dedicated server environments, the session stack space is kept in the PGA.
D. The sort areas and private SQL areas are included in the PGA, regardless of the server configuration.
Answer: C
By definition, the session stack space is kept in the PGA, whether the process memory is assigned to a shared server or a dedicated server. The sort areas and private SQL areas are stored in the PGA for dedicated servers, and in the SGA for shared servers.
Q . The default buffer cache can be expanded dynamically if needed, but only in granule-size increments of? (Choose two.)
A. 2Mb if the SGA is less than 128Mb
B. 4Mb if the SGA is less than 128Mb
C. 8Mb if the SGA is less than 128Mb
D. 8Mb if the SGA is larger than 128Mb
E. 16Mb if the SGA is larger than 128Mb
Answer: B, E
The granule is defined as a unit of allocation in the dynamic SGA. When an SGA component (DB buffer cache, shared pool) is expanded or shrunk, it must be in granule increments. The minimum number of granules allocated is 1 for each of the SGA structures (buffer cache; shared pool, and fixed SGA). The maximum number of granules is determined by MAX_SGA_SIZE, which is measured in bytes.
Q . You want to allocate the memory structures in the shared pool so that you can begin collecting cache advice information at a later time. What is the correct setting for the DB_CACHE_ADVICE parameter?
A. FALSE
B. OFF
C. READY
D. ON
E. ALLOCATE
Answer: C
Setting DB_CACHE_ADVICE to READY allocates the memory structures in the shared pool to collect the statistics needed to populate the V$DB_CACHE_ADVICE view. The memory structures, and the view, are not populated with data until the DB_CACHE_ADVICE is set to ON, which signals the collection of buffer cache advice statistics. Setting the parameter to OFF stops collecting data and deallocates the shared pool memory structures.
Q . Which of these tuning efforts should occur first?
A. Tune the OS.
B. Tune database I/O.
C. Tune memory
D. Tune the network.
E. Tune the application.
F. Tune the design.
Answer: F
Tuning the design is the first step in the application development phase of the Performance Tuning Model. Tuning the application should follow this step during the coding phase. The remaining tuning opportunities are usually the DBA’s responsibility and occur later in the design and implementation process.
Q . It arrears that several user processes are at a standstill, that is, no throughput. However, other user processes seem to be working fine. Which dynamic performance view could you look at to ascertain the cause?
A. V$LIBRARYCACHE
B. V$LOCK
C. V$PARAMETER
D. V$INSTANCE
E. V$DB_OBJECT_CACHE
Answer: B
Of the choices, V$LOCK should be the first place to look if some processes appear to be frozen while other processes are working without problems. If you see locked objects and sessions waiting on the locked objects, take action. Answers A and E are incorrect because they specifically show the state of the library cache and the objects cached there. Although these are good performance views to look at, they don’t apply in this case. Answers C and D are incorrect; these views are informational only and won’t help you resolve the locking conflict described in the question.
Q . You have decided that a table is a good candidate for the RECYCLE pool. Which of these factors convinced you of that? (Choose two.)
A. The table is small and used often.
B. The table is greater than twice the size of the DEFAULT pool.
C. The table is large and seldom used.
D. The table is used often, but accessed solely with full table scans.
E. The table is already CACHED.
F. The table is less than 10 percent the size of the DEFAULT pool.
Answer: B, C
Recycle pool candidates have almost the exact opposite access pattern as KEEP pool candidates. A good candidate will be large and infrequently used. If the table is small and used often or the table is already CACHED it’s more like a KEEP object. If the table is used often, but accessed solely with full table scans or the table is less than 10 percent the size of the DEFAULT pool it is good candidate for cached objects.
Q . In the shared server environment, cursor state information resides in which pool? (Choose two.)
A. The reserved shared pool
B. The large pool if it is configured
C. The PGA
D. The shared pool if the large pool is not configured
E. The large pool if the shared pool is not configured
Answer: B, D
In the shared server environment, UGA cursor state information and user session data are stored in the large pool if it is configured; otherwise, it is stored in the shared pool. This configuration uses less overall memory than the dedicated server environment.
Q . Which of the following will not benefit from the NOLOGGING attribute? (Choice two.)
A. CREATE INDEX
B. Conventional path data loads
C. Direct path data loads when the database is in noarchivelog mode
D. CREATE TABLE … AS SELECT
E. Direct path data loads when the database is in archivelog mode
Answer: B, C
Conventional path data loads generate redo logging just like other Data Manipulation Language (DML) statement, so the NOLOGGING attribute has no effect. When the database is in noarchivelog mode, direct path data loads do not generate redo; therefore, the NOLOGGING attribute is irrelevant. To create index, create table as select from other table or to use direct path load with the database in archivelog mode you can benefit from the NOLOGGING attribute. CREATE INDEX and CREATE TABLE … AS SELECT are two SQL statements that utilize NOLOGGING to improve performance.
Continue.....