1Z0-064 · Question #1
You are administering a database that supports an OLTP workload. Users complain about the degraded response time of a query. You want to gather new statistics for objects accessed by the query and…
The correct answer is D. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE for the session in which E. Set the PUBLISH statistic preference to FALSE, and then gather statistics. See the full explanation below for the reasoning.
Question
You are administering a database that supports an OLTP workload. Users complain about the degraded response time of a query. You want to gather new statistics for objects accessed by the query and test query performance with the new statistics without affecting other sessions connected to the instance. The STALE_PERCENT statistic preference is set to a default value and the STATISTICS_LEVEL parameter is set to TYPICAL. Which two actions would you take to accomplish the task? (Choose two.)
Options
- ASet the STALE_PERCENT statistic preference to a higher value than the default, and then gather
- BSet the STATISTICS_LEVEL parameter to ALL for the instance.
- CSet the INCREMENTAL preference to TRUE, and then gather statistics.
- DSet the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE for the session in which
- ESet the PUBLISH statistic preference to FALSE, and then gather statistics.
- FSet the NO_INVALIDATE statistic preference to TRUE, and then gather statistics.
How the community answered
(34 responses)- A9% (3)
- B3% (1)
- C3% (1)
- D71% (24)
- F15% (5)
Community Discussion
12
Prof. SaraApr 10, 2026The correct answers are D and E, and the logic ties together perfectly: setting PUBLISH to FALSE before gathering causes Oracle to store the new statistics as pending rather than publishing them immediately, so no other session is affected, and then setting OPTIMIZER_USE_PENDING_STATISTICS to TRUE only for your session lets you test query performance against those pending statistics in isolation. Think of it as a two-key lock, PUBLISH=FALSE puts the stats in a staging area, and OPTIMIZER_USE_PENDING_STATISTICS=TRUE is the key that only your session uses to open that staging area for testing.
Bao N.Apr 12, 2026Good analogy but worth flagging that you also need to call DBMS_STATS.PUBLISH_PENDING_STATS after testing if you decide the stats are worth keeping, otherwise they just sit in the pending table indefinitely.
Yusuf A.May 26, 2026The correct answers are D and E, and once a senior walked me through this it clicked pretty fast. The key idea is that Oracle has a "pending statistics" staging area, so if you set the PUBLISH preference to FALSE before gathering, the new stats go there instead of replacing the live dictionary stats, which means every other session keeps running on the old numbers untouched. Then you flip OPTIMIZER_USE_PENDING_STATISTICS to TRUE at the session level only, so your own session tests the query against the fresh stats while nobody else even knows anything changed. The other options do not isolate the change to a single session, for example setting STATISTICS_LEVEL to ALL is an instance-wide change that adds overhead for everyone, and NO_INVALIDATE controls cursor invalidation but does not keep the stats pending or hidden from other sessions.
Nina C.May 28, 2026Good breakdown, and the piece I kept forgetting on practice questions is that once your session confirms the pending stats look right, you still have to call DBMS_STATS.PUBLISH_PENDING_STATS to actually push them live, or DBMS_STATS.DELETE_PENDING_STATS to throw them away, so the workflow is not done just because the testing session looked good.
Marisol N.Mar 25, 2026D and E are correct, PUBLISH FALSE keeps stats pending, session-level only.
Mateus R.Mar 18, 2026Think of pending statistics like a new menu that the restaurant printed but locked in the manager's office, so only the manager can read it while customers still see the old menu. If you set PUBLISH to FALSE before gathering, the new stats sit in a holding area and do not go live for anyone, and then flipping OPTIMIZER_USE_PENDING_STATISTICS to TRUE only for your own session is how you, the manager, sneak a look at that locked menu to test it. Here is what I am curious about though: if you had left PUBLISH at its default of TRUE and gathered stats normally, would the old cursors get invalidated right away for everyone, or does NO_INVALIDATE give you any real protection there in a busy OLTP system?
Prof. SaraMar 20, 2026YES to your analogy, and to answer your question directly: the default NO_INVALIDATE of DBMS_STATS.AUTO_INVALIDATE is your real OLTP friend here because instead of a thundering-herd hard wipe, Oracle staggers cursor invalidations over a window controlled by the hidden parameter _optimizer_invalidation_period (roughly five hours), so the shared pool does not crater all at once the way it would if you explicitly passed NO_INVALIDATE => FALSE.
Nina C.Jun 5, 2026So if I'm reading this right, E puts the new stats in a "pending" state instead of publishing them live, and D lets only your session see those pending stats, which is how you test without touching everyone else. Can someone confirm whether OPTIMIZER_USE_PENDING_STATISTICS has to be set at the session level specifically, or would setting it at the instance level still protect other users?
Yusuf A.Jun 5, 2026Setting it at the instance level would actually expose those pending stats to every session on the database, so you have to use the session level if you want your testing to stay isolated from everyone else.
Bao N.Apr 21, 2026Honestly I kept circling back to F thinking NO_INVALIDATE would protect other sessions, but that just controls cursor invalidation, it has nothing to do with keeping new stats hidden from the rest of the instance. Once I remembered that PUBLISH FALSE drops the stats into a pending state where only sessions with OPTIMIZER_USE_PENDING_STATISTICS set to TRUE can see them, D and E clicked immediately as the only pair that lets you test in isolation without touching anyone else.
Fatima Z.Mar 30, 2026CRUNCH and PUBLISH, picture a news editor who holds the story before it hits print, that is option E locking stats as pending, and D is your session flipping the switch to read those held stats only for you. INCREMENTAL is a partitioning trick, not a pending stats tool, so C is a trap that sounds smart but misfires here. D plus E, full stop.
Bao N.Mar 30, 2026Fatima, you actually landed on D and E, which is exactly right, so no correction needed there. CRUNCH with PUBLISH option E holds stats in pending, and D lets your session read those pending stats before they go live.