Oracle Oracle Database 1Z0-064 Questions & Answers

• Question 91:

  You are administering a database that supports an OLTP workload. The CURSOR_SHARING parameter is set to EXACT for the instance. The performance of queries issued by one of the modules has degraded. The queries executed by the module are almost identical in syntax. To investigate, you analyze the latest AWR report and find a large number of latch:shared pool wait events and also a high percentage of the hard parse elapsed time.

  Which two can be reasons for this? (Choose two.)

  A. The I/O performance is slow.

  B. Bind variables are not used for similar queries, causing hard parses.

  C. Repeated access to a small number of blocks.

  D. Excessive time is spent on finding cached cursors in the library cache.

  E. The CURSOR_SHARING parameter is set to EXACT, which does not allow similar queries to share a cursor.

  Correct Answer: BC

• Question 92:

  Which two result in the least internal fragmentation within segments and the least external fragmentation within tablespaces? (Choose two.)

  A. automatic segment space managed segments

  B. locally managed tablespaces with uniform extent size

  C. freelist managed segments with one freelist

  D. dictionary managed tablespaces

  E. locally managed tablespaces that were converted from dictionary managed tablespaces

  F. freelist managed segments with multiple freelists

  Correct Answer: AD

• Question 93:

  Which three methods can you use to create a pre-change SQL trial to capture performance data by using SQL Performance Analyzer? (Choose three.)

  A. executing SQL statements in a SQL Tuning Set (STS) on a test database by using database links to the production database.

  B. generating only execution plans on a test database without actually running SQL statements.

  C. generating an execution plan and statistics for selective SQL statements captured in an STS

  D. loading performance data and execution plans from an STS.

  E. generating both execution plans and statistics for each SQL statement in an STS by actually running the SQL statements on a test database.

  Correct Answer: ABC

  Reference: https://docs.oracle.com/cd/E11882_01/server.112/e41481/spa_pre_change.htm#RATUG181

• Question 94:

  Identify two effects of the DB_FILE_MULTIBLOCK_READ_COUNT parameter on the optimizer. (Choose two.)

  A. Decreasing the value of DB_FILE_MULTIBLOCK_READ_COUNT from the default increases the cost of index probes for DSS workloads.

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

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

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

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

• Question 95:

  Examine the query and its output:

  

  Which two statements are true? (Choose two.)

  A. Session 2832 had to wait 2029 seconds for a message to arrive because of a network bottleneck.

  B. Session 4208 is either idle or experiencing poor response time due to a network or resource bottleneck on the client process.

  C. Session 3346 is in wait state because it wants to lock a row in a block in which other sessions have already locked rows, and there is no free ITL slot available in this block.

  D. Session 3346 is in wait state because either it is waiting to update a row that is locked by another session or another session is trying to insert the same key value in a UNIQUE index.

  E. Session 4208 is definitely idle and should be killed to free network resources.

  Correct Answer: AD

• Question 96:

  In which three situations does DB time always increase? (Choose three.)

  A. when the host is CPU bound for foreground processes

  B. when I/O wait time increases for foreground processes

  C. when more connections are made to a database instance

  D. when CPU consumption by background processes increases

  E. when wait time for data to be sent over a network increases

  Correct Answer: BCD

• Question 97:

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

  A. Increase the size of the large pool.

  B. Increase the value of the PGA_AGGREGATE_TARGET parameter.

  C. Create a temporary tablespace group and assign it to users.

  D. Increase the value of the PGA_AGGREGATE_LIMIT parameter.

  E. Create another temporary tablespace and assign it to users.

  F. Enable temporary undo.

  Correct Answer: AD

• Question 98:

  Examine the query and its output:

  

  Which two inferences can be definitely derived from this output? (Choose two.)

  A. The db file scattered read event has occurred 104 times in this session for file# 12.

  B. The session has completed performing a full table scan.

  C. The SQL statements in this session are performing excessive disk reads.

  D. The multiblock factor is 8 for this I/O but it could vary for the other I/O events.

  Correct Answer: AC

• Question 99:

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

  A. Increase the size of the undo tablespace and enable undo retention guarantee.

  B. Increase the size of the database buffer cache.

  C. Enable temporary undo.

  D. Increase the size of the temporary tablespace or make it autoextensible.

  E. Enable Automatic Segment Space Management (ASSM) for the undo tablespace.

  Correct Answer: A

• Question 100:

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

  A. creating a bitmap index on the COUNTRY_ID column

  B. regathering statistics on the CUSTOMERS table

  C. gathering statistics on the COUNTRY_IDX index

  D. creating a histogram on the COUNTRY_ID column

  E. increasing the size of the PGA

  F. creating a SQL profile

  G. creating a KEEP cache

  Correct Answer: AD