[20231016]增加字段与统计分析问题.txt
发布时间 2023-10-23 21:52:10作者: lfree
--//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天完成，注意我上面提到的时间问题以注解方式提供。