oracle统计信息手动部署定时任务

背景：

由于oracle自动的统计信息是对全库级别的表分析，且采样率高，导致耗时较久等原因，可以关闭自动任务，手动部署表分析脚本定时对schema级别进行统计信息收集

1、关闭oracle自动任务

set lines 400;

col REPEAT_INTERVAL for a60;

select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2

where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

select client_name ,status from DBA_AUTOTASK_CLIENT;

exec DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);

exec DBMS_AUTO_TASK_ADMIN.disable(client_name => '',operation => NULL,window_name => NULL);

exec DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);

exec dbms_scheduler.disable( name => 'MONDAY_WINDOW',force => TRUE);

exec dbms_scheduler.disable( name => 'TUESDAY_WINDOW',force => TRUE);

exec dbms_scheduler.disable( name => 'WEDNESDAY_WINDOW',force => TRUE);

exec dbms_scheduler.disable( name => 'THURSDAY_WINDOW',force => TRUE);

exec dbms_scheduler.disable( name => 'FRIDAY_WINDOW',force => TRUE);

exec dbms_scheduler.disable( name => 'SATURDAY_WINDOW',force => TRUE);

exec dbms_scheduler.disable( name => 'SUNDAY_WINDOW',force => TRUE);

exec dbms_scheduler.disable( name => 'TUESDAY_WINDOW',force => TRUE);

2、sys用户创建错误日志表

CREATE TABLE "WERRORLOG"

( "PROCEDURE_NAME" VARCHAR2(50) NOT NULL ENABLE,

"ERR_MSG" VARCHAR2(255) NOT NULL ENABLE,

"SYS_ERR_CODE" VARCHAR2(10) NOT NULL ENABLE,

"SYS_ERR_MSG" VARCHAR2(1000) NOT NULL ENABLE,

"CREATE_TIME" DATE NOT NULL ENABLE

) ;

3、sys用户创建错误日志存过

CREATE OR REPLACE PROCEDURE "PRC_ERR_LOG"

(

i_procedure_name Varchar2

,i_err_msg Varchar2

)

As

v_sqlcode Varchar(10);

v_sqlerrm Varchar(1000);

Begin

v_sqlcode:=Sqlcode;

v_sqlerrm:=Sqlerrm;

Insert Into wErrorLog Values(i_procedure_name,i_err_msg,v_sqlcode,v_sqlerrm,Sysdate);

commit;

Exception

WHEN OTHERS

Then

v_sqlcode:=Sqlcode;

v_sqlerrm:=Sqlerrm;

Insert Into wErrorLog Values('prc_err_log','Error of writing log table',v_sqlcode,v_sqlerrm,Sysdate);

commit;

END;

/

4、sys创建表分析存过

create or replace procedure pro_schema_analyze(v_owner varchar2) as

CURSOR STALE_TABLE IS

SELECT OWNER,

SEGMENT_NAME,

CASE

WHEN SIZE_GB < 0.5 THEN

5

WHEN SIZE_GB >= 0.5 AND SIZE_GB < 1 THEN

5

WHEN SIZE_GB >= 1 AND SIZE_GB < 5 THEN

5

WHEN SIZE_GB >= 5 AND SIZE_GB < 10 THEN

5

WHEN SIZE_GB >= 10 THEN

1

END AS PERCENT,

8 AS DEGREE

FROM (SELECT OWNER,

SEGMENT_NAME,

SUM(BYTES / 1024 / 1024 / 1024) SIZE_GB

FROM DBA_SEGMENTS

WHERE OWNER = upper(v_owner)

--AND SEGMENT_TYPE = 'TABLE'

AND SEGMENT_NAME IN

(SELECT /*+ UNNEST */

DISTINCT TABLE_NAME

FROM DBA_TAB_STATISTICS

WHERE (LAST_ANALYZED IS NULL OR STALE_STATS = 'YES')

AND OWNER = upper(v_owner)

AND OBJECT_TYPE = 'TABLE'

AND stattype_locked IS NULL

AND TABLE_NAME NOT LIKE 'BIN$%'

AND TABLE_NAME NOT LIKE 'TMP_%'

AND TABLE_NAME NOT LIKE 'TEMP_%'

AND TABLE_NAME NOT LIKE 'BAK_%'

AND TABLE_NAME NOT LIKE '%DWH%'

AND TABLE_NAME NOT LIKE '%BAK'

AND TABLE_NAME NOT LIKE 'SO_%'

AND TABLE_NAME NOT LIKE 'DB_%'

AND TABLE_NAME NOT LIKE 'D_ARCHIVE_%'

AND TABLE_NAME NOT LIKE 'S_U_I%'

AND TABLE_NAME NOT LIKE 'TEMABR_SUBS_INFO_%'

AND TABLE_NAME NOT LIKE 'SYS_EXPORT_TABLE_%')

GROUP BY OWNER, SEGMENT_NAME);

BEGIN

FOR STALE IN STALE_TABLE LOOP

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => STALE.OWNER,

TABNAME => STALE.SEGMENT_NAME,

ESTIMATE_PERCENT => STALE.PERCENT,

METHOD_OPT => 'for all indexed columns size skewonly',

DEGREE => 10,

GRANULARITY => 'GLOBAL',

CASCADE => TRUE);

END LOOP;

exception

when others then

PRC_ERR_LOG('pro_schema_analyze', 'execute failed');

commit;

raise;

END pro_schema_analyze;

/

5、部署脚本

以cc为例：

在/backup/rman/shell/下创建脚本

vi schema_analyze.sh

export ORACLE_SID=cc

export logfile=/backup/shell/schema_analyze_cc.log

export ORACLE_BASE=/oracle

export ORACLE_HOME=/oracle/product/19c

export PATH=$ORACLE_HOME/bin:$PATH

sqlplus -S / as sysdba >>${logfile}<<EOF

set timing on

exec pro_schema_analyze('cc');

exec pro_schema_analyze('crm');

.......

EXIT;

EOF

echo "oracle gather schema stats end at:" `date +'%Y-%m-%d %H:%M:%S'` >>${logfile}

6、手动测试存过和脚本

sqlplus / as sysdba

exec pro_schema_analyze('cc');

chown oracle.dba /backup/rman/shell/schema_analyze.sh

chmod +x /backup/rman/shell/schema_analyze.sh

su - oracle

sh /backup/rman/shell/schema_analyze.sh

7、部署定时任务

su - root

crontab -e

00 01 * * * su - oracle -c "/backup/rman/shell/schema_analyze.sh" >> /dev/null 2>&1