[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,现在有直方图信息。