오라클 analyze 자동화

 

출처 :http://cafe.naver.com/prodba/3416

제가 AIX에서 사용하는 analyze 프로그램입니다.

매주 일요일 오전에 시작 하도록 cron에 등록 시켜 사용합니다.

파티션 테이블과 일반 테이블을 한 번에 실행합니다.

많은 도움이 되었으면 합니다.

 

 

 

#!/bin/ksh
#******************************************************************************#
#*  파일명     : analyze_week.sh                                              *#
#*  모듈명     : analyze_week.sh                                              *#
#*  내용설명   : 매주 일요일 오전 analyze 실행                                *#
#*  참고 사항  : 일반테이블, 타피션 테이블 수행                               *#
#******************************************************************************#
#*변경이력                                                                    *#
#******************************************************************************#
#*   작성일자    작성자    작성내용*#
#*  ----------  --------  ----------------------------------------------------*#
#*  2003.03.28             First Edition                                      *#
#******************************************************************************#

################################################################################
# Environment Setting#
################################################################################

#환경 변수를 세팅 하는 곳입니다.
DBOWN=아이디/암호
CRONLOG=cron log디렉토리 위치

################################################################################
# Make Host Variable Header File Lists
################################################################################
DATE=`date +'%Y%m%d'`

#-------------------------
# cronjob log start
#-------------------------
echo analyze started !! : `date`>> ${CRONLOG}/analyze_week.${DATE}

sqlplus -s /nolog << EXIT
connect ${DBOWN}

SET ECHO OFF
SET HEADING OFF
SET PAGESIZE 0
SET FEEDBACK OFF

spool analyze_p_week.tmp
timing analyze_p_week
SELECT 'analyze table ' || table_name || ' partition ( ' || partition_name ||
' ) estimate statistics
        sample 10 percent for table for all local indexes;'
  FROM user_tab_partitions
 WHERE table_name like '%'<===여기에 해당 테이블들...
 ORDER BY table_name, partition_name
/

spool off

!echo '######## analyze partitioned table ########'
@@analyze_p_week.tmp

spool analyze_n_week.tmp

SELECT 'analyze table ' || table_name || ' estimate statistics
        sample 10 percent for table for all indexed columns for all indexes;'
  FROM user_tables
 WHERE table_name like '%'<===여기에 해당 테이블들...
 ORDER BY table_name
/

spool off

!echo '######## analyze general table ########'
@@analyze_n_week.tmp

prompt =======================================
timing stop
prompt =======================================
prompt
EXIT