Oracle Oracle Database 1Z0-064 Questions & Answers

• Question 71:

  You observe that queries are performing poorly on the SALES_RECORDS table in your database. On investigation, you find that at the end of each day the contents of the SALES_RECORDS table are moved to the SALES_HISTORY table. The delete operations cause the table to be sparsely populated.

  The SALES_RECORDS table is created in a tablespace using Automatic Segment Space Management (ASSM) and row movement is enabled. The table must be accessible 24x7.

  Which two tasks would you recommend to improve the performance? (Choose two.)

  A. Perform EXPORT, DROP, and IMPORT operations on the SALES_RECORDS table.

  B. Shrink the SALES_RECORDS table by using the ALTER TABLE...SHRINK SPACE command.

  C. Move the SALES_RECORDS table to a different location by using the ALTER TABLE...MOVE command.

  D. Deallocate the space in the SALES_RECORDS table by using the ALTER TABLE...DEALLOCATE UNUSED command.

  E. Move the SALES_RECORDS table to a tablespace by using manual segment space management.

  F. Reorganize the SALES_RECORDS table online by using the DBMS_REDEFINITION package.

  Correct Answer: BD

• Question 72:

  Your database supports an OLTP workload during the day and batch processing at night. You want to monitor performance metrics to detect any degradation of performance in both types of workloads over a time period of 30 days.

  Examine this list of possible steps:

  1.

  Create a fixed baseline.

  2.

  Create a baseline template.

  3.

  Create a new moving window baseline.

  4.

  Increase the retention period default value to 30 days.

  5.

  Increase the size of the existing moving window baseline to 30 days.

  6.

  Create warning and critical alerts for the relevant metrics.

  7.

  Enable adaptive thresholds to detect the workload patterns and specify a high-significance-level threshold type.

  8.

  Enable adaptive thresholds to detect the workload patterns and set different threshold values as a percentage of the maximum value.

  Which option represents the required steps in the correct order? (Choose the best answer.)

  A. 5, 7

  B. 2, 4, 3

  C. 3, 4, 8

  D. 4, 5, 7

  E. 5, 1, 6, 8

  Correct Answer: E

• Question 73:

  You execute this query twice in a session:

  

  Then you query V$SQL_SHARED_CURSOR for details about child cursors as shown.

  

  Which two statements are true? (Choose two.)

  A. No statistics were collected during the first execution of the query.

  B. A subsequent execution of the query in this session is likely to undergo a soft parse.

  C. The second execution of the query was hard parsed because the estimated cardinality was inaccurate.

  D. A subsequent execution of the query in this session will undergo a hard parse.

  E. The second execution of the query was hard parsed because extended statistics were collected after the first execution of the query.

  Correct Answer: BC

• Question 74:

  Which three statements are true about using Real-Time Database Operations? (Choose three.)

  A. The STATISTICS_LEVEL initialization parameter must be set to ALL to enable automatic SQL monitoring for all long-running queries.

  B. The CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter must be set to DIAGNOSTIC+TUNUNG to use Real-Time Database Operations.

  C. The STATISTICS_LEVEL initialization parameter can be set to TYPICAL or ALL to enable Real-Time Database Operations.

  D. Real-Time Database Operations can be enabled only at the system level.

  E. Real-Time Database Operations can be created by using the DBMS_MONITOR or DBMS_SESSION packages.

  F. Database operation monitoring starts automatically when a database operation consumes at least five seconds of the CPU or I/O time in a single execution.

  Correct Answer: ABC

• Question 75:

  For which two requirements can you always use the V$ACTIVE_SESSION_HISTORY view? (Choose two.)

  A. to investigate intermittent performance problems in a session, only when the problem lasted less than five minutes in the last twelve hours

  B. to find the exact number of executions of a specific query in a session in the last two minutes

  C. to identify which module in an application was being executed in a session

  D. to identify a scheduler job that is not responding

  E. to find the amount of Program Global Architecture (PGA) memory being currently consumed by a session

  Correct Answer: CE

• Question 76:

  Which two statements are true about ADDM? (Choose two.)

  A. It analyzes the performance of a database instance based on the time period covered by the most recent AWR snapshot, and generates recommendations based on hard-coded criteria.

  B. It can analyze performance issues that occurred in past events provided they fall within the AWR retention period.

  C. ADDM resource utilization and cost of analysis depends on the actual load on the database and the number of performance problems analyzed.

  D. It first identifies the performance symptoms, and then refines them to reach the root cause with the singular aim of reducing the DB CPU metric.

  E. It documents only those components and wait classes that are significantly impacting the performance of the database.

  Correct Answer: DE

• Question 77:

  Examine the initialization parameters set for a database instance:

  

  The database supports an OLTP workload. Applications connect to the instance using shared server connections and perform small, random I/Os. All the data files are on the same disk. You notice free buffer wait events for sessions in the database instance.

  To solve the problem, you increase the size of the buffer cache. But after some time, you notice sessions waiting again on free buffer waits.

  What will you recommend to alleviate the issue? (Choose the best answer.)

  A. Run the I/O calibration tool.

  B. Configure the database instance to make asynchronous I/O available to DBWR.

  C. Spread the data files over multiple disks, controllers, and I/O buses to ensure that there are no hotspots in the I/O subsystem.

  D. Configure dedicated server connections for the applications.

  Correct Answer: B

• Question 78:

  You are administering a database that supports an OLTP workload. CURSOR_SHARING is set to EXACT for the instance. An application is frequently executing almost identical queries that vary in literal values in the WHERE clause, causing a large number of hard parses to occur.

  Which four statements would be true if you use bind variables for these queries? (Choose four.)

  A. Mutex contention in the library cache will be reduced.

  B. The optimizer will use one parent cursor and one child cursor for each SQL statement with different literal values.

  C. Hard parses will be reduced for the queries.

  D. The optimizer will use bind peeking and subsequent execution of the queries will always generate the same plans irrespective of the cardinality.

  E. The optimizer will generate the same plan for all bind values if no histograms exist on the columns used in the WHERE clause of these queries.

  F. The optimizer will use bind peeking and use the literal value to determine the execution plan for these queries.

  Correct Answer: ABCD

• Question 79:

  Examine an extract from a PGA Memory Advisory for your database: Which two inferences are correct? (Choose two.)

  

  A. Automatic management of PGA memory is disabled.

  B. The current PGA size requires the use of a temporary tablespace for sorting operations.

  C. The current PGA size is sufficient and does not require the memory manager to allocate more memory.

  D. PGA size should be increased at least four times its current size for significant improvement in performance and disk space management.

  Correct Answer: BD

• Question 80:

  Examine the parameters set for a database instance: The database supports a mixed workload. Users complain about the increased response time of a few DSS queries. During investigation, you execute the query:

  

  

  Based on the output, which two are possible ways to improve the performance of the queries? (Choose two.)

  A. Enable temporary undo.

  B. Enable Automatic Memory Management.

  C. Increase the number of DBWn processes.

  D. Enable Automatic Shared Memory Management.

  E. Increase the value of the SGA_TARGET parameter.

  F. Increase the value of the PGA_AGGREGATE_TARGET parameter.

  Correct Answer: CE