NEW QUESTION 1
Which two statements are true about ADDM or Real-Time ADDM? (Choose two.)

• A. ADDM can be run manually by selecting any range of AWR snapshots available within the AWR retention period, provided they do not cover a time period when the instances were restarted.
• B. ADDM runs in Partial mode to analyze any hung database issues.
• C. Real-Time ADDM can proactively detect and diagnose transient performance issues that last for a few seconds.
• D. Real-Time ADDM is automatically invoked by ADDM at the end of every hour.

Answer: AC

NEW QUESTION 2
Examine an extract from a PGA Memory Advisory for your database:

Which two inferences are correct? (Choose two.)

• A. Automatic management of PGA memory is disabled.
• B. The current PGA size requires the use of a temporary tablespace for sorting operations.
• C. The current PGA size is sufficient and does not require the memory manager to allocate more memory.
• D. PGA size should be increased at least four times its current size for significant improvement in performance and disk space management.

Answer: BD

NEW QUESTION 3
Examine the initialization parameters set for a database instance:

The database supports an OLTP workload. Applications connect to the instance using shared server connections and perform small, random I/Os. All the data files are on the same disk. You notice free buffer wait events for sessions in the database instance.
To solve the problem, you increase the size of the buffer cache. But after some time, you notice sessions waiting again on free buffer waits.
What will you recommend to alleviate the issue? (Choose the best answer.)

• A. Run the I/O calibration tool.
• B. Configure the database instance to make asynchronous I/O available to DBWR.
• C. Spread the data files over multiple disks, controllers, and I/O buses to ensure that there are no hotspots in the I/O subsystem.
• D. Configure dedicated server connections for the applications.

Answer: B

NEW QUESTION 4
Which two result in the latest fragmentation within segments and the least external fragmentation within tablespaces? (Choose two.)

• A. automatic segment space managed segments
• B. locally managed tablespaces with uniform extent size
• C. freelist managed segments with one freelist
• D. dictionary managed tablespace
• E. locally managed tablespaces that were converted from dictionary managed tablespaces
• F. freelist managed segments with multiple freelist

Answer: AB

Explanation: Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14220/logical.htm

NEW QUESTION 5
Which three actions should you perform to reduce shared pool fragmentation and avoid the “ORA-04031: unable to allocate .... bytes of shared memory” error for the shared pool? (Choose three.)

• A. Configure the Server Result Cache.
• B. Configure shared server mode.
• C. Identify the packages or procedures that are causing the “ORA-04031: unable to allocate .... bytes of shared memory” error and use the DBMS_SHARED_POOL.KEEP to keep them in the shared pool.
• D. Use DBMS_SHARED_POOL.KEEP to keep the SYS.STANDARD,SYS.DBMS_STANDARD, and SYS.DIUTIL packages and frequently executed compiled triggers.
• E. Use more anonymous procedures.
• F. Standardize the type, size, and naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.

Answer: BCD

NEW QUESTION 6
Users complain about increased response time for queries in your production database that supports an OLTP workload. On investigation, you notice a large number of db file scattered read, latch: cache buffers lru chain, and latch: cache buffers chains wait events:
Identify three possible reasons for the increased response time. (Choose three.)

• A. too many sort operations being performed
• B. repeated simultaneous access to a block or small number of blocks
• C. the shared pool is inadequately sized
• D. queries not using indexes and performing full table scans
• E. queries repeatedly fetching blocks that are not in the database buffer cache
• F. cursors are closed explicitly after each execution

Answer: BDE

NEW QUESTION 7
Your database supports an online transaction processing (OLTP) workload. The database uses ASM storage. One of the ASM disks goes offline because of hardware failure. When the disk is replaced and then added back to the diskgroup, database performance is affected by rebalance operations.
Which two actions would you recommend to lower the impact of rebalance operations on the performance of the database? (Choose two.)

• A. Increase the number of ASMB processes.
• B. Decrease the value of the ASM_POWER_LIMIT parameter.
• C. Set the DISK_REPAIR_TIME disk attribute to a lower value.
• D. Specify the POWER clause with a lower value in an ALTER DISKGROUP statement.
• E. Set the DISK_REPAIR_TIME disk attribute to a higher value.

Answer: BD

NEW QUESTION 8
In the CUSTOMERS table, the values in the CUST_STATE column are dependent on the values in the COUNTRY_ID column. You want to make the optimizer aware of this dependency when these columns are used together in WHERE clause predicates that contain equalities or in-lists.
Which two methods achieve this? (Choose two.)

• A. gathering statistics on the CUSTOMERS table and its dependent objects, and then locking the statistics
• B. using SQL plan directives to generate an optimal plan
• C. setting the dynamic statistics level to 4 and setting the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to true
• D. creating column group statistics, regathering statistics, and ensuring that histograms exist on both these columns

Answer: AD

NEW QUESTION 9
You are administering a database that supports an OLTP workload. Users complain about the degraded response time of a query. You want to gather new statistics for objects accessed by the query and test query performance with the new statistics without affecting other sessions connected to the instance.
The STALE_PERCENT statistic preference is set to a default value and the STATISTICS_LEVEL parameter is set to TYPICAL.
Which two actions would you take to accomplish the task? (Choose two.)

• A. Set the STALE_PERCENT statistic preference to a higher value than the default, and then gather statistics.
• B. Set the STATISTICS_LEVEL parameter to ALL for the instance.
• C. Set the INCREMENTAL preference to TRUE, and then gather statistics.
• D. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE for the session in which you want to test the query.
• E. Set the PUBLISH statistic preference to FALSE, and then gather statistics.
• F. Set the NO_INVALIDATE statistic preference to TRUE, and then gather statistics.

Answer: BE

NEW QUESTION 10
Examine this list of possible tasks:
1. Ensure that STATISTICS_LEVEL is set to TYPICAL or ALL.
2. Ensure that TIMED_STATISTICS is set to TRUE.
3. Set MAX_DUMP_FILE_SIZE to UNLIMITED and DIAGNOSTIC_DEST to an appropriate destination.
4. Ensure that SQL_TRACE is set to TRUE.
5. Enable tracing at the database instance level by using the
DBMS_MONITOR.DATABASE_TRACE_ENABLE procedure.
6. Enable tracing in the required session by using the DBMS_SESSION.SET_SQL_TRACE procedure.
7. Run TKPROF with the EXPLAIN parameter on the output trace file.
8. Run the trcsess utility on the output trace files, and then run TKPROF on the output of the trcsess utility.
Select the minimum tasks to perform, in the correct order, to generate both a formatted trace file with timing information and an explain plan for each SQL statement for all sessions. (Choose the best answer.)

• A. 1, 2, 5, 8
• B. 1, 3, 6, 7
• C. 2, 4, 5, 8
• D. 1, 3, 4, 5, 6, 7
• E. 1, 2, 4, 8

Answer: C

NEW QUESTION 11
Your database supports a mixed workload. The ERP application creates short sessions and performs small, random I/Os; the REPORTING application executes long-running DSS queries.
You want to set a priority for the workload generated by the ERP application and optimize resource usage for them.
Which three objectives can be achieved by the Resource Manager? (Choose three.)

• A. limiting the amount of time that a session is idle and blocking other sessions of the ERP application
• B. limiting the amount of undo generated by operations performed by sessions created by the ERP application
• C. creating two resource plans with resource limits defined for the workload generated by the applications and automatically changing resource plans based on the workload
• D. allocating a lower percentage of CPU to sessions used by the REPORTING application than to those used by the ERP application
• E. limiting the physical I/O performed by the sessions or users of the ERP application that are connected to the database

Answer: BDE

NEW QUESTION 12
Which two statements are true about viewing the details of Real-Time Database Operations? (Choose two.)

• A. In V$SQL_MONITOR monitoring, statistics are cumulative over several executions of the SQL statement that is being monitored in a session.
• B. SQL Developer can be used to view running database operations.
• C. Oracle Enterprise Manager Database Express can be used to view running database operations.
• D. When the SQL statement that is being monitored is executing, V$SQL_MONITOR is refreshed once every minut
• E. After the execution ends, the monitoring information in V$SQL_MONITOR is deleted immediately.
• F. Oracle Enterprise Manager Cloud Control can be used to view running database operations.

Answer: AD

NEW QUESTION 13
Your database supports an OLTP workload during the day and batch processing at night. You want to monitor performance metrics to detect any degradation of performance in both types of workloads over a time period of 30 days.
Examine this list of possible steps:
1. Create a fixed baseline.
2. Create a baseline template.
3. Create a new moving window baseline.
4. Increase the retention period default value to 30 days.
5. Increase the size of the existing moving window baseline to 30 days.
6. Create warning and critical alerts for the relevant metrics.
7. Enable adaptive thresholds to detect the workload patterns and specify a high- significance-level threshold type.
8. Enable adaptive thresholds to detect the workload patterns and set different threshold values as a percentage of the maximum value.
Which option represents the required steps in the correct order? (Choose the best answer.)

• A. 5, 7
• B. 2, 4, 3
• C. 3, 4, 8
• D. 4, 5, 7
• E. 5, 1, 6, 8

Answer: E

NEW QUESTION 14
Your database supports an OLTP system.
Examine the parameter values configured in your database:
sga_max_size = 480M sga_target = 480M pga_aggregate_target = 160M
The CUSTOMERS table contains 8,000 rows. The CUST_ID column is the primary key and the COUNTRY_ID column contains only three possible values: 1111, 2222, and 3333.
You execute the commands:
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(‘SH’,‘CUSTOMERS’);
PL/SQL procedure successfully completed.
SQL> CREATE INDEX COUNTRY_IDX ON CUSTOMERS (COUNTRY_ID);
Index created.
You then perform a series of INSERT, UPDATE, and DELETE operations on the table. View the Exhibit to examine the query and its execution plan.

Which two options can improve the performance of the query without significantly slowing down the DML operations? (Choose two.)

• A. creating a bitmap index on the COUNTRY_ID column
• B. regathering statistics on the CUSTOMERS table
• C. gathering statistics on the COUNTRY_IDX index
• D. creating a histogram on the COUNTRY_ID column
• E. increasing the size of the PGA
• F. creating an SQL profile
• G. creating a KEEP cache

Answer: AD

NEW QUESTION 15
Examine the query and its output:

Which two inferences can be definitely derived from this output? (Choose two.)

• A. The db file scattered read event has occurred 104 times in this session for file# 12.
• B. The session has completed performing a full table scan.
• C. The SQL statements in this session are performing excessive disk reads.
• D. The multiblock factor is 8 for this I/O but it could vary for the other I/O events.

Answer: AC

NEW QUESTION 16
Which two statements are true about gathering statistics? (Choose two.)

• A. If an application has only SQL statements with bind variables, it is better to drop existing histograms, disable creation of histograms, and allow the optimizer to select the best execution plans.
• B. If end users query newly inserted data, it is possible to get a suboptimal execution plan even if the automatic statistics gathering job is enabled.
• C. If concurrent statistics gathering is done by using parallel execution, the Resource Manager should be used for efficient resource management.
• D. For each session that is accessing a global temporary table, the optimizer uses only theshared statistics.

Answer: AB