Oracle 1z0-054 Exam Questions

Question 6 of 192

You plan to use adaptive thresholds as part of the performance tuning activity. You decide to increase the window size of the default moving window baseline for all metric observations and comparisons in your database. The following error occurs when you try to increase the window size through Enterprise Manager:
Failed to commit: ORA-13541: system moving window baseline size (1296000) greater than retention (1036800) ORA-06512: at
"SYS.DBMS_WORKLOAD_REPOSITORY", line 601 ORA-06512: at line 2 Which action would allow you to perform the preceding task successfully?

increasing the flashback retention period

increasing the retention period for SQL Management Base

increasing the undo retention period for the database instance

increasing the Automatic Workload Repository (AWR) retention period

Correct Answer: D

Question 7 of 192

Identify two correct statements about the Active Session History (ASH) data. (Choose two.)

A part of SGA memory is used to store ASH data as rolling buffer.

The ASH data can be analyzed between any two small time intervals.

All ASH data in memory are flushed to disk by MMON in every 3 seconds.

All ASH data in memory are flushed to disk by MMNL process whenever the buffer is full.

Correct Answer: A, B

Question 8 of 192

A user in a session executed the following SQL statement to set the optimizer mode:
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS
What impact would it have on the goal of the optimizer for that session? (Choose all that apply.)

Statement level OPTIMIZER_MODE hints take precedence over the session-level setting.

The OPTIMIZER_MODE parameter set at instance level takes precedence over the session-level value.

The optimizer uses a cost-based approach, regardless of the presence of statistics; it optimizes with a goal of best response time.

The optimizer uses a cost-based approach for all SQL statements in the session, regardless of the presence of statistics; it optimizes with a goal of best

Correct Answer: A, D

Question 9 of 192

You are working on an online transaction processing (OLTP) system. By day most of the application 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.)

setting the OPTIMIZER_MODE parameter to ALL_ROWS at the instance level

setting the OPTIMIZER_MODE parameter to FIRST_ROWS_n at the instance level

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

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

Correct Answer: B, C

Question 10 of 192

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

parsing of a SQL statement

operating system (OS) statistics

object statistics in the data dictionary

the OPTIMIZER_MODE initialization parameter

optimizer SQL hints for changing the query optimizer goal

Correct Answer: C, D, E