Oracle Database 19c: Performance Management and Tuning

Here you have the best Oracle 1z0-084 practice exam questions

You have 31 total questions to study from
Each page has 5 questions, making a total of 7 pages
You can navigate through the pages using the buttons at the bottom
This questions were last updated on May 31, 2025
This site is not affiliated with or endorsed by Oracle.

Question 1 of 31

The CURSOR_SHARING and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameters are set to default. The top five wait events in an AWR report are due to a large number of hard parses because of several almost identical SQL statements.

Which two actions could reduce the number of hard parses? (Choose two.)

Create the KEEP cache and cache tables accessed by the SQL statements.

Create the RECYCLE cache and cache tables accessed by the SQL statements.

Increase the size of the library cache.

Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE.

Set the CURSOR_SHARING parameter to FORCE.

Correct Answer: D, E

Setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE will help in stabilizing execution plans for repeated queries and thus reduce the likelihood of new parse operations. Setting the CURSOR_SHARING parameter to FORCE will replace literals with bind variables, allowing SQL statements that are identical except for literals to share the same execution plan, which reduces the number of hard parses.

Question 2 of 31

Which three types of statistics are captured by Statspack with snap level 6? (Choose three.)

Parent and child latches

Optimizer execution plans

Plan usage data

Enqueue statistics

Segment-level statistics

Correct Answer: C, D, E

Question 3 of 31

Examine this AWR report excerpt:

You must reduce the impact of database I/O, without increasing buffer cache size and without modifying the SQL statements.

Which compression option satisfies this requirement?

COLUMN STORE COMPRESS FOR QUERY LOW

ROW STORE COMPRESS

ROW STORE COMPRESS ADVANCED

COLUMN STORE COMPRESS FOR QUERY HIGH

Correct Answer: C

Question 4 of 31

Examine this output of a query of V$PGA_TARGET_ADVICE:

Which statements is true?

With a target of 700 MB or more, all multipass executions work areas would be eliminated.

PGA_AGGREGATE_TARGET should be set to at least 800 MB

PGA_AGGREGATE_TARGET should be set to at least 700 MB.

With a target of 800 MB or more, all one-pass execution work areas would be eliminated.

Correct Answer: B

Question 5 of 31

Examine this statement and its corresponding execution plan:

Which phase introduces the CONCATENATION step?

SQL Semantic Check

SQL Execution

SQL Row Source Generation

SQL Transformation

SQL Adaptive Execution

Correct Answer: D