Examine the parameters set for your database instance:

You upgrade your database to Oracle Database 12c. The database supports a mixed workload and works with different workloads at different times. You notice in an ADDM report that the shared pool is inadequately sized. You resize the shared pool by decreasing the sizes of other pools, which results in inadequate sizes for other pools. You want to automate the sizing of SGA components.
Which two actions should you perform? (Choose two.)

• A. Set the PGA_AGGREGATE_TARGET parameter to 0 and the SGA_TARGET parameter to 1.5G.
• B. Set the SGA_TARGET parameter equal to SGA_MAX_SIZE.
• C. Set the MEMORY_MAX_TARGET parameter to the sum of DB_CACHE_SIZE, SHARED_POOL, and LARGE_POOL_SIZE.
• D. Set DB_CACHE_SIZE, SHARED_POOL, and LARGE_POOL_SIZE to their minimum required values.
• E. Set the SGA_TARGET parameter to the sum of DB_CACHE_SIZE, SHARED_POOL, and LARGE_POOL_SIZE.

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. creating column group statistics, regathering statistics, and ensuring that histograms exist on both these columns
• C. using SQL plan directives to generate an optimal plan
• D. setting the dynamic statistics level to 4 and setting the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to true

You are administrating a database that supports a mixed workload. Applications are running on a middle tier and use connection pooling to connect to the database instance. You want to trace all applications and modules that use the ORCL1 service to connect to the database instance.
How would you consolidate the SQL statements for sessions that are connected by using the ORCL1 service?

• A. by setting TRACE_ENABLED = TRUE and using the tkprof utility to consolidate and interpret trace files
• B. by using the DBMS_MONITOR package to enable tracing, the tkprof utility to consolidate trace files, and the trcsess utility to interpret trace files
• C. by using the DBMS_MONITOR package to enable tracing, the trcsess utility to consolidate trace files, and the tkprof utility to interpret trace files
• D. by using the DBMS_TRACE package to enable tracing and the tkprof utility to consolidate and interpret trace files
• E. by setting SQL_TRACE = TRUE and using the tkprof utility to consolidate and interpret trace files

You upgrade your database from Oracle Database 11g to 12c. You capture execution plans for a resource-intensive SQL workload before the upgrade. After the upgrade, you load the plans into the SQL Management Base.
Examine the parameters set for the instance:

Which two statements are true?

• A. A better execution plan may be found for the SQL statements and can be evolved manually.
• B. New execution plans generated will always be added to the SQL plan baselines as accepted plans.
• C. New execution plans cannot be evolved until execution plans from the previous version are dropped.
• D. New execution plans generated will always be added to the SQL plan baselines as fixed plans.
• E. New SQL plan baselines might be generated automatically for the repeatable SQL statements.

You are administering a database that supports a mixed workload. The CURSOR_SHARING parameter is set to the default value. While analyzing the latest Automatic Workload Repository (AWR) report, you find a large number of cursor: pin S wait on X, cursor: pin X wait on S, and library cache mutex waits in the Top 10 foreground events section. Examine the Instance Efficiency Percentages section in the AWR report:

Which three statements are true in this scenario? (Choose three.)

• A. The buffers required by queries are not found in the buffer cache, thereby increasing expensive disk I/O.
• B. Sessions are waiting for mutexes in exclusive mode on cursors but other sessions are holding the mutexes in share mode.
• C. Cursors are not getting shared, resulting in a large number of hard parses.
• D. Sessions are waiting for mutexes in share mode on cursors but other sessions are holding the mutexes in exclusive mode.
• E. The CPU is spending more time in finding cursors in the library cache.