1z0-064 sample questions

Question 1

You plan to upgrade your production database from Oracle Database 11 g to 12c and also to introduce new objects to the database. You also want to upgrade the hardware. You have already created a test system with the upgrades to be made to the production database. As part of the testing, you want to:

• analyze and compare the overall database workload with concurrency and transaction characteristics
• find SQL statements that might get regressed because of the upgrade
• analyze execution plans for SQL statements for which performance might get regressed
• analyze the impact of new schema objects on database performance

Which two tools would you recommend to achieve the objective? (Choose two.)

Accepted Answer

SQL Tuning Advisor

Accepted Answer

SQL Performance Analyzer

Answer

Database Replay

Answer

SQL Access Advisor

Answer

Automatic Database Diagnostic Monitor (ADDM) compare periods report

Answer

Automatic Workload Repository (AWR) compare periods report

Question 2

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

Accepted Answer

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

Accepted Answer

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

Answer

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

Answer

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

Answer

to identify a scheduler job that is not responding

Question 3

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.)

Accepted Answer

setting the CURSOR_SHARING parameter to EXACT

Accepted Answer

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

Answer

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

Answer

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

Answer

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

Answer

configuring shared server connections

Question 4

To investigate the slow response time of queries on the TRANS table, you gathered the table statistics and executed the query:

The table is stored in a tablespace that has Automatic Segment Space Management (ASSM) enabled. The tablespace is created with a standard block size of 8192 bytes.

Which three can be reasons for the slow response time of the queries? (Choose three.)

Accepted Answer

Row size is too large to fit into a single block during insert operations.

Accepted Answer

Row moves from one data block to another data block because the row grows too large to fit in the original block.

Accepted Answer

The value of PCTUSED is set to a value lower than the default, causing row chaining.

Answer

The table is subject to frequent insert, update, and delete DML activity leading to sparsely populated blocks.

Answer

The value of PCTFREE is set to a value lower than the default, causing row chaining.

Question 5

Examine the parameters set for your database instance:

You are administrating a database that supports a DSS workload. You make some changes to the sizes of memory components. Consequently, you receive this error:

Which three actions might resolve the issue?

Accepted Answer

enabling Automatic Shared Memory Management for the database instance

Accepted Answer

setting the value of MEMORY_MAX_TARGET to the value of the SGA_MAX_SIZE parameter

Accepted Answer

enabling Automatic Memory Management for the database instance

Answer

pinning the cursors in the shared pool

Answer

setting the PGA_AGGREGATE_TARGET parameter to 0 and the MEMORY_TARGET parameter to the value of the PGA_AGGREGATE_TARGET parameter

Answer

increasing the size of the shared pool

Question 6

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.)

Accepted Answer

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

Answer

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

Answer

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

Answer

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.

1z0-064 Oracle Database 12c: Performance Management and Tuning Practice Questions

Create an account to continue.

Create an account to continue.

Related exams