TRẦN VĂN BÌNH MASTER: Script gather cả Oracle Database

Mục đích: Script gather cả Oracle Database

Script:

Có thể chạy tay trước, sau đó đặt job chạy lúc 22h hàng ngày

CREATE OR REPLACE PACKAGE BODY TEST_OWNER.pck_gather_table_stats

p_error VARCHAR2 (1000);

p_gather_date_par DATE := SYSDATE;

p_gather_date DATE := SYSDATE;

PROCEDURE exec_gather_tables

BEGIN

IF (TO_NUMBER (TO_CHAR (SYSDATE, 'dd')) NOT IN(6,16,26)) and (to_number(to_char(sysdate,'hh24')) < 7 or to_number(to_char(sysdate,'hh24')) > 22)

THEN

pck_gather_table_stats.gather_partitioned_tables();

END IF;

IF (TO_NUMBER(TO_CHAR (SYSDATE, 'dd')) IN(9, 19, 29)) and (to_number(to_char(sysdate,'hh24')) < 7 or to_number(to_char(sysdate,'hh24')) > 22)

THEN

pck_gather_table_stats.gather_unpartitioned_tables;

END IF;

null;

END;

PROCEDURE gather_partitioned_tables

CURSOR c_partitioned_tables

SELECT table_owner, table_name, partition_name, last_analyzed,

'begin

dbms_stats.gather_table_stats

(ownname=>''' || TABLE_OWNER || ''',

tabname=>''' || table_name || ''',

partname=>''' || partition_name || ''',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => ''FOR ALL COLUMNS SIZE AUTO'',

cascade=>true,

degree=>10);

end;

script

FROM all_tab_partitions

WHERE table_name NOT LIKE '%$%'

AND NVL (last_analyzed, trunc(SYSDATE) - 15) < trunc(sysdate)-1

AND NVL(last_analyzed, SYSDATE) > trunc(sysdate)-2

and ((length(partition_name) = 12 AND to_date(substr(partition_name,5,8),'YYYYMMDD')<trunc(sysdate)

AND to_date(substr(partition_name,5,8),'YYYYMMDD')>trunc(sysdate)-15)

or (length(partition_name) =10 AND to_date(substr(partition_name,5,6),'YYYYMM')<trunc(sysdate)

AND to_date(substr(partition_name,5,6),'YYYYMM')>=add_months(trunc(sysdate,'month'),-1)))

and table_owner in ('TEST_OWNER')

ORDER BY table_name, partition_name;

v_table_name VARCHAR2 (100);

v_par_name VARCHAR2 (100);

BEGIN

FOR v_partitioned_tables IN c_partitioned_tables

LOOP

IF (TO_NUMBER (TO_CHAR (SYSDATE, 'dd')) NOT IN(0)) and (to_number(to_char(sysdate,'hh24')) < 7 or to_number(to_char(sysdate,'hh24')) > 22)

THEN

v_table_name := v_partitioned_tables.table_name;

v_par_name := v_partitioned_tables.partition_name;

EXECUTE IMMEDIATE v_partitioned_tables.script;

-- pr_insert_log_gather(v_partitioned_tables.table_owner, v_partitioned_tables.table_name, v_partitioned_tables.partition_name,v_partitioned_tables.script);

end if;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

null;

END;

-- Gather stats for unpartitioned tables

-- Input:

-- p_Owner: Owner of tables

-- p_Ignored_Tables_List: List of Tables that will not be gathered

-- Output

-- p_Gathered_Tables: Tables already gathered

-- p_Error: Errors descriptions(if any), NULL value means no error occurred

PROCEDURE gather_unpartitioned_tables

CURSOR c_tables

SELECT owner, table_name, last_analyzed,

'begin

dbms_stats.gather_table_stats

(ownname=>''' || OWNER || ''',

tabname=>''' || table_name || ''',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => ''FOR ALL COLUMNS SIZE AUTO'',

cascade=>true,

degree=>10);

end;

script

FROM all_tables

WHERE partitioned = 'NO'

AND tablespace_name IS NOT NULL

AND table_name NOT LIKE '%$%'

AND table_name not like '%XX%'

and table_name not like '%BK%'

and owner in ('TEST_OWNER')

AND NVL (last_analyzed, SYSDATE - 15) < p_gather_date

ORDER BY last_analyzed;

v_table_name VARCHAR2 (100);

BEGIN

FOR v_tables IN c_tables

LOOP

v_table_name := v_tables.table_name;

EXECUTE IMMEDIATE v_tables.script;

-- pr_insert_log_gather(v_tables.owner, v_tables.table_name, null,v_tables.script);

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

null;

END;

--------------------------------------------------------------------------------

END;

Kiểm tra lại sau khi gather:

· Table

· Bảng partition: (trong TOAD dùng F4 --> partition để check)

select table_name, partition_name, last_analyzed from DBA_TAB_PARTITIONS where table_owner=‘TEST_OWNER’ and table_name like upper(‘TEST_TAB1’)