【新特性】03.实时统计信息收集
Oracle Database 19c当中的实时统计信息收集是自动打开的，如果不想使用，可以通过Hint（NO_GATHER_OPTIMIZER_STATISTICS）来阻止收集。统计值实时收集，是通过存储过程进行操作的，有如下存储过程可用：
| 子程序 | 描述 |
| EXPORT_TABLE_STATS and EXPORT_SCHEMA_STATS | 这些子程序可以导出统计信息，默认stat_category参数包含实时统计，REALTIME_STATS值仅指定实时统计 |
| IMPORT_TABLE_STATS and IMPORT_SCHEMA_STATS | 这些子程序可以导入统计信息，默认stat_category参数包含实时统计，REALTIME_STATS值仅指定实时统计 |
| DELETE_TABLE_STATS and DELETE_SCHEMA_STATS | 这些子程序可以删除统计信息，默认stat_category参数包含实时统计，REALTIME_STATS值仅指定实时统计 |
| DIFF_TABLE_STATS_IN_STATTAB | 此函数用于比较来自两个源的表统计信息。这些统计数据通常包括实时统计数据 |
| DIFF_TABLE_STATS_IN_HISTORY | 此函数用于将表的统计信息与两个指定的时间戳进行比较。这些统计数据通常包括实时统计数据 |
如果想通过view进行查询，可以通过如下视图进行查询。需要注意的是，目前不支持分区统计值的查询。
| 视图 | 描述 |
| DBA_TAB_COL_STATISTICS | 此视图显示从DBA_TAB_COLUMN列中提取的列统计信息和直方图信息，实时统计信息由NOTES列中STATS_ON_CONVENTIONAL_DML表示，并且在SCOPE列中共享 |
| DBA_TAB_STATISTICS | 此视图显示当前用户可访问的表的optmizer统计信息，实时统计信息由NOTES列中STATS_ON_CONVENTIONAL_DML表示，并且在SCOPE列中共享 |
一、授权
我们使用sh这个schema中的数据（没有sh用户可以参考此文章创建），授予sh用户DBA角色，并以sh用户登录
SQL> grant dba to sh; Grant succeeded. SQL> conn sh/sh@pdb Connected.
二、手动收集sales表上的统计信息，看看实时统计收集是否已经工作
我们首先收集sales表上的统计信息，然后查询字段级别的统计信息
SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',METHOD_OPT=>'FOR ALL COLUMNS SIZE 2 '); 3 END; 4 / PL/SQL procedure successfully completed.
我们发现在note部分，什么都没有。说明 real-time statistics还没有收集。
SQL> SET LINESIZE 200 SQL> COL COLUMNS_NAME FOR A13 SQL> COL LOW_VALUE FOR A14 SQL> COL HIGH_VALUE FOR A14 SQL> COL NOTES FOR A5 SQL> COL PARTITION_NAME FOR A13 SQL> SELECT COLUMN_NAME,LOW_VALUE,HIGH_VALUE,SAMPLE_SIZE,NOTES FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'SALES' ORDER BY 1,5; COLUMN_NAME LOW_VALUE HIGH_VALUE SAMPLE_SIZE NOTES ------------------------------ -------------- -------------- ----------- ----- AMOUNT_SOLD C10729 C2125349 5655 CHANNEL_ID C103 C10A 918843 CUST_ID C103 C30B0B 5654 PROD_ID C10E C20231 5653 PROMO_ID C122 C20A64 918843 QUANTITY_SOLD C102 C102 5653 TIME_ID 77C60101010101 78650C1F010101 5653 7 rows selected.
三、查询表级别的统计信息
看到的结果一样，在NOTES部分，都是空白，说明 real-time statistics还没有收集。
SQL> SELECT NVL(PARTITION_NAME,'GLOBAL') PARTITION_NAME,NUM_ROWS,BLOCKS,NOTES FROM USER_TAB_STATISTICS WHERE TABLE_NAME='SALES' ORDER BY 1,4; PARTITION_NAM NUM_ROWS BLOCKS NOTES ------------- ---------- ---------- ----- GLOBAL 918843 1872 SALES_1995 0 0 SALES_1996 0 0 SALES_H1_1997 0 0 SALES_H2_1997 0 0 SALES_Q1_1998 43687 97 SALES_Q1_1999 64186 126 SALES_Q1_2000 62197 125 SALES_Q1_2001 60608 124 SALES_Q1_2002 0 0 SALES_Q1_2003 0 0 SALES_Q2_1998 35758 86 SALES_Q2_1999 54233 110 SALES_Q2_2000 55515 114 SALES_Q2_2001 63292 125 SALES_Q2_2002 0 0 SALES_Q2_2003 0 0 SALES_Q3_1998 50515 103 SALES_Q3_1999 67138 128 SALES_Q3_2000 58950 116 SALES_Q3_2001 65769 130 SALES_Q3_2002 0 0 SALES_Q3_2003 0 0 SALES_Q4_1998 48874 117 SALES_Q4_1999 62388 121 SALES_Q4_2000 55984 112 SALES_Q4_2001 69749 138 SALES_Q4_2002 0 0 SALES_Q4_2003 0 0 29 rows selected.
四、向sales表插入大量数据，然后提交
SQL> INSERT INTO sales(prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold) SELECT prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold * 2,amount_sold * 2 FROM sales; 918843 rows created. SQL> commit; Commit complete.
五、获取执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 9vmm48sg5xbc9, child number 0 ------------------------------------- INSERT INTO sales(prod_id,cust_id,time_id,channel_id,promo_id,quantity_s old,amount_sold) SELECT prod_id,cust_id,time_id,channel_id,promo_id,quan tity_sold * 2,amount_sold * 2 FROM sales Plan hash value: 1550251865 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | 517 (100)| | | | | 1 | LOAD TABLE CONVENTIONAL | SALES | | | | | | | | 2 | OPTIMIZER STATISTICS GATHERING | | 918K| 25M| 517 (2)| 00:00:01 | | | | 3 | PARTITION RANGE ALL | | 918K| 25M| 517 (2)| 00:00:01 | 1 | 28 | | 4 | TABLE ACCESS FULL | SALES | 918K| 25M| 517 (2)| 00:00:01 | 1 | 28 | ---------------------------------------------------------------------------------------------------------- 18 rows selected.
六、再次执行刚才字段级别的统计值查询
发现下面NOTES所示的区域，已经显示STATS_ON_CONVENTIONAL_DML，表示刚才做DML的时候，数据库已经进行了统计值收集的动作。
SQL> SELECT COLUMN_NAME,LOW_VALUE,HIGH_VALUE,SAMPLE_SIZE,NOTES FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'SALES' ORDER BY 1,5; COLUMN_NAME LOW_VALUE HIGH_VALUE SAMPLE_SIZE NOTES ------------- -------------- -------------- ----------- ------------------------- AMOUNT_SOLD C10729 C224422D 9149 STATS_ON_CONVENTIONAL_DML AMOUNT_SOLD C10729 C2125349 5560 CHANNEL_ID C103 C10A 9149 STATS_ON_CONVENTIONAL_DML CHANNEL_ID C103 C10A 918843 CUST_ID C103 C30B0B 9149 STATS_ON_CONVENTIONAL_DML CUST_ID C103 C30B0B 5559 PROD_ID C10E C20231 9149 STATS_ON_CONVENTIONAL_DML PROD_ID C10E C20231 5558 PROMO_ID C122 C20A64 9149 STATS_ON_CONVENTIONAL_DML PROMO_ID C122 C20A64 918843 QUANTITY_SOLD C102 C103 9149 STATS_ON_CONVENTIONAL_DML QUANTITY_SOLD C102 C102 5558 TIME_ID 77C60101010101 78650C1F010101 9149 STATS_ON_CONVENTIONAL_DML TIME_ID 77C60101010101 78650C1F010101 5558 14 rows selected.
七、强制将统计值写入数据字典
实时统计值不会立刻写入数据字典，我们可以通过存储过程强制它立即写入。
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; PL/SQL procedure successfully completed.
八、再次查询表级别的统计信息
发现sales表中的统计数据都是最新的。
SQL> SELECT NVL(PARTITION_NAME,'GLOBAL') PARTITION_NAME,NUM_ROWS,BLOCKS,NOTES FROM USER_TAB_STATISTICS WHERE TABLE_NAME='SALES' ORDER BY 1,4; PARTITION_NAM NUM_ROWS BLOCKS NOTES ------------- ---------- ---------- ------------------------- GLOBAL 1837686 16128 STATS_ON_CONVENTIONAL_DML GLOBAL 918843 1872 SALES_1995 0 0 SALES_1996 0 0 SALES_H1_1997 0 0 SALES_H2_1997 0 0 SALES_Q1_1998 43687 97 SALES_Q1_1999 64186 126 SALES_Q1_2000 62197 125 SALES_Q1_2001 60608 124 SALES_Q1_2002 0 0 SALES_Q1_2003 0 0 SALES_Q2_1998 35758 86 SALES_Q2_1999 54233 110 SALES_Q2_2000 55515 114 SALES_Q2_2001 63292 125 SALES_Q2_2002 0 0 SALES_Q2_2003 0 0 SALES_Q3_1998 50515 103 SALES_Q3_1999 67138 128 SALES_Q3_2000 58950 116 SALES_Q3_2001 65769 130 SALES_Q3_2002 0 0 SALES_Q3_2003 0 0 SALES_Q4_1998 48874 117 SALES_Q4_1999 62388 121 SALES_Q4_2000 55984 112 SALES_Q4_2001 69749 138 SALES_Q4_2002 0 0 SALES_Q4_2003 0 0 30 rows selected.
九、我们做一个sales表上的查询，然后看看执行计划
通过执行计划下方的Note看出，本次执行计划已经使用到自动统计值收集功能。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 99b6wg3ygub09, child number 0 ------------------------------------- SELECT COUNT(*) FROM sales WHERE quantity_sold > 50 Plan hash value: 3519235612 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4392 (100)| | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | PARTITION RANGE ALL| | 1 | 13 | 4392 (1)| 00:00:01| 1 | 28 | |* 3 | TABLE ACCESS FULL | SALES | 1 | 13 | 4392 (1)| 00:00:01| 1 | 28 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("QUANTITY_SOLD">50) Note ----- - dynamic statistics used: statistics for conventional DML 24 rows selected.
