[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 http://tanelpoder.com -- 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.