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

Ensurepass

QUESTION 121

You received complaints from users about slow response time in some parts of the application. On investigation, you find that this happens whenever the application runs any query against the PROD_DET

 

table that contains detailed information about all the products.

 

You gathered the table statistics and executed the following statements to find more information about this table:

 

SQL> SELECT chain_cnt, 2 round(chain_cnt/num_rows*100,2) pct_chained, 3 avg_row_len, pct_free ,

 

pct_used 4 FROM user_tables 5 WHERE table_name = ‘PROD_DET’;CHAIN_CNT PCT_CHAINED

 

AVG_ROW_LEN PCT_FREE PCT_USED

 

——— ———– ———– ———- ———-

 

4789 100 3691 10 40

 

Which methods can you use to improve the performance? (Choose all that apply.)

 

A.

online table redefinition

B.

export and import the table

C.

decrease the PCTFREE value

D.

move the table using the ALTER TABLE command

 

Correct Answer: ABD

 

 

QUESTION 122

You are working on an online transaction processing (OLTP) system. You are investigating the reasons for performance degradation in the database.

 

View the Exhibit named TOP5EVENTS and note the top five events.

 

clip_image002

View the Exhibit named TOPSQL and note the problems related to these statements.

 

clip_image004

 

View the Exhibit named INSTACT and note the table scans.

 

clip_image006

 

Which is the most appropriate solution?

 

A.

Create indexes on short tables.

B.

Use the CACHE hint to access long tables.

C.

Keep the long tables in the keep buffer pool.

D.

Keep the short tables in the keep buffer pool.

E.

Keep the short tables in the recycle buffer pool.

 

Correct Answer: D

 

QUESTION 123

Which two inferences are correct about the CPU time and the wait time that appear in the Automatic Workload Repository AWR and Statspack reports in the Top 5 Event section? (Choose two.)

 

A.

Adding CPUs to systems showing increased wait time due to contention will improve response time.

B.

Systems where the CPU time is dominant in comparison to the wait time have optimal response time.

C.

Increase in wait time when the CPU time increases or remains the same indicates an increase in the load on the system.

D.

Systems where the proportion of CPU time does not decrease significantly with increasing load would most likely benefit from adding CPUs.

E.

The appearance of CPU time together with wait time in the Top 5 Event section of the AWRreport of a system always indicates high contention.

 

Correct Answer: CD

 

 

QUESTION 124

You work in an online transaction processing (OLTP) environment and users are experiencing slow response time on their queries. These users connect as FINANCE to run their application and run hundreds of sessions. Your investigation shows that there is lot of I/O contention happening on only one temporary tablespace and there is no load on other temporary tablespaces. View the Exhibit and examine the details. After some time you observed that sort operations are running out of temporary space. What would be a long-term solution?

 

clip_image008

 

A.

Change the temporary tablespace assigned to this user ID.

B.

Create and assign temporary tablespace group to this user ID.

C.

Increase the size of the temporary tablespace assigned to this user ID.

D.

Make the bigger temporary tablespace the default temporary tablespace ID.

 

Correct Answer: B

 

 

 

 

 

 

 

QUESTION 125

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

 

Correct Answer: D

 

 

QUESTION 126

The users in your online transaction processing (OLTP) environment complain that the query response time has increased considerably. On investigation, you find that the Program Global Area (PGA) is undersized and using temporary segments for sort operations. You plan to increase the size of the PGA and there is ample amount of memory available to you. You used the PGA advisor to check the correct size for PGA. View the Exhibit and examine the content of the PGA advisor. What size of PGA would give the maximum benefit?

 

clip_image010

 

A.

125 MB

B.

3009 MB

C.

8024 MB

D.

1003 MB

E.

1204 MB

F.

1404 MB

 

Correct Answer: B

 

 

 

 

QUESTION 127

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.

 

Correct Answer: BC

 

 

QUESTION 128

View the Exhibit and examine the statistics of a SQL statement.Which two conclusions can you draw from the statistics? (Choose two.)

 

clip_image012

 

A.

The SQL statement has high logical I/O.

B.

The SQL statement has high CPU usage.

C.

The SQL statement has high physical I/O.

D.

The SQL statement has long sort operations.

 

Correct Answer: AB

 

 

QUESTION 129

Your database has the HR and Reporting applications. The HR application does most of the transactions on the database and the Reporting application does most of the batch processing operations.The Reporting application has more workload during nonpeak hours. Both the HR and Reporting applications run on the middle tier using connection pooling for connecting to database.You want to statistically measure the workload generated by both the applications and set priority for the workload generated by the applications. Which option would help you achieve this?

 

A.

Assigning individual sessions created by the applications to consumer groups and setting relative priority

B.

Assigning profiles to individual sessions created by the applications to set the relative priority for the resource usage

C.

Creating services for the applications and setting the relative priority of services within an instance by binding the services directly to consumer groups

D.

Creating services for the applications and assigning different profiles to the sessions created by the sessions to set the relative priority for the resource usage

 

Correct Answer: C


 

 

QUESTION 130

The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE for a 24×7 database instance. The space consumption has significantly increased by the automaticallycaptured plan baselines. You are suggested to run the following block:

 

BEGIN

 

dbms_spm.configure(‘SPACE_BUDGET_PERCENT’, 30);

 

END;

 

After a few weeks of work, you observe warnings on exceeding space usage by SQL Management Base (SMB) in alert log file. Which two actions can be solutions for this? (Choose two.)

 

A.

increasing the AWR retention period

B.

purging the older SQL management objects

C.

increasing the size of the SYSTEM tablespace< /span>

D.

increasing the size of the SYSAUX tablespace

 

Correct Answer: BD

 

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.