[20220421]完善查詢表分析的歷史th.sql指令碼.txt

[20220421]完善查詢表分析的歷史th.sql指令碼.txt

--//有時候要快速還原表統計相關資訊，自己編寫指令碼如下：

$ 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  <filters>
SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT <filters> ,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  <filters>
SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT <filters> ,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:31:22    1 EMPNO       NOT NULL   NUMBER(4,0)            14   .03571428571          0 HEIGHT BALANCED          14 7369                7934
                          14 2022-04-22 08:31:22    2 ENAME                  VARCHAR2(10)           14   .03571428571          0 HEIGHT BALANCED          14 ADAMS               WARD
                          14 2022-04-22 08:31:22    3 JOB                    VARCHAR2(9)             5   .03571428571          0 FREQUENCY                 5 ANALYST             SALESMAN
                          13 2022-04-22 08:31:22    4 MGR                    NUMBER(4,0)             6   .03846153846          1 FREQUENCY                 6 7566                7902
                          14 2022-04-22 08:31:22    5 HIREDATE               DATE(7)                13   .03571428571          0 FREQUENCY                13 1980-12-17 00:00:00 1987-05-23 00:00:00
                          14 2022-04-22 08:31:22    6 SAL                    NUMBER(7,2)            12   .03571428571          0 FREQUENCY                12 800                 5000
                           4 2022-04-22 08:31:22    7 COMM                   NUMBER(7,2)             4   .12500000000         10 HEIGHT BALANCED           4 0                   1400
                          14 2022-04-22 08:31:22    8 DEPTNO                 NUMBER(2,0)             3   .03571428571          0 FREQUENCY                 3 10                  30
8 rows selected.

--//注意看LAST_ANALYZED= 2022-04-22 08:31:22,現在有直方圖資訊。