Oracle Oracle Database 1Z0-064 Questions & Answers

• Question 101:

  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 STATISTICS_LEVEL parameter to ALL for the instance.

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

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

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

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

  Correct Answer: BE

• Question 102:

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

  A. Create a histogram on the DEPARTMENT_ID column.

  B. Change the default CURSOR_SHARING value to FORCE.

  C. Execute the query with the same DEPARTMENT_ID value multiple times.

  D. Create an index on the DEPARTMENT_ID column.

  E. Gather statistics for the index.

  F. Regather statistics on the table.

  Correct Answer: CD

• Question 103:

  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 number of disk sorts.

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

  C. Check the number of chained or migrated rows.

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

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

  Correct Answer: ABC

• Question 104:

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

  A. Create it in a locally managed tablespace with ASSM enabled and assign a high value for the PCTFREE attribute.

  B. Create it in a locally managed tablespace with manual segment space management.

  C. Create it in a locally managed tablespace with a bigger nonstandard block size and ASSM enabled.

  D. Create it in locally managed tablespace with ASSM enabled and an additional freelist.

  Correct Answer: C

• Question 105:

  Examine the partial AWR report taken for a time period of 60 minutes: Which two inferences can you draw from this report? (Choose two.)

  

  A. The database user calls are issuing frequent explicit commits.

  B. The CPUs are busy executing server processes and background processes for a considerable amount of CPU time.

  C. The database user calls are spending most of their time in I/O for single block reads.

  D. The database user calls are spending most of their time waiting for sessions that are in more important consumer groups.

  Correct Answer: BC

• Question 106:

  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. Create a profile for the users with the LOGICAL_READS_PER_SESSION and LOGICAL_READS_PER_CALL limits defined.

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

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

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

  Correct Answer: D

• Question 107:

  Which two actions should you take to monitor the throughput generated by the modules of an application? (Choose two.)

  A. Use the Resource Manager.

  B. Enable SQL Trace at the session level.

  C. Create a service.

  D. Use a dedicated server configuration.

  E. Use the DBMS_APPLICATION_INFO package to define the current module and action so that they appear in V$SESSION.

  Correct Answer: AB

• Question 108:

  You recently joined a new team administering a database.

  You notice that full table scans are performing poorly compared with full table scans on the databases you administered in a previous job.

  You decide that performance problems are caused by a misconfiguration of factors affecting full table scans.

  Which three factors should you investigate to determine the cause of the poorly performing Full Table Scans (FTS)? (Choose three.)

  A. value of DB_FILE_MULTIBLOCK_READ_COUNT

  B. storing query results in the result cache

  C. setting of the DISK_ASYNC_IO parameter to TRUE

  D. setting of the OPTIMIZER_MODE parameter to ALL_ROWS

  E. use of parallel queries

  F. block size of the tablespaces in which the tables being scanned are stored

  G. value of the OPTIMIZER_DYNAMIC_SAMPLING parameter

  Correct Answer: ABC

• Question 109:

  Examine the Time Model Statistics section of an AWR report:

  

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

  A. The available CPU resources were not utilized to their maximum capacity.

  B. All sequence numbers used during this AWR time interval were cached.

  C. A large number of connected user sessions were idle.

  D. New child cursors were created because of new bind values or usage of literal values as well as different bind types or sizes.

  E. The DB CPU time was not spent exclusively for processing SQL statements.

  Correct Answer: DE

• Question 110:

  Examine the partial TOP 10 Foreground Events by Total Wait Time section of an AWR report:

  

  What should you examine to diagnose the cause of the top three wait events? (Choose the best answer.)

  A. the V$ACTIVE_SESSION_HISTORY view

  B. the Time Model Statistics section of the AWR report

  C. the SQL statements based on elapsed time from the AWR report

  D. the Latch Activity section

  E. the Segment Statistics section of the AWR report

  Correct Answer: B