Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 41:

  You identified a SQL statement with SQL ID 9g485acn2n30m, which is expensive on the resources. To view the execution plan for the statement, you executed the following command:

  SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('9g485acn2n30m',1));

  What would this command accomplish?

  A. It formats and displays the contents of the execution plan for the cursor with the SQL_ID '9g485acn2n30m' from the library cache.
  B. It formats and displays the contents of the execution plan but does not guarantee accuracy if the SQL statement has bind variables.
  C. It formats and displays the contents of the execution plan but does not guarantee that the same execution plan would be executed.
  D. It formats and displays the contents of the execution plan for the cursor with the SQL_ID '9g485acn2n30m' from the DBA_HIST_SQL_PLAN view.
  A. It formats and displays the contents of the execution plan for the cursor with the SQL_ID '9g485acn2n30m' from the library cache.

• Question 42:

  View the Exhibit and examine the partial output obtained when you issued the following query:

  SQL> SELECT name,class,value FROM v$sysstat ORDER BY class;Which two interpretations are correct about the information obtained from this view? (Choose two.)

  

  A. db block changes indicates the number of data blocks that have been changed.
  B. buffer is pinned count indicates the number of buffers that are currently pinned in the database instance.
  C. Usage of CACHE and NOCACHE hints affects statistics for table scans (long tables) and table scans (short tables).
  D. redo log space requests indicates the number of times a there were waits for disk space to be allocated for the redo log entries.
  C. Usage of CACHE and NOCACHE hints affects statistics for table scans (long tables) and table scans (short tables).
  D. redo log space requests indicates the number of times a there were waits for disk space to be allocated for the redo log entries.

• Question 43:

  You work on an online transaction processing (OLTP) database in which the SALES table has 10,000 rows but only four distinct products are sold. View the Exhibit named HIST to check the distribution of values in the table and the histograms on the table.

  

  View the Exhibits named QUERY-1 and QUERY-2 that show details in the V$SQL view for the queries executed on the SALES table.

  

  You check the plan table and notice that both the queries that executed on the SALES table used index range scan. The second query retrieved most of the rows in the table but used index range scan.

  

  Why would the second query use the same plan?

  A. because the plan was bind aware
  B. because the bind peeking never happened
  C. because the OPEN_CURSORS parameter is set to a very low value
  D. because the optimizer did not consider selectivity due to the use of bind variables
  D. because the optimizer did not consider selectivity due to the use of bind variables

• Question 44:

  View the Exhibit.You decide to select the setting option in the Exhibit to choose a baseline to be displayed in the performance page. What would you achieve if the baseline is selected in the performance page?

  

  A. A line appears in the chart that displays the 99th percentile level of the baseline.
  B. The performance page displays session-wise statistics compared with the baseline.
  C. The performance page displays the metric names and corresponding threshold values set to them.
  D. An extra chart appears that shows the performance at the time when the base line was last computed.
  A. A line appears in the chart that displays the 99th percentile level of the baseline.

• Question 45:

  Your system has been upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets (STS) from the previous version that contains important SQL statements. You are not sure about the elapsed time that the SQL statements will have in the new system due to the version change of the optimizer. Which tool provides a comparative report with respect to the elapsed time in both versions of the optimizer?

  A. SQL Access Advisor
  B. SQL Tuning Advisor (STA)
  C. SQL Performance Analyzer (SPA)
  D. Automatic Workload Repository (AWR) compare period report
  C. SQL Performance Analyzer (SPA)

• Question 46:

  Examine the SQL statement given below:

  SQL> EXPLAIN PLAN for SELECT department_name,count(*) FROM hr.employees e, hr.departments d WHERE e.department_id=d.department_id GROUP BY d.department_name;

  To view the execution plan for the statement, you queried plan_table. View the Exhibit and examine the execution plan.Which two statements are true about the execution plan? (Choose two.)

  

  A. The execution plan is displayed for the executed SQL statement.
  B. The EXPLAIN PLAN command does not actually execute the statement.
  C. The row with the id column having a value of 4 is the first step executed in the execution plan.
  D. The row with the id column having a value of 0 is the first step executed in the execution plan.
  B. The EXPLAIN PLAN command does not actually execute the statement.
  C. The row with the id column having a value of 4 is the first step executed in the execution plan.

• Question 47:

  You are working 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. However, 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.Performance is the primary goal of the database because applications are demanding in terms of I/O operations and it should have lower mean recovery time (MTTR) than the current MTTR. Which RAID option would you recommend?

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

• Question 48:

  View the Exhibit that shows your investigation on degraded performance.What would you do? (Choose all that apply.)

  

  A. Shrink the temporary segment.
  B. Keep the large tables in the KEEP pool.
  C. Use free list groups wherever applicable.
  D. Increase the value set to the PGA_AGGREGATE_TARGET parameter.
  E. Switch the segments to Automatic Segment Space Management (ASSM).
  C. Use free list groups wherever applicable.
  E. Switch the segments to Automatic Segment Space Management (ASSM).

• Question 49:

  You are working on an online transaction processing (OLTP) database that runs on a machine having multiple CPUs. The database uses normal file system storage. Examine the following initialization parameters and their respective values for the instance: NAME TYPE VALUE ------------------------------------ ---------- ---------------------- dbwr_io_slaves integer 0 db_writer_processes integer 1 filesystemio_options string NONE disk_asynch_io boolean TRUE

  The applications perform small, random I/Os in which each foreground process reads a data block into the buffer cache for updates and the changed blocks are written in batches by the DBWR process. You noticed free buffer waits events for your database. To solve the problem, you increased the size of the buffer cache.

  But after some time, you notice the same event again.Which three possible actions would you recommend? (Choose three.)

  A. configuring multiple DBWR processes
  B. decreasing the size of the buffer cache
  C. configuring I/O slaves for the DBWR process
  D. configuring instances to make asynchronous I/O available to DBWR
  E. spread data files over multiple disks, controllers, and I/O buses to ensure that there are no hotspots in the I/O subsystem
  A. configuring multiple DBWR processes
  D. configuring instances to make asynchronous I/O available to DBWR
  E. spread data files over multiple disks, controllers, and I/O buses to ensure that there are no hotspots in the I/O subsystem

• Question 50:

  View the Exhibit and examine the content of the V$SGA_RESIZE_OPS view.

  

  You executed the following command to increase the size of the Large Pool:

  SQL> ALTER SYSTEM SET large_pool_size=8M;

  System altered.

  Which component would shrink to increase the size of the Large Pool?

  A. the Java Pool
  B. the Shared Pool
  C. KEEP Buffer Cache
  D. Default Buffer Cache
  D. Default Buffer Cache