1z0-054 | A Review Of Simulation 1z0-054 exam dumps


Q61. View the Exhibit and examine the details of the Top 5 Timed Events from an Automatic Workload Repository (AWR) report.What can be the three possible causes for the latch-related wait events? (Choose three.)

Exhibit:

A. Shared pool size is too small.

B. Cursors are not being shared.

C. There are frequent logons/logoffs.

D. A large number of COMMITs are being performed.

E. A large number of data definition language (DDL) and query statements are being executed simultaneously by different sessions.

Answer: ABC

Q62. 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

Answer: A

Q63. You are working on a single node database. The storage for the database was recently upgraded to new, faster hard disks and new disk controllers. You ran the I/O calibration procedure to check the I/O metrics for the upgraded storage. The details for the execution of the I/O calibration procedure are given below:

SQL>DECLARE

2 l_latency INTEGER;

3 l_iops INTEGER;

4 l_mbps INTEGER;

5 BEGIN

6 dbms_resource_manager.calibrate_io (1,10,l_iops,l_mbps,l_latency);

7 END;/

You queried the DBA_RSRC_IO_CALIBRATE data dictionary view to check the results of the I/O calibration. The output for the query is given as follows:

SQL> SELECT max_iops, max_mbps, max_pmbps, latency FROM DBA_RSRC_IO_CALIBRATE;

MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY

----------      ----------       ---------- ----------

137                           12                    6             64

What do you infer from the output?

A. The actual latency is 10 milliseconds for one disk.

B. The maximum sustainable I/O for a single process is 12 MB per second.

C. The system can sustain a maximum of 137 I/O operations per second with a throughput of 6 MB per second.

D. The system can sustain a maximum of 137 I/O operations per second with a throughput of 12 MB per second.

Answer: D

Q64. 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

Answer: C

Q65. 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.)

Exhibit:

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.

Answer: BC

Q66. 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.

Answer: C

Q67. 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 '%latch_name%' ORDER BY a.latch#;

LATCH NAME       ADDR      GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

        ------------         --------      --------      -------        ------                --------------                 ----------------

shared pool      20016544   8520540    14112       3137                            0                              0

Which two conclusions can you draw from this? (Choose two.)

A. The latch was requested in no wait mode.

B. The latch was requested in willing-to-wait mode.

C. The shared pool operations to allocate and free memory in it caused the misses.

D. Automatic Memory Management is not enabled because the IMMEDIATE_GETS and IMMEDIATE_MISSES columns have zero in them.

Answer: BC

Q68. You find that in the Top 5 Timed Events section of the Automatic Workload Repository (AWR) report, the wait event buffer busy waits shows significantly high values.The database uses only non-ASSM locally managed tablespaces.On further investigation, you find that the contention is on data blocks.

Which option would you consider first to decrease the wait event values on a long-term basis?

A. decreasing PCTUSED

B. decreasing PCTFREE

C. increasing the number of DBWn processes

D. using automatic segment space management (ASSM)

E. increasing db_buffer_cache based on the V$DB_CACHE_ADVICE recommendation

Answer: D

Q69. You are working as a DBA for a DSS system, on which applications primarily perform large sequential I/Os. The company wants to upgrade the storage used by the database to faster hard disks.

What other factors would you consider before giving the go-ahead for the new hard disks? (Choose all that apply.)

A. the sizing of PGA to meet the faster data access

B. the sizing of buffer cache to meet the faster data access

C. the time it takes for a submitted I/O request to be serviced by the storage

D. the capacity of the I/O channel that connects the server to the storage array

Answer: CD

Q70. Examine the output of the query given below:

SQL> SELECT mutex_type, location, sum(gets), sum(sleeps)

FROM v$mutex_sleep_history

GROUP BY mutex_type, location;

MUTEX_TYPE LOCATION SUM(GETS) SUM(SLEEPS)

-------------- ------------------------------------- ---------- -----------

Library Cache kglhdgn1 62 8669586 4538

Library Cache kglget2 2 2021618 24

Cursor Stat kkocsStoreBindAwareStats [KKSSTALOC8] 2975 1

Cursor Pin kkslce [KKSCHLPIN2] 666831 678

Library Cache kgllkdl1 85 3369224 110

Library Cache kglpnal1 90 224199 13

Library Cache kglic1 49 42068 10

Library Cache kglpin1 4 9620087 374

Library Cache kglpndl1 95 2065089 79

9 rows selected.

Which statement is true?

A. Each row in the output represents a SQL statement that had to wait for mutexes.

B. The Cursor Stat and Cursor Pin SLEEPS indicate that the CURSOR_SHARING parameter is set to EXACT.

C. The GETS column shows the number of times a mutex/location was requested by the requesting session while being held by the blocking session.

D. The sum of numbers in the GETS and SLEEPS columns indicates the number of times a mutex/location was requested by the requesting session while being held by the blocking session.

Answer: C