[20231016]增加字段与统计分析问题.txt

来源：这里教程网时间：2026-03-03 19:02:08 作者：

[20231016]增加字段与统计分析问题.txt --//oracle从12c引入了volatile tables概念,所谓volatile tables可以理解为频繁DML的表,通过sys.optstat_snapshot$了解生产系统验 --//证一些dml比较多的表的操作情况。但是通过修改表结构,增加字段也可能在特定情况下触发统计分析,自己通过测试验证。 1.环境: TTT@192.168.2.7:1521/orcl> @ver1 TTT@192.168.2.7:1521/orcl> @ pr ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 18.0.0.0.0 BANNER : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production BANNER_FULL : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 BANNER_LEGACY : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. TTT@192.168.2.7:1521/orcl> create table tx as select deptno from dept; Table created. TTT@192.168.2.7:1521/orcl> @ gts tx '' '' '' exec dbms_stats.gather_table_stats('TTT', 'TX', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false) if lock table tx, add force=>true. press ctrl+c cancel, enter continue... PL/SQL procedure successfully completed. TTT@192.168.2.7:1521/orcl> @ tab2 tx TTT@192.168.2.7:1521/orcl> @ pr ============================== TAB_OWNER : TTT TAB_TABLE_NAME : TX TAB_TYPE : TAB TAB_NUM_ROWS : 4 TAB_BLOCKS : 4 TAB_EMPTY_BLOCKS : 0 TAB_AVG_SPACE : 0 TAB_AVG_ROW_LEN : 3 TAB_LAST_ANALYZED : 2023-10-17 09:04:12 DEGREE : 1 COMPRESSION : DISABLED PL/SQL procedure successfully completed. TTT@192.168.2.7:1521/orcl> @ desczz tx 1=1 TTT@192.168.2.7:1521/orcl> @ pr ============================== DESC_OWNER : TTT DESC_TABLE_NAME : TX SAMPLE_SIZE : 4 LAST_ANALYZED : 2023-10-17 09:04:12 DESC_COLUMN_ID : 1 DESC_COLUMN_NAME : DEPTNO DESC_NULLABLE : NOT NULL DESC_DATA_TYPE : NUMBER(2,0) NUM_DISTINCT : 4 DESC_DENSITY : .25 NUM_NULLS : 0 HISTOGRAM : NUM_BUCKETS : 1 TRANS_LOW : 10 TRANS_HIGH : 40 PL/SQL procedure successfully completed. TTT@192.168.2.7:1521/orcl> @ o2 tx TTT@192.168.2.7:1521/orcl> @ pr ============================== O_OWNER : TTT O_OBJECT_NAME : TX O_OBJECT_TYPE : TABLE SEG_PART_NAME : O_STATUS : VALID OID : 423843 D_OID : 423843 CREATED : 2023-10-17 09:04:09 LAST_DDL_TIME : 2023-10-17 09:04:09 PL/SQL procedure successfully completed. --//注意最后的分析时间是2023-10-17 09:04:12。 TTT@192.168.2.7:1521/orcl> select * from sys.optstat_snapshot$ where obj#=423843 order by timestamp; no rows selected 2.建立测试脚本: $ cat add_col_field.sql spool dml.txt prompt add column field status1 ... select sysdate status1_before from dual; alter table tx add (status1 varchar2(10) ); alter table tx modify status1 default 'AAAAA'; host sleep 660 select sysdate status1_after from dual; @ tab2 tx @ desczz tx 1=1 prompt add column field status2 ... select sysdate status2_before from dual; alter table tx add (status2 varchar2(10) default 'BBBBB'); host sleep 660 select sysdate status2_after from dual; @ tab2 tx @ desczz tx 1=1 prompt add column field status3 ... select sysdate status3_before from dual; alter table tx add (status3 varchar2(10)); host sleep 660 select sysdate status3_after from dual; @ tab2 tx @ desczz tx 1=1 prompt insert into tx as select * from tx .... select sysdate insert_before from dual; insert into tx select * from tx; insert into tx select * from tx; commit ; host sleep 660 select sysdate insert_after from dual; @ tab2 tx @ desczz tx 1=1 spool off --//每次操作我自己延迟660秒，相当于11分钟。之所以这样写看下面的具体测试结果。 --//执行以上脚本，输出结果我加入了一些说明。 TTT@192.168.2.7:1521/orcl> @ add_col_field.sql add column field status1 ... STATUS1_BEFORE ------------------- 2023-10-17 09:05:59 Table altered. Table altered. STATUS1_AFTER ------------------- 2023-10-17 09:16:59 Show tables matching condition "tx" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESSION ----- ---------- ---- -------- ------ ----- ------ ------ ------------------- ---------- ----------- TTT TX TAB 4 4 0 0 3 2023-10-17 09:08:25 1 DISABLED eXtended describe of tx DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- ----------- -------- ------------ ------------ -------------- ---------- --------- ----------- ---------- ----------- TTT TX 4 2023-10-17 09:04:12 1 DEPTNO NOT NULL NUMBER(2,0) 4 .25000000000 0 1 10 40 2023-10-17 09:08:25 2 STATUS1 VARCHAR2(10) 0 .00000000000 4 0 --//注意看输出的时间，可以发现增加字段status1后(2023-10-17 09:05:59),在2023-10-17 09:08:25后分析表以及字段STATUS1。 add column field status2 ... STATUS2_BEFORE ------------------- 2023-10-17 09:16:59 Table altered. STATUS2_AFTER ------------------- 2023-10-17 09:28:00 Show tables matching condition "tx" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESSION ----- ---------- ---- -------- ------ ----- ------ ------ ------------------- ---------- ----------- TTT TX TAB 4 4 0 0 9 2023-10-17 09:18:26 1 DISABLED eXtended describe of tx DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- ------------ ---------- ------------ ------------ -------------- ---------- --------- ----------- ---------- ------------ TTT TX 4 2023-10-17 09:04:12 1 DEPTNO NOT NULL NUMBER(2,0) 4 .25000000000 0 1 10 40 2023-10-17 09:08:25 2 STATUS1 VARCHAR2(10) 0 .00000000000 4 0 4 2023-10-17 09:18:26 3 STATUS2 VARCHAR2(10) 1 1.00000000000 0 1 BBBBB BBBBB 2023-10-17 09:18:26 H SYS_NC00003$ RAW(126) 0 .00000000000 4 0 --//注意看输出的时间，可以发现增加字段status2后(2023-10-17 09:16:59),在2023-10-17 09:18:26后分析表以及字段STATUS2。 --//另外注意一个细节，还增加了一个隐含字段SYS_NC00003$，这是12c的一个新特性，在增加字段带缺省值时增加1个SYS_NC00003$来标 --//识字段的取值来源。它并不需要修改数据块，执行相对很快。 add column field status3 ... STATUS3_BEFORE ------------------- 2023-10-17 09:28:00 Table altered. STATUS3_AFTER ------------------- 2023-10-17 09:39:00 Show tables matching condition "tx" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESSION ----- ---------- ---- -------- ------ ----- ------ ------ ------------------- ---------- ----------- TTT TX TAB 4 4 0 0 9 2023-10-17 09:28:36 1 DISABLED eXtended describe of tx DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- ------------ ---------- ------------ ------------ -------------- ---------- --------- ----------- ---------- ---------- TTT TX 4 2023-10-17 09:04:12 1 DEPTNO NOT NULL NUMBER(2,0) 4 .25000000000 0 1 10 40 2023-10-17 09:08:25 2 STATUS1 VARCHAR2(10) 0 .00000000000 4 0 4 2023-10-17 09:18:26 3 STATUS2 VARCHAR2(10) 1 1.00000000000 0 1 BBBBB BBBBB 2023-10-17 09:28:36 4 STATUS3 VARCHAR2(10) 0 .00000000000 4 0 2023-10-17 09:18:26 H SYS_NC00003$ RAW(126) 0 .00000000000 4 0 --//注意看输出的时间，可以发现增加字段status3后(2023-10-17 09:28:00),在2023-10-17 09:28:36后分析表以及字段STATUS3。 insert into tx as select * from tx .... INSERT_BEFORE ------------------- 2023-10-17 09:39:00 4 rows created. 8 rows created. Commit complete. INSERT_AFTER ------------------- 2023-10-17 09:50:00 Show tables matching condition "tx" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESSION ----- ---------- ---- -------- ------ ----- ------ ------ ------------------- ---------- ----------- TTT TX TAB 4 4 0 0 9 2023-10-17 09:28:36 1 DISABLED eXtended describe of tx DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- ------------ ---------- ------------ ------------ -------------- ---------- --------- ----------- --------- ---------- TTT TX 4 2023-10-17 09:04:12 1 DEPTNO NOT NULL NUMBER(2,0) 4 .25000000000 0 1 10 40 2023-10-17 09:08:25 2 STATUS1 VARCHAR2(10) 0 .00000000000 4 0 4 2023-10-17 09:18:26 3 STATUS2 VARCHAR2(10) 1 1.00000000000 0 1 BBBBB BBBBB 2023-10-17 09:28:36 4 STATUS3 VARCHAR2(10) 0 .00000000000 4 0 2023-10-17 09:18:26 H SYS_NC00003$ RAW(126) 0 .00000000000 4 0 --//增加记录以后并没有分析,也许记录增加不够多。实际上我前面的测试对1个有39条记录的表，再次insert记录翻倍的情况下触发了表 --//分析。 TTT@192.168.2.7:1521/orcl> select * from sys.optstat_snapshot$ where obj#=423843 order by timestamp; OBJ# INSERTS UPDATES DELETES FLAGS TIMESTAMP ---------- ---------- ---------- ---------- ---------- --------------------------------- 423843 0 0 0 36 2023-10-17 09:08:25.554800 +08:00 423843 0 0 0 36 2023-10-17 09:18:26.268934 +08:00 423843 0 0 0 36 2023-10-17 09:28:36.515208 +08:00 423843 12 0 0 0 2023-10-17 10:31:25.180976 +08:00 423843 12 0 0 0 2023-10-17 11:31:31.807047 +08:00 423843 12 0 0 0 2023-10-17 12:31:32.106040 +08:00 .. 423843 12 0 0 0 2023-10-18 05:33:18.382129 +08:00 423843 12 0 0 32 2023-10-18 06:00:13.522094 +08:00 24 rows selected. --//今天上午6点分析了1次（注：已经过了1天）。 --//我估计满足了链接提到的条件：https://blog.dbi-services.com/12cr2-dml-monitoring-and-statistics-advisor/ --//Good I have a 'T' here for true. I conclude that the Statistics Advisor recommends to lock the stats on tables when --//half of the last 24h hours snapshots have encountered more than STALE_PERCENT modifications. --//很好，我在这里没有，这是真的。我的结论是，当过去24小时的一半快照遇到了超过STALE_PERCENT的修改时，统计顾问建议锁定表上的 --//统计数据。 --//前面3次在增加字段后，触发分析，注意看时间戳，间隔10分析记录并且分析表以及相关字段。这就是我为什么测试脚本间隔11分钟 --//的原因。你可以发现缺省1个小时记录dml的情况，flag=0. --//补充说明：实际上到了2023-10-18 08:32:37.424052 +08:00，又做了1次分析，我仅仅增加了1条记录，看后面的查询。 --//贴上我一个另外测试的情况，仅仅对测试表在次insert记录翻倍的情况下触发了表分析的情况。 TTT@192.168.2.7:1521/orcl> select * from sys.optstat_snapshot$ where obj#=296480 and timestamp>=trunc(sysdate-1)+12/24 order by timestamp; OBJ# INSERTS UPDATES DELETES FLAGS TIMESTAMP ---------- ---------- ---------- ---------- ---------- ---------------------------------- 296480 0 0 0 36 2023-10-16 09:24:14.577623 +08:00 296480 0 0 0 36 2023-10-16 09:34:22.763922 +08:00 296480 39 0 0 32 2023-10-16 09:44:17.255157 +08:00 --//36 = 0x24 --//32 = 0x20 --//第3条记录增加了表insert 39条记录后,触发表分析.你可以猜测oracle使用flag的bit来表示某种意思. --//可以猜测32应该对应dbms_stats分析.而36实际上0x24,对应做了分析,0x4我估计表示增加字段的情况. --//这也是我在生产系统遇到的情况类似.有一点点不同，我lock表，所以表的分析时间没有变化，但是 --//增加的字段都做了分析，并且我生产系统许多字段还建立了直方图，我估计查询的where条件涉及到这些字段。 --//贴上生产系统看到的情况： SYS@192.168.100.235:1521/orcl> @ desczz lis.LIS_LOG_INFECTION_ITEMS 1=1 ... eXtended describe of lis.LIS_LOG_INFECTION_ITEMS DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- -------------------- ---------- --------------- ------------ ------------ ---------- --------- ----------- --------------- ----------------------------- ... 2848036 2022-10-04 22:01:32 51 ORDER_ITEM_NAME NVARCHAR2(1000) 53 .01886792453 848 1 丙型肝炎RNA测定血清肌钙蛋白I测定(化学发光法) 2095518 2022-10-04 22:01:32 52 OFFICE_ID NUMBER(10,0) 15 .06666666667 753366 1 241 1022 2095518 2022-10-04 22:01:32 53 OFFICE_NAME NVARCHAR2(100) 17 .05882352941 753366 1 东院临检门诊组 54 AUDIT_TIME DATE(7) -- :: -- :: --//以下字段的LAST_ANALYZED>'2023-09-06' 2023-09-06 18:38:16 55 GERM_ID NUMBER(10,0) 0 .00000000000 2848884 0 2023-09-06 18:38:16 56 GERM_NAME_CN NVARCHAR2(100) 0 .00000000000 2848884 0 2848884 2023-09-06 18:38:37 57 IS_NEED_NOTICE NOT NULL NUMBER(1,0) 1 .00000017551 0 FREQUENCY 1 0 0 2848884 2023-09-06 18:38:37 58 IS_NOTICE NOT NULL NUMBER(1,0) 1 .00000017551 0 FREQUENCY 1 0 0 2848884 2023-09-06 18:38:38 59 CRIT_NOTICE_TYPE NOT NULL NUMBER(10,0) 1 .00000017551 0 FREQUENCY 1 0 0 2848884 2023-09-06 18:38:38 60 IS_RECEIVE NOT NULL NUMBER(1,0) 1 .00000017551 0 FREQUENCY 1 0 0 2023-09-06 18:38:38 61 NOTICE_USER_NAME NVARCHAR2(40) 0 .00000000000 2848884 0 2023-09-06 18:38:38 62 NOTICE_NAME NVARCHAR2(40) 0 .00000000000 2848884 0 2023-09-06 18:38:38 63 NOTICE_TIME DATE(7) 0 .00000000000 2848884 0 -- :: -- :: 2023-09-06 18:38:38 64 NOTICE_REMARK NVARCHAR2(200) 0 .00000000000 2848884 0 2023-09-06 18:38:38 65 NOTICE_CONTENT NVARCHAR2(600) 0 .00000000000 2848884 0 2023-09-06 18:38:38 66 REASON NVARCHAR2(200) 0 .00000000000 2848884 0 2023-09-06 18:38:38 67 RECEIVE_USER_NAME NVARCHAR2(40) 0 .00000000000 2848884 0 2023-09-06 18:38:38 68 RECEIVE_NAME NVARCHAR2(40) 0 .00000000000 2848884 0 2023-09-06 18:38:38 69 RECEIVE_TIME DATE(7) 0 .00000000000 2848884 0 -- :: -- :: 69 rows selected. --//我猜测是2023-09-06 18:38:16增加1堆字段,你可以发现没有字段最大最小值.其中几个被缺省赋值为0.建立了直方图. --//而且开发的操作很奇怪，没有出现隐含字段，也就是没有使用12c的新特性增加字段，我给测试看看，另外写一篇blog。 SYS@192.168.100.235:1521/orcl> select * from dba_objects where owner='LIS' and object_name='LIS_LOG_INFECTION_ITEMS' 2 @ pr ============================== OWNER : LIS OBJECT_NAME : LIS_LOG_INFECTION_ITEMS SUBOBJECT_NAME : OBJECT_ID : 73699 DATA_OBJECT_ID : 98548 OBJECT_TYPE : TABLE CREATED : 2020-11-27 16:43:09 LAST_DDL_TIME : 2023-09-06 18:38:38 TIMESTAMP : 2023-09-06:18:38:39 STATUS : VALID TEMPORARY : N GENERATED : N SECONDARY : N NAMESPACE : 1 EDITION_NAME : SHARING : NONE EDITIONABLE : ORACLE_MAINTAINED : N APPLICATION : N DEFAULT_COLLATION : USING_NLS_COMP DUPLICATED : N SHARDED : N CREATED_APPID : CREATED_VSNID : MODIFIED_APPID : MODIFIED_VSNID : PL/SQL procedure successfully completed. --//LAST_DDL_TIME也验证我的判断。 3.注意一些缺省值还可以修改: SELECT obj# ,col# ,segcol# ,name ,default$ ,type# FROM sys.col$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE owner = user AND object_name = 'TX') ORDER BY segcol#; OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE# ---------- ---------- ---------- ------------ ----------- ---------- 423843 1 1 DEPTNO 2 423843 2 2 STATUS1 'AAAAA' 1 423843 0 3 SYS_NC00003$ 23 423843 3 4 STATUS2 'BBBBB' 1 423843 4 5 STATUS3 1 TTT@192.168.2.7:1521/orcl> column SYS_NC00003$ format a30 TTT@192.168.2.7:1521/orcl> select deptno ,status1,status2,status3,SYS_NC00003$ from tx where deptno = 10; DEPTNO STATUS1 STATUS2 STATUS3 SYS_NC00003$ ------ ------- ------- ------- ------------ 10 BBBBB 10 BBBBB 01 10 BBBBB 01 10 BBBBB 01 --//前面一条status的取值来源其它地方. --//修改缺省值: --//时间2023-10-18 上班继续测试。 TTT@192.168.2.7:1521/orcl> alter table TX modify status2 default 'XXXXX'; Table altered. TTT@192.168.2.7:1521/orcl> insert into tx (deptno) values (50) ; 1 row created. TTT@192.168.2.7:1521/orcl> commit ; Commit complete. TTT@192.168.2.7:1521/orcl> select deptno ,status1,status2,status3,SYS_NC00003$ from tx where deptno in (10,50); DEPTNO STATUS1 STATUS2 STATUS3 SYS_NC00003$ ---------- ------- ------- ------- ------------ 10 BBBBB 10 BBBBB 01 10 BBBBB 01 10 BBBBB 01 50 AAAAA XXXXX 01 --//status1的缺省值仅仅对新增加的记录有效。而且你可以发现前面的STATUS2依旧等于BBBBB。 SELECT obj# ,col# ,segcol# ,name ,default$ ,type# FROM sys.col$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE owner = user AND object_name = 'TX') ORDER BY segcol#; OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE# ---------- ---------- ---------- ------------ ---------- ----- 423843 1 1 DEPTNO 2 423843 2 2 STATUS1 'AAAAA' 1 423843 0 3 SYS_NC00003$ 23 423843 3 4 STATUS2 'XXXXX' 1 423843 4 5 STATUS3 1 --//可见status2='BBBB'在数据块没有赋值的情况下,该缺省值还保存在其它数据字段里面. TTT@192.168.2.7:1521/orcl> select * from sys.ecol$ where tabobj#= 423843 ; TABOBJ# COLNUM BINARYDEFVAL GUARD_ID ---------- ---------- ------------ ---------- 423843 4 4242424242 0 --// 4242424242 = BBBBB，保存在sys.ecol$数据字段里面。 4.看执行计划的问题: TTT@192.168.2.7:1521/orcl> select * from tx where status2='xxx'; no rows selected TTT@192.168.2.7:1521/orcl> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID g2dwxfh3x266x, child number 0 ------------------------------------- select * from tx where status2='xxx' Plan hash value: 40191160 --------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4 (100)| | |* 1 | TABLE ACCESS FULL| TX | 1 | 11 | 4 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / TX@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL(" STATUS2",'BBBBB'),'0',NVL("STATUS2",'BBBBB'),'1',"STATUS2")='xxx') --//注意看过滤条件,不要以为要建立这样的函数索引. TTT@192.168.2.7:1521/orcl> create index i_tx_status2 on tx(status2); Index created. TTT@192.168.2.7:1521/orcl> select * from tx where status2='xxx'; no rows selected TTT@192.168.2.7:1521/orcl> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID g2dwxfh3x266x, child number 0 ------------------------------------- select * from tx where status2='xxx' Plan hash value: 4147895842 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TX | 1 | 11 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_TX_STATUS2 | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / TX@SEL$1 2 - SEL$1 / TX@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("STATUS2"='xxx') 5.补充： --//时间2023-10-19 上班继续测试，主要目的测试大量增加数据的情况以及truncate后flag的变化。 TTT@192.168.2.7:1521/orcl> @zzdate C30 C30 C31 -------------------------------------- -------------------------------------- -------------------------------------- 2023-10-19 08:42:03 trunc(sysdate)+08/24+42/1440+03/86400 "timestamp'2023-10-19 08:42:03'" TTT@192.168.2.7:1521/orcl> select * from sys.optstat_snapshot$ where obj#=423843 order by timestamp; OBJ# INSERTS UPDATES DELETES FLAGS TIMESTAMP ---------- ---------- ---------- ---------- ---------- --------------------------------- 423843 0 0 0 36 2023-10-17 09:08:25.554800 +08:00 423843 0 0 0 36 2023-10-17 09:18:26.268934 +08:00 423843 0 0 0 36 2023-10-17 09:28:36.515208 +08:00 423843 12 0 0 0 2023-10-17 10:31:25.180976 +08:00 423843 12 0 0 0 2023-10-17 11:31:31.807047 +08:00 423843 12 0 0 0 2023-10-17 12:31:36.690795 +08:00 .... 423843 12 0 0 0 2023-10-17 23:32:40.926880 +08:00 423843 12 0 0 0 2023-10-18 00:32:47.034004 +08:00 423843 12 0 0 0 2023-10-18 01:32:53.365285 +08:00 423843 12 0 0 0 2023-10-18 02:33:00.040114 +08:00 423843 12 0 0 0 2023-10-18 03:33:06.688206 +08:00 423843 12 0 0 0 2023-10-18 04:33:12.429391 +08:00 423843 12 0 0 0 2023-10-18 05:33:18.382129 +08:00 423843 12 0 0 32 2023-10-18 06:00:13.522094 +08:00 423843 1 0 0 40 2023-10-18 08:32:37.424052 +08:00 25 rows selected. --//你可以发现昨天6点分析1次.2023-10-18上班我仅仅增加1条记录.到了2023-10-18 08:32:37又分析1次. --//40 = 0x28 36 = 0x24 32 = 0x20 ,0x8表示什么我就不清楚了. --//难道是执行这个语句 alter table TX modify status2 default 'XXXXX';。 TTT@192.168.2.7:1521/orcl> insert into tx select * from tx; 17 rows created. TTT@192.168.2.7:1521/orcl> insert into tx select * from tx; 34 rows created. --//注意我并没有提交事务. TTT@192.168.2.7:1521/orcl> execute dbms_stats.flush_database_monitoring_info; PL/SQL procedure successfully completed. TTT@192.168.2.7:1521/orcl> select * from sys.optstat_snapshot$ where obj#=423843 order by timestamp; ... --//对比上面没有变化,执行execute dbms_stats.flush_database_monitoring_info;并不刷新sys.optstat_snapshot$. TTT@192.168.2.7:1521/orcl> select * from sys.mon_mods_all$ where obj#=423843; OBJ# INSERTS UPDATES DELETES TIMESTAMP FLAGS DROP_SEGMENTS ---------- ---------- ---------- ---------- ------------------- ---------- ------------- 423843 51 0 0 2023-10-19 08:47:53 0 0 --//更新的是sys.mon_mods_all$. --//等一段时间看看. TTT@192.168.2.7:1521/orcl> select * from sys.optstat_snapshot$ where obj#=423843 order by timestamp; OBJ# INSERTS UPDATES DELETES FLAGS TIMESTAMP ---------- ---------- ---------- ---------- ---------- --------------------------------- 423843 0 0 0 36 2023-10-17 09:08:25.554800 +08:00 423843 0 0 0 36 2023-10-17 09:18:26.268934 +08:00 423843 0 0 0 36 2023-10-17 09:28:36.515208 +08:00 423843 12 0 0 0 2023-10-17 10:31:25.180976 +08:00 423843 12 0 0 0 2023-10-17 11:31:31.807047 +08:00 423843 12 0 0 0 2023-10-17 12:31:36.690795 +08:00 423843 12 0 0 0 2023-10-17 13:31:42.607126 +08:00 423843 12 0 0 0 2023-10-17 14:31:48.262543 +08:00 423843 12 0 0 0 2023-10-17 15:31:54.059829 +08:00 423843 12 0 0 0 2023-10-17 16:32:00.032815 +08:00 423843 12 0 0 0 2023-10-17 17:32:05.838078 +08:00 423843 12 0 0 0 2023-10-17 18:32:11.771700 +08:00 423843 12 0 0 0 2023-10-17 19:32:17.749662 +08:00 423843 12 0 0 0 2023-10-17 20:32:23.142896 +08:00 423843 12 0 0 0 2023-10-17 21:32:29.072126 +08:00 423843 12 0 0 0 2023-10-17 22:32:34.474556 +08:00 423843 12 0 0 0 2023-10-17 23:32:40.926880 +08:00 423843 12 0 0 0 2023-10-18 00:32:47.034004 +08:00 423843 12 0 0 0 2023-10-18 01:32:53.365285 +08:00 423843 12 0 0 0 2023-10-18 02:33:00.040114 +08:00 423843 12 0 0 0 2023-10-18 03:33:06.688206 +08:00 423843 12 0 0 0 2023-10-18 04:33:12.429391 +08:00 423843 12 0 0 0 2023-10-18 05:33:18.382129 +08:00 423843 12 0 0 32 2023-10-18 06:00:13.522094 +08:00 423843 1 0 0 40 2023-10-18 08:32:37.424052 +08:00 423843 51 0 0 32 2023-10-19 08:56:15.357337 +08:00 26 rows selected. --//2023-10-19 08:56:15 做了分析. TTT@192.168.2.7:1521/orcl> @ tab2 tx TTT@192.168.2.7:1521/orcl> @ pr ============================== TAB_OWNER : TTT TAB_TABLE_NAME : TX TAB_TYPE : TAB TAB_NUM_ROWS : 17 TAB_BLOCKS : 8 TAB_EMPTY_BLOCKS : 0 TAB_AVG_SPACE : 0 TAB_AVG_ROW_LEN : 12 TAB_LAST_ANALYZED : 2023-10-19 08:56:15 DEGREE : 1 COMPRESSION : DISABLED PL/SQL procedure successfully completed. TTT@192.168.2.7:1521/orcl> @ desczz tx 1=1 eXtended describe of tx DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- ------------ ---------- ------------ ------------ -------------- ---------- --------- ----------- --------- ---------- TTT TX 17 2023-10-19 08:56:15 1 DEPTNO NOT NULL NUMBER(2,0) 5 .02941176471 0 FREQUENCY 5 10 50 1 2023-10-19 08:56:15 2 STATUS1 VARCHAR2(10) 1 1.00000000000 16 1 AAAAA AAAAA 17 2023-10-19 08:56:15 3 STATUS2 VARCHAR2(10) 2 .02941176471 0 FREQUENCY 2 BBBBB XXXXX 2023-10-19 08:56:15 4 STATUS3 VARCHAR2(10) 0 .00000000000 17 0 13 2023-10-19 08:56:15 H SYS_NC00003$ RAW(126) 1 1.00000000000 4 1 TTT@192.168.2.7:1521/orcl> rollback; Rollback complete. TTT@192.168.2.7:1521/orcl> select count(*) from tx; COUNT(*) ---------- 17 TTT@192.168.2.7:1521/orcl> truncate table tx; Table truncated. TTT@192.168.2.7:1521/orcl> execute dbms_stats.flush_database_monitoring_info; PL/SQL procedure successfully completed. TTT@192.168.2.7:1521/orcl> select * from sys.mon_mods_all$ where obj#=423843; OBJ# INSERTS UPDATES DELETES TIMESTAMP FLAGS DROP_SEGMENTS ---------- ---------- ---------- ---------- ------------------- ---------- ------------- 423843 0 0 17 2023-10-19 09:17:34 1 0 --//truncate 操作.deletes=17,flags=1.再等一小会... TTT@192.168.2.7:1521/orcl> select * from sys.optstat_snapshot$ where obj#=423843 and timestamp>=trunc(sysdate) order by timestamp; OBJ# INSERTS UPDATES DELETES FLAGS TIMESTAMP ---------- ---------- ---------- ---------- ---------- --------------------------------- 423843 51 0 0 32 2023-10-19 08:56:15.357337 +08:00 423843 0 0 17 33 2023-10-19 09:26:32.809422 +08:00 --//估计10分钟做1次分析收集决定是否分析等操作. --//33 = 0x21 , 0x20(32)表示分析,0x1表示truncate. TTT@192.168.2.7:1521/orcl> @ tab2 tx TTT@192.168.2.7:1521/orcl> @ pr ============================== TAB_OWNER : TTT TAB_TABLE_NAME : TX TAB_TYPE : TAB TAB_NUM_ROWS : 0 TAB_BLOCKS : 0 TAB_EMPTY_BLOCKS : 0 TAB_AVG_SPACE : 0 TAB_AVG_ROW_LEN : 0 TAB_LAST_ANALYZED : 2023-10-19 09:26:32 DEGREE : 1 COMPRESSION : DISABLED PL/SQL procedure successfully completed. 6.总结: --//19c注意这类变化带来的分析问题,可能导致执行计划发生变化的问题. --//注意增加字段修改字段属性以及大量dml操作都有可能带来表的统计分析变化。 --//写的有点乱,思路也有点乱，工作上琐碎的事情打断了测试工作，测试分3天完成，注意我上面提到的时间问题以注解方式提供。

编辑推荐：

返回列表

[20231016]增加字段与统计分析问题.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推