1Z0-084 practice question 4 of 15

Which procedure gathers statistics that are always used in the generation of any execution plan?

Full set: 55 questions
Answer options
A: DBMS_STATS.GATHER_DICTIONARY_STATS
B: DBMS_STATS.GATHER_FIXED_ OBJECTS_STATS
C: DBMS_STATS.GATHER_DATABASE_STATS
D: DBMS_STATS.GATHER_SYSTEM_STATS Correct answer
Correct Answer:
D. DBMS_STATS.GATHER_SYSTEM_STATS
Explanation

System statistics, gathered by DBMSSTATS.GATHERSYSTEMSTATS, describe the database server's hardware characteristics, such as I/O and CPU performance. The cost-based optimizer uses these statistics to model the cost of every operation (e.g., a full table scan vs. an index scan) when generating any execution plan. Unlike object statistics, which are specific to the tables and indexes in a query, system statistics provide the fundamental cost model for all optimizer decisions.

Why Incorrect

A. Dictionary statistics are only used for queries against data dictionary objects, not for any execution plan.
B. Fixed object statistics are only used for queries against fixed objects (X$ tables, V$ views), not for any execution plan.
C. Database (object) statistics are used for queries involving those specific objects, but the underlying cost model for operations relies on system statistics.

References

1. Oracle Database SQL Tuning Guide, 19c, "Optimizer Statistics Concepts", Section 4.2.4 "System Statistics". It states, "System statistics enable the query optimizer to more accurately estimate I/O and CPU costs."

2. Oracle Database PL/SQL Packages and Types Reference, 19c, "DBMSSTATS", "GATHERSYSTEMSTATS Procedure". The documentation describes this procedure as the method for gathering system statistics for the cost-based optimizer.

Scroll to Top