/*-----------------------------------------------------------------------------------*/
[ 이슈 상황 ]
1시간 1회 주기 배치를, 30분으로 단축작업 이후, DB 현저하게 느려지는 현상 발생
> 단축작업: truncate추가 & 배치 serial -> parallel 변경 (사유는 1회차 시간단축을 위해)
[ 업무 설명 ]
#1 09:00 ~ 09:30 초기이관 DB_LINK사용, delete, truncate
#2 09:30 ~ 09:50
관련 배치 시작
/*-----------------------------------------------------------------------------------*/
[ 성능 개선 ]
1.1 성능 개선 대상
- DB서버 : Oracle 19C
- DB명 : 장중DB
- 업무명 : 장중 컴플라이언스
1.2 성능 저하 현상
- '장중 컴플라이언스' 배치 작업은 'ETL(아침에 1회)->메인 로직처리(1시간 간격으로 수행)'의 순서로 진행되며 정상적인 전체 작업 시간은 40 분 정도
- 일주일 전 부터 '메인 로직처리' 작업이 수 시간 이상 소요되고 있으며 가장 큰 변경 내용은 불필요한 데이터의 증가를 방지하기 위한 이력성 테이블들에 대한 Truncate 절차 추가 임
- 작업 지연 시 조치 방법은 정상 속도로 처리될 때까지 DB를 재기동하고 재작업 수행
1.3 성능 저하 원인 및 조치 사항
- 원인 : 성능저하가 발생하는 SQL들은 여러 개의 실행계획으로 수행되고 있으며, 비효율적인 실행계획으로 실행될 때 성능저하가 발생 함
1) 동적인 실행 계획 수립
Oracle DBMS는 10g 이상부터 통계정보를 동적으로 수집하고 있으며, 버전이 올라갈 수록 더 다양한 실행계획이 발생할 수 있음
2) Truncate 로 인한 통계정보의 변경
Truncate를 적용하기 전과 후의 대폭적인 데이터 건 수의 차이로 인한 실행계획 변경
(가장 핵심적으로 많이 사용되고 있는 CL_CPL_ASET 테이블의 영향이 대부분이며 7천5백 만 건 => 61만 건으로 변경되었음)
- 조치 사항
1) 동적인 실행 계획 수립 제한
옵티마이저와 관련된 파라미터를 수정하여 실행계획의 변동성을 줄임
2) 통계정보 최적화
핵심 테이블 CL_CPL_ASET 의 통계정보를 효율적인 실행계획이 수립될 수 있는 상태로 고정
(메인DB의 통계정보를 복사한 후 고정 시킴)
2. 성능 개선 조치 사항
2.1 통계정보 최적화
- 핵심 테이블 CL_CPL_ASET 의 통계정보를 최적의 상태로 고정
- 수행 작업 처리 절차
1) 운영 통계정보 복제 절차
#1 통계정보 백업 Table 생성 - 메인DB (1회 수행 후 재사용 가능)
SQL> exec dbms_stats.create_stat_table('SYSTEM','MIG_STATS','USERS');
#2 통계정보 백업 수행 - 메인DB
SQL> exec dbms_stats.export_table_stats(OWNNAME=>'MAFS',TABNAME=>'CL_CPL_ASET',PARTNAME=>null,STATTAB=>'MIG_STATS',CASCADE=>TRUE,STATOWN=>'SYSTEM');
SQL> exec dbms_stats.export_table_stats(OWNNAME=>'MAFS',TABNAME=>'CL_CPL_CKASET_NEW',PARTNAME=>null,STATTAB=>'MIG_STATS',CASCADE=>TRUE,STATOWN=>'SYSTEM');
SQL> exec dbms_stats.export_table_stats(OWNNAME=>'MAFS',TABNAME=>'CL_CKASET_MAP',PARTNAME=>null,STATTAB=>'MIG_STATS',CASCADE=>TRUE,STATOWN=>'SYSTEM');
#3 통계정보 백업 Table 이행 - 메인DB/COMPDB
- 백업 받은 Table을 export/import를 수행하여 메인DB에서 COMPDB로 이행
$ exp system/패스워드 tables=SYSTEM.MIG_STATS file=expstats.dmp
dmp 파일을 target으로 전송
$ imp system/패스워드 full=y file=expstats.dmp log=impstats.log
#4 통계정보 백업 Table 확인 - COMPDB
SQL> select count(*) from sys.mig_stats;
#5 통계정보 백업 정보 변환 - COMPDB
- 백업 받은 통계정보는 MAFS계정의 Table의 정보기 때문에 변환없이 통계정보 import를 수행할 수 없다.
- 통계정보 백업 내용 중 OWNNAME 내용을 update한 후 통계정보 import를 수행해야한다.
SQL> select c1, c5 from system.mig_stats group by c1,c5 ;
SQL> update system.MIG_STATS set c5='COMPLE1' where c5 = 'MAFS';
SQL> commit;
SQL> select c1, c5 from system.mig_stats group by c1,c5 ;
#6 통계정보 복구 수행 - COMPDB
- 통계정보 복구 시 즉시 커서 invalid가 수행되어 SQL들은 Parsing이 필요함(운영중 주의)
SQL> select NUM_ROWS, LAST_ANALYZED from dba_tab_statistics where table_name = '<TABLE_NAME>';
SQL> exec dbms_stats.import_table_stats(OWNNAME=>'COMPLE1',TABNAME=>'CL_CKASET_MAP',PARTNAME=>null,STATTAB=>'MIG_STATS',CASCADE=>TRUE,STATOWN=>'SYSTEM',NO_INVALIDATE=>FALSE,FORCE=>TRUE);
SQL> exec dbms_stats.import_table_stats(OWNNAME=>'COMPLE1',TABNAME=>'CL_CPL_ASET',PARTNAME=>null,STATTAB=>'MIG_STATS',CASCADE=>TRUE,STATOWN=>'SYSTEM',NO_INVALIDATE=>FALSE,FORCE=>TRUE);
SQL> exec dbms_stats.import_table_stats(OWNNAME=>'COMPLE1',TABNAME=>'CL_CPL_CKASET_NEW',PARTNAME=>null,STATTAB=>'MIG_STATS',CASCADE=>TRUE,STATOWN=>'SYSTEM',NO_INVALIDATE=>FALSE,FORCE=>TRUE);
#7 통계정보 확인
SQL> select NUM_ROWS, LAST_ANALYZED from dba_tab_statistics where table_name = '<TABLE_NAME>';
#8 통계정보 고정
SQL> exec dbms_stats.lock_table_stats('COMPLE1','CL_CPL_ASET');
2.2 파라미터 최적화
동적인 실행계획의 파생을 제한하도록 옵티마이저 파라미터 운영과 동일하게 변경
- 변경항목 (메인DB기준으로 히든파라미터 및 옵티마이저 파라미터 신규 설정)
-- hidden - 20230117
*._b_tree_bitmap_plans=FALSE
*._cleanup_rollback_entries=2000
*._dlm_stats_collect=0
*._drop_stat_segment=1
*._gc_bypass_readers=FALSE
*._gc_persistent_read_mostly=FALSE
*._gc_policy_time=0
*._gc_read_mostly_locking=FALSE
*._gc_undo_affinity=FALSE
*._in_memory_undo=FALSE
*._nlj_batching_enabled=0
*._nlj_batching_misses_enabled=0
*._optim_peek_user_binds=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_aggr_groupby_elim=FALSE
*._optimizer_batch_table_access_by_rowid=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_gather_stats_on_load=FALSE
*._optimizer_unnest_scalar_sq=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._rollback_segment_count=1000
*._undo_autotune=FALSE
-- recommend - 20230117
*.deferred_segment_creation=FALSE
*.open_cursors=1000
*.optimizer_adaptive_plans=FALSE
*.optimizer_adaptive_reporting_only=FALSE
*.optimizer_adaptive_statistics=FALSE
*.optimizer_dynamic_sampling=0
*.session_cached_cursors=1000
*.undo_retention=3600