Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 101:

  The database application developers are planning to make some major schema changes such as creating new indexes and materialized views. They want to check the net impact of these changes on the workload performance. This activity has to be performed in the production database, so they want only the query part of the data manipulation language (DML) statements to be executed so that the side effects to the database or user data can be prevented. What should they use to achieve this?

  A. Database Replay
  B. SQL Tuning Advisor
  C. SQL Access Advisor
  D. SQL Performance Analyzer
  D. SQL Performance Analyzer

• Question 102:

  Which three factors influence the optimizer's behavior while choosing an optimization approach and goal for a SQL statement? (Choose three.)

  A. parsing of a SQL statement
  B. operating system (OS) statistics
  C. object statistics in the data dictionary
  D. the OPTIMIZER_MODE initialization parameter
  E. optimizer SQL hints for changing the query optimizer goal
  C. object statistics in the data dictionary
  D. the OPTIMIZER_MODE initialization parameter
  E. optimizer SQL hints for changing the query optimizer goal

• Question 103:

  View the Exhibit and examine the statistics of a SQL statement.Which two conclusions can you draw from the statistics? (Choose two.)

  

  A. The SQL statement has high logical I/O.
  B. The SQL statement has high CPU usage.
  C. The SQL statement has high physical I/O.
  D. The SQL statement has long sort operations.
  A. The SQL statement has high logical I/O.
  B. The SQL statement has high CPU usage.

• Question 104:

  View the Exhibit and examine the partial output from the query on the v$file_histogram view: Which statement correctly interprets the output displayed in the Exhibit?

  

  A. The database waited 58 times in a time interval of two milliseconds for FILE# 1.
  B. The database experienced 58 waits of two milliseconds duration each for FILE# 1.
  C. The database performed 58 synchronous single block reads in a time interval of two milliseconds for FILE# 1.
  D. The database experienced 58 waits of duration less than two milliseconds each that are not included in any smaller bucket.
  D. The database experienced 58 waits of duration less than two milliseconds each that are not included in any smaller bucket.

• Question 105:

  In your organization the Oracle Database 11g installation includes the Oracle Diagnostic Pack.In your database, the STATISTICS_LEVEL and CONTROL_MANAGEMENT_PACK_ACCESS parameters, and the snapshot interval and retention period have been set to default values.Two users of your database have complained that a particular job or session is very slow for a transient interval of about 10 minutes during the day when the rest of the instance is performing as usual.Where would you begin the investigation of this performance issue?

  A. ASH
  B. ADDM
  C. Alert log
  D. Load Profile in an AWR Compare Period report
  A. ASH

• Question 106:

  A batch workload that historically completed in the maintenance window between 10:00 PM and midnight is currently showing poor performance and completing at 2 AM. To help in the diagnosis of the performance degradation, the senior DBA in your organization asks you to execute the awrddrpt.sql script to produce a Compare Periods report. Which two statements are true about the report produced by this script? (Choose two.)

  A. It is refreshed automatically based on the moving window baseline.
  B. It compares details between any two selected time periods of the same duration.
  C. It normalizes the statistics by the amount of time spent on the database for each time period.
  D. It compares details between two consecutive time periods of the same or different durations and is refreshed every 60 minutes.
  B. It compares details between any two selected time periods of the same duration.
  C. It normalizes the statistics by the amount of time spent on the database for each time period.

• Question 107:

  The users in your online transaction processing (OLTP) environment complain that the query response time has increased considerably. You checked the Program Global Area (PGA)- related information in the AWR report to investigate it further. View the Exhibit named PGAHIT and note the PGA cache-hit percentage.

  

  View the Exhibit named PGAADV and analyze the content.

  

  If you want to increase the size of PGA, you have sufficient memory available in the system. What would be the correct decision?

  A. Double the size of PGA.
  B. Increase the size of PGA to 120 MB.
  C. Increase the size of PGA to 160 MB or more.
  D. Leave the PGA at its current size and investigate other possible causes.
  C. Increase the size of PGA to 160 MB or more.

• Question 108:

  After running SQL Performance Analyzer (SPA), you observe a few regressed SQL statements in the SPA output. Identify the two actions that you would suggest for these regressed SQL statements. (Choose two.)

  A. Running SQL Access Advisor
  B. Adding them to SQL Plan Baseline
  C. Submitting them to SQL Tuning Advisor
  D. Running Automatic Database Diagnostic Monitor (ADDM)
  B. Adding them to SQL Plan Baseline
  C. Submitting them to SQL Tuning Advisor

• Question 109:

  You are managing an online transaction processing (OLTP) system. The database is supporting a large number of applications using connection pools to connect to the database.Which method would you use for performance management?

  A. Service aggregation for action within application module
  B. Monitoring individual sessions by querying V$SESSION_LONGOPS
  C. Monitoring individual sessions in an application by querying V$SESSION
  D. Mapping the sessions for an application to one resource consumer group
  A. Service aggregation for action within application module

• Question 110:

  View the Exhibit and examine the content of the V$PGA_TARGET_ADVICE_HISTOGRAM view. What would you infer from this?

  

  A. If the PGA size is set to 4 MB, then all the work areas would execute in optimal mode.
  B. If the PGA size is doubled, then all the work areas that are less than 2 MB would execute in optimal mode.
  C. If the PGA size is set to 32 MB, then the number of work areas would reduce. So it is the appropriate size for PGA.
  D. If the size of the Program Global Area (PGA) is set to more than 64 MB, then all the work areas would execute in optimal mode. So it is the appropriate size for PGA.
  B. If the PGA size is doubled, then all the work areas that are less than 2 MB would execute in optimal mode.