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

Ensurepass

QUESTION 101

You are working in an online transaction processing (OLTP) environment. You received many complaints from users about degraded performance. Your senior DBA asked you to execute the following command to improve the performance:

 

SQL> ALTER TABLE subscribe_log STORAGE(BUFFER_POOL recycle);

 

You checked the data in the SUBSCRIBE_LOG table and found that it is a large table having one million rows. Which factor could be a reason for this recommendation?

 

A.

The keep pool is not configured.

B.

The automatic Program Global Area (PGA) is not configured.

C.

The CURSOR_SPACE_FOR_TIME initialization parameter is set to FALSE.

D.

The most of the rows in SUBSCRIBE_LOG table are accessed once a week.

E.

All the queries on the SUBSCRIBE_LOG table are rewritten using a materialized view.

 

Correct Answer: D

 

 

QUESTION 102

Examine the following information from the AWR report:

 

Top 5 Timed Events

 

~~~~~~~~~~~~~~~~~~ % Total

 

Event Waits Time (s) Call Time

 

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

 

CPU time 559 88.80

 

log file parallel write 2,181 28 4.42

 

SQL*Net more data from client 516,611 27 4.24

 

 

db file parallel write 13,383 13 2.04

 

db file sequential read 563 2 .27

 

Which could cause waits on the db file sequential read event?

 

A.

too many full table scans

B.

too large a database buffer cache

C.

too many waits occurring while reading multiple blocks synchronously

D.

too many index range scans occurring which refers to blocks not in the database buffer cache

 

Correct Answer: D

 

 

QUESTION 103

View the Exhibit and analyze the content of V$BUFFER_POOL_STATISTICS. Why does the query on the EMP table not access buffers from the keep pool?

 

clip_image002

 

A.

because the query used SCOTT.EMP instead of EMP

B.

because the query with * always uses the default pool

C.

because the query with * always forces physical I/Os that bypass the keep pool

D.

because the blocks of the EMP table are already available in one of the other buffer pools

 

Correct Answer: D

 

 

QUESTION 104

You are managing an online transaction processing (OLTP) database with multiple applications. The applications support a large number of users and most of the applications execute groups of SQL that run together. Which option would you use to monitor the performance issues for users of these applications?

 

A.

Monitor statistics for individual sessions in an application by querying V$SESSION.

B.

Specify service-level thresholds and priorities for users of applications and monitor the alerts.

C.

Monitor statistics and wait events collected for a MODULE and ACTION by performing service aggregation.

D.

Configure application users as part of the resource consumer group and monitor the resource usage statistics for the resource consumer group.

 

Correct Answer: C

 

 

QUESTION 105

Identify the two direct sources from where SQL plans can be loaded into the SQL plan baselines. (Choose two.)

 

A.

Cursor cache

B.

Stored outline

C.

SQL Tuning Set

D.

Automatic Workload Repository (AWR) snapshots

 

Correct Answer: AC

 

 

QUESTION 106

In which two scenarios would you recommend using RAID level 5 as an option for database storage? (Choose two.)

 

A.

For a database with lower recovery (MTTR) overhead

B.

For a database in which the applications primarily perform read operations and predominantly sequential I/O is performed

C.

For a database in which performance is the primary goal of the database because applications are demanding in terms of I/O operations

D.

For a database in which the database size grows rapidly, the cost of storage is critical and data accessibility is more important than performance

 

Correct Answer: BD

 

 

 

 

 

 

 

 

QUESTION 107

View the Exhibit and examine the plans in the SQL plan baseline.You observe that the last two SELECT statements (outlined in red and blue) are identical, but have two different plans. Also, the second plan (in blue) is not being used by the optimizer.Which two steps do you perform to ensure that the optimizer uses the second plan (in blue) if the plan is more efficient? (Choose two.)

 

clip_image004

 

A.

Mark the second plan (in blue) as fixed.

B.

Use SQL Tuning Advisor to tune the plan.

C.

Use SQL Access Advisor to tune the plan.

D.

Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve the plan.

 

Correct Answer: BD

 

 

QUESTION 108

You work as a DBA for a company and as a performance improvement measure, you implemented the result cache in your database. Many users in the company say that performance has improved on the queries they use, but some users complain that they have not got any performance benefiton the queries they use.

 

You checked all the queries they use and the following is one of them:

 

SQL> SELECT /*+ RESULT_CACHE */ slnoq.currval as “SLNO”, prod_id, pdname, 2 cust_name FROM sales WHERE sl_date < sysdate;

 

View the Exhibit and examine the testing performed to check this.

 

Why is the result cache not used? (Choose all that apply.)

 

clip_image006

 

A.

because the query uses SYSDATE

B.

because the query uses an alias for a column

C.

because the query uses the SLNOQ.CURRVAL sequence

D.

because the table might have an index on the SL_DATE column

 

Correct Answer: AC

 

 

QUESTION 109

You used SQL Tuning Advisor for a long-running SQL statement that suggested a SQL profile which can be used by the query subsequently for a better execution plan. After implementing the profile, you altered the profile to include it in a category as follows:

 

SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name=>

 

‘SYS_SQLPROF_0146ced728214000, attribute_name => ‘CATEGORY’, value => ‘DEV2’).

 

But the performance has not improved for the user session. The profile is enabled and on investigating the query plan, you find that the query from the user session does not use the SQL profile. View the Exhibit and examine the parameters for the user session. What could be the reason for this?

 

clip_image008

 

A.

The CONTROL_MANAGEMENT_PACK_ACCESS parameter is not set to DEV2.

B.

The OPTIMIZER_MODE parameter is set to ALL_ROWS for the user session.

C.

The SQLTUNE_CATEGORY parameter is set to DEFAULT for the user session.

D.

The OPTIMIZER_USE_PLAN_BASELINES parameter is set to TRUE for the user session.

 

Correct Answer: C

 

 

QUESTION 110

View the Exhibit and analyze the output of the query.What does the high value for the table fetch continued rows statistics indicate?

 

clip_image009

 

A.

chained rows fetched only

B.

migrated rows fetched only

C.

rows fetched from external tables

D.

both chained and migrated rows fetched

E.

rows fetched from Index Organized Tables (IOT)

 

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.