[Free] Download New Updated (December) Oracle 1Z0-054 Exam Questions 11-20

Ensurepass

QUESTION 11

Which three statements are true about Statspack usage? (Choose three.)

 

A.

If both Statspack and AWR are used, they cannot be scheduled to capture snapshots at the same time.

B.

Time Model data cannot be captured by Statspack unless the STATISTICS_LEVEL parameter is set to ALL.

C.

Both Statspack and AWR can be used on the same database only if the Database Diagnostics pack is present.

D.

Statspack can be used to generate a report for a specific instance as well as a specific SQL statement in an instance.

E.

For best performance when running the performance reports, optimizer statistics should be gathered on the Statspack schema.

 

Correct Answer: CDE

 

 

QUESTION 12

A user, SCOTT, complains that his database session, SID 249, is extremely slow in processing transactions. You check the ADDM report and do not find any overall database performance issues.

 

You decide to check the wait events, specifically in SCOTT’s session and issue the following command:

 

SQL>SELECT wait_class_id, wait_class, total_waits, time_waited

 

FROM v$session_wait_class

 

WHERE sid = 249;

 

View Exhibit1 and examine the output.

 

clip_image002

 

You note that the APPLICATION wait class is the second most expensive wait class after the IDLE wait class.

 

You issue the following query to identify individual waits in the APPLICATION wait class:

 

SQL>select event, total_waits, time_waited

 

from v$system_event e, v$event_name n

 

where n.event_id = e.event_id

 

and wait_class_id = 4217450380;

 

View Exhibit2 and examine the output.

 

clip_image004

 

Which view would you examine next to pinpoint the problem that is causing this performance issue?

 

A.

DBA_HIST_SESSMETRIC_HISTORY – to find the highest value metrics in the session.

B.

V$SQLAREA statistics – to find the SQL statements with the highest number of executions.

C.

V$SESS_TIME_MODEL – to identify whether the SQL statements in the session werespending more of execution time in parsing or waiting for the CPU.

D.

V$EVENT_HISTOGRAM – to determine whether the TIME_WAITED is evenly distributed across the occurrences of the wait or whether some waits for the events were very long whilst others were short indicating more erratic response times.

 

Correct Answer: D

 

 

QUESTION 13

View the Exhibit and examine the output for the WRC command-line utility that you executed to estimate the number of replay clients.Identify three prerequisites for completing the replay. (Choose three.)

 

clip_image006

< td style="border-top-style: none; border-left-style: none; background: white; border-bottom-style: none; padding-bottom: 0cm; padding-top: 0cm; border-right-style: none; padding-left: 0cm; padding-right: 0cm" valign="top" width="26">

C.

A.

The replay database should be in the RESTRICTED mode.

B.

You need to start at least 21 replay clients on six CPUs to start the replay.

The replay directory must have all the replay files that have been preprocessed.

D.

The user should be able to use the workload replay package and should have the DBA role.

E.

The replay options for think_time_scale and connect_time_scale must be set to 100 each.

 

Correct Answer: BCD

 

 

QUESTION 14

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 data distribution in the table and the histograms on the table.

 

clip_image008

 

View the Exhibit named QUERY-1 that shows details in the V$SQL view for the query executed on the SALES table having product id 1.

 

clip_image010

 

Further, you query the SALES table thrice more in the following order:

 

clip_image012query rows having product ID 4

clip_image012[1]query rows having product ID 2

clip_image012[2]query rows having product ID 4

 

View the Exhibit named QUERY-n to see the details about these queries.

 

clip_image014

 

What do you infer from this?

 

A.

The second plan was created because the first plan was aged out.

B.

The CURSOR_SHARING parameter value was changed to EXACT after the second query was executed.

C.

The third plan was created because the first plan was aged out and the second plan had different selectivity.

D.

The first two executions used the same plan because at parse time the optimizer did not consider selectivity as the cursor was not yet considered bind-aware.

 

Correct Answer: D

 

 

 

 

 

 

 

QUESTION 15

The users in your online transaction processing (OLTP) environment complain that the query response time has increased considerably. You checked the Program Global Area (PGA)- related information in the AWR report to investigate it further.

 

View the Exhibit named PGAHIT and note the PGA cache-hit percentage.

 

clip_image016

 

View the Exhibit named PGAADV and analyze the content.

 

clip_image018

 

If you want to increase the size of PGA, you have sufficient memory available in the system. What would be the correct decision?

 

A.

Double the size of PGA.

B.

Increase the size of PGA to 120 MB.

C.

Increase the size of PGA to 160 MB or more.

D.

Leave the PGA at its current size and investigate other possible causes.

 

Correct Answer: C

 

 

QUESTION 16

You plan to collect statistics for an important table in your database to help improved query performance. You are uncertain about the optimizer behavior after the statistics have been collected because currently there are no statistics for indexes or partitions on the table. You wish to test the performance effect of the newly collected statistics. How would you prevent the optimizer from using the newly collected statistics for other sessions immediately after statistics collection?

 

A.

Set the PUBLISH preference to FALSE for the table.

B.

Set the NO_VALIDATE preference to TRUE for the table.

C.

Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.

D.

Set the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter to FALSE for the session.

E.

Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE for the session.

 

Correct Answer: A

 

 

QUESTION 17

The Service-Level Agreement for the production database includes a clause that the database down time should not be more than 15 minutes.Which two details would you examine in the alert log to diagnose whether your database meets this requirement? (Choose two.)

 

A.

log switch frequency

B.

data file recovery time

C.

instance recovery time

D.

deadlock and timeout errors

E.

the FAST_START_MTTR_TARGET parameter setting

 

Correct Answer: CE

 

 

QUESTION 18

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

 

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

 

Correct Answer: AB

 

QUESTION 19

You are working on a single node database. The storage for the database was recently upgraded tonew, 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.

 

Correct Answer: D

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 20

View the Exhibit. Examine the query on the v$servicemetric view and the output.You wish to define a service-level threshold based on wall-clock time to monitor the performance of the service. Which metric should be used to set the appropriate threshold?

 

clip_image020

 

A.

DELTA

B.

CPUPERCALL

C.

CALLSPERSEC

D.

DBTIMEPERSEC

E.

DBTIMEPERCALL

 

Correct Answer: E

 

Free VCE & PDF File for Oracle 1Z0-054 Real Exam

Instant Access to Free VCE Files: CompTIA | VMware | SAP …
Instant Access to Free PDF Files: CompTIA | VMware | SAP …

This entry was posted in 1Z0-054 Exam Questions (December) and tagged , , , , , , . Bookmark the permalink.