1Z0-182 practice question 9 of 15

Which two statements describe how Optimizer Statistics are collected?

Answer options

A: Optimizer Statistics are collected automatically by an automatic maintenance job that runsduring predefined maintenance windows. Correct answer

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

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

D: Optimizer Statistics are collected by the Statistics Advisor.

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

Correct Answer:

A. Optimizer Statistics are collected automatically by an automatic maintenance job that runsduring predefined maintenance windows.; C. Optimizer Statistics can be manually collected at multiple levels using DBMS_STATS.GATHER_*_STATS PL/SQL procedures.

Explanation

Optimizer statistics, which are crucial for generating efficient execution plans, are collected in two primary ways. First, Oracle Database includes an automated maintenance task, "auto optimizer stats collection," that runs during predefined maintenance windows to gather stale or missing statistics. Second, for more granular control or immediate updates, a Database Administrator can manually collect statistics at various levels (e.g., table, schema, database) using the procedures within the DBMSSTATS PL/SQL package, such as GATHERTABLESTATS or GATHERSCHEMASTATS.

Why Incorrect

B. While Real-Time Statistics exist for bulk loads, general DML operations do not trigger real-time statistics collection; it is primarily a scheduled or manual task.
D. The Statistics Advisor analyzes existing statistics and provides recommendations for improvement; it does not perform the collection itself.
E. The Automatic Workload Repository (AWR) collects performance metrics and workload data, not the optimizer statistics used for query planning.

References

Oracle Database 23ai SQL Tuning Guide, "Managing Optimizer Statistics", section "Automatic Optimizer Statistics Collection".

Oracle Database 23ai SQL Tuning Guide, "Managing Optimizer Statistics", section "Manual Optimizer Statistics Collection".

Oracle Database 23ai PL/SQL Packages and Types Reference, "DBMSSTATS", for a full list of manual gathering procedures.

Keep practicing

Take the free 1Z0-182 practice test

Ten exam-style questions with answers and explanations, plus the exam facts and study guides.

Start the free practice test