Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 61:

  You queried V$SYSTEM_EVENT in your online transaction processing (OLTP) environment to check the following wait events: Event Total Seconds Total Average Wait Name Waits Waiting Timeouts (in secs) ------------------------------ ------------ -------- ------------ --------- direct path read temp 18,274 11,312 16,282 .619 direct path write temp 3,251 416 25 .127

  What do these wait events indicate?

  A. The DBWn processes are not creating free buffers fast enough.
  B. The buffer cache is too small and block replacement is excessive.
  C. The aggressive checkpointing policy is causing too many writes to disk.
  D. The work areas are too large to fit in memory and are, therefore, written to disk.
  D. The work areas are too large to fit in memory and are, therefore, written to disk.

• Question 62:

  You query the V$SYSSTAT view and notice a significantly high value for the redo log space requests statistic.Which three components would you consider for further investigation based on this information? (Choose three.)

  A. LGWR
  B. checkpoints
  C. archiver activity
  D. size of the log buffer
  E. size of the redo log files
  B. checkpoints
  C. archiver activity
  E. size of the redo log files

• Question 63:

  View the Exhibit and examine the Automatic Database Diagnostic Monitor (ADDM) performance analysis. Identify the correct description of the Impact(%) column in the Exhibit.

  

  A. It is the time consumed by the corresponding issue compared with the database time for the analysis period.
  B. It is the time consumed by the corresponding issue compared with the response time for the analysis period.
  C. It is the workload volume by the corresponding issue compared with the SYSTEM_MOVING_WINDOW baseline for the analysis period.
  D. It is the resource consumed by the corresponding issue compared with the SYSTEM_MOVING_WINDOW baseline for the analysis period.
  A. It is the time consumed by the corresponding issue compared with the database time for the analysis period.

• Question 64:

  A user, SCOTT, complains that his database session, SID 249, is extremely slow in processing transactions. You check the ADDM report and do not find any overall database performance issues.

  You decide to check the wait events, specifically in SCOTT's session and issue the following command:

  SQL>SELECT wait_class_id, wait_class, total_waits, time_waited FROM v$session_wait_class

  WHERE sid = 249;

  View Exhibit1 and examine the output.

  

  You note that the APPLICATION wait class is the second most expensive wait class after the IDLE wait class.

  You issue the following query to identify individual waits in the APPLICATION wait class:

  SQL>select event, total_waits, time_waited

  from v$system_event e, v$event_name n

  where n.event_id = e.event_id

  and wait_class_id = 4217450380;

  View Exhibit2 and examine the output.

  

  Which view would you examine next to pinpoint the problem that is causing this performance issue?

  A. DBA_HIST_SESSMETRIC_HISTORY - to find the highest value metrics in the session
  B. V$SQLAREA statistics - to find the SQL statements with the highest number of executions
  C. V$SESS_TIME_MODEL - to identify whether the SQL statements in the session were spending more of execution time in parsing or waiting for the CPU
  D. V$EVENT_HISTOGRAM - to determine whether the TIME_WAITED is evenly distributed across the occurrences of the wait or whether some waits for the events were very long whilst others were short indicating more erratic response times.
  D. V$EVENT_HISTOGRAM - to determine whether the TIME_WAITED is evenly distributed across the occurrences of the wait or whether some waits for the events were very long whilst others were short indicating more erratic response times.

• Question 65:

  You are asked to implement Automatic Memory Management (AMM) and you added two initialization parameters to do this. The following are the some of the parameter settings: MEMORY_MAX_TARGET=600M SGA_MAX_SIZE=500M MEMORY_TARGET=600M OPEN_CURSORS=300 SGA_TARGET=300M PROCESSES=150 STATASTICS_LEVEL=BASIC PGA_AGGREGATE_TARGET=0

  When you tried to start the database instance with these parameter settings, you received the following error: SQL> startup ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings, see alert log for more information You modified the parameter settings as shown below and the database instance started successfully. MEMORY_MAX_TARGET=600M SGA_MAX_SIZE=400M MEMORY_TARGET=550M OPEN_CURSORS=400 SGA_TARGET=300M PROCESSES=200 STATASTICS_LEVEL=TYPICAL PGA_AGGREGATE_TARGET=100M

  Analyze the parameter settings and identify why the instance failed to start the first time?

  A. because the STATISTICS_LEVEL parameter was set to BASIC
  B. because the PGA_AGGREGATE_TARGET parameter was set to zero
  C. because both the SGA_TARGET and MEMORY_TARGET parameters were set
  D. because MEMORY_TARGET and MEMORY_MAX_TARGET parameters were equal
  E. because the PROCESSES parameter was set to a value less than the minimum required
  A. because the STATISTICS_LEVEL parameter was set to BASIC

• Question 66:

  Examine the following information from the AWR report: Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Call Time -------------------------------------- ------------ ----------- --------- CPU time 559 88.80 log file parallel write 2,181 28 4.42 SQL*Net more data from client 516,611 27 4.24 db file parallel write 13,383 13 2.04 db file sequential read 563 2 .27

  Which could cause waits on the db file sequential read event?

  A. too many full table scans
  B. too large a database buffer cache
  C. too many waits occurring while reading multiple blocks synchronously
  D. too many index range scans occurring which refers to blocks not in the database buffer cache
  D. too many index range scans occurring which refers to blocks not in the database buffer cache

• Question 67:

  Which two statements are true about the database buffer cache? (Choose two.)

  A. A buffer can be written to by many processes at the same time.
  B. A buffer containing a block that is identical to the block on disk is called free buffer.
  C. A buffer containing a block that is being accessed by a process is called dirty buffer.
  D. A buffer available to be overwritten with a different database block at any time is called a dirty buffer.
  E. A buffer can contain a different version of a block that is available in a different buffer of the same cache.
  B. A buffer containing a block that is identical to the block on disk is called free buffer.
  E. A buffer can contain a different version of a block that is available in a different buffer of the same cache.

• Question 68:

  You receive a recommendation for a SQL statement through the automatic SQL tuning process that suggests implementing a SQL profile. You implement the SQL profile. The tables associated with the SQL statement grow, and indexes are created and dropped on these tables. What would happen to the SQL profile that is created for the SQL statement?

  A. It becomes outdated immediately after the tables change.
  B. It is maintained automatically along with the changes in the tables.
  C. The profile will be used for the changed plans of the SQL statement.
  D. The plan for the SQL statement is frozen until it is regenerated by the SQL Tuning Advisor.
  C. The profile will be used for the changed plans of the SQL statement.

• Question 69:

  A user in a session executed the following SQL statement to set the optimizer mode:

  ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS

  What impact would it have on the goal of the optimizer for that session? (Choose all that apply.)

  A. Statement level OPTIMIZER_MODE hints take precedence over the session-level setting.
  B. The OPTIMIZER_MODE parameter set at instance level takes precedence over the session- level value.
  C. The optimizer uses a cost-based approach, regardless of the presence of statistics; it optimizes with a goal of best response time.
  D. The optimizer uses a cost-based approach for all SQL statements in the session, regardless of the presence of statistics; it optimizes with a goal of best throughput.
  A. Statement level OPTIMIZER_MODE hints take precedence over the session-level setting.
  D. The optimizer uses a cost-based approach for all SQL statements in the session, regardless of the presence of statistics; it optimizes with a goal of best throughput.

• Question 70:

  You work as a database administrator to manage an online transaction processing (OLTP) system.The application code was recently tuned for optimum performance and users acknowledged the performance improvement. However, the users are now again complaining that the application response time has gone down considerably. You used a few queries to investigate the problem and a couple of important queries are shown in the Exhibit. View the Exhibit and analyze the output shown. Which actions would improve the performance? (Choose all that apply.)

  

  A. increasing the number database writers
  B. decreasing the number of database writers
  C. increasing the size of the database buffer cache
  D. decreasing the size of the database buffer cache
  E. decreasing the value set to the FAST_START_MTTR_TARGET parameter
  A. increasing the number database writers
  C. increasing the size of the database buffer cache