Test: Pending Statistics

I read from Oracle Document and interest about Pending Statistics. When Objects are gathered, the statistics will be automatically published. Check!!!

SQL> SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;

PUBLISH
----------
TRUE

Result = TRUE or FALSE. TRUE = the statistics will be published as and when they are gathered, FALSE = the statistics will be kept pending.

You can change PUBLISH at either the schema or the table level. This example, I use SH.SALES table. *** Before test, I delete statistics on this table.

SQL> select * from sales where PROD_ID=12;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 511273406
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 352 | 10208 | 94 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 352 | 10208 | 94 (0)| 00:00:01 | 1 | 28 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 352 | 10208 | 94 (0)| 00:00:01 | 1 | 28 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 |
---------------------------------------------------------------------------------------------------------------------

SQL> exec dbms_stats.DELETE_TABLE_STATS('SH','SALES');

PL/SQL procedure successfully completed.

SQL> select * from sales where PROD_ID=12;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1550251865
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 351 (1)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 1 | 87 | 351 (1)| 00:00:01 | 1 | 28 |
|* 2 | TABLE ACCESS FULL | SALES | 1 | 87 | 351 (1)| 00:00:01 | 1 | 28 |
---------------------------------------------------------------------------------------------

After I deleted statistics. Execution Plan - "TABLE ACCESS FULL". Then set preference (PUBLISH=false) for SALES table. *** not publish after it is gathered.

SQL> exec dbms_stats.set_table_prefs('SH','SALES','PUBLISH','false');

PL/SQL procedure successfully completed.

Gather SALES table and test...

SQL> exec dbms_stats.gather_table_stats('SH','SALES');

PL/SQL procedure successfully completed.

SQL> select * from sales where PROD_ID=12;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1550251865
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 351 (1)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 1 | 87 | 351 (1)| 00:00:01 | 1 | 28 |
|* 2 | TABLE ACCESS FULL | SALES | 1 | 87 | 351 (1)| 00:00:01 | 1 | 28 |
---------------------------------------------------------------------------------------------

Still "TABLE ACCESS FULL", because not use statistics. However use optimizer_use_pending_statistics=true for using pending statistics.

SQL> alter session set optimizer_use_pending_statistics=true;

Session altered.

SQL> select * from sales where PROD_ID=12;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 511273406
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 352 | 10208 | 94 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 352 | 10208 | 94 (0)| 00:00:01 | 1 | 28 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 352 | 10208 | 94 (0)| 00:00:01 | 1 | 28 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 |
---------------------------------------------------------------------------------------------------------------------

This shows new Execution Plan, because it used new statistics.
When Execution Plan shows good plan and want to publish (use dbms_stats.publish_pending_stats)... and if want to delete pending statistics(use dbms_stats.delete_pending_stats)

SQL> exec dbms_stats.publish_pending_stats('SH','SALES');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_pending_stats('SH','SALES');

PL/SQL procedure successfully completed.

Good!!! use Pending Statistics for test Execution Plan before Publish (statistics).