1z1-064 Exam Training Practice Tests (Oracle Database 12c: Performance Management and Tuning)]

Question 1

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

A. Create database services for the applications, assign individual sessions created by the applications to consumer groups, and then set a priority. B. Create database services for the applications and assign different profiles to the sessions to set a relative priority for resource usage. C. Assign profiles to users running the batch operations and make sure that a priority is set for resource limits in profiles. D. 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.

Correct Answer: B

Question 2

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 services for the applications and creating job classes associated with each service B. by using job classes with the Resource Manager C. by creating an active session pool using the Resource Manager D. by using services that are assigned different priorities with the Resource Manager

Correct Answer: D

Question 3

You are administrating a database that supports a mixed workload. Given are the details of the workload:
During the day, short transactions and syntactically similar queries are repeatedly issued.
At night, DSS batch queries and jobs are executed with large sort operations.
Examine the parameters set for the database instance:

To automate memory requirements for both workloads, which three changes to parameters would you recommend?

A. Set the SGA_TARGET parameter to the value of the SGA_MAX_SIZE parameter. B. Set DB_CACHE_SIZE, SHARED_POOL_SIZE, and LARGE_POOL_SIZE to their minimum required values. C. Set the MEMORY_TARGET parameter to the value of SGA_MAX_SIZE. D. Set the SGA_TARGET and PGA_AGGREGATE_TARGET parameters to their minimum required values. E. Set the MEMORY_MAX_TARGET and MEMORY_TARGET parameters to a value greater than the sum of SGA_MAX_SIZE and PGA_AGGREGATE_TARGET.

Correct Answer: A,B,D

Question 4

In the CUSTOMERS table, the values in the CUST_STATE column are dependent on the values in the COUNTRY_ID column. You want to make the optimizer aware of this dependency when these columns are used together in WHERE clause predicates that contain equalities or in-lists.
Which two methods achieve this? (Choose two.)

A. gathering statistics on the CUSTOMERS table and its dependent objects, and then locking the statistics B. setting the dynamic statistics level to 4 and setting the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to true C. using SQL plan directives to generate an optimal plan D. creating column group statistics, regathering statistics, and ensuring that histograms exist on both these columns

Correct Answer: A,D

Question 5

A senior DBA asks you to decrease the values of the connect_time_scale and think_time_scale replay processing parameters to 50 to preprocess the workload for replay.
What three could be reasons for this change? (Choose three.)

A. to decrease the number of concurrent users during replay B. to reduce the elapsed time between two successive user calls from a session. C. to reduce the time of replay D. to decrease the wait for a query, caused by noncommitted transactions E. to increase the number of concurrent users during replay

Correct Answer: C,D,E

Question 6

Your database supports a DSS system.
Examine the parameter values configured in your database:
sga_max_size = 480M
sga_target = 480M
pga_aggregate_target = 160M
The CUSTOMERS table contains 8,000 rows. The CUST_ID column is the primary key and the COUNTRY_ID column contains only three possible values: 1111, 2222, and 3333.
You execute the commands:
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS');
PL/SQL procedure successfully completed.
SQL> CREATE INDEX COUNTRY_IDX ON CUSTOMERS (COUNTRY_ID);
Index created.
You then perform several INSERT, UPDATE, and DELETE operations, significantly altering the data in the table.
View the Exhibit to examine the query and its execution plan.

Which three options would improve the performance of the query? (Choose three.)

A. increasing the size of the PGA B. creating a histogram on the COUNTRY_ID column C. creating a bitmap index on the COUNTRY_ID column D. regathering statistics on the CUSTOMERS table E. creating a SQL profile F. creating a KEEP cache

Correct Answer: C,D,F

Question 7

Which two statements are true about the interpretation of Buffer Cache Hit Ratio in the Instance Efficiency Percentages section of an AWR report? (Choose two.)

A. Poor hit ratios indicate that a large number of indexed lookups or small table scans are being performed. B. A high value indicates that the buffer cache is adequately sized for the current workload. C. A high hit ratio may indicate that repeated scanning of the same large table or index is being performed. D. A low hit ratio indicates that a KEEP buffer pool should be configured based on the size of the largest object accessed in the buffer cache. E. A low hit ratio does not necessarily imply that increasing the size of the buffer cache will improve performance.

Correct Answer: D,E

Question 8

Users complain about increased response time for queries in your production database that supports an OLTP workload. On investigation, you notice a large number of db file scattered read, latch: cache buffers lru chain, and latch: cache buffers chains wait events:
Identify three possible reasons for the increased response time. (Choose three.)

A. the shared pool is inadequately sized B. cursors are closed explicitly after each execution C. queries not using indexes and performing full table scans D. repeated simultaneous access to a block or small number of blocks E. too many sort operations being performed F. queries repeatedly fetching blocks that are not in the database buffer cache

Correct Answer: A,D,F

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