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

Ensurepass

QUESTION 111

You work as a consultant DBA for various clients. A performance issue in one of the online transaction processing (OLTP) systems is reported to you and you received the Automatic Workload Repository (AWR) report generated in the database. The main sections of the AWR report are shown in the Exhibits.

 

View the Exhibit named DBTIME and note % DB Time.

 

clip_image002

 

View the Exhibit named TIMEMODEL and note what has contributed to % DB Time.

 

clip_image004

 

View the Exhibit named EFFICIENCY and examine the various percentages shown.

 

clip_image006

 

Identify the option that has the correct answers for the questions given below:

 

1. Which factor indicates the main problem?

 

2. What is the m
ain problem?

 

3. What solution would you recommend?

 

A.

1. The Exhibit DBTIME shows that the DB CPU event consumed very high % DB Time.

2. The CPU is very slow.

3. Increase the number of processors.

B.

1. hard parse elapsed time has the majority time in parse time elapsed, which is shown in the TIMEMODEL Exhibit.

2. This is due to inadequate Database Buffer Cache.

3. Increase the size of database buffer cache.

C.

1. sql execute elapsed time and parse time elapsed are consuming very high % DB Time in the TIMEMODEL Exhibit.

2. There are too many soft parses.

3. Investigate the HOLD_CURSOR parameter setting in the application and set it appropriately.

D.

1. % Non-Parse CPU is low and Soft Parse % is also low in the EFFICIENCY Exhibit. This shows that very few statements are found in the cache.

2. There are too many hard parses.

3. Investigate the CURSOR_SHARING parameter setting and set it appropriately.

 

Correct Answer: D

 

 

QUESTION 112

View the Exhibit exhibit1 to examine the series of SQL commands and parameter settings.

 

clip_image007

View the Exhibit exhibit2 to examine the plans available in the SQL plan baseline.

 

clip_image009

 

The first plan (in red) is created when OPTIMIZER_MODE is set to ALL_ROWS and the second plan (in blue) is created when OPTIMIZER_MODE is set to FIRST_ROWS.Which SQL plan baseline would be used if the SQL query in exhibit1 is executed again when the value of OPTIMIZER_MODE is set to FIRST_ROWS?

 

A.

the
second plan, because it is a fixed plan

B.

the first plan, because it is an accepted plan

C.

the second plan, because it is the latest generated plan in FIRST_ROW mode

D.

A new plan, because the second plan in FIRST_ROW mode is not an accepted plan

 

Correct Answer: B

 

 

QUESTION 113

Identify the correct statements about the SYSTEM_MOVING_WINDOW baseline. (Choose two.)

 

A.

It is used for adaptive threshold setting.

B.

The statistics computation cannot be scheduled explicitly for it.

C.

Only significance-level thresholds can be set for metrics under it.

D.

It is the only baseline that can be used on the performance page.

 

Correct Answer: AB

 

 

QUESTION 114

You are working on an online transaction processing (OLTP) system. By day most of theapplication users perform queries accessing the most recently added or modified rows. The applications have most of the queries based on multiple tables. But at night, some batch processing is also done. Which two actions would you recommend to choose a goal for the optimizer based on the needs of your application? (Choose two.)

 

A.

setting the OPTIMIZER_MODE parameter to ALL_ROWS at the instance level

B.

setting the OPTIMIZER_MODE parameter to FIRST_ROWS_n at the instance level

C.

asking the developer to add a hint /*ALL_ROWS*/ in the long-running batch processing queries

D.

asking the developer to add a hint /*FIRST_ROWS_n*/ in the long-running batch processing queries

 

Correct Answer: BC

 

 

QUESTION 115

During a proactive database performance monitoring routine, on examining the AWR report you find that log file sync appears among the top 5 wait events.What does this event indicate?

 

A.

Frequent logfile switches are occurring.

B.

Redo is generated faster than LGWR can write it out.

C.

Frequent commits or rollbacks are taking place in the application.

D.

Frequent incremental checkpoints are taking place in the database.

 

Correct Answer: C

 

 

QUESTION 116

You observed that some of the queries are performing poorly on the SALES_RECORDS table in your database. On further investigation, you find that at the end of each day the contents of the SALES_RECORDS table are transferred to the SALES table and deleted from the SALES_RECORDS table. The deleted operations cause the table to be sparsely populated. You decided to use the ALTER TABLE…SHRINK SPACE COMPACT command to shrink the table. Why would you choose this method? (Choose all that apply.)

 

A.

because it can be used during peak hours

B.

because it avoids unnecessary cursor invalidation

C.

because it adjusts the high-water mark (HWM) immediately

D.

because you have long-running queries that might span the shrink operation

E.

because it does not allow any data manipulation language (DML) operations, thereby making the shrink operation faster

 

Correct Answer: ABD

 

 

QUESTION 117

What indicates that the rate of writing data blocks to the disk is very slow?

 

A.

very high cache-hit ratio

B.

increasing number of free buffer waits

C.

increasing number of buffer busy waits

D.

waits on latch: cache buffers chains event

 

Correct Answer: B

 

 

QUESTION 118

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

 

Correct Answer: AD

 

 

QUESTION 119

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

 

Correct Answer: B

 

 

QUESTION 120

In which case is the database buffer cache NOT used?

 

A.

when physical I/Os are performed to read from data files

B.

when the database writer process writes to the SYSTEM tablespace

C.

when the server process writes sort data to the temporary tablespace

D.

when the database writer process writes undo data to the undo tablespace

 

Correct Answer: C

 

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.