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