Oracle Oracle Database 1Z0-064 Questions & Answers

• Question 21:

  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

  Correct Answer: C

  Reference: https://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN11875

• Question 22:

  You are administrating a database that supports a DSS workload. Syntactically similar queries using joins and aggregate functions are frequently executed by users to fetch data from remote databases by using database links.

  Which three tasks should you perform to improve the performance of these queries?

  A. Ensure that the RESULT_CACHE_MAX_RESULT parameter is set to 100.

  B. Ensure that the RESULT_CACHE_REMOTE_EXPIRATION parameter is set to a nonzero value for the local instance.

  C. Ensure that the RESULT_CACHE_MAX_SIZE parameter is set to a nonzero value on the local database instance.

  D. Ensure that the RESULT_CACHE_MAX_SIZE parameter is set to a nonzero value on both the local and remote database instances.

  E. Ensure that the RESULT_CACHE_MAX_SIZE parameter is set to a nonzero value on the remote database instance.

  F. Ensure that the RESULT_CACHE_MAX_MODE parameter is set to FORCE for the local instance.

  G. Ensure that the RESULT_CACHE_MODE parameter is set to FORCE for remote database instances.

  Correct Answer: BCF

• Question 23:

  Your database supports an OLTP workload.

  An application frequently performs update operations on an indexed column of a table; some bulk delete

  and insert operations are also performed.

  The table is created in a locally managed tablespace with manual segment space management.

  A user complains about poor response time when querying the table.

  The queries mostly perform index range scans.

  You notice a high number of buffer busy waits and contention on segment headers.

  Which three would you investigate further to diagnose the poor response time?

  A. Check for freelist contention.

  B. Check table blocks for migrated or chained rows.

  C. Check the size of the shared pool.

  D. Check the size of the large pool.

  E. Chick whether the indexes have only a few entries per block.

  Correct Answer: BCE

• Question 24:

  The CUSTOMERS table has 55,500 rows and 620 distinct values in the CUST_CITY_ID column. The number of popular values is 54 and less than 99% of the rows contain popular values.

  Which type of histogram should you create to accurately determine the cardinality estimate on the CUST_CITY_ID column?

  A. high-frequency histogram

  B. height-balanced histogram

  C. hybrid histogram

  D. frequency histogram

  Correct Answer: B

  Reference: https://www.scribd.com/document/308438994/Statistics

• Question 25:

  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.

  Correct Answer: AC

• Question 26:

  Examine the query and its output:

  

  How would you investigate the cause of three entries of identical SQL statements by using dynamic performance views?

  A. Query V$SQLAREA to find out if the SQL statement is bind aware.

  B. Query V$SQLAREA to find out if multiple sessions executed this SQL statement, which resulted in multiple entries in V$SQL.

  C. Query V$SQL to find out if any invalidations or reloads occurred for this SQL statement.

  D. Query V$SQL_SHARED_CURSOR to identify the reason that a child cursor cannot be shared.

  Correct Answer: D

• Question 27:

  You want to enable the Database Smart Flash Cache feature on your database. For this purpose, you configure two flash devices: /dev/sdj and /dev/sdk, with sizes of 64 GB and 32 GB, respectively.

  Examine the parameters set for your database instance:

  

  How would you make sure that the database uses these devices for Database Smart Flash Cache?

  A. by disabling Automatic Memory Management, setting SGA_TARGET to 96G, and setting the required

  memory that you want to set for SGA

  B. by setting the value of the DB_FLASH_CACHE_SIZE parameter to 8G

  C. by configuring the DB_FLASH_CACHE_SIZE parameter and making sure that all flash devices are of the same size

  D. by setting the DB_FLASH_CACHE_SIZE parameter to 86G and the MEMORY_TARGET parameter to 112G

  E. by setting the value of the DB_FLASH_CACHE_SIZE parameter to 64G, 32G

  Correct Answer: E

• Question 28:

  Examine the parameters set for your database instance:

  

  To investigate the slow response time of queries on the TRANS table, you gather table and execute the query:

  

  The table is stored in a tablespace with Automatic Segment Space Management (ASSM), and some rows of the TRANS table are migrated and chained.

  Which two actions would you recommend to improve query response time?

  A. Reorganize the TRANS table online by using the DBMS_REDEFINITION package.

  B. Create a bigger non-standard blocksize tablespace and move the TRANS table to that tablespace.

  C. Move the TRANS table to a tablespace with manual segment space management with a lower value set for the PCTUSED attribute.

  D. Move the TRANS table to a tablespace with manual segment space management with a higher value set for the PCTFREE attribute.

  Correct Answer: AD

• Question 29:

  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 MEMORY_MAX_TARGET and MEMORY_TARGET parameters to a value greater than the sum of SGA_MAX_SIZE and PGA_AGGREGATE_TARGET.

  B. Set the SGA_TARGET and PGA_AGGREGATE_TARGET parameters to their minimum required values.

  C. Set DB_CACHE_SIZE, SHARED_POOL_SIZE, and LARGE_POOL_SIZE to their minimum required values.

  D. Set the SGA_TARGET parameter to the value of the SGA_MAX_SIZE parameter.

  E. Set the MEMORY_TARGET parameter to the value of SGA_MAX_SIZE.

  Correct Answer: BCD

• Question 30:

  A parallel SQL statement is taking a long time to execute. You want to verify how the server processes are dividing work.

  How should you determine this?

  A. by using Real-Time ADDM

  B. by using services

  C. by using SQL Tuning Advisor

  D. by using ASH

  E. by using Real-Time SQL monitoring

  Correct Answer: B

  Reference: https://docs.oracle.com/cd/B10501_01/server.920/a96520/tuningpe.htm