[20221101]完善gts.sql指令碼.txt

[20221101]完善gts.sql指令碼.txt

--//原始的tpt gts.sql指令碼 ,使用的是method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE 1',實際上導致我在分析生產系統時清除了一些欄位
--//的直方圖資訊,導致出現嚴重效能問題.我修改為method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT'.

--//而且對方版本僅僅分析當前schema下的表,我決定嘗試做一些改動更加靈活一些.

$ cat gts.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

set verify off
set termout off
column xx format a60 new_value xx
column xy format a60 new_value xy
select decode(upper('&&2'),'','REPEAT','R','REPEAT','A','AUTO','&&2') xx from dual;
select decode(upper('&&3'),'','NULL','AUTO','sys.dbms_stats.auto_sample_size','A','sys.dbms_stats.auto_sample_size','&&3') xy from dual;

column v_owner new_value v_owner
column v_table new_value v_table
select
 upper(CASE
                    WHEN INSTR('&1','.') > 0 THEN
                        SUBSTR('&1',INSTR('&1','.')+1)
                    ELSE
                        '&1'
                    END
                     )  v_table,
 nvl(upper(CASE WHEN INSTR('&1','.') > 0 THEN
            UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
        ELSE
            user
        END),user) v_owner
from dual;

set termout on

prompt
prompt @ gts table_name  <method_opt> <estimate_percent>
prompt method_opt accept  R or r  => REPEAT , A or a => auto
prompt estimate_percent accept "" => NULL , AUTO or A or auto or a=> sys.dbms_stats.auto_sample_size
prompt flush database monitoring info;
prompt execute dbms_stats.flush_database_monitoring_info;
prompt

prompt lock and unlock
prompt execute sys.dbms_stats.lock_table_stats (ownname => 'v_owner',tabname  => 'v_table');
prompt execute sys.dbms_stats.unlock_table_stats (ownname => 'v_owner',tabname  => 'v_table');
prompt

prompt estimate_percent : sys.dbms_stats.auto_sample_size  n 1-100
prompt block_sample     : false true
prompt method_opt       : for all columns size [auto|repeat|1-254]     
prompt method_opt       : for all hidden columns size repeat
prompt method_opt       : for all columns size 1 for columns xx size 254
prompt methid_opt       : for all indexed columns size repeat
prompt
prompt cascade          : true false
prompt
prompt No_Invalidate    : false true  dbms_stats.auto_invalidate
prompt
prompt delete table stats
prompt execute sys.dbms_stats.delete_table_stats ('&v_owner', '&v_table',cascade_columns=> false,cascade_indexes=> false,cascade_parts=>false,no_invalidate=> false);
prompt

prompt Gather Table Statistics for table &1....
prompt exec dbms_stats.gather_table_stats('&v_owner', '&v_table', estimate_percent => &xy, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE &xx', cascade=>true, no_invalidate=>false);
prompt if lock table &1, add force=>true.
prompt
pause press ctrl+c cancel, enter continue...
exec dbms_stats.gather_table_stats('&v_owner', '&v_table', estimate_percent => &xy, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE &xx', cascade=>true, no_invalidate=>false);
column xx clear
column xy clear


--//這樣可以完全不使用gtsh.sql指令碼.測試例子如下:
@gts emp 1 ''
@gts emp R 100
@gts emp R ''
@gts emp repeat ''
@gts emp auto ''
@gts emp 254  ''
@gts emp 1024 ''
@gts scott.emp 1 ''
@gts scott.emp "1 for columns sal size 14" a

@gts emp 2048 ''
@gts emp 2049 ''

SCOTT@test01p> @gts1 emp 2049
Gather Table Statistics for table emp...
exec dbms_stats.gather_table_stats('SCOTT', 'EMP', null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE 2049', cascade=>true, no_invalidate=>false)
press ctrl+c cancel, enter continue...

BEGIN dbms_stats.gather_table_stats('SCOTT', 'EMP', null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE 2049', cascade=>true, no_invalidate=>false); END;

*
ERROR at line 1:
ORA-20000: Cannot parse for clause: FOR TABLE FOR ALL COLUMNS SIZE 2049
ORA-06512: at "SYS.DBMS_STATS", line 36873
ORA-06512: at "SYS.DBMS_STATS", line 36507
ORA-06512: at "SYS.DBMS_STATS", line 35428
ORA-06512: at "SYS.DBMS_STATS", line 21023
ORA-06512: at "SYS.DBMS_STATS", line 35009
ORA-06512: at "SYS.DBMS_STATS", line 36230
ORA-06512: at "SYS.DBMS_STATS", line 36716
ORA-06512: at line 1
--//目前12c以上版本可以支援2048 bucket.