Oracle Database 12c: Performance Management and Tuning

Question 1

You are administrating a database that supports an OLTP workload. A few users complain about the poor performance of some SQL statements. You notice that these SQL statements belong to different applications and conclude that there is no correlation between them. You create a SQL Tuning Set (STS) containing these SQL statements.
What must you do to analyze and generate recommendations for the SQL statements in the STS?

A. Submit the STS as input to the SQL Tuning Advisor to generate recommendations for indexes or SQL profiles or both.

B. Submit the STS as input to the SQL Access Advisor to generate recommendations for SQL profiles.

C. Submit the STS as input to the SQL Tuning Advisor to generate recommendations for SQL plan baselines.

D. Submit the STS as input to the SQL Performance Analyzer to generate recommendations for creating indexes and materialized views.

Correct Answer: C

Question 2

Examine the parameters set for your database instance:

You notice that for one particular SQL statement, the optimizer generates a new better plan than the plans in the SQL Plan Management Base.
Which action is taken by the optimizer? (Choose the best answer.)

A. It adds the newly generated plan as an accepted but non-fixed plan.

B. It adds the newly generated plan as a fixed plan, which will be used each time the SQL statement is executed.

C. It adds the newly generated plan as enabled but not accepted.

D. It adds the newly generated plan as enabled and accepted.

Correct Answer: D

Question 3

Examine the parameters set for a database instance:

The database supports a mixed workload. Users complain about the increased response time of a few DSS queries. During investigation, you execute the query:

Based on the output, which two are possible ways to improve the performance of the queries? (Choose two.)

A. Enable Automatic Shared Memory Management.

B. Increase the value of the SGA_TARGET parameter.

C. Increase the number of DBWn processes.

D. Increase the value of the PGA_AGGREGATE_TARGET parameter.

E. Enable Automatic Memory Management.

F. Enable temporary undo.

Correct Answer: B,C

Question 4

Examine the parameters set for a database instance:

The database supports a DSS application that performs huge sort operations on large temporary tables with LOB columns. Schema objects used in queries are updated and new rows are inserted regularly. Users complain about the poor performance of a few queries. During investigation, you execute these queries:

Which two actions would you recommend to improve the performance of these queries?

A. Increase the value of the PGA_AGGREGATE_TARGET initialization parameter.

B. Alter the temporary tablespace to increase the extent size.

C. Enable Automatic Segment Space Management for the temporary tablespace.

D. Enable Automatic Shared Memory Management for the instance.

E. Enable Automatic Memory Management for the instance.

Correct Answer: B,C

Question 5

Which two actions should you take to monitor the throughput generated by the modules of an application? (Choose two.)

A. Use the DBMS_APPLICATION_INFO package to define the current module and action so that they appear in V$SESSION.

B. Use the Resource Manager.

C. Use a dedicated server configuration.

D. Create a service.

E. Enable SQL Trace at the session level.

Correct Answer: B,E

Question 6

Examine the query and its output:

Which two statements are true? (Choose two.)

A. Session 3346 is in wait state because either it is waiting to update a row that is locked by another session or another session is trying to insert the same key value in a UNIQUE index.

B. Session 3346 is in wait state because it wants to lock a row in a block in which other sessions have already locked rows, and there is no free ITL slot available in this block.

C. Session 4208 is definitely idle and should be killed to free network resources.

D. Session 2832 had to wait 2029 seconds for a message to arrive because of a network bottleneck.

E. Session 4208 is either idle or experiencing poor response time due to a network or resource bottleneck on the client process.

Correct Answer: A,D

Question 7

In your database, supporting an OLTP workload, the SALES table is range-partitioned on the SALES_DATE column. A new partition is created every quarter and is frequently used for updates and queries. You recently noticed degraded performance of queries on a new partition due to stale statistics. A local partitioned index exists on the SALES_DATE column.
What would you recommend to improve performance?

A. setting the INCREMENTAL preference to TRUE and the CASCADE preference to FALSE for the partitioned table while gathering statistics

B. dropping the local partitioned index and creating a global partitioned index on the SALES_DATE column

C. setting the INCREMENTAL preference to TRUE and the GRANULARITY preference to AUTO for the partitioned table while gathering statistics

D. setting the INCREMENTAL preference to TRUE and the NO_INVALIDATE preference to TRUE for the partitioned table while gathering statistics

Correct Answer: C

Oracle Database 12c: Performance Management and Tuning - 1z1-064 Exam Practice Test

Latest Update

Useful Links

Contact Us