Oracle Exam 1Z0-084 Topic 13 Question 10 Discussion

Actual exam question for Oracle's 1Z0-084 exam

Question #: 10
Topic #: 13

[All 1Z0-084 Questions]

18. The application provider has given full indications regarding the procedure to collect statistics.

To reduce the space used in the SYSAUX tablespace, you want to prevent the optimizer statistics Advisor from running.

Which method will allow you to do this?

ASet the parameter OPTIMIZER_ADAPTIVE_STATISTICS to FALSE.

BUse DBMS_AUTO_TASK_ADMIN. DISABLE to disable the AUTO_STATS_ADVISOR_TASK task.

CSet the AUTO_STATS_ADVISOR_TASK global statistics preference to FALSE.

DUse DBMS STATS.DROP ADVISOR TASK to drop the AUTO_STATS_ADVISOR_TASK task.

Show Suggested Answer

Suggested Answer: A, E, F

Statspack is a performance diagnostic tool provided by Oracle prior to the introduction of the Automatic Workload Repository (AWR). At snap level 6, Statspack captures the following types of statistics:

A (Correct): Parent and child latches are captured. Latch statistics provide information about contention for latches, which are low-level serialization mechanisms used by Oracle.

E (Correct): Enqueue statistics, which provide information on the waits for locks that manage the concurrency between users.

F (Correct): Segment-level statistics, which provide detailed information on database segments such as tables, indexes, etc., to identify I/O and contention issues.

C (Incorrect): While optimizer execution plans are an essential aspect of performance tuning, detailed execution plan capture is not part of the Statspack report at level 6.

D (Incorrect): Plan usage data refers to how frequently a plan is being used, which is more associated with AWR and not typically captured in Statspack reports.

Oracle Database Performance Tuning Guide: Using Statspack

by Denny at May 17, 2024, 05:13 PM

Limited Time Offer

25%

Off

Get Premium 1Z0-084 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Paola

2 months ago

Option C looks tempting, but setting a global preference to FALSE? That's like asking for a lifetime ban from the stats advisor club. I'm going with Option B - it's the surgical strike approach to SYSAUX space reduction.

upvoted 0 times

Alisha

9 days ago

I think Option B is the most efficient choice here. Let's disable that task and free up some space.

upvoted 0 times

...

Gracia

15 days ago

Yeah, Option B seems like the most targeted solution. Let's keep it simple.

upvoted 0 times

...

Annette

16 days ago

I agree, Option B is the way to go. No need to go overboard with a global preference change.

upvoted 0 times

...

Tamie

2 months ago

Haha, drop the AUTO_STATS_ADVISOR_TASK task? That's like asking the Oracle gods to smite you with a thousand tablespace errors. I'll stick with Option B and keep the advisor on a leash, thank you very much.

upvoted 0 times

...

Alesia

2 months ago

Hmm, I'm not sure about setting the OPTIMIZER_ADAPTIVE_STATISTICS parameter to FALSE. Isn't that going to mess with my query performance? I think I'll go with Option B and shut down the AUTO_STATS_ADVISOR_TASK instead.

upvoted 0 times

Muriel

2 months ago

Yeah, I think that's the best way to prevent the optimizer statistics Advisor from running.

upvoted 0 times

...

Chi

2 months ago

I agree, disabling the AUTO_STATS_ADVISOR_TASK seems like a safer option.

upvoted 0 times

...

Charisse

2 months ago

Option B seems like the way to go. DBMS_AUTO_TASK_ADMIN is the key to disabling that pesky AUTO_STATS_ADVISOR_TASK. I'm all about keeping that SYSAUX tablespace lean and mean!

upvoted 0 times

...