New Year Sale 2026! Hurry Up, Grab the Special Discount - Save 25% - Ends In 00:00:00 Coupon code: SAVE25
Welcome to Pass4Success

- Free Preparation Discussions
Mail Us support@pass4success.com
Location US
Mob_nav_barsMENU

Oracle 1Z0-182 Exam - 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.


by Dorinda at Apr 05, 2025, 01:24 AM

Limited Time Offer

25%

Off

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

Contribute your Thoughts:

0/2000 characters
Submit Cancel
An
3 months ago
Surprised to see A mentioned, I thought it was all manual!
upvoted 0 times
...
Luisa
3 months ago
I think B is misleading, it's not real-time.
upvoted 0 times
...
Nieves
3 months ago
A and C are correct!
upvoted 0 times
...
Truman
3 months ago
Wait, I thought D was the way stats were collected?
upvoted 0 times
...
Gilberto
3 months ago
Definitely A and C, those are the standard methods.
upvoted 0 times
...
Kallie
4 months ago
I vaguely remember something about AWR snapshots, but I can't quite recall if they directly collect optimizer statistics or just monitor performance.
upvoted 0 times
...
Rutha
4 months ago
I'm a bit confused about the role of the Statistics Advisor. I don't recall it being a primary method for collecting optimizer statistics.
upvoted 0 times
...
Annice
4 months ago
I feel like I've seen a question about DBMS_STATS before, so I think option C might be correct since it mentions manual collection.
upvoted 0 times
...
Lucina
4 months ago
I think I remember that optimizer statistics can be collected automatically, but I'm not sure if it's through a maintenance job or something else.
upvoted 0 times
...
Suzi
4 months ago
The key is to remember that Optimizer Statistics can be collected in multiple ways - automatically, manually, and potentially even in real-time. I'll need to carefully evaluate each option to determine which two are accurate descriptions of the process.
upvoted 0 times
...
Natalie
5 months ago
Wait, I thought Optimizer Statistics were collected by the Statistics Advisor, not automatically. And what about real-time updates? I'm a bit confused on the details here, so I'll need to spend some time reviewing the material before answering.
upvoted 0 times
...
Mirta
5 months ago
Okay, I've got this. Option A and C are the correct answers - Optimizer Statistics are collected automatically by a maintenance job, and they can also be manually collected using DBMS_STATS procedures. I'm confident in this approach.
upvoted 0 times
...
Thersa
5 months ago
Hmm, I'm a bit unsure about this one. The options seem to cover different methods of collecting Optimizer Statistics, but I'm not entirely sure which ones are correct. I'll need to review my notes on this topic before attempting to answer.
upvoted 0 times
...
Avery
5 months ago
This looks like a straightforward question about how Optimizer Statistics are collected. I'll need to carefully read through the options and think about which ones accurately describe the process.
upvoted 0 times
...
Vicente
10 months 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
10 months 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
Caprice
8 months ago
Kathrine: Yeah, I'll remember that for next time.
upvoted 0 times
...
Shonda
8 months ago
User 3: Good to know, thanks for clarifying.
upvoted 0 times
...
Kathrine
8 months ago
User 2: It's actually A and C that describe how Optimizer Statistics are collected.
upvoted 0 times
...
Stephaine
9 months ago
User 1: I thought B was one of the options too.
upvoted 0 times
...
...
Felix
10 months 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
Juan
9 months ago
User 3: Yeah, A and C are the way to go for collecting Optimizer Statistics.
upvoted 0 times
...
Herminia
9 months ago
User 2: I agree, real-time collection seems excessive. A and C make more sense.
upvoted 0 times
...
Nieves
10 months 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
11 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
Flo
9 months ago
E) Optimizer Statistics are collected automatically by Automatic Workload Repository (AWR) Snapshot.
upvoted 0 times
...
Jeanice
9 months ago
C) Optimizer Statistics can be manually collected at multiple levels using DBMS_STATS.GATHER_*_STATS PL/SQL procedures.
upvoted 0 times
...
Michael
10 months ago
A) Optimizer Statistics are collected automatically by an automatic maintenance job that runs during predefined maintenance windows.
upvoted 0 times
...
...
Catalina
11 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
9 months ago
Yes, the automatic maintenance job and the DBMS_STATS procedures are the ways to collect Optimizer Statistics.
upvoted 0 times
...
Kelvin
10 months ago
I think A and C are the correct answers.
upvoted 0 times
...
...
Angelica
11 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
11 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
11 months ago
I think A and C are correct.
upvoted 0 times
...

Save Cancel