Oracle Pending Statistics: Test Before You Publish

Oracle pending statistics let you gather a candidate set of optimizer statistics, test it in a selected session, and decide whether to publish it for wider use. I use that separation as the organising principle for a change: collect evidence first, make the decision second, and keep the cleanup step written down before either.

This is an Oracle 19c runbook for a controlled table-level experiment. It is an illustrative procedure, not a report of a customer incident or a claim that the commands below have been executed against your database.

What are Oracle pending statistics?

Oracle pending statistics are newly collected optimizer statistics stored separately from the published statistics normally used to compile statements. A session can opt into the pending set for evaluation, while other sessions continue using published statistics by default. The candidate can then be published or discarded after its behaviour has been assessed.

The distinction is about compilation inputs. Statistics describe such things as table size and column distributions; they help the optimizer estimate the cost and size of the work it might perform.

They do not freeze the underlying rows. If the application changes the table during your test, the test data can change even though the candidate statistics remain the same.

There are also two controls to keep separate. The table's PUBLISH preference determines what happens when statistics are gathered, while the session parameter determines whether that session may use an available pending set.

Changing only the session parameter does not create a candidate. Changing only the publishing preference does not tell a normal application session to use that candidate.

When should you test pending statistics instead of gathering and hoping?

A pending-statistics exercise is useful when a statistics change deserves a decision before wider exposure. A sensitive reporting table, a changed data distribution or a known history of plan changes can justify the extra work.

That does not make it the default response to every slow query. First establish whether the statement is waiting on a lock, reading more data, facing resource contention or actually compiling a different plan.

My general guide to Oracle optimizer statistics explains gathering, histograms and the automatic job. This article goes deeper into the narrower publish-or-reject workflow rather than repeating that overview.

Write down the question the test will answer. Define whether this candidate improves the affected report without harming representative transactional statements, rather than asking vaguely whether the new statistics are better.

What should you record before changing anything?

Start with a short change sheet, not a gather call. Record the database and container, object owner, table name, affected statements and the person who will decide whether the evidence is sufficient.

Include a small set of application behaviours that must remain acceptable. A report finishing sooner is not enough if a frequent lookup becomes much more expensive or the gather itself overruns the agreed window.

The commands below use APP.ORDERS as an illustrative object. Replace it with an authorised test target, connect to the correct database or PDB, and check the actual table and statistics policy before running a modifying procedure.

Run with the privileges appropriate to the object and the procedure. Oracle's DBMS_STATS reference documents the security model; querying DBA views also requires suitable dictionary access.

Do not work around a missing grant by taking a more powerful account and carrying on. A privilege error is a reason to check the intended operator, especially when a schema-wide operation could touch more than the approved table.

-- Read-only SQL: record the effective publishing preference.
SELECT DBMS_STATS.GET_PREFS('PUBLISH', 'APP', 'ORDERS')
       AS publish_preference
FROM dual;

Record whether there was an explicit table preference as well as its effective value. An inherited setting and a table override can currently produce the same answer, but they need different cleanup if the aim is to restore the original configuration.

Also check for an existing pending candidate, locked statistics and other work on the same object. Do not overwrite another person's experiment or silently unlock statistics simply because your gather was refused.

How do you turn the test into a controlled change?

I divide the work into three decisions: may we collect a candidate, is the evidence representative, and may we publish it? Keeping those decisions separate prevents a successful gather from being mistaken for a successful application test.

A useful preparation checklist has explicit stop conditions. Examples are an unexpected container, an existing candidate with no owner, a missing recovery path, or a workload that cannot be reproduced safely.

Choose a start and finish for the experiment. A publishing preference left false indefinitely can affect later gathers, including work performed by a different operator who reasonably expects the normal policy.

That final box is part of the experiment. Publishing a candidate and restoring the PUBLISH preference are separate tasks, so put both on the worksheet rather than trusting memory at the end of a maintenance window.

How do you gather a table-level pending candidate?

After the preparation checks, change the publishing preference only for the agreed table. Table scope makes the effect easier to explain than a schema-wide change, though it still needs normal change control.

-- Modifies the table's statistics preference.
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(
    ownname => 'APP', tabname => 'ORDERS',
    pname   => 'PUBLISH', pvalue => 'FALSE');
END;
/

-- Gathers statistics; it consumes database resources.
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'APP', tabname => 'ORDERS');
END;
/

This deliberately short gather call does not invent a new sampling percentage, histogram policy or parallel degree. Review the applicable preferences and your existing gathering policy instead of copying unexplained tuning arguments from an unrelated example.

Verify that a pending set was created for the intended object. Oracle documents the pending-statistics views for tables, columns and indexes; the table view also covers partitions and subpartitions.

A successful procedure return proves that the call completed, not that the candidate represents the workload. Keep the gather timestamp and object scope beside the later test results so nobody compares against the wrong candidate.

Pending collection still performs work. Schedule and monitor its CPU, I/O and elapsed time as you would any other statistics gather, and agree what happens if it runs longer than expected.

Why is a dedicated test session important?

Oracle's parameter reference says OPTIMIZER_USE_PENDING_STATISTICS controls whether pending statistics are used when compiling SQL. Its default is false, and it can be changed at session or system scope.

For a controlled evaluation, use session scope. Enabling the parameter across the database widens the experiment and defeats the simple explanation that one selected session is testing the candidate.

-- SQL in a dedicated test session, not a pooled application session.
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;

-- Run the approved representative workload here.
-- Record its actual execution evidence and bind values.

ALTER SESSION SET optimizer_use_pending_statistics = FALSE;

Do not leave the opt-in setting on a connection that is returned to an application pool. Prefer a clearly identified testing connection that is closed after the exercise, and record the reset even if the session will be disconnected.

The parameter applies when statements are compiled. A previously cached cursor can make a supposedly new comparison less clear, so verify the compilation context rather than assuming that changing a session setting proves which statistics every execution used.

Do not flush the shared pool on production merely to simplify this test. Agree a controlled method with the application and database owners, or use a representative isolated environment when compilation and load cannot be controlled safely.

What should a fair workload comparison include?

The useful comparison is between conditions you can describe, not between two screenshots labelled before and after. Record the statement, bind values, statistics mode, compilation context and measurement window for each run.

Include common and less common inputs where distribution matters. For example, a frequently ordered product and a rarely ordered product can exercise different cardinality assumptions; label the chosen inputs as test cases rather than presenting invented production volumes.

Keep other variables stable where practical. A changed application build, a different session setting, a quieter system or warm caches can make a candidate look better without demonstrating that the statistics caused the improvement.

Measure more than wall-clock time. The actual plan, row estimates versus observed rows, logical work and resource consumption can explain a result that elapsed time alone hides.

Use observability that is available and authorised in your environment. This runbook does not require you to enable a separately licensed feature or start an unbounded trace just to complete a comparison.

These are worksheet fields, not measured results or pass thresholds. Set acceptance criteria with the application owner before seeing the numbers, and keep a failed or inconclusive result visible.

A single fast execution is a useful clue. It is not permission to claim that the candidate improves the whole application, particularly when the affected table participates in many different statements.

What happens when a query joins tables with different statistics states?

A session opting into pending statistics can still use published statistics for an object that has no pending candidate. Oracle's guide explicitly describes that mixed case.

Write the participating object list into the test record. If one table supplies pending statistics and another supplies published statistics, say so; calling the entire query a “pending-statistics plan” can hide an important detail.

Partitioned objects deserve the same care. Confirm whether the candidate and the gather scope concern the table, particular partitions, dependent indexes or other relevant statistics, rather than extending a table-level conclusion to the whole maintenance design.

If the experiment grows beyond its original scope, pause and revise the plan. Expanding the candidate to several objects after a disappointing first test makes it harder to know which change produced the later result.

How do you publish an accepted candidate?

Publishing is the wider-impact step. It makes the pending statistics available as published statistics, so take it only after the evidence and the agreed change decision are recorded.

-- Modifies published statistics for this named table.
BEGIN
  DBMS_STATS.PUBLISH_PENDING_STATS(
    ownname => 'APP', tabname => 'ORDERS');
END;
/

Keep the table name explicit. Oracle's package reference says a null table name can widen publication to all tables of the specified schema, which is not the scope of this example.

Review cursor invalidation behaviour before publication. The procedure supports NO_INVALIDATE, and its default can be influenced by preferences; do not promise that every application execution immediately uses a newly compiled plan.

Monitor the actual application afterwards. The test establishes a reasoned decision under its recorded conditions, not a permanent guarantee against future data changes, plan changes or a different workload mix.

The distinction also helps during an incident. Write “candidate published at this time; these statements observed afterwards” rather than “statistics fixed the database,” unless the evidence supports that broader claim.

How do you reject a candidate and plan recovery?

If the candidate is harmful or the evidence is inconclusive, do not publish it simply because the maintenance window is closing. Record the finding and remove the pending candidate when that is the agreed outcome.

-- Deletes the pending candidate; it is not a table-data rollback.
BEGIN
  DBMS_STATS.DELETE_PENDING_STATS(
    ownname => 'APP', tabname => 'ORDERS');
END;
/

Deleting pending statistics is different from restoring a previous published set. If the candidate has already been published, use a separately planned statistics-recovery procedure appropriate to your history and retention, not the assumption that deleting a pending set reverses publication.

Oracle maintains statistics history, and its DBMS_STATS facilities include restore and export operations. Check that the required history or saved statistics exists before the change; a recovery step that depends on unavailable history is only a sentence in a document.

Do not describe these procedures as a transaction you can undo with an ordinary SQL ROLLBACK. They are statistics-management operations with their own documented behaviour and operational consequences.

What if the experiment is interrupted?

An interrupted gather, a lost testing connection and a completed publication with unfinished cleanup are different states. The next operator needs to know which state exists before deciding whether anything should be repeated.

Start with a read-only reconciliation of the object, its effective preference, the pending candidate and the recorded change history. A disconnected console is not evidence that a database operation did or did not complete.

If a candidate exists but its owner or test record is unclear, do not gather again merely to obtain a fresh timestamp. Reconcile the candidate with the person responsible for the change, because another collection can make the earlier comparison harder to interpret.

If the dedicated testing session was lost, reconnect deliberately and establish a new session context. Do not assume the new connection inherited the old opt-in parameter or that its first execution is comparable with the previous run.

If publication finished but preference cleanup did not, complete the configuration reconciliation separately from application monitoring. The application may now use the published set while a later gather still follows the temporary publishing preference.

This is also a useful handover drill before a sensitive change: ask another DBA what they would inspect if the operator became unavailable halfway through. A procedure that answers that question is easier to recover than one that assumes every step will finish in one uninterrupted console session.

What does restoring an inherited preference look like?

Suppose the pre-change record confirms that APP.ORDERS had no explicit table-level PUBLISH preference. In that case, removing the temporary override restores inheritance; setting an arbitrary value of TRUE would leave a new override behind.

-- Oracle 19c PL/SQL; modifying cleanup, not a read-only check.
-- Use ONLY when the pre-change record confirms no PUBLISH override existed.
BEGIN
  DBMS_STATS.DELETE_TABLE_PREFS(
    ownname => 'APP', tabname => 'ORDERS',
    pname   => 'PUBLISH');
END;
/

-- Read back the effective value after cleanup.
SELECT DBMS_STATS.GET_PREFS('PUBLISH', 'APP', 'ORDERS')
       AS publish_preference
FROM dual;

If a table override existed before the experiment, use SET_TABLE_PREFS to restore its saved value instead. Do not run both cleanup branches, and do not confuse DELETE_TABLE_PREFS, which removes the named preference, with DELETE_TABLE_STATS, which deletes statistics.

The final GET_PREFS reading confirms the effective value, not whether that value was inherited. Retain the pre-change configuration record and the actual cleanup operation so the next operator can distinguish the two.

Oracle's package reference documents the procedure and its ownname, tabname and pname arguments. This example deliberately names one table and one preference; it neither removes every preference nor claims that deleting a preference reverses a published candidate.

What must the closing handover contain?

Restore the publishing preference to its original configuration after the decision. If an explicit table override existed, restore its saved value; if you introduced an override where the table previously inherited its setting, remove that override using the documented preference-management procedure.

Do not assume the original value was true. Recording it at the start is what lets the closing action be precise rather than a well-intentioned new configuration change.

1. Record whether the candidate was published, rejected or retained under a named owner.
2. Confirm the testing session was reset or disconnected.
3. Restore the original preference configuration and verify the effective setting.
4. Attach the workload comparison and any observed regressions.
5. Name the person watching the next representative application window.
6. Keep the recovery reference, change time and follow-up decision together.

A useful closing note can be short: object, candidate timestamp, decision, preference state and next observation. It should let another DBA distinguish an intentional retained candidate from forgotten work without reconstructing the entire chat.

For plan stability after statistics changes, see my guide to SQL Plan Management and baseline selection. For diagnosing whether a slowdown is a statistics problem at all, start with the Oracle performance troubleshooting workflow.

The practical next step with Oracle pending statistics is to write the comparison and cleanup sheet before gathering anything. A modest test with a clear decision is more useful than an impressive gather followed by an undocumented guess.

Frequently Asked Questions

Do pending statistics affect every session?

Not by default. The session parameter defaults to false, so a dedicated session must opt into available pending statistics for compilation; changing the parameter at system scope would deliberately widen that behaviour.

Does PUBLISH=FALSE stop the statistics gather?

No. It changes whether the newly collected statistics are published immediately, while the gather still consumes resources and needs an appropriate operating window.

Does publishing pending statistics restore PUBLISH to true?

Treat publication and preference management as separate operations. Restore the original preference configuration explicitly and verify it, rather than assuming publication performs that cleanup.

Can I test a query when only one joined table has pending statistics?

Yes, Oracle documents using pending statistics where available and published statistics for the other objects. Record that mixed scope so the test conclusion describes what was actually evaluated.

Can I undo publication by deleting pending statistics?

No: deleting a pending candidate is not a recovery procedure for an already published set. Plan published-statistics recovery separately, and verify the required history or exported statistics before the change.

Should I flush the production shared pool to make the test simple?

Do not make a broad production change merely to simplify this experiment. Verify compilation context through an agreed testing method, and use a suitable isolated environment when representative testing cannot be controlled safely.

Is one faster execution enough to approve the candidate?

It is evidence about that execution, not the workload as a whole. Compare representative statements, inputs and resource readings against criteria agreed before the test.

Primary references

• Oracle 19c SQL Tuning Guide: controlling the use of optimizer statistics
• Oracle 19c package reference: DBMS_STATS procedures and security
• Oracle 19c parameter reference: OPTIMIZER_USE_PENDING_STATISTICS