Download Oracle Database 12c: Performance Management and Tuning.1z0-064.PracticeTest.2018-05-06.56q.vcex

Vendor: Oracle
Exam Code: 1z0-064
Exam Name: Oracle Database 12c: Performance Management and Tuning
Date: May 06, 2018
File Size: 2 MB
Download Exam

How to open VCEX files?

Files with VCEX extension can be opened by ProfExam Simulator.

Download
ProfExam Simulator

Purchase
Coupon: EXAM_HUB

Discount: 20%

ProfExam Simulator ProfExam Simulator ProfExam Simulator ProfExam Simulator ProfExam Simulator

Demo Questions

Question 1
Which two actions should you take to monitor the throughput generated by the modules of an application? (Choose two.)
  1. Use the Resource Manager.
  2. Enable SQL Trace at the session level.
  3. Create a service.
  4. Use a dedicated server configuration.
  5. Use the DBMS_APPLICATION_INFO package to define the current module and action so that they appear in V$SESSION.
Correct answer: AB
Question 2
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.)
  1. Set the STALE_PERCENT statistic preference to a higher value than the default, and then gather statistics.
  2. Set the STATISTICS_LEVEL parameter to ALL for the instance.
  3. Set the INCREMENTAL preference to TRUE, and then gather statistics.
  4. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE for the session in which you want to test the query.
  5. Set the PUBLISH statistic preference to FALSE, and then gather statistics.
  6. Set the NO_INVALIDATE statistic preference to TRUE, and then gather statistics.
Correct answer: BE
Question 3
Examine the structure of the EMPLOYEES table. 
     
EMPLOYEE_ID is the primary key. No other indexes exist on this table. 
View the Exhibit to examine the commands and their output. 
     
Which two actions should you perform to make the cursor bind aware? (Choose two.)
  1. Create a histogram on the DEPARTMENT_ID column.
  2. Change the default CURSOR_SHARING value to FORCE.
  3. Execute the query with the same DEPARTMENT_ID value multiple times.
  4. Create an index on the DEPARTMENT_ID column.
  5. Gather statistics for the index.
  6. Regather statistics on the table.
Correct answer: CD
Question 4
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.)
  1. Check the number of disk sorts.
  2. Check for the causes of the full table scans.
  3. Check the number of chained or migrated rows.
Correct answer: ABC
Question 5
Examine the parameters set for your database instance:
    
 
You are asked by a developer to create a table for an application with these requirements:
  • The table will be used for a DSS application. 
  • High volume bulk loads will be performed. 
  • The table will be used to store archival data on which large full-table scans (FTS) will be performed. 
Which attributes are the best for the tablespace in which this table should be created? (Choose the best answer.)
  1. Create it in a locally managed tablespace with ASSM enabled and assign a high value for the PCTFREE attribute.
  2. Create it in a locally managed tablespace with manual segment space management.
  3. Create it in a locally managed tablespace with a bigger nonstandard block size and ASSM enabled.
  4. Create it in locally managed tablespace with ASSM enabled and an additional freelist.
Correct answer: C
Question 6
You are administering a database that supports a DSS workload. Automatic Shared Memory Management is enabled for the database instance. Users issue queries to perform large soft operations and complain about degraded performance of the queries. On investigation, you notice that the queries are performing multipass work area executions and the I/O contention on one of the temporary tablespaces is very high. 
Which two can be possible resolutions for this issue? (Choose two.)
  1. Increase the size of the large pool.
  2. Increase the value of the PGA_AGGREGATE_TARGET parameter.
  3. Create a temporary tablespace group and assign it to users.
  4. Increase the value of the PGA_AGGREGATE_LIMIT parameter.
  5. Create another temporary tablespace and assign it to users.
  6. Enable temporary undo.
Correct answer: AD
Question 7
Examine the query and its output:
    
 
Which two inferences can be definitely derived from this output? (Choose two.)
  1. The db file scattered read event has occurred 104 times in this session for file# 12.
  2. The session has completed performing a full table scan.
  3. The SQL statements in this session are performing excessive disk reads.
  4. The multiblock factor is 8 for this I/O but it could vary for the other I/O events.
Correct answer: AC
Question 8
You are administering a database that supports an OLTP workload. An application regularly creates global temporary tables and a large number of transactions are performed on them. You notice that performance is degraded because of excessive generation of undo due to a large number of transactions on the global temporary tables. 
What is the recommended action to improve performance? (Choose the best answer.)
  1. Increase the size of the undo tablespace and enable undo retention guarantee.
  2. Increase the size of the database buffer cache.
  3. Enable temporary undo.
  4. Increase the size of the temporary tablespace or make it autoextensible.
  5. Enable Automatic Segment Space Management (ASSM) for the undo tablespace.
Correct answer: A
Explanation:
Reference: https://docs.oracle.com/cd/B13789_01/server.101/b10739/undo.htm
Reference: https://docs.oracle.com/cd/B13789_01/server.101/b10739/undo.htm
Question 9
Your database supports an OLTP 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 a series of INSERT, UPDATE, and DELETE operations on the table. 
View the Exhibit to examine the query and its execution plan. 
    
 
Which two options can improve the performance of the query without significantly slowing down the DML operations? (Choose two.)
  1. creating a bitmap index on the COUNTRY_ID column
  2. regathering statistics on the CUSTOMERS table
  3. gathering statistics on the COUNTRY_IDX index
  4. creating a histogram on the COUNTRY_ID column
  5. increasing the size of the PGA
  6. creating an SQL profile
  7. creating a KEEP cache
Correct answer: AD
Question 10
Identify two effects of the DB_FILE_MULTIBLOCK_READ_COUNT parameter on the optimizer. (Choose two.)
  1. Decreasing the value of DB_FILE_MULTIBLOCK_READ_COUNT from the default increases the cost of index probes for DSS workloads.
  2. A full table scan can become cheaper than index scans if the database instance has a high enough DB_FILE MULTIBLOCK_READ_COUNT for both OLTP and DSS workloads.
  3. Increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT within OS limits lowers the costing of an index probe that is done in conjunction with a nested loop for OLTP workloads.
  4. In DSS workloads where full table scans may run in parallel and bypass the buffer cache, decreasing the value of DB_FILE_MULTIBLOCK_READ_COUNT from the default increases the cost of full table scans.
  5. Increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT within OS limits lowers the cost of full table scans and can result in the optimizer choosing a full table scan over an index scan for both OLTP and DSS workloads.
Correct answer: CD
CONNECT US
HOW TO OPEN VCE FILES

Use VCE Exam Simulator to open VCE files
Avanaset

HOW TO OPEN VCEX AND EXAM FILES

Use ProfExam Simulator to open VCEX and EXAM files
ProfExam Screen

ProfExam
ProfExam at a 20% markdown

You have the opportunity to purchase ProfExam at a 20% reduced price

Get Now!