[20230821]还原表统计信息问题2.txt
--//在还原表统计信息时，遇到丢失直方图信息的情况，做一个记录.
1.环境：
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2.建立测试表:
create table t3 as select rownum id , cast(dbms_random.string('a',6 ) as varchar2(10)) name from dual connect by level <=1e5;
create unique index pk_t3 on t3 (id);
alter table t3 add constraint pk_t3 primary key (id);
--//@ opstat scott t3 1=1
3.分析表:
--//@ gts table_name <method_opt> <estimate_percent>
--//执行如下：
@ gts t3 1 ''
--//exec dbms_stats.gather_table_stats('SCOTT', 'T3', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE 1', cascade=>true, no_invalidate=>false)
@ gts t3 254 ''
--//exec dbms_stats.gather_table_stats('SCOTT', 'T3', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE 254', cascade=>true, no_invalidate=>false)
--//输出略。
--//修改表以及列统计信息:
SCOTT@test01p> @ zzdate
C30 C30 C31
------------------- -------------------------------------- --------------------------------------
2023-08-21 20:40:44 trunc(sysdate)+20/24+40/1440+44/86400 "timestamp'2023-08-21 20:40:44'"
--//修改表统计信息：
--//@ modtab t3 numrows 2e5
exec dbms_stats.set_table_stats('SCOTT','T3',numrows=>2e5,NO_INVALIDATE=>false,force=>true);
SCOTT@test01p> @ zzdate
C30 C30 C31
------------------- -------------------------------------- --------------------------------------
2023-08-21 20:41:38 trunc(sysdate)+20/24+41/1440+38/86400 "timestamp'2023-08-21 20:41:38'"
--//修改字段统计信息：
--//@ modcol t3 ename density .000011
exec dbms_stats.SET_COLUMN_STATS('SCOTT','T3','name',density=>.000005,NO_INVALIDATE=>false,force=>true);
SCOTT@test01p> @ zzdate
C30 C30 C31
------------------- -------------------------------------- --------------------------------------
2023-08-21 20:42:35 trunc(sysdate)+20/24+42/1440+35/86400 "timestamp'2023-08-21 20:42:35'"
SCOTT@test01p> @desczz t3 ''
eXtended describe of t3
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
----- ---------- ----------- ------------------- ---- ----------- ---------- ------------ ------------ -------------- ---------- --------- ----------- --------- ----------
SCOTT T3 100000 2023-08-21 20:40:22 1 ID NOT NULL NUMBER(,) 100000 .00001000000 0 HYBRID 254 1 100000
100000 2023-08-21 20:42:22 2 NAME VARCHAR2(10) 100000 .00000500000 0 1 AAAigv zzzZbS
--//可以发现name Density=.00000500000,同时name字段的直方图信息破坏。
SCOTT@test01p> @tab2 t3
Show tables matching condition "t3" (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 COMPRESS
----- ---------- ---- ------------ ------------- --------- ------ ------ ------------------- -------------------- --------
SCOTT T3 TAB 200000 246 0 0 12 2023-08-21 20:41:05 1 DISABLED
--//表NUM_ROWS=200000.
4.还原表统计信息:
SCOTT@test01p> @ th t3
OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME STATS_UPDATE_TIME1
------ ---------- -------------- -------------------- ------------------------------ -------------------
SCOTT T3 2023-08-21 20:40:13.551000 2023-08-21 20:40:13
SCOTT T3 2023-08-21 20:40:22.352000 2023-08-21 20:40:22
SCOTT T3 2023-08-21 20:41:05.210000 2023-08-21 20:41:05
-- exec dbms_stats.restore_table_stats('SCOTT','T3','2023-08-21 20:41:05',No_Invalidate => false)
--//简单说明我的脚本生成的dbms_stats.restore_table_stats取得时间是STATS_UPDATE_TIME1,但是没有秒后面的时间。这样还原应该是没有直方图的信息。
SCOTT@test01p> exec dbms_stats.restore_table_stats('SCOTT','T3','2023-08-21 20:41:05',No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@test01p> @desczz t3 ''
eXtended describe of t3
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
----- ---------- ----------- ------------------- ---- ----------- ---------- ------------ ------------ -------------- ---------- --------- ----------- --------- ----------
SCOTT T3 100000 2023-08-21 20:40:22 1 ID NOT NULL NUMBER(,) 100000 .00001000000 0 HYBRID 254 1 100000
100000 2023-08-21 20:40:22 2 NAME VARCHAR2(10) 100000 .00001000000 0 HYBRID 254 AAAigv zzzZbS
--//可以发现还原后实际上存在直方图信息。为什么？
SCOTT@test01p> @ opstat scott t3 1=1
ID START_TIME END_TIME OPERATION TARGET STATUS NOTES
---- -------------------------- -------------------------- -------------------- -------- --------- ------------------------------------------------------------------------------------------
5203 2023-08-21 20:40:12.726000 2023-08-21 20:40:15.533000 gather_table_stats SCOTT.T3 COMPLETED <params><param name="block_sample" val="FALSE"/><param name="cascade" val="TRUE"/><param n
ame="concurrent" val="FALSE"/><param name="degree" val="NULL"/><param name="estimate_perce
nt" val=""/><param name="force" val="FALSE"/><param name="granularity" val="AUTO"/><param
name="method_opt" val="FOR TABLE FOR ALL COLUMNS SIZE 1"/><param name="no_invalidate" val=
"FALSE"/><param name="ownname" val="SCOTT"/><param name="partname" val=""/><param name="re
porting_mode" val="FALSE"/><param name="statid" val=""/><param name="statown" val=""/><par
am name="stattab" val=""/><param name="stattype" val="DATA"/><param name="tabname" val="T3
"/></params>
--//method_opt=FOR TABLE FOR ALL COLUMNS SIZE 1
5204 2023-08-21 20:40:17.984000 2023-08-21 20:40:23.206000 gather_table_stats SCOTT.T3 COMPLETED <params><param name="block_sample" val="FALSE"/><param name="cascade" val="TRUE"/><param n
ame="concurrent" val="FALSE"/><param name="degree" val="NULL"/><param name="estimate_perce
nt" val=""/><param name="force" val="FALSE"/><param name="granularity" val="AUTO"/><param
name="method_opt" val="FOR TABLE FOR ALL COLUMNS SIZE 254"/><param name="no_invalidate" va
l="FALSE"/><param name="ownname" val="SCOTT"/><param name="partname" val=""/><param name="
reporting_mode" val="FALSE"/><param name="statid" val=""/><param name="statown" val=""/><p
aram name="stattab" val=""/><param name="stattype" val="DATA"/><param name="tabname" val="
T3"/></params>
--//method_opt=FOR TABLE FOR ALL COLUMNS SIZE 254
5223 2023-08-21 20:41:05.161000 2023-08-21 20:41:05.273000 set_table_stats SCOTT.T3 COMPLETED <params><param name="avgrlen" val=""/><param name="cachedblk" val=""/><param name="cachehi
t" val=""/><param name="flags" val=""/><param name="force" val="TRUE"/><param name="im_blo
ck_count" val=""/><param name="im_imcu_count" val=""/><param name="no_invalidate" val="FAL
SE"/><param name="numblks" val=""/><param name="numrows" val="200000"/><param name="ownnam
e" val="SCOTT"/><param name="partname" val=""/><param name="scanrate" val=""/><param name=
"statid" val=""/><param name="statown" val=""/><param name="stattab" val=""/><param name="
tabname" val="T3"/></params>
--//exec dbms_stats.set_table_stats('SCOTT','T3',numrows=>2e5,NO_INVALIDATE=>false,force=>true);
5243 2023-08-21 20:47:56.238000 2023-08-21 20:47:57.563000 restore_table_stats SCOTT.T3 COMPLETED <params><param name="as_of_timestamp" val="08-21-2023 20:41:05"/><param name="force" val="
FALSE"/><param name="no_invalidate" val="FALSE"/><param name="ownname" val="SCOTT"/><param
name="restore_cluster_index" val="FALSE"/><param name="tabname" val="T3"/></params>
--//实际上执行dbms_stats.set_table_stats，对应th.sql第3条记录。要取消直方图信息，执行如下：
SCOTT@test01p> exec dbms_stats.restore_table_stats('SCOTT','T3','2023-08-21 20:40:22',No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@test01p> @ desczz t1 ''
eXtended describe of t1
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
----- ---------- ----------- ------------------- ---- ----------- ---------- ------------ ------------ -------------- ---------- --------- ----------- --------- -----------
SCOTT T1 100000 2023-08-19 21:28:26 1 ID NOT NULL NUMBER(,) 100000 .00001000000 0 1 1 100000
100000 2023-08-19 21:28:26 2 NAME VARCHAR2(10) 100000 .00001000000 0 1 AAAlsW zzyuku
--//仅仅提醒自己在工作中注意这些细节!!篡改表统计信息时在DBA_TAB_STATS_HISTORY视图有记录。
5.附上执行代码：
$ cat opstat.sql
column start_time format a26
column end_time format a26
column notes format a90
SELECT id
, start_time
, end_time
, operation
, target
, status
, notes
FROM DBA_OPTSTAT_OPERATIONS
where target = upper('&&1..&&2') and &&3
order by 1;
--//th.sql代码有点长，不贴上来了。
--//来自 [20220421]完善查询表分析的历史th.sql脚本.txt =>http://blog.itpub.net/267265/viewspace-2888146/
--//其它 gts ,modtab,modcol对应脚本都可以在我的blog上查询找到，不贴上来了。
6.疑问:
--//按照这个道理12c假设开始ctas脚本的统计信息没有记录，测试看看。
SCOTT@test01p> create table t4 as select rownum id , cast(dbms_random.string('a',6 ) as varchar2(10)) name from dual connect by level <=1e3;
Table created.
SCOTT@test01p> @ th scott.t4 ''
no rows selected
-- exec dbms_stats.restore_table_stats('SCOTT','T4','',No_Invalidate => false)
SCOTT@test01p> @ opstat scott t4 1=1
no rows selected
--//确实没有。
SCOTT@test01p> @ desczz t4 ''
eXtended describe of t4
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
---------- ---------- ----------- ------------------- ---- ----------- ---------- ------------ ------------ -------------- ---------- --------------- ----------- --------- ----------
SCOTT T4 1000 2023-08-21 21:26:18 1 ID NUMBER(,) 1000 .00100000000 0 1 1 1000
1000 2023-08-21 21:26:18 2 NAME VARCHAR2(10) 1000 .00100000000 0 1 AATULn zzNXdI
--//表相关统计信息是存在了。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2977876/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
