New optimizer statistics can change row estimates and plan choices. Pending statistics let you retain the published baseline while testing a candidate in an isolated session. Compare representative work, then make a deliberate publication or discard decision.
Oracle DBA Lesson 100 Part 2 — Test pending statistics before publication
Published and pending statistics
On Oracle 19c, both sets reside in the data dictionary and describe the same underlying table. Statistics are optimizer inputs. Gathering or deleting pending statistics does not insert or delete application rows.
| Setting or action | Meaning |
|---|---|
Table preference PUBLISH=FALSE, followed by a gather | Save newly gathered statistics as pending, and retain the published set. |
Session OPTIMIZER_USE_PENDING_STATISTICS=FALSE | Use published statistics. This is the parameter's default. |
Session OPTIMIZER_USE_PENDING_STATISTICS=TRUE | When compiling SQL, use pending statistics where available and published statistics for other tables. |
DBMS_STATS.PUBLISH_PENDING_STATS | Publish an existing candidate for general optimizer use. |
DBMS_STATS.DELETE_PENDING_STATS | Remove the candidate without replacing the published set. |
The session setting applies to all SQL compiled in that session, including other referenced tables with pending statistics. It is not a switch for only the next statement or only one table. Pending statistics are not private to the session that gathered them. Another session with OPTIMIZER_USE_PENDING_STATISTICS=TRUE can use them. Other sessions keep their own settings.
Changing PUBLISH back to TRUE controls subsequent gathers. Publish an existing candidate with DBMS_STATS.PUBLISH_PENDING_STATS. A compilation setting or a publication does not mean that every existing cursor immediately gets a new plan.
See controlling the use of optimizer statistics and OPTIMIZER_USE_PENDING_STATISTICS.
Gather a pending candidate
The comparison uses a disposable, ordinary, nonpartitioned permanent table, STATS_PENDING, owned by the connected user. Copy a bounded set of rows from ORDERS, including a numeric CUSTOMER_ID. A source with at least two customer values and unequal frequencies makes the later comparison useful.
CREATE TABLE stats_pending AS
SELECT * FROM orders WHERE ROWNUM <= 100;CREATE TABLE ... AS SELECT can collect statistics during creation. It does not reproduce all source indexes, constraints, or storage properties, so its plan is not proof about the source table. A small copy may use the same full scan in every test.
Record the session parameter and the table's effective and explicit PUBLISH preference before changing either one. Test the published baseline while OPTIMIZER_USE_PENDING_STATISTICS is FALSE. A missing preference row means there is no explicit table override, and the effective value is inherited.
SHOW PARAMETER optimizer_use_pending_statistics
SELECT DBMS_STATS.GET_PREFS('PUBLISH', USER, 'STATS_PENDING')
AS effective_publish
FROM dual;
SELECT preference_name, preference_value
FROM user_tab_stat_prefs
WHERE table_name = 'STATS_PENDING'
AND preference_name = 'PUBLISH';For this new table, establish a published baseline:
EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'STATS_PENDING','PUBLISH','TRUE');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'STATS_PENDING');Collection for a permanent table commits before gathering, and DDL commits as well. PUBLISH=TRUE applies to this gather and to later gathers until the preference changes.
To make the published baseline older than the current rows, duplicate the bounded copy and commit. An initial N rows becomes 2N. An empty source stays empty. The copy has no source primary-key constraint, so the duplicated rows are allowed. Finish data changes before the comparison, and keep the table unchanged across the baseline and candidate runs. Record published-query behavior before gathering the candidate.
INSERT INTO stats_pending SELECT * FROM stats_pending;
COMMIT;Save the new statistics as pending:
BEGIN
DBMS_STATS.SET_TABLE_PREFS(
USER, 'STATS_PENDING', 'PUBLISH', 'FALSE');
DBMS_STATS.GATHER_TABLE_STATS(USER, 'STATS_PENDING');
END;
/PUBLISH=FALSE persists until it is restored. It affects later gathers on this table, not only this call. Confirm both sets:
SELECT num_rows, last_analyzed
FROM user_tab_statistics
WHERE table_name = 'STATS_PENDING';
SELECT num_rows, last_analyzed
FROM user_tab_pending_stats
WHERE table_name = 'STATS_PENDING';NUM_ROWS is a collected statistic and can reflect sampling. LAST_ANALYZED records collection time. Different dates can help identify the candidate. Neither a newer date nor a particular row count establishes better query performance. Investigate an empty pending view before treating the gather as a candidate test.
See DBMS_STATS, ALL_TAB_PENDING_STATS, ALL_TAB_STAT_PREFS, and optimizer statistics concepts.
Compare the published and pending executions
For the published baseline, leave the session parameter FALSE. After gathering the candidate, opt in for its test:
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;For each statistics mode, test at least two predicate and selectivity cases. Use the same data, bind types, bind values, projection, and full-fetch behavior. Choose one frequent customer and one rare customer. The statement below binds 501.
VARIABLE customer NUMBER
EXEC :customer := 501;
SET TIMING ON
SELECT /*+ GATHER_PLAN_STATISTICS */ /* trial_pub_case_a_r01 */ *
FROM stats_pending
WHERE customer_id = :customer;Fetch every row. Use a fresh, never-before-used trial comment for each compilation: trial_pub_case_a_r01 for the published test and trial_pending_case_a_r01 for the pending test. Change only the comment between comparable runs. Bind the same value before each fresh parse. Use further unique tags for the other value and for repeat trials. A new comment requests a new SQL identity without flushing shared state. Verify the actual cursor. A new comment or a changed session parameter does not by itself prove the intended compilation.
Locate the executed query by its exact trial tag:
SELECT sql_id, child_number, plan_hash_value, executions, sql_text
FROM v$sql
WHERE sql_text LIKE
'SELECT /*+ GATHER_PLAN_STATISTICS */ /* trial_pub_case_a_r01 */%';Choose the child actually executed for that tagged trial. Record its real SQL_ID and CHILD_NUMBER, and enter those values when prompted. If several children exist, or the statement has aged out, resolve the identity or rerun a fresh tagged trial before comparing. Do not substitute another cursor because its plan looks familiar.
ACCEPT target_sql_id CHAR PROMPT 'Actual trial SQL_ID: '
ACCEPT target_child NUMBER PROMPT 'Actual trial CHILD_NUMBER: '
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
'&target_sql_id', &target_child, 'ALLSTATS LAST'));
SELECT name, value
FROM v$sql_optimizer_env
WHERE sql_id = '&target_sql_id'
AND child_number = &target_child
AND name = 'optimizer_use_pending_statistics';Oracle documents SELECT or READ access to V$SQL, V$SQL_PLAN, V$SESSION, and V$SQL_PLAN_STATISTICS_ALL for DBMS_XPLAN.DISPLAY_CURSOR. The environment query also reads V$SQL_OPTIMIZER_ENV.
Repeat the lookup with the exact pending-test tag for that execution. Confirm the compiled environment's setting together with the pending set and the trial identity. The check shows the compilation inputs. A missing view row does not prove which statistics were used. Resolve missing or ambiguous evidence before drawing a conclusion.
Compare estimated and actual plan rows at the relevant operations, and interpret actual rows with Starts as in the preceding lesson. Use buffers as logical work and elapsed time as timing evidence under comparable conditions. Keep per-execution figures separate from cumulative cursor totals. ALLSTATS LAST shows the last execution of the selected child when runtime statistics were collected. Complete the fetch before inspection. Client timing includes fetch and client effects, so use the same measurement method and repeat the trials. Account for cache warming, system load, bind peeking, and noise. An unchanged plan hash can still have different runtime behavior.
One comparison of logical gets per fully fetched execution:
| Predicate case | Published buffers | Pending buffers | Interpretation |
|---|---|---|---|
| Common value | 200 | 120 | Less logical work in this case. |
| Rare value | 40 | 90 | More logical work. Investigate this regression. |
The common value does less logical work, and the rare value does more. That mixed result is a reason to retain the baseline while the regression is investigated. A single improved case is not enough. An unchanged plan, or no improvement on the small copy, is a valid outcome. Record plan rows and repeated elapsed observations as well as buffers before a broader decision.
See DBMS_XPLAN and V$SQL_OPTIMIZER_ENV.
Discard the candidate or publish it
The default path discards the candidate:
ALTER SESSION SET optimizer_use_pending_statistics = FALSE;
EXEC DBMS_STATS.DELETE_PENDING_STATS(USER,'STATS_PENDING');Verify that the pending view has no rows for this table, that the published statistics remain, and that the session setting is FALSE. Resetting the session controls later compilations. It does not prove that a particular earlier cursor has been recompiled. Use the same identity and environment checks for any further query.
Restore the recorded preference when the table stays. If an explicit table PUBLISH override existed, restore that recorded value with SET_TABLE_PREFS. If no explicit override existed, remove only the temporary override:
EXEC DBMS_STATS.DELETE_TABLE_PREFS(USER,'STATS_PENDING','PUBLISH');Recheck GET_PREFS and USER_TAB_STAT_PREFS against the record. After the results are recorded, remove the copy:
DROP TABLE stats_pending PURGE;PURGE bypasses the recycle bin. Drop only the newly created copy. The source ORDERS table remains.
Publishing the candidate is a separate action:
BEGIN
DBMS_STATS.PUBLISH_PENDING_STATS(USER, 'STATS_PENDING');
END;
/Publication replaces the published optimizer inputs with the candidate and follows the cursor-invalidation behavior controlled by the relevant settings. An existing table needs an agreed acceptance criterion, representative workload coverage, a recorded explicit or inherited preference state, and a tested statistics-restore path before publication. Ordinary transaction rollback does not restore DDL or a statistics publication. Locking statistics controls their modification. Plan choice also depends on binds, optimizer settings, and other inputs, so a statistics lock is not an execution-plan lock.
Recap
Pending statistics hold a candidate while the published statistics stay in place. A gather with the table preference PUBLISH=FALSE saves that candidate. Ordinary sessions keep using published statistics while OPTIMIZER_USE_PENDING_STATISTICS remains FALSE, the default. Setting it to TRUE applies to SQL compiled in that session and can use pending statistics for every referenced table that has them. Setting PUBLISH back to TRUE controls later gathers. PUBLISH_PENDING_STATS publishes an existing candidate. Compare the same predicates under both modes. In the buffer example, a common value falls from 200 to 120 while a rare value rises from 40 to 90, so the baseline stays while that regression is investigated. Discarding the candidate returns the session parameter to FALSE, deletes the pending statistics, and restores the prior table preference. Publication replaces the published set, and ROLLBACK does not undo it.
No comments:
Post a Comment