Oracle12cR1 new feature-Online Statistics Gathering

在Oracle12cR1这个版本里面,如果执行了ctas或者是insert into select之类的语句,调用了批量direct api的。都会触发收集统计信息的动作。首先我们来看一下这项新功能,我们先开启一个dbms_stats的0x10000(转换成十进制是65536)跟踪模式。这里要注意我的测试不是用sys执行的,是切换到个人用户下执行的。因为sys用户这个功能是受限制的。而DBMS_STATS开了跟踪会打印收集统计信息的具体操作

[oracle@ol6 ~]$ sqlplus u1/u1@pdb
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 1 23:19:34 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set serveroutput on
SQL> exec dbms_stats.set_global_prefs('trace',1+65536);

SQL> create table test as select * from dba_tables; 
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 1         1         1         to_char(count("OWNER"))                                                68
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 2         1         1         substrb(dump(min("OWNER"),16,0,64),1,240)                              9
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 3         1         1         substrb(dump(max("OWNER"),16,0,64),1,240)                              17
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 4         2         2         to_char(count("TABLE_NAME"))                                           68
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 5         2         2         substrb(dump(min("TABLE_NAME"),16,0,64),1,240)                         9
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 6         2         2         substrb(dump(max("TABLE_NAME"),16,0,64),1,240)                         17
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 7         3         3         to_char(count("TABLESPACE_NAME"))                                      100
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 8         3         3         substrb(dump(min("TABLESPACE_NAME"),16,0,64),1,240)                    9
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 9         3         3         substrb(dump(max("TABLESPACE_NAME"),16,0,64),1,240)                    17
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 10        4         4         to_char(count("CLUSTER_NAME"))                                         100
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 11        4         4         substrb(dump(min("CLUSTER_NAME"),16,0,64),1,240)                       9
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 12        4         4         substrb(dump(max("CLUSTER_NAME"),16,0,64),1,240)                       17
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 13        5         5         to_char(count("IOT_NAME"))                                             100
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 14        5         5         substrb(dump(min("IOT_NAME"),16,0,64),1,240)                           9
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 15        5         5         substrb(dump(max("IOT_NAME"),16,0,64),1,240)                           17
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------
DBMS_STATS: 16        6         6         to_char(count("STATUS"))                                               100
DBMS_STATS: SELMAPPOS CLISTIDX  INTCOL    SELITEM                                                                GATHFLG
DBMS_STATS: ----------------------------------------------------------------------------------------------------

通过查看执行计划,可以发现在执行计划中有一条”OPTIMIZER STATISTICS GATHERING”的操作。

SQL> select * from table(dbms_xplan.display_cursor('4sf3vb7sb5hwp'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4sf3vb7sb5hwp, child number 0
-------------------------------------
create table test as select * from dba_tables

Plan hash value: 964865090

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT                      |                  |       |       |  1169 (100)|          |
|   1 |  LOAD AS SELECT                             | TEST             |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING            |                  |  2272 |  2059K|  1035   (1)| 00:00:01 |
|*  3 |    FILTER                                   |                  |       |       |            |          |
|*  4 |     HASH JOIN                               |                  |  2272 |  2059K|  1035   (1)| 00:00:01 |
|   5 |      TABLE ACCESS FULL                      | TS$              |     8 |   152 |     5   (0)| 00:00:01 |
|*  6 |      HASH JOIN RIGHT OUTER                  |                  |  2272 |  2016K|  1030   (1)| 00:00:01 |
|   7 |       TABLE ACCESS FULL                     | USER$            |   130 |  2340 |     4   (0)| 00:00:01 |
|   8 |       NESTED LOOPS OUTER                    |                  |  2272 |  1976K|  1026   (1)| 00:00:01 |
|*  9 |        HASH JOIN OUTER                      |                  |  2272 |   776K|  1026   (1)| 00:00:01 |
|  10 |         NESTED LOOPS OUTER                  |                  |  2272 |   758K|   968   (1)| 00:00:01 |
|* 11 |          HASH JOIN RIGHT OUTER              |                  |  2272 |   670K|   798   (1)| 00:00:01 |
|  12 |           TABLE ACCESS FULL                 | SEG$             |  2399 |   107K|    21   (0)| 00:00:01 |
|* 13 |           HASH JOIN                         |                  |  2132 |   533K|   777   (1)| 00:00:01 |
|  14 |            TABLE ACCESS FULL                | USER$            |   130 |  2340 |     4   (0)| 00:00:01 |
|* 15 |            HASH JOIN RIGHT OUTER            |                  |  2132 |   495K|   773   (1)| 00:00:01 |
|  16 |             TABLE ACCESS FULL               | DEFERRED_STG$    |  3361 | 97469 |     9   (0)| 00:00:01 |
|* 17 |             HASH JOIN                       |                  |  2132 |   435K|   764   (1)| 00:00:01 |
|* 18 |              TABLE ACCESS FULL              | TAB$             |  2132 |   172K|   430   (0)| 00:00:01 |
|* 19 |              HASH JOIN                      |                  | 72635 |  8937K|   333   (1)| 00:00:01 |
|  20 |               MERGE JOIN CARTESIAN          |                  |   130 |  8710 |     1   (0)| 00:00:01 |
|  21 |                NESTED LOOPS                 |                  |     1 |    43 |     0   (0)|          |
|* 22 |                 FIXED TABLE FIXED INDEX     | X$KSPPI (ind:1)  |     1 |    31 |     0   (0)|          |
|* 23 |                 FIXED TABLE FIXED INDEX     | X$KSPPCV (ind:1) |     1 |    12 |     0   (0)|          |
|  24 |                BUFFER SORT                  |                  |   130 |  3120 |     1   (0)| 00:00:01 |
|  25 |                 INDEX FULL SCAN             | I_USER2          |   130 |  3120 |     1   (0)| 00:00:01 |
|* 26 |               TABLE ACCESS FULL             | OBJ$             | 72635 |  4185K|   332   (1)| 00:00:01 |
|  27 |          TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$             |     1 |    40 |     2   (0)| 00:00:01 |
|* 28 |           INDEX RANGE SCAN                  | I_OBJ1           |     1 |       |     1   (0)| 00:00:01 |
|  29 |         INDEX FAST FULL SCAN                | I_OBJ1           | 72635 |   567K|    57   (0)| 00:00:01 |
|  30 |        TABLE ACCESS BY INDEX ROWID BATCHED  | IMSVC$           |     1 |   541 |     0   (0)|          |
|* 31 |         INDEX RANGE SCAN                    | I_IMSVC1         |     1 |       |     0   (0)|          |
|* 32 |     TABLE ACCESS FULL                       | USER_EDITIONING$ |     1 |     6 |     2   (0)| 00:00:01 |
|* 33 |     TABLE ACCESS FULL                       | USER_EDITIONING$ |     1 |     6 |     2   (0)| 00:00:01 |
|  34 |     NESTED LOOPS SEMI                       |                  |     1 |    29 |     2   (0)| 00:00:01 |
|* 35 |      INDEX SKIP SCAN                        | I_USER2          |     1 |    20 |     1   (0)| 00:00:01 |
|* 36 |      INDEX RANGE SCAN                       | I_OBJ4           |     1 |     9 |     1   (0)| 00:00:01 |

可以看到表上的统计信息已经收集了,列上的统计它也收集了,但是它没有收集列上的直方图。如果有索引的话,它也不会收集索引的统计信息。

SQL> select last_analyzed,num_rows,blocks from user_tables where table_name='TEST';

LAST_ANAL   NUM_ROWS     BLOCKS
--------- ---------- ----------
03-JUN-18       2129        100

SQL> select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,HISTOGRAM from user_tables where table_name='TEST';

TABLE_NAME                     COLUMN_NAME                              NUM_DISTINCT HISTOGRAM
------------------------------ ---------------------------------------- ------------ ---------------
TEST                           CONTAINER_MAP_OBJECT                                1 NONE
TEST                           INMEMORY_SERVICE_NAME                               0 NONE
TEST                           INMEMORY_SERVICE                                    0 NONE
TEST                           EXTENDED_DATA_LINK_MAP                              1 NONE
TEST                           EXTENDED_DATA_LINK                                  1 NONE
TEST                           CONTAINER_MAP                                       1 NONE
TEST                           CONTAINERS_DEFAULT                                  1 NONE
TEST                           CELLMEMORY                                          0 NONE
TEST                           EXTERNAL                                            2 NONE
TEST                           SHARDED                                             1 NONE
TEST                           DUPLICATED                                          1 NONE
TEST                           DEFAULT_COLLATION                                   1 NONE
TEST                           INMEMORY_DUPLICATE                                  0 NONE
TEST                           INMEMORY_COMPRESSION                                0 NONE
TEST                           INMEMORY_DISTRIBUTE                                 0 NONE
TEST                           INMEMORY_PRIORITY                                   0 NONE
TEST                           INMEMORY                                            1 NONE
TEST                           CONTAINER_DATA                                      1 NONE
TEST                           HAS_IDENTITY                                        1 NONE
TEST                           DML_TIMESTAMP                                       0 NONE
TEST                           ACTIVITY_TRACKING                                   0 NONE
TEST                           CLUSTERING                                          1 NONE
TEST                           RESULT_CACHE                                        1 NONE
TEST                           SEGMENT_CREATED                                     3 NONE
TEST                           READ_ONLY                                           2 NONE
TEST                           DROPPED                                             1 NONE
TEST                           COMPRESS_FOR                                        1 NONE
TEST                           COMPRESSION                                         1 NONE
TEST                           DEPENDENCIES                                        1 NONE
TEST                           CLUSTER_OWNER                                       1 NONE
TEST                           MONITORING                                          2 NONE
TEST                           SKIP_CORRUPT                                        1 NONE
TEST                           DURATION                                            2 NONE
TEST                           USER_STATS                                          1 NONE
TEST                           GLOBAL_STATS                                        2 NONE
TEST                           ROW_MOVEMENT                                        2 NONE
TEST                           CELL_FLASH_CACHE                                    1 NONE
TEST                           FLASH_CACHE                                         1 NONE
TEST                           BUFFER_POOL                                         1 NONE
TEST                           NESTED                                              2 NONE
TEST                           SECONDARY                                           1 NONE
TEST                           TEMPORARY                                           2 NONE
TEST                           IOT_TYPE                                            2 NONE
TEST                           PARTITIONED                                         2 NONE
TEST                           LAST_ANALYZED                                     203 NONE
TEST                           SAMPLE_SIZE                                       198 NONE
TEST                           TABLE_LOCK                                          1 NONE
TEST                           CACHE                                               1 NONE
TEST                           INSTANCES                                           3 NONE
TEST                           DEGREE                                              3 NONE
TEST                           NUM_FREELIST_BLOCKS                                 1 NONE
TEST                           AVG_SPACE_FREELIST_BLOCKS                           1 NONE
TEST                           AVG_ROW_LEN                                       164 NONE
TEST                           CHAIN_CNT                                           1 NONE
TEST                           AVG_SPACE                                           1 NONE
TEST                           EMPTY_BLOCKS                                        1 NONE
TEST                           BLOCKS                                             49 NONE
TEST                           NUM_ROWS                                          198 NONE
TEST                           BACKED_UP                                           1 NONE
TEST                           LOGGING                                             2 NONE
TEST                           FREELIST_GROUPS                                     1 NONE
TEST                           FREELISTS                                           1 NONE
TEST                           PCT_INCREASE                                        0 NONE
TEST                           MAX_EXTENTS                                         1 NONE
TEST                           MIN_EXTENTS                                         1 NONE
TEST                           NEXT_EXTENT                                         6 NONE
TEST                           INITIAL_EXTENT                                     11 NONE
TEST                           MAX_TRANS                                           2 NONE
TEST                           INI_TRANS                                           5 NONE
TEST                           PCT_USED                                            3 NONE
TEST                           PCT_FREE                                            4 NONE
TEST                           STATUS                                              1 NONE
TEST                           IOT_NAME                                           22 NONE
TEST                           CLUSTER_NAME                                       10 NONE
TEST                           TABLESPACE_NAME                                     4 NONE
TEST                           TABLE_NAME                                       2123 NONE
TEST                           OWNER                                              20 NONE
有一些情况下是不会收集统计信息的。
  • a.SYS用户执行的操作.
  • b.嵌套表.
  • c.索引组织表.
  • d.外部表.
  • e.全局临时表(定义为ON COMMIT DELETE ROWS).
  • f.包含虚拟列的表.
  • g.统计信息是那种待发布的状态.
  • h.统计信息是锁定的。
  • i.表是分区表,但是属性INCREMENTAL设置了true。在这种情况下,执行INSERT INTO sales SELECT数据库不收集统计,即使sales是空的。
  • j.使用多个表的insert语法
  • k.数据库启动到了restricted模式.

我们来做个测试,把数据库修改成restricted模式。收集一下10053。就会发现下面的内容。

alter system enable restricted session;
set serveroutput on
create table test1 as select * from dba_tables

ONLINEST: Checking validity of online stats gathering
ONLINEST: Failed validity check: database not open, in restricted/migrate mode, suspended, instance not open or OCI not available.

那么怎么才能关闭这个功能呢?我们可以通过hint NO_GATHER_OPTIMIZER_STATISTICS来阻止自动收集统计信息。当然要彻底的关闭还可以修改隐含参数“_optimizer_gather_stats_on_load”为false.

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569