Oracle Oracle Database 1Z0-064 Questions & Answers

• Question 61:

  Examine this list of possible tasks:

  1.

  Ensure that STATISTICS_LEVEL is set to TYPICAL or ALL.

  2.

  Ensure that TIMED_STATISTICS is set to TRUE.

  3.

  Set MAX_DUMP_FILE_SIZE to UNLIMITED and DIAGNOSTIC_DEST to an appropriate destination.

  4.

  Ensure that SQL_TRACE is set to TRUE.

  5.

  Enable tracing at the database instance level by using the DBMS_MONITOR.DATABASE_TRACE_ENABLE procedure.

  6.

  Enable tracing in the required session by using the DBMS_SESSION.SET_SQL_TRACE procedure.

  7.

  Run TKPROF with the EXPLAIN parameter on the output trace file.

  8.

  Run the trcsess utility on the output trace files, and then run TKPROF on the output of the trcsess utility.

  Select the minimum tasks to perform, in the correct order, to generate both a formatted trace file with timing information and an explain plan for each SQL statement for all sessions. (Choose the best answer.)

  A. 1, 2, 5, 8

  B. 1, 3, 6, 7

  C. 2, 4, 5, 8

  D. 1, 3, 4, 5, 6, 7

  E. 1, 2, 4, 8

  Correct Answer: C

• Question 62:

  Which two are prerequisites for running the I/O calibration tool? (Choose two.)

  A. The database must be in MOUNT state.

  B. The database should be opened in restricted mode.

  C. For determining latency time, the STATISTICS_LEVEL parameter must be set to TYPICAL or ALL.

  D. The disks to be tested must be configured to use asynchronous I/O for data files.

  E. The database instance must be started using an SPFILE.

  Correct Answer: AE

• Question 63:

  You are administering a database that supports a mixed workload. Many applications are running on the middle tier that use connection pools to connect to the database instance. Application users perform OLTP operations during the day and another application performs batch job operations at night. You want to measure and prioritize the two workloads.

  Which action would you take to achieve this? (Choose the best answer.)

  A. Create database services for the applications, assign individual sessions created by the applications to consumer groups, and then set a priority.

  B. Assign profiles to users running the batch operations and make sure that a priority is set for resource limits in profiles.

  C. Create database services for the applications and assign different profiles to the sessions to set a relative priority for resource usage.

  D. Create database services for the applications, create a job class associated with the service, batch the jobs, and then create jobs by using the job class.

  Correct Answer: C

• Question 64:

  Users complain about increased response time for queries in your production database that supports an OLTP workload. On investigation, you notice a large number of db file scattered read, latch: cache buffers lru chain, and latch: cache buffers chains wait events:

  Identify three possible reasons for the increased response time. (Choose three.)

  A. too many sort operations being performed

  B. repeated simultaneous access to a block or small number of blocks

  C. the shared pool is inadequately sized

  D. queries not using indexes and performing full table scans

  E. queries repeatedly fetching blocks that are not in the database buffer cache

  F. cursors are closed explicitly after each execution

  Correct Answer: BCE

• Question 65:

  You want to capture the performance of your database during the last ten days of the first quarter of the current financial year, so that you can compare this performance against the remaining quarter ends of the current financial year.

  Which method should you use? (Choose the best answer.)

  A. Create a static baseline that can be used with AWR compare reports.

  B. Create a new moving window baseline and enable adaptive thresholds for relevant metrics.

  C. Use a repeating baseline template to create and drop baselines based on a repeating time schedule and set adaptive thresholds at a high significance level.

  D. Use fixed baseline templates to create a new moving window baseline and set relevant warning alerts that are computed as a percentage multiple of the maximum value observed for the data in the moving window baseline.

  Correct Answer: D

• Question 66:

  Your database supports multiple applications. The applications run on the middle tier and use connection pooling for connecting to the database.

  You notice that the sessions created by the applications are competing for resources. You want to statistically measure the workload and set priorities.

  What action must you perform to achieve this? (Choose the best answer.)

  A. Create services for the applications and set a relative priority by assigning them to application users and using the DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE procedure to trace the services.

  B. Create services for the applications and set a relative priority by assigning them to application users and using the DBMS_MONITOR.SESSION_TRACE_ENABLE procedure to trace the services.

  C. Create services for the applications and set the relative priority of services within an instance by mapping the services directly to consumer groups.

  D. Create services for the applications and set a relative priority by assigning them to application users.

  Correct Answer: A

• Question 67:

  You plan to upgrade your production database from Oracle Database 11g to 12c and also to introduce new objects to the database. You also want to upgrade the hardware. You have already created a test system with the upgrades to be made to the production database. As part of the testing, you want to:

  analyze and compare the overall database workload with concurrency and transaction characteristics find SQL statements that might get regressed because of the upgrade analyze execution plans for SQL statements for which performance might get regressed analyze the impact of new schema objects on database performance

  Which two tools would you recommend to achieve the objective? (Choose two.)

  A. Database Replay

  B. SQL Tuning Advisor

  C. SQL Access Advisor

  D. Automatic Database Diagnostic Monitor (ADDM) compare periods report

  E. SQL Performance Analyzer

  F. Automatic Workload Repository (AWR) compare periods report

  Correct Answer: BE

• Question 68:

  You have been asked to assess if using column store compression (previously known as hybrid columnar compression or HCC) would help improve the performance of queries on some large tables.

  Which three aspects should you consider before you choose this compression method? (Choose three.)

  A. Check whether direct path load operations are used to insert rows in the table.

  B. Check whether the table is frequently queried using full table scans as column store compression only minimizes I/O during full table scans.

  C. Check whether the table is frequently updated because it will have overhead for insert and update operations.

  D. Check whether the table has LOB columns as it will minimize I/O for the queries.

  E. Check whether the table blocks are sparsely populated as this will defragment the blocks.

  Correct Answer: ABD

• Question 69:

  You define the warning threshold for the tablespace usage metric for the USERS tablespace to be 60% and the critical threshold to be 80%.

  Which two sources should you check for the alert information when either the warning or the critical threshold is exceeded? (Choose two.)

  A. the alert log

  B. Oracle Enterprise Manager Cloud Control

  C. DBA_ALERT_HISTORY

  D. DBA_OUTSTANDING_ALERTS

  E. DBA_ACTIVE_SESSION_HISTORY

  F. DBA_THRESHOLDS

  Correct Answer: AF

• Question 70:

  To investigate the slow response time of queries on the TRANS table, you gathered the table statistics and executed the query:

  

  The table is stored in a tablespace that has Automatic Segment Space Management (ASSM) enabled. The tablespace is created with a standard block size of 8192 bytes.

  Which three can be reasons for the slow response time of the queries? (Choose three.)

  A. Row size is too large to fit into a single block during insert operations.

  B. Row moves from one data block to another data block because the row grows too large to fit in the original block.

  C. The table is subject to frequent insert, update, and delete DML activity leading to sparsely populated blocks.

  D. The value of PCTUSED is set to a value lower than the default, causing row chaining.

  E. The value of PCTFREE is set to a value lower than the default, causing row chaining.

  Correct Answer: ABD