It is more faster and easier to pass the Oracle 1z0-054 exam by using High quality Oracle Oracle Database 11g: Performance Tuning questuins and answers. Immediate access to the Up to date 1z0-054 Exam and find the same core area 1z0-054 questions with professionally verified answers, then PASS your exam with a high score now.

2017 Jan 1z0-054 test question

Q91. You analyze an Automatic Database Diagnostic Monitor (ADDM) report to identify the component that contributes most to the database time.

Which is the correct description of database time?

A. It is the sum of the CPU time of all nonidle sessions.

B. It is the sum of wait time and the CPU time of all nonidle sessions.

C. It is the time spent in processing the input/output (I/O) request by users.

D. It is the difference between the wait time and the CPU time of all nonidle sessions.

Answer: B


Q92. You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. You observed that a few tables are being updated regularly, but not being queried often. However statistics are being gathered for these tables automatically and regularly, thereby affecting the database performance.

What would you do to increase the interval of statistics collection for these tables so that the statistics collection jobs spend less time during the automatic maintenance task?

A. Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.

B. Increase the value of the STALE_PERCENT preference for these tables.

C. Mark those associated indexes that are less frequently used as invisible.

D. Increase the value of the ESTIMATE_PERCENT preference for these tables

Answer: B


Q93. Examine the following block:

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',

cascade => FALSE, dbid => 3310949047);

END;

Why would you use the FALSE value for the CASCADE parameter while dropping the baseline?

A. because it does not remove snapshots associated with the baseline

B. because it does not drop the template that is used to create the baseline

C. because it does not remove the baseline from the performance page if it appears there

D. because it does not remove the Automatic Database Diagnostic Management (ADDM) results from the Automatic Workload Repository (AWR) that were generated using the baseline

Answer: A


Q94. You want information in the form of charts for throughput and services that shows baseline values and thresholds for the baseline. How would you achieve this?

A. By quickly configuring the metric threshold

B. By computing statistics for the desired baseline

C. By adding a baseline to the performance page in Oracle Enterprise Manager

D. By adding a baseline template to the performance page in Oracle Enterprise Manager

Answer: C


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

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

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

Answer: B


Up to the minute 1z0-054 exam engine:

Q96. View Exhibit1 and examine the indexes on the CUSTOMERS table.

The statistics for the CUSTOMERS table have been updated recently by using the following command:

SQL> EXEC

DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

View Exhibit2 to examine a query plan. Even though the index is present on the COUNTRY_ID and CUST_GENDER columns, the query uses a full table scan.

What could be the reason?

A. because the histogram statistics for the COUNTRY_ID column are not updated

B. because the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a high value

C. because the optimizer calculates the cost of accessing blocks by using a full table scan to be less as compared to index scans, even though indexes are available

D. because indexes on CUST_GENDER and COUNTRY_ID columns are of different types, the index on the CUST_GENDER column is bitmap index, and on COUNTRY_ID columns is btree index.

Answer: C


Q97. View the Exhibit and examine the output for the WRC command-line utility that you executed to estimate the number of replay clients.

Identify three prerequisites for completing the replay. (Choose three.)

Exhibit:

A. The replay database should be in the RESTRICTED mode.

B. You need to start at least 21 replay clients on six CPUs to start the replay.

C. The replay directory must have all the replay files that have been preprocessed.

D. The user should be able to use the workload replay package and should have the DBA role.

E. The replay options for think_time_scale and connect_time_scale must be set to 100 each.

Answer: BCD


Q98. You work as a DBA and 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

What can you conclude based on this ratio?

A. The database performance is very good because of reduced logical I/Os.

B. The database performance cannot be determined based only on this ratio.

C. Good database performance is guaranteed because very few physical I/Os are performed.

D. The database performance is very good because most of the requested data blocks are found in the buffer cache.

Answer: B


Q99. Examine the following block that executes a SQL Performance Analyzer task:

begin

    dbms_sqlpa.execute_analysis_task(task_name => 'SPA_SKD4', execution_type => 'TEST EXECUTE', execution_name => 'before');

end;

Which two statements are true about the execution? (Choose two.)

A. It generates execution plans and execution statistics.

B. It executes only the query part of the DML statements.

C. The test plans are generated and added to plan baseline.

D. The test plans are executed, but not generated if they are available in the plan baseline.

Answer: AB


Q100. Which three factors influence the optimizer's behavior while choosing an optimization approach and goal for a SQL statement? (Choose three.)

A. parsing of a SQL statement

B. operating system (OS) statistics

C. object statistics in the data dictionary

D. the OPTIMIZER_MODE initialization parameter

E. optimizer SQL hints for changing the query optimizer goal

Answer: CDE