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

Ensurepass

QUESTION 51

You notice that two wait events, Latch:cache buffer chains and Latch:cache buffer LRU chains, appear consistently in the Top 5 Timed Events for your database.Which two options describe what these wait events indicate? (Choose two.)

 

A.

Latch:cache buffer LRU chains – indicates excessive block replacement

B.

Latch:cache buffer LRU chains – indicates insufficient size of application cursor cache

C.

Latch:cache buffer chains – indicates inefficient SQL that accesses many blocks repeatedly

D.

Latch:cache buffer chains – indicates that there are several sessions waiting for a buffer that is presently being read into the buffer cache by another session

 

Correct Answer: AC

 

 

QUESTION 52

The SALES table in your database undergoes frequent changes. While observing the statistics for the table in the middle of the day, you find a change since the last observation. But the information about the statistics collection is not recorded in the DBA_TAB_STATS_HISTORY. Identify the reason for this.

 

A.

The AWR retention time was set to zero.

B.

The STATISTICS_LEVEL parameter was set to BASIC.

C.

Some of the statistics were collected using the ANALYZE command.

D.

There were user-defined statistics that were set by using the DBMS_STATS.SET_TABLE_STATS procedure.

 

Correct Answer: C

 

 

QUESTION 53

Identify the type of recommendation that can automatically be implemented by the Automatic Tuning Advisor as part of automatic SQL tuning task?

 

A.

recommendation for SQL profiles

B.

recommendation about effective indexing

C.

recommendation about stale or no statistics

D.

recommendation about the structure of SQL statements

 

Correct Answer: A

 

 

QUESTION 54

View the Exhibit and examine the sample Automatic Database Diagnostic Monitor (ADDM) report.Which statement is true about the report?

 

clip_image002

 

A.

The sum of impacts under recommendations in Finding 2 cannot exceed 100%.

B.

It is possible that the sum of impacts under recommendations in Finding 2 can exceed 100%.

C.

The recommendations regarding “buffer busy” wait cannot appear more than once in the report.

D.

The sum of impacts of recommendation 2 in Finding 1 and recommendation 2 in Finding 2 cannot exceed 100%.

 

Correct Answer: B

QUESTION 55

The columns CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_ID are frequently used together in the WHERE clause of the queries. The CUSTOMERS table is a big table with 20 GB of data. You notice that the selectivity for these three columns varies from the selectivity that the optimizer calculates. What would you recommend to influence the selectivity calculated by the optimizer?

 

A.

creating function-based indexes by concatenating all the columns

B.

updating histogram statistics for these columns by using the DBMS_STATS.GATHER_TABLE_STATS procedure

C.

using the DBMS_STATS.CREATE_EXTENDED_STATS function to create a virtual column and create index on the virtual column

D.

using the DBMS_STATS.CREATE_EXTENDED_STATS function to create a virtual column and the DBMS_STATS.GATHER_TABLE_STATS procedure to collect statistics on the virtual column

 

Correct Answer: D

 

 

QUESTION 56

What is the purpose of running the statspack.snap script during the installation of the Statspack package?

 

A.

It creates the initial baseline.

B.

It automates the collection of statistics.

C.

It stores the current performance statistics in the Statspack tables.

D.

It creates the PERFSTAT user, the Statspack tables, and constraints

 

Correct Answer: C

 

 

QUESTION 57

You work as a DBA for a company and you have the responsibility of managing one of its onlinetransaction processing (OLTP) systems. The database encountered performance- related problems and you generated an Automatic Workload Repository (AWR) report to investigate it further. View the Exhibits and examine the AWR report. Which is the appropriate solution to the problem in this database?

 

clip_image004

clip_image006

clip_image008

clip_image010

 

A.

increasing the size of the shared pool

B.

adding one more CPU to the system

C.

setting the CURSOR_SHARING parameter to EXACT

D.

configuring Java pool because it is not configured

 

Correct Answer: < /span>A

 

 

 

QUESTION 58

You identified poorly performing SQL statements that use bind variables. You want to examine the execution plans for the most recently executed statements for further diagnosis. Which method would you recommend to view the execution plans?

 

A.

SQL*Plus Autotrace

B.

the dbms.xplan package

C.

EXPLAIN PLAN for SQL statements

D.

the SQL trace utility to generate trace files and read the formatted output using the tkprof utility

 

Correct Answer: B

 

 

QUESTION 59

You have a range-partitioned table in your database. Each partition in the table contains the sales data for a quarter. The partition related to the current quarter is modified frequently, whereas other partitions undergo fewer data manipulations. The preferences for the table are set to their default values. You collect statistics for the table using the following command at regular intervals:

 

SQL> EXECUTE

DBMS_STATS.GATHER_TABLE_STATS(‘SH’,’SALES’,GRANULARITY=>’AUTO’);

 

You need statistics to be collected more quickly. How do you achieve this?

 

A.

Set the PUBLISH preference to TRUE for the partition table.

B.

Set the NO_VALIDATE preference to TRUE for the partition table.

C.

Set the INCREMENTAL preference to TRUE for the partition table.

D.

Increase the value of the STALE_PERCENT preference for the partition table.

 

Correct Answer: C

 

 

QUESTION 60

Which statement is true regarding the setting of the PCTFREE value for an index?

 

A.

It decides how much of a block to fill when the index is created.

B.

It has no role to play when segment space management is automatic.

C.

It keeps space reserved in a block for possible updates to existing rows in that block.

D.

It is the minimum percentage of
a block that can be used for row data and overhead before new rows are added to the block.

 

Correct Answer: A

 

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.