11G新特性，待定的統計資訊

------------建立測試表
apollo@CRMG>drop table wxh_tbd;

Table dropped.

apollo@CRMG>create table wxh_tbd as select * from dba_objects;

Table created.

apollo@CRMG>exec dbms_stats.gather_table_stats(user,'wxh_tbd');

PL/SQL procedure successfully completed.

apollo@CRMG>create index t_t on wxh_tbd(object_type);

Index created.

-------------檢視執行計劃
apollo@CRMG>set autotrace traceonly
apollo@CRMG>select * from wxh_tbd where object_type='TABLE';

3456 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2676807931

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   502 | 46686 |    38   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WXH_TBD |   502 | 46686 |    38   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_T     |   502 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

-------------設定表的統計資訊模式為待定
apollo@CRMG>begin
  2  dbms_stats.set_table_prefs(ownname => user,
  3  tabname => 'wxh_tbd',
  4  pname => 'PUBLISH',
  5  pvalue => 'FALSE');
  6  END;
  7  /
PL/SQL procedure successfully completed.


------------更新object_type欄位，全部更新為TABLE值
apollo@CRMG>update wxh_tbd set object_type='TABLE';

19069 rows updated.
apollo@CRMG>commit;

Commit complete.


----------------重新收集統計資訊
apollo@CRMG>exec dbms_stats.gather_table_stats(ownname => user, tabname => 'wxh_tbd')

PL/SQL procedure successfully completed.

----------------檢視執行計劃。還是沒變
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   502 | 46686 |    38   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WXH_TBD |   502 | 46686 |    38   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_T     |   502 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


----------------設定optimizer_use_pending_statistics為true.發現執行計劃正確了
apollo@CRMG>ALTER SESSION SET optimizer_use_pending_statistics = TRUE;

Session altered.

apollo@CRMG>explain plan for
  2  select * from wxh_tbd where object_type='TABLE';

Explained.

apollo@CRMG>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 3295978849

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         | 19069 |  1713K|    76   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| WXH_TBD | 19069 |  1713K|    76   (2)| 00:00:01 |
-----------------------------------------------------------------------------

----------------釋出待定的統計資訊
apollo@CRMG>exec dbms_stats.publish_pending_stats(ownname => user, tabname => 'WXH_TBD',no_invalidate => false);

PL/SQL procedure successfully completed.

apollo@CRMG>explain plan for
  2  select * from wxh_tbd where object_type='TABLE';

Explained.

apollo@CRMG>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3295978849

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         | 19069 |  1713K|    76   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| WXH_TBD | 19069 |  1713K|    76   (2)| 00:00:01 |
-----------------------------------------------------------------------------


-----------別忘了，把表的待定狀態解除

apollo@CRMG>begin
  2  dbms_stats.set_table_prefs(ownname => user,
  3  tabname => 'wxh_tbd',
  4  pname => 'PUBLISH',
  5  pvalue => 'TRUE');
  6  END;
  7  /


PL/SQL procedure successfully completed.

參考資料：

Working with Pending Statistics
Usually, as soon as gathering statistics is finished, the object statistics are published (that is,
made available) to the query optimizer. This means that it is not possible (for testing purposes,
for instance) to gather statistics without overwriting the current object statistics. Of course, test
databases should be used for testing purposes, but sometimes it is not possible to do so; you
might want to do it in production. An example of this is when the data stored in the test database
is not the same as the data in the production database.
As of Oracle Database 11g, it is possible to separate gathering statistics from publishing
them, and it is possible to use objects statistics that are unpublished, which are called pending
statistics, for testing purposes. Here is the procedure (a full example is provided in the script
pending_object_statistics.sql):

1. Disable automatic publishing by setting the preference publish to FALSE (the default
value is TRUE). As described in the previous section, for other preferences, this can be
done at the global, database, schema, or table level. The following example shows how
to do it for the table t belonging to the current user:
dbms_stats.set_table_prefs(ownname => user,
tabname => 'T',
pname => 'PUBLISH',
pvalue => 'FALSE')
2. Gather object statistics for the table t belonging to the current user. Since the preference
publish is set to FALSE for this table, the newly gathered object statistics are not published.
This means the query optimizer keeps using the statistics available before their gathering.
At the same time, cursors depending on that table are not invalidated.
dbms_stats.gather_table_stats(ownname => user, tabname => 'T')
3. To test the impact of the new object statistics on an application or a set of SQL statements,
you can set the dynamic initialization parameter optimizer_use_pending_
statistics to TRUE at the session level. With this setting, the pending statistics are
available for the current session only.
ALTER SESSION SET optimizer_use_pending_statistics = TRUE
4. If the test is successful, the pending statistics can be published (in other words, made
available to all users) by calling the procedure publish_pending_stats. The following
example shows how to do it for a single table. If the parameter tabname is set to NULL, all
pending statistics of the specified schema are published. This procedure also has two
additional parameters. The third, no_invalidate, controls the invalidation of the cursors
depending on the modified object statistics. The fourth, force, is used to override a
potential lock of the statistics (the section “Locking Object Statistics” later in this chapter
describes such locks). Its default value is FALSE, which means that locks are honored
by default.
dbms_stats.publish_pending_stats(ownname => user, tabname => 'T')
5. If the test is not successful, you can delete the pending statistics by calling the procedure
delete_pending_stats. If the parameter tabname is not specified or set to NULL, pending
statistics for the whole schema specified by the parameter ownname are deleted.
dbms_stats.delete_pending_stats(ownname => user, tabname => 'T')
To execute the procedures publish_pending_stats and delete_pending_stats, you need to
be connected as owner or have the system privilege analyze any.
If you are interested to know the values of the pending statistics, the following data dictionary
views provide all the necessary information. For each view there are dba and all versions
as well.
• user_tab_pending_stats shows pending table statistics.
• user_ind_pending_stats shows pending index statistics.

• user_col_pending_stats shows pending column statistics.
• user_tab_histgrm_pending_stats shows pending histograms.
The content and structure of these data dictionary views is similar to user_tab_statistics,
user_ind_statistics, user_tab_col_statistics, and user_tab_histograms, respectively.