Which two actions can reduce library cache latch contention for an OLTP application that repeatedly executes queries containing a mix of literals and bind variables? (Choose two.)

A. setting the OPEN_CURSORS parameter to hold a sufficient number of concurrently open cursors

B. coding the application such that a frequently executed statement is parsed only once and executed repeatedly as required

C. setting the CURSOR_SHARING parameter to EXACT

D. avoiding the granting of privileges on objects associated with cursors during peak load

E. enabling Automatic Memory Management and allocating at least 50% of the available memory for SHARED_POOL_SIZE

F. configuring shared server connections

Examine the output of the query executed to diagnose the reason for performance degradation of queries:

Which three factors will you investigate further to identify the cause of the performance degradation? (Choose three.)

A. Check the number of disk sorts.

B. Check for the causes of the full table scans.

C. Check the number of chained or migrated rows.

D. Check the indexes on the tables used in queries for clustering factor.

E. Check the size of the temporary tablespace for sorting operations.

Which three statements are true about using Real-Time Database Operations? (Choose three.)

A. The STATISTICS_LEVEL initialization parameter must be set to ALL to enable automatic SQL monitoring for all long-running queries.

B. The CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter must be set to DIAGNOSTIC+TUNUNG to use Real-Time Database Operations.

C. The STATISTICS_LEVEL initialization parameter can be set to TYPICAL or ALL to enable Real-Time Database Operations.

D. Real-Time Database Operations can be enabled only at the system level.

E. Real-Time Database Operations can be created by using the DBMS_MONITOR or DBMS_SESSION packages.

F. Database operation monitoring starts automatically when a database operation consumes at least five seconds of the CPU or I/O time in a single execution.

For which two requirements can you always use the V$ACTIVE_SESSION_HISTORY view? (Choose two.)

A. to investigate intermittent performance problems in a session, only when the problem lasted less than five minutes in the last twelve hours

B. to find the exact number of executions of a specific query in a session in the last two minutes

C. to identify which module in an application was being executed in a session

D. to identify a scheduler job that is not responding

E. to find the amount of Program Global Architecture (PGA) memory being currently consumed by a session

You define the warning threshold for the tablespace usage metric for the USERS tablespace to be 60% and the critical threshold to be 80%.

Which two sources should you check for the alert information when either the warning or the critical threshold is exceeded? (Choose two.)

A. the alert log

B. Oracle Enterprise Manager Cloud Control

C. DBA_ALERT_HISTORY

D. DBA_OUTSTANDING_ALERTS

E. DBA_ACTIVE_SESSION_HISTORY

F. DBA_THRESHOLDS

You are administering a database that supports a mixed workload. Many applications are running on the middle tier that use connection pools to connect to the database instance. Application users perform OLTP operations during the day and another application performs batch job operations at night. You want to measure and prioritize the two workloads.

Which action would you take to achieve this? (Choose the best answer.)

A. Create database services for the applications, assign individual sessions created by the applications to consumer groups, and then set a priority.

B. Assign profiles to users running the batch operations and make sure that a priority is set for resource limits in profiles.

C. Create database services for the applications and assign different profiles to the sessions to set a relative priority for resource usage.

D. Create database services for the applications, create a job class associated with the service, batch the jobs, and then create jobs by using the job class.

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

You are administering a database that supports a mixed workload. You upgrade your database from Oracle Database 11g to 12c and after the upgrade, users complain about degraded performance of some queries. The SQL plan baselines imported from the previous version are present for the queries and are loaded to the SQL Management Base as accepted plans. On further investigation, you find that better plans are generated but not used by the optimizer.

Examine the parameters set for the instance: Which three tasks would you perform to improve the performance of these queries? (Choose three.)

A. Gather statistics for the objects used in the queries.

B. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve new plans and fix the plans for the statements.

C. Create a SQL Tuning Set (STS) and run it through the SQL Access Advisor to generate recommendations.

D. Create an STS and run it through the SQL Tuning Advisor to generate recommendations.

E. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE.

F. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to alter the accepted plans as fixed plans.

For which two tasks would you recommend the use of a repeating baseline template?

A. to capture AWR data during a system test that is scheduled for a weekend

B. to capture AWR data for a scheduled job that runs every day over a specified period of time

C. to capture user activity information for a consumer group over a specified period of time

D. to capture AWR data for a specified contiguous time period on an ongoing basis

E. to capture statistics performance variation over a specified period of time

Which four statements are true about Consolidated Database Replay?

A. Multiple replay schedules can be defined and any existing replay schedules can also be used.

B. Multiple database workloads can be replayed only if both the capture and replay databases are on the same operating system.

C. The replay starts simultaneously for all participating workload captures if no order is defined in the replay schedule.

D. Consolidated workloads from one or multiple databases can be replayed concurrently on a single system.

E. A subset of the captured workload can be replayed on a test system.

F. Multiple database workloads can be replayed only if both the capture and replay databases have the same database version.

G. The number of replay clients must be equal to the number of captured workloads.