Oracle Database 12c: Performance Management and Tuning (1z1-064) Free Practice Test

Question 1

Your database supports an OLTP workload. You are planning to migrate database storage from file system storage to Automatic Storage Management (ASM). The applications supported by the database primarily perform small, random I/Os.
Which two statements are true regarding ASM striping in combination with RAID 0?

A. It improves availability and reliability of data be ensuring that logical data corruptions are found and repaired.

B. It improves the performance of only write-intensive workloads.

C. It helps in reducing network latency.

D. It provides higher I/O bandwidth.

E. It allows you to evenly spread data over multiple disks.

Correct Answer: D,E

Question 2

In your database, the locally managed tablespace, USERS, has the default space usage alert set to 85% for the warning level and 97% for the critical level.
Which two statements are true? (Choose two.)

A. Alert settings for the warning and critical levels must be disabled before taking the USERS tablespace offline.

B. Alerts are recorded in both Oracle Enterprise Manager Cloud Control and DBA_OUTSTANDING_ALERTS only when the critical threshold is exceeded.

C. Alerts are triggered when the space usage reaches the warning level, again when it reaches the critical level, and yet again when the space usage falls below the critical level.

D. Alerts that are triggered are automatically recorded in DBA_ALERT_HISTORY after they are cleared.

Correct Answer: A,B

Question 3

You want to generate statistics for new objects added to an OLTP application without affecting old statistics and the sessions that currently use them.
Which two tasks would you perform to test the new statistics? (Choose two.)

A. Set the PUBLISH statistics preference to FALSE and then gather statistics.

B. Use the DBMS_STATS.PUBLISH_PENDING_STATS procedure to make pending statistics the current statistics.

C. Set the STALE_PERCENT statistics preference to zero and then gather statistics.

D. Set the NO_INVALIDATE statistics preference to FALSE and gather statistics without affecting old statistics.

E. Set the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to TRUE for the session.

Correct Answer: C,E

Question 4

Your database supports an OLTP workload where applications primarily perform small random I/Os. You notice an increase in the I/O requests queued up against the storage, and an increase in the wait time in queue. The database uses file system storage.
What would you recommend to decrease the wait time of I/O requests?

A. Increase the value of the DB_WRITER_PROCESSES parameter.

B. Suggest storage admin to add more disks (spindles) to storage and stripe data across disks.

C. Decrease the value of the DBWR_IO_SLAVES parameter.

D. Increase the size of the buffer cache.

Correct Answer: D

Question 5

For your database some users complain about not being able to execute transactions. Upon investigation, you find that the problem is caused by some users performing long-running transactions that consume huge amounts of space in the UNDO tablespace.
You want to control the usage of the UNDO tablespace only for these user sessions.
How would you avoid the issue from repeating in future? (Choose the best answer.)

A. Implement a Database Resource Manager plan by mapping the users to a resource consumer group with limits defined for UNDO tablespace usage.

B. Create external roles to restrict the usage of the UNDO tablespace and assign them to the users.

C. Create a profile for the users with the LOGICAL_READS_PER_SESSION and LOGICAL_READS_PER_CALL limits defined.

D. Set the threshold for UNDO tablespace usage for the users.

Correct Answer: A

Question 6

Examine the output of the query executed to diagnose the reason for performance degradation of queries:

Which three factors will you investigate further to identify the cause of the performance degradation? (Choose three.)

A. Check the indexes on the tables used in queries for clustering factor.

B. Check for the causes of the full table scans.

C. Check the number of chained or migrated rows.

D. Check the size of the temporary tablespace for sorting operations.

E. Check the number of disk sorts.

Correct Answer: B,C,E

Question 7

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

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

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

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

D. limiting the number of concurrent sessions for a user

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

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

Correct Answer: B,C,E,F

Question 8

You are administering a database that supports an OLTP workload. RESULT_CACHE_MODE is set to the default value and a result cache is configured for the instance. Multiple sessions execute syntactically similar queries without dblinks, containing functions and expressions, on tables with no DML activity. Some users complain about poor performance of these queries.
You investigate and find that the queries are frequently performing physical I/O, even though the results fetched by the queries are similar.
Which two actions do you recommend to overcome the problem affecting these queries? (Choose two.)

A. Use the result cache hint in the queries.

B. Set the RESULT_CACHE_REMOTE_EXPIRATION parameter to a nonzero value.

C. Configure the KEEP pool and cache the queried tables used in the KEEP pool.

D. Set the RESULT_CAHCE_MODE parameter to FORCE for the instance.

E. Use bind variables for similar queries instead of literals.

Correct Answer: A,D

Question 9

You are administering a database that supports an OLTP workload. Users complain about the degraded response time of a query. You want to gather new statistics for objects accessed by the query and test query performance with the new statistics without affecting other sessions connected to the instance.
The STALE_PERCENT statistic preference is set to a default value and the STATISTICS_LEVEL parameter is set to TYPICAL.
Which two actions would you take to accomplish the task? (Choose two.)

A. Set the STALE_PERCENT statistic preference to a higher value than the default, and then gather statistics.

B. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE for the session in which you want to test the query.

C. Set the NO_INVALIDATE statistic preference to TRUE, and then gather statistics.

D. Set the INCREMENTAL preference to TRUE, and then gather statistics.

E. Set the PUBLISH statistic preference to FALSE, and then gather statistics.

F. Set the STATISTICS_LEVEL parameter to ALL for the instance.

Correct Answer: E,F

Welcome to TestSimulate

Oracle Database 12c: Performance Management and Tuning (1z1-064) Free Practice Test