1z0-064 sample questions

Question 1

Examine the parameters set for a database instance:

An application performs a large number of identical queries on small lookup tables very frequently. Users complain about the slow response time of queries on these tables. On investigation, you notice that buffers are getting aged out of the buffer cache. To mitigate the issue, you increase the value of the SGA_MAX_SIZE and SGA_TARGET parameters, but after some time, you notice the same issue again.

Which two would you recommend as long-term solutions for this issue? (Choose two.)

Accepted Answer

increasing the size of the database buffer cache

Accepted Answer

configuring Automatic Memory Management

Answer

configuring the KEEP buffer pool and altering tables to use the KEEP pool

Answer

pinning the cursors of the queries in the library cache

Answer

configuring the result cache for the instance

Question 2

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 3

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 4

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 5

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 6

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

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

Create an account to continue.

Create an account to continue.

Related exams