Deal of The Day! Hurry Up, Grab the Special Discount - Save 25% - Ends In 00:00:00 Coupon code: SAVE25
Welcome to Pass4Success

- Free Preparation Discussions

Oracle Exam 1Z0-182 Topic 1 Question 7 Discussion

Actual exam question for Oracle's 1Z0-182 exam
Question #: 7
Topic #: 1
[All 1Z0-182 Questions]

Which two statements describe how Optimizer Statistics are collected?

Show Suggested Answer Hide Answer
Suggested Answer: A, C

Optimizer Statistics drive the cost-based optimizer's query plans. Let's dissect each option:

A . Optimizer Statistics are collected automatically by an automatic maintenance job that runs during predefined maintenance windows.

True. Oracle 23ai uses the AutoTask framework to gather stats automatically during maintenance windows (e.g., nightly 10 PM--2 AM). The GATHER_STATS_PROG job, managed by DBMS_AUTO_TASK_ADMIN, collects stats for stale or missing objects.

Mechanics:Controlled by STATISTICS_LEVEL=TYPICAL (default) and the DEFAULT_MAINTENANCE_PLAN. It prioritizes objects with >10% changes (stale stats) or no stats.

Practical Use:Ensures stats are current without manual intervention, critical for dynamic workloads.

Edge Case:Disabled if STATISTICS_LEVEL=BASIC or the job is manually disabled via DBMS_AUTO_TASK_ADMIN.DISABLE.

B . Optimizer Statistics are collected in real-time as data is inserted, deleted, or updated.

False. Stats aren't updated in real-time; this would be too resource-intensive. Instead, Oracle tracks changes (e.g., via DBA_TAB_MODIFICATIONS) and updates stats periodically via AutoTask or manually. Real-time stats exist in 23ai for specific cases (e.g., GATHER_TABLE_STATS with REAL_TIME_STATS), but it's not the default.

Why Incorrect:Real-time collection would degrade performance for OLTP systems, contradicting Oracle's batch approach.

C . Optimizer Statistics can be manually collected at multiple levels using DBMS_STATS.GATHER_*_STATS PL/SQL procedures.

True. The DBMS_STATS package offers granular control: GATHER_TABLE_STATS, GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS, etc., allowing stats collection for tables, schemas, or the entire database.

Mechanics:Example: BEGIN DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES'); END;. Options like ESTIMATE_PERCENT and DEGREE fine-tune the process.

Practical Use:Used for immediate stats updates post-DML or for custom schedules outside maintenance windows.

Edge Case:Overuse can lock stats (e.g., FORCE=TRUE), requiring careful management.

D . Optimizer Statistics are collected by the Statistics Advisor.

False. The Statistics Advisor (new in 23ai) analyzes and recommends stats improvements but doesn't collect them. Collection is still via DBMS_STATS or AutoTask.

Why Incorrect:It's a diagnostic tool, not an executor.

E . Optimizer Statistics are collected automatically by Automatic Workload Repository (AWR) Snapshot.

False. AWR snapshots capture performance metrics (e.g., wait times), not optimizer stats. Stats collection is a separate process via AutoTask or manual commands.

Why Incorrect:AWR and stats collection serve distinct purposes---monitoring vs. optimization.


Contribute your Thoughts:

Vicente
13 days ago
This is a trick question! Everyone knows the Optimizer collects the stats while you're not looking, and then laughs at your attempts to understand it.
upvoted 0 times
...
Lashaun
14 days ago
I was sure B was one of the options. Guess I shouldn't have relied on my memory for this one.
upvoted 0 times
...
Felix
29 days ago
Real-time collection of stats? That's a bit overkill, don't you think? I'll go with A and C.
upvoted 0 times
Herminia
3 days ago
User 2: I agree, real-time collection seems excessive. A and C make more sense.
upvoted 0 times
...
Nieves
9 days ago
User 1: Real-time collection of stats? That's a bit overkill, don't you think? I'll go with A and C.
upvoted 0 times
...
...
Salome
1 months ago
Hmm, I thought the Statistics Advisor was responsible for collecting the stats. Guess I need to brush up on my Oracle knowledge.
upvoted 0 times
Michael
19 days ago
A) Optimizer Statistics are collected automatically by an automatic maintenance job that runs during predefined maintenance windows.
upvoted 0 times
...
...
Catalina
1 months ago
A and C are the correct answers. The automatic maintenance job and the DBMS_STATS procedures are the ways to collect Optimizer Statistics.
upvoted 0 times
Casey
4 days ago
Yes, the automatic maintenance job and the DBMS_STATS procedures are the ways to collect Optimizer Statistics.
upvoted 0 times
...
Kelvin
28 days ago
I think A and C are the correct answers.
upvoted 0 times
...
...
Angelica
1 months ago
I'm not sure about D and E. I think they are not related to how optimizer statistics are collected.
upvoted 0 times
...
Lacresha
1 months ago
I agree with you, Colette. A and C make sense because optimizer statistics can be collected automatically or manually.
upvoted 0 times
...
Colette
1 months ago
I think A and C are correct.
upvoted 0 times
...

Save Cancel
a