1Z0-064 Online Practice Questions and Answers

Examine the parameters set for a database instance supporting a mixed workload:

The database instance supports shared server and dedicated server connections simultaneously. Users complain about increased response times of a few DSS queries. During investigation, you execute the queries:

Based on the output, which two courses of action would you recommend to improve query performance? (Choose two.)

A. Use a parallel hint in the queries.

B. Increase the number of DBWn processes.

C. Increase the value of the SORT_AREA_SIZE initialization parameter.

D. Increase the size of the temporary tablespace or add a new temporary tablespace.

E. Increase the value of the PGA_AGGREGATE_TARGET initialization parameter.

F. Increase the size of the large pool.

You execute this query twice in a session:

Then you query V$SQL_SHARED_CURSOR for details about child cursors as shown.

Which two statements are true? (Choose two.)

A. No statistics were collected during the first execution of the query.

B. A subsequent execution of the query in this session is likely to undergo a soft parse.

C. The second execution of the query was hard parsed because the estimated cardinality was inaccurate.

D. A subsequent execution of the query in this session will undergo a hard parse.

E. The second execution of the query was hard parsed because extended statistics were collected after the first execution of the query.

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

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

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

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

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

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

Which two statements are true about viewing the details of Real-Time Database Operations? (Choose two.)

A. In V$SQL_MONITOR monitoring, statistics are cumulative over several executions of the SQL statement that is being monitored in a session.

B. SQL Developer can be used to view running database operations.

C. Oracle Enterprise Manager Database Express can be used to view running database operations.

D. When the SQL statement that is being monitored is executing, V$SQL_MONITOR is refreshed once every minute. After the execution ends, the monitoring information in V$SQL_MONITOR is deleted immediately.

E. Oracle Enterprise Manager Cloud Control can be used to view running database operations.

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.

Which two statements are true about Active Session History (ASH)? (Choose two.)

A. The Data Sample size available in an ASH report is dynamic and, at any given moment, is directly related to the amount of work being performed.

B. ASH contains sampled data from all sessions that are connected to a database instance at any given moment.

C. ASH samples data from V$SESSION every second.

D. An ASH report can be used to identify the service that may be the cause of a transient performance problem.

Which four objectives are achieved by using Resource Manager to manage multiple concurrent user sessions that are competing for resources? (Choose four.)

A. distributing available CPU by allocating percentages of CPU time to different users and applications

B. limiting the degree of parallelism of any operation performed by members of a group of users

C. limiting queries based on resource consumption of runaway sessions or calls that consume more than a specified amount of CPU, physical I/O, logical I/O, or elapsed time

D. limiting the number of concurrent sessions for a user

E. limiting the number of user sessions allowed to be concurrently active within a group of users

F. limiting the number of parallel executions that can be executed by a user

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. Alter the temporary tablespace to increase the extent size.

B. Increase the value of the PGA_AGGREGATE_TARGET initialization parameter.

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

D. Enable Automatic Memory Management for the instance.

E. Enable Automatic Shared Memory Management for the instance.

You want to set a priority for the workloads generated by the applications in your database instance such that report-generating tasks are assigned a lower priority.

How would you do this?

A. by using job classes with the Resource Manager

B. by creating an active session pool using the Resource Manager

C. by using services that are assigned different priorities with the Resource Manager

D. by using services for the applications and creating job classes associated with each service

In which three scenarios would you recommend the use of the SQL Performance Analyzer?

A. to identify SQL statements that require SQL profiles

B. to analyze the impact of network and interconnect changes on database workload

C. to identify SQL statements whose performance may have regressed due to a hardware upgrade

D. to analyze the impact of new indexes and materialized views on the SQL statements executed by an application

E. to identify SQL statements in an application whose performance may have regressed due to migration from Oracle Database 11g to 12c

F. to analyze the impact of using bind variables for syntactically similar SQL statements