完善查询表分析的历史th.sql脚本-CSDN博客

本文链接：https://blog.csdn.net/m0_70068478/article/details/124366955

-//有时候要快速还原表统计相关信息，自己编写脚本如下：

$ cat th.sql
set term off
column v_owner new_value v_owner
column v_table new_value v_table
column STATS_UPDATE_TIME1 new_value V_STATS_UPDATE_TIME1
select
upper(CASE
                    WHEN INSTR('&1','.') > 0 THEN
                        SUBSTR('&1',INSTR('&1','.')+1)
                    ELSE
                        '&1'
                    END
                     ) v_table,
CASE WHEN INSTR('&1','.') > 0 THEN
            UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
        ELSE
            user
        END v_owner
from dual;
set term on

select DBA_TAB_STATS_HISTORY.*,
to_char(STATS_UPDATE_TIME,'yyyy-mm-dd hh24:mi:ss') STATS_UPDATE_TIME1
from DBA_TAB_STATS_HISTORY
where
    upper(table_name) LIKE
                upper(CASE
                    WHEN INSTR('&1','.') > 0 THEN
                        SUBSTR('&1',INSTR('&1','.')+1)
                    ELSE
                        '&1'
                    END
                     ) ESCAPE '\'
AND owner LIKE
        CASE WHEN INSTR('&1','.') > 0 THEN
            UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
        ELSE
            user
        END ESCAPE '\'
order by STATS_UPDATE_TIME
/

prompt
prompt -- exec dbms_stats.restore_table_stats('&v_owner','&v_table','&V_STATS_UPDATE_TIME1',No_Invalidate => false);
prompt

--//测试如下：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> @th emp
no rows selected
-- exec dbms_stats.restore_table_stats('SCOTT','EMP','',No_Invalidate => false)

SCOTT@book> @ gtsh emp
Gather Table Statistics with histograms for table emp...
PL/SQL procedure successfully completed.

SCOTT@book> host sleep 10

SCOTT@book> @ gts emp
Gather Table Statistics for table emp...
PL/SQL procedure successfully completed.

SCOTT@book> exec dbms_stats.gather_table_stats(null, 'EMP', null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE 1', cascade=>true, no_invalidate=>false);
PL/SQL procedure successfully completed.

SCOTT@book> @ th scott.emp
OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME                 STATS_UPDATE_TIME1
----- ---------- -------------- ----------------- --------------------------------- -------------------
SCOTT EMP                                         2022-04-22 08:29:52.448354 +08:00 2022-04-22 08:29:52
SCOTT EMP                                         2022-04-22 08:31:22.452939 +08:00 2022-04-22 08:31:22
SCOTT EMP                                         2022-04-22 08:33:00.223730 +08:00 2022-04-22 08:33:00

-- exec dbms_stats.restore_table_stats('SCOTT','EMP','2022-04-22 08:33:00',No_Invalidate => false)

--//分析3次，最后1次没有建立直方图。

SCOTT@book> @ descz scott.emp 1=1
eXtended describe of scott.emp

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER.TABLE_NAME
SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT ,USE "1=1" .

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 EMP                 14 2022-04-22 08:33:00    1 EMPNO       NOT NULL   NUMBER(4,0)            14   .07142857143          0                     1 7369                7934
                          14 2022-04-22 08:33:00    2 ENAME                  VARCHAR2(10)           14   .07142857143          0                     1 ADAMS               WARD
                          14 2022-04-22 08:33:00    3 JOB                    VARCHAR2(9)             5   .20000000000          0                     1 ANALYST             SALESMAN
                          13 2022-04-22 08:33:00    4 MGR                    NUMBER(4,0)             6   .16666666667          1                     1 7566                7902
                          14 2022-04-22 08:33:00    5 HIREDATE               DATE(7)                13   .07692307692          0                     1 1980-12-17 00:00:00 1987-05-23 00:00:00
                          14 2022-04-22 08:33:00    6 SAL                    NUMBER(7,2)            12   .08333333333          0                     1 800                 5000
                           4 2022-04-22 08:33:00    7 COMM                   NUMBER(7,2)             4   .25000000000         10                     1 0                   1400
                          14 2022-04-22 08:33:00    8 DEPTNO                 NUMBER(2,0)             3   .33333333333          0                     1 10                  30
8 rows selected.

SCOTT@book> exec dbms_stats.restore_table_stats('SCOTT','EMP','2022-04-22 08:33:00',No_Invalidate => false)
PL/SQL procedure successfully completed.
--//因为我取的时间没有秒数后面的数字，这样取中间的表统计信息还原。

SCOTT@book> @ descz scott.emp 1=1
eXtended describe of scott.emp

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER.TABLE_NAME
SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT ,USE "1=1" .

Owner Table_Name SAMPLE_SIZE LAST_ANALYZED       Col# Column Name Null?      Type         NUM_DISTINCT        Density NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value           High_value
----- ---------- ----------- ------------------- ---- ----------- ---------- ------------ ------------ -------------- ---------- --------------- ----------- ------------------- -------------------