Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 21:

  As a DBA, you notice a change in the performance of your database. Which two pieces of tuning- related information can you examine in the alert log to identify possible causes? (Choose two.)

  A. the number of block corruption errors
  B. SQL statements that are consuming maximum resources
  C. the top five sessions that are consuming maximum resources
  D. the instance parameter values that have been changed using ALTER SYSTEM since the last baseline
  A. the number of block corruption errors
  D. the instance parameter values that have been changed using ALTER SYSTEM since the last baseline

• Question 22:

  Which is the correct description of SQL profiling while using SQL Tuning Advisor?

  A. It is a set of recommendations by the optimizer to create new indexes.
  B. It is auxiliary information collected by the optimizer for a SQL statement to eliminate estimation error.
  C. It is a set of recommendations by the optimizer to refresh stale statistics to avoid bad execution plan.
  D. It is a set of recommendations by the optimizer to restructure a SQL statement to avoid bad execution plan.
  B. It is auxiliary information collected by the optimizer for a SQL statement to eliminate estimation error.

• Question 23:

  You are working on an online transaction processing (OLTP) system. The middle-tier applications use connection pooling to connect to the database. Presently, you have a single-node database. The company plans to migrate the database to a RAC environment. Before you move to a RAC environment, you want to test the performance of the SQL statements and the peak workload on the new environment. To accomplish the Database Capture for replay, you identified the peak workload period on the existing system and started the Database Capture. Which client requests to the database can be captured as part of the workload capture? (Choose all that apply.)

  A. flashback query
  B. distributed transactions
  C. logging in and logging out of sessions
  D. all DDL statements having bind variables
  E. direct path load of data from external files
  C. logging in and logging out of sessions
  D. all DDL statements having bind variables

• Question 24:

  Examine the output of the following query:

  SQL> SELECT c.name,a.addr,a.gets,a.misses,a.sleeps,

  2 a.immediate_gets,a.immediate_misses,b.pid

  3 FROM v$latch a, v$latchholder b, v$latchname c

  4 WHERE a.addr = b.laddr(+) and a.latch# = c.latch#

  5 AND c.name LIKE 'andlatch_name%' ORDER BY a.latch#;

  LATCH NAME ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES ------------ -------- -------- ------- ------ -------------- ---------------- shared pool

  20016544 8520540 14112 3137 0 0

  You calculated the Gets-to-Misses ratio, which is .99834, and this ratio is dropping over a period of time.

  Which two actions can improve this? (Choose two.)

  A. reducing hard parses
  B. increasing the size of the shared pool
  C. using only dedicated server connections
  D. setting the CURSOR_SHARING parameter to EXACT
  E. encouraging the use of more literal SQL statements
  A. reducing hard parses
  B. increasing the size of the shared pool

• Question 25:

  You want information in the form of charts for throughput and services that shows baseline values and thresholds for the baseline. How would you achieve this?

  A. By quickly configuring the metric threshold
  B. By computing statistics for the desired baseline
  C. By adding a baseline to the performance page in Oracle Enterprise Manager
  D. By adding a baseline template to the performance page in Oracle Enterprise Manager
  C. By adding a baseline to the performance page in Oracle Enterprise Manager

• Question 26:

  A user session executes a query and the plan for the query is shown in the Exhibit as Plan-1. After verifying with the SQL Access advisor, an index is created on the table in the JOB_ID column and the query is executed again. A new plan is generated against the second query shown in the Exhibit as Plan-2. When explaining the plan for the second query, you observe that the optimizer uses the first plan instead of the second. The following parameters are set for the user session: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE SQLTUNE_CATEGORY=DEFAULT Why does the optimizer not use the second plan?

  

  A. because the second plan is not verified
  B. because the second plan is not part of the SQL plan baseline
  C. because the SQLTUNE_CATEGORY parameter is set to DEFAULT
  D. because the explain plan option in the user session does not allow the new plan because SQLTUNE_CATEGORY parameter is set to DEFAULT
  A. because the second plan is not verified

• Question 27:

  View the Exhibit and analyze the output of the query.What does the high value for the table fetch continued rows statistics indicate?

  

  A. chained rows fetched only
  B. migrated rows fetched only
  C. rows fetched from external tables
  D. both chained and migrated rows fetched
  E. rows fetched from Index Organized Tables (IOT)
  D. both chained and migrated rows fetched

• Question 28:

  Examine the query output shown below:

  SQL> SELECT sum(pins), sum(reloads), sum(reloads)/sum(pins) FROM v$librarycache;

  SUM(PINS) SUM(RELOADS) SUM(RELOADS)/SUM(PINS)

  16479 16000 0.9709327

  Which statement describes the correct interpretation of the reloads-to-pin ratio and the action to be taken?

  A. This ratio is reasonable. However, decrease the shared pool size for better performance.
  B. This ratio is very high and the shared pool size should be increased immediately to reduce this ratio.
  C. This ratio is reasonable. However, this needs monitoring. Increase the shared pool size only if the ratio crosses 1.
  D. This ratio is reasonable. However, this needs monitoring. Increase the shared pool size only if the ratio falls below 0.1.
  B. This ratio is very high and the shared pool size should be increased immediately to reduce this ratio.

• Question 29:

  Examine the output of the following query: SQL> SELECT tablespace_name, tablespace_size, allocated_space, free_space 2 FROM DBA_TEMP_FREE_SPACE; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE ------------------------------ --------------- --------------- ---------- TEMP_L 157286400 144048128 157220864 TEMP 105906176 105906176 0 After some time you executed the same command and observed a different output, as shown below: SQL> SELECT tablespace_name, tablespace_size, allocated_space, free_space 2 FROM DBA_TEMP_FREE_SPACE; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE ------------------------------ --------------- --------------- ---------- TEMP_L 131072 65536 65536 TEMP 105906176 105906176 0

  Why has the output changed?

  A. because checkpoint occurred
  B. because the TEMP_L tablespace was shrunk
  C. because the database instance was restarted
  D. because no sort operations are running currently
  B. because the TEMP_L tablespace was shrunk

• Question 30:

  View the Exhibit that shows your investigation of performance degradation in your database.You also know that there are a few tablespaces using automatic segment-space management in the database.What could be the problem?

  

  A. There are excessive disk sorts.
  B. There are excessive memory sorts.
  C. There is a contention due to free list management.
  D. The tablespace that contains the segments on which waits are occurring is full.
  E. The server process is processing buffers at a rate that is faster than the rate at which the I/O system can return the buffers.
  C. There is a contention due to free list management.