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

Ensurepass

QUESTION 81

A user in a session executed the following SQL statement to set the optimizer mode:

 

ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS

 

What impact would it have on the goal of the optimizer for that session? (Choose all that apply.)

 

A.

Statement level OPTIMIZER_MODE hints take precedence over the session-level setting.

B.

The OPTIMIZER_MODE parameter set at instance level takes precedence over the session-level value.

C.

The optimizer uses a cost-based approach, regardless of the presence of statistics; it optimizes with a goal of best response time.

D.

The optimizer uses a cost-based approach for all SQL statements in the session, regardless of the presence of statistics; it optimizes with a goal of best throughput.

 

Correct Answer: AD

 

 

 

 

 

 

 

 

 

 

QUESTION 82

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

 

clip_image002

 

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.

 

Correct Answer: CD

 

 

QUESTION 83

You work for ABC Pvt Ltd. The company has recently upgraded one of its development databases to Oracle Database 11g from Oracle Database 10g.

 

You noticed that the System Global Area (SGA) is undersized as shown in the Exhibit.

 

clip_image004

 

To investigate further, you checked the related parameters as shown below:

SQL> show parameter sga_max_size

 

NAME TYPE VALUE

 

—————————– ————— —————–

 

sga_max_size big integer 500M

 

SQL> show parameter target

 

NAME TYPE VALUE

 

—————————– ————— ——————

 

archive_lag_target integer 0

 

db_flashback_retention_target integer 1440

 

fast_start_io_target integer 0

 

fast_start_mttr_target integer 0

 

memory_max_target big integer 0

 

memory_target big integer 0

 

pga_aggregate_target big integer 384M

 

sga_target big integer 384M

 

Which is the best solution that you would recommend?

 

A.

Increase the value of the SGA_MAX_SIZE parameter.

B.

Set MEMORY_MAX_TARGET and MEMORY_TARGET parameters.

C.

Reduce the value of the PGA_AGGREGATE_TARGET parameter.

D.

Increase the value of both SGA_TARGET and PGA_AGGREGATE_TARGET parameters.

E.

Diagnose further to identify which component in SGA is undersized and then resize it.

 

Correct Answer: B

 

 

QUESTION 84

You are managing an online transaction processing (OLTP) application database. Some users on the system are reporting degradation in the performance of SQL statements. To diagnose the problem, you want to identify the SQL statements that are expensive on system resources.What would you recommend to accomplish the task? (Choose all that apply.)

 

A.

running SQL Access Advisor

B.

running SQL Performance Analyzer

C.

querying the V$SQLSTATS and V$SQL_PLAN views to get the information

D.

creating an Automatic Workload Repository (AWR) report for the problematic period

 

Correct Answer: CD

 

QUESTION 85

You are working on a decision support system (DSS). The index is available on the COUNTRY_ID column of the CUSTOMERS table.View the Exhibit and examine the parameter settings and the query execution plan. Why is the query using a full table scan instead of an index scan?

 

clip_image006

 

A.

because the histogram statistics for the COUNTRY_ID column are not updated

B.

because the index statistics for the index on the COUNTRY_ID column are not current

C.

because the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a higher value

D.

because the optimizer predicts that most of the blocks in the table are accessed. Therefore, it uses a full table scan, even though indexes are available.

Correct Answer: D

 

 

QUESTION 86

View the Exhibit and examine the content. ALLOCATED_SPACE and FREE_SPACE indicate that a large amount of space is allocated to the temporary tablespace that is being used.Which action would reclaim the space and refresh these values?

 

clip_image008

 

A.

shrinking the tablespace

B.

closing all the current sessions

C.

restarting the database instance

D.

forcing a checkpoint or waiting for a checkpoint to occur

 

Correct Answer: A

 

 

QUESTION 87

You created two services HR and FINANCE on a single instance database. These services are used by the middle-tier applications to connect to the database by using connection pools. The applications have various modules and you asked the developers to invoke the DBMS_APPLICATION_INFO.SET_MODULE procedure to set the module name and action in the code for the application. As part of performance management, you want to gather statistics for these modules and see the amount of workload created by these modules. What would you recommend to accomplish the task?

 

A.

Using the DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE procedure to collect statistics for a module and querying the V$SERVICE_EVENT view

B.

Using the DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE procedure to collect statistics for a module and querying the V$SERVICE_STATS view

C.

Using the DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE procedure to collect statistics for a module and querying the V$SERV_MOD_ACT_STATS view

D.

Querying the V$SERVICE_STATS view because the statistics for the modules are collected automatically when you invoke the DBMS_APPLICATION_INFO.SET_MODULE procedure

 

Correct Answer: C

 

 

 

 

QUESTION 88

View the Exhibit and examine the content of the V$MEMORY_RESIZE_OPS view.Which two conclusions can you draw? (Choose two.)

 

clip_image010

 

A.

Automatic Memory Management (AMM) is enabled.

B.

Automatic Shared Memory Management (ASMM) is enabled but not AMM.

C.

The Shared Pool grew in IMMEDIATE mode to avoid an out-of-memory error.

D.

The Default Buffer Cache grew in DEFERRED mode to avoid an out-of-memory error.

E.

The memory is transferred in IMMEDIATE mode only if there are entirely empty granules available in other components.

 

Correct Answer: AC

 

 

QUESTION 89

You work as a DBA and you have the responsibility of managing a large online transaction processing (OLTP) system. You used the following query during the performance tuning activity:

 

SQL> SELECT (1-((phy.value-phyd.value) / (cur.value + con.value))) * 100

 

2 “Cache Hit ratio”

 

3 FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd

 

4 WHERE cur.name = ‘db block gets’

 

5 AND con.name = ‘consistent gets’

 

6 AND phy.name = ‘physical reads’

 

7 AND phyd.name = ‘physical reads direct’;

 

Cache Hit Ratio

 

————–

 

98.43

 

Which understanding of this ratio is correct?

 

A.

98.43% of times the requests have found free buffers.

B.

98.43% of the total number of requests used the buffer cache.

C.

98.43% of times the requests were satisfied by performing physical I/Os.

D.

98.43% of times the requests have found the required data blocks in the buffer cache.

 

Correct Answer: D

 

 

QUESTION 90

View the Exhibit and examine the query based on the DBA_THRESHOLDS view and its partial output. Which observation about the output is correct?

 

clip_image012

 

A.

Logons Per Sec triggers an alert when the number of users logging on in an interval of one second is equal to or greater than 100.

B.

Blocked User Session Count triggers an alert based on the ELAPSED_TIME_PER_CALL and DB_TIME_WAITING metrics collection per minute.

C.

Database Time Spent Waiting(%) triggers an alert when more than 30% of the DB time per second for three consecutive seconds is spent on lock waits.

D.

Average Users Waiting Counts triggers an alert when the count is more than 30 for 3 consecutive 1 minute intervals and is related to the log file sync wait event.

 

Correct Answer: D

 

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.