Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 81:

  Which statement is correct about server-generated alerts?

  A. All server-generated alerts are based on threshold levels.
  B. Alerts are not issued for locally managed tablespaces in read-only mode.
  C. The STATISTIC_LEVEL initialization parameter must be set to ALL to enable server- generated alerts.
  D. Alerts for both locally managed tablespaces and dictionary-managed tablespaces are server generated.
  B. Alerts are not issued for locally managed tablespaces in read-only mode.

• Question 82:

  You find that the OS utilization is over 40% in your database server. Examine the following set of performance monitoring-related activities:

  1.

  Investigate the operating system for network transfers, paging, and swapping.

  2.

  Check the SQL and the transactions submitting the SQL for optimal execution.

  3.

  Investigate the top SQL by CPU utilization.

  4.

  Consider rescheduling some work to off-peak hours.

  5.

  Check whether there are any nondatabase jobs consuming CPU time on the machine.

  What is the correct sequence in which the above activities should be performed to diagnose and reduce potential performance issues?

  A. 1,5,3,2,4
  B. 1,2,3,4,5
  C. 2,4,5,3,1
  D. 4,5,3,1,2
  A. 1,5,3,2,4

• Question 83:

  While reviewing the Top 5 Timed Events section in the Automatic Workload Repository report, you notice that the db file scattered read and db file sequential read events appear in the list. Which two reasons could have caused these wait events? (Choose two.)

  A. SQL statements performing frequent full table scans
  B. tablespaces using manual segment space management
  C. SQL statements performing frequent fast full index scans
  D. excessive writes due to an aggressive checkpointing policy
  A. SQL statements performing frequent full table scans
  C. SQL statements performing frequent fast full index scans

• Question 84:

  The Automatic Workload Repository (AWR) snapshot interval is set to 1 hour. A user of your database notifies you that the system was slow between 10:00 PM and 10:10 PM, but the Automatic Database Diagnostic Management (ADDM) analysis for the time period between 10:00 PM and 11:00 PM does not show this performance problem.What would you suggest to analyze this performance problem for the user?

  A. SQL Tuning Advisor
  B. SQL Performance Analyzer
  C. Active Session History report
  D. AWR Compare Periods report
  C. Active Session History report

• Question 85:

  In which case is the database buffer cache NOT used?

  A. when physical I/Os are performed to read from data files
  B. when the database writer process writes to the SYSTEM tablespace
  C. when the server process writes sort data to the temporary tablespace
  D. when the database writer process writes undo data to the undo tablespace
  C. when the server process writes sort data to the temporary tablespace

• Question 86:

  Examine the following block that executes a SQL Performance Analyzer task:

  begin dbms_sqlpa.execute_analysis_task(task_name => 'SPA_SKD4', execution_type => 'TEST EXECUTE',

  execution_name => 'before');

  end;

  Which two statements are true about the execution? (Choose two.)

  A. It generates execution plans and execution statistics.
  B. It executes only the query part of the DML statements.
  C. The test plans are generated and added to plan baseline.
  D. The test plans are executed, but not generated if they are available in the plan baseline.
  A. It generates execution plans and execution statistics.
  B. It executes only the query part of the DML statements.

• Question 87:

  You work as a DBA and you have the responsibility of managing a large online transaction processing (OLTP) system. You used the following query during the

  performance tuning activity:

  SQL> SELECT (1-((phy.value-phyd.value) / (cur.value + con.value))) * 100 2 "Cache Hit ratio"

  3 FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd 4 WHERE cur.name = 'db block gets'

  5 AND con.name = 'consistent gets'

  6 AND phy.name = 'physical reads'

  7 AND phyd.name = 'physical reads direct';

  Cache Hit Ratio

  98.43

  Which understanding of this ratio is correct?

  A. 98.43% of times the requests have found free buffers.
  B. 98.43% of the total number of requests used the buffer cache.
  C. 98.43% of times the requests were satisfied by performing physical I/Os.
  D. 98.43% of times the requests have found the required data blocks in the buffer cache.
  D. 98.43% of times the requests have found the required data blocks in the buffer cache.

• Question 88:

  Examine the values for the following initialization parameters:

  STATISTICS_LEVEL = TYPICAL TIMED_STATISTICS = true You are managing an online transaction processing (OLTP) system. Application users notice that some queries have poor response time. You determine that queries from session ID 27, serial number 60, for user OE are heavy-resource

  consumers. To investigate further, you enabled tracing for the session by executing the following command: SQL> EXECUTE dbms_system.set_sql_trace_in_session (27, 60, true); Which statement is true?

  A. The tkprof output file for the trace generated would display only the timed statistics for the SQL statements.
  B. The tkprof output file for the trace generated would display statistics for all the sessions created by the user OE.
  C. The tkprof output file for the trace generated would display bind variable values if bind variables are used in the queries executed in the session.
  D. The tkprof output file for the trace generated would not display bind variable values if bind variables are used in the queries executed in the session.
  D. The tkprof output file for the trace generated would not display bind variable values if bind variables are used in the queries executed in the session.

• Question 89:

  You work as a DBA in ABC Corp. You are working on an online transaction processing (OLTP) database. The database size has grown to 100 GB. The database will support new applications and more users will be added. Presently, the database uses normal file system storage. Before the new applications are added, you want to move the storage to RAID. You are in consultation with the OS Administrator for finalizing the RAID option for the storage. You have the following requirements for the database:

  1) Less mean time to recover (MTTR) 2) Striped for performance 3) Mirrored for safety

  Which RAID option would you recommend?

  A. RAID level 0
  B. RAID level 1
  C. RAID level 0 over 1(1+0)
  D. RAID level 1 over 0(0+1)
  C. RAID level 0 over 1(1+0)

• Question 90:

  View Exhibit1 to examine the description of the CUSTOMERS table.

  

  You observed that optimizer selectivity is not accurate when the CUST_STATE_PROVINCE and COUNTRY_ID columns are used together in the WHERE clause of a query. View Exhibit2 to examine the query execution plan and the commands executed to gather the statistics.

  

  The optimizer predicts that 20 rows will be processed rather than the 3,341 rows, which is the actual number of rows returned from the table. What can you do to make the optimizer detect the actual number of rows?

  A. Set the STATISTICS_LEVEL parameter to ALL.
  B. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE.
  C. Create extended statistics for the CUST_STATE_PROVINCE and COUNTRY_ID columns.
  D. Increase the STALE_PERCENT value for the CUSTOMERS table by using the DBMS_STATS.SET_TABLE_PREFS procedure.
  C. Create extended statistics for the CUST_STATE_PROVINCE and COUNTRY_ID columns.