#Tuning(튜닝)
Tuning이란 시스템이 제공하는 한정된 자원을 이용해서 시스템의 성능을 최적화시키는 작업을 의미한다. 즉, 튜닝은 문제가 되는 요인을 조기에 발견하여 데이터베이스로부터 사용자가 만족할 만한 정보를 얻게 하기 위해 정보 시스템을 개발하고 유지하기 위한 것이다.
#튜닝의 주체
튜닝은 개발이 시작되면서 끝날때까지 계속되는 작업이므로 어느 한 사람만이 튜닝과 관련된 업무를 담당하는 것은 아니다. 튜닝 할동을 하는 담당자는 다음과 같다.
• 응용 프로그램 설계자
• 응용 프로그램 개발자
• DB 설계자
• DB 관리자
• 소프트웨어/하드웨어 관리자
#튜닝 순서
오라클 튜닝을 수행하기 위해서는 튜닝 순서가 매우 중요하다. 일반적으로 좋은 시스템과 DB의 성능은 우수한 응용 프로그램 설계와 SQL 문장 작성에서부터 시작된다할 수 있다. 다음은 주요 튜닝의 순서이다.
1) 사용자 요구분석 및 업무 튜닝
2) 데이터 디자인 튜닝
3) 응용 프로그램 설계 튜닝
4) 데이터 운영 튜닝
5) 메모리 튜닝
6) 물리적 I/O 튜닝
7) 시스템 튜닝
#튜닝 도구
오라클 DB에서는 간편하고 쉽게 튜닝할 수 있는 튜닝 도구를 제공하고 있다.
alert log 파일 DB 상황이나, 오라클 인스턴스가 수행될 때 발생하는 오류 등을 기록 explain plan명령어 SQL 문이 어떻게 실행되는 것이 가장 빠른지를 결정한 결과 SQL*Trace와 TKPROF 시스템을 튜닝하는데 실행소요시간과 디스크나 메모리로부터 읽은 데이터 블록 수등에 대한 정보, SQL*Trace에 의한 파일은 2진 파일이므로 TKPROF로 읽을 수 있음 뷰와 유틸리티 v$fixed_table 뷰를 사용하면 모든 동적 성능 테이블, 뷰등을 확인가능 statspack 패키지 dba_*,v$*등과 같이 자료사전 참조순간 정보가 아닌, 일정한 시간동안 데이터베이스의 각 구조에 대한 튜닝 결과를 제공
#alert log 파일
DB 상황이나, 오라클 인스턴스가 수행될 때 발생하는 오류 등을 기록하는 파일로 DB마다 하나의 로그 파일이 생성된다. alert log 파일에 기록된 내용은 지우지 않으면 계속 누적되므로 불필요한 경고 파일들은 삭제하는 것이 좋다.
alert log 파일에 기록되는 내용은 다음과 같다.
• 서버 프로세스와 인스턴스의 중요 이벤트 명령어 기록
• 내부 에러와 블록 손상 에러
• 인스턴스 시작때 초기화된 값이 아닌 모든 매개변수의 값
• DB 구조나 DB 구성 변경에 관련된 명령어
- DB 생성, startup, shutdown, archive log, recovery
- create tablespace, create rollback segment, alter system switch logfile
• 체크포인트 시작시간과 종료시간(checkpoint_to_alert=TRUE로 설정된 경우에만 기록됨)
• DB 백업과 복구 작업에 대한 처리 절차
alert log 파일은 매개변수 파일인 initDB이름.ora의 BACKGROUND_DUMP_DEST에 지정된 경로에 생성된다.
매개변수 파일을 직접 확인하지 않고서도 SQL*Plus에서 다음 명령어를 사용하여 확인할 수 있다.
SQL> SHOW PARAMETER dump;
SQL> show parameter dump;
NAME TYPE VALUE
------------------------------------ ----------- -------------------------------------------------
background_core_dump string partial
background_dump_dest string /export/home/oracle/app/oracle/admin/orcl/bdump
core_dump_dest string /export/home/oracle/app/oracle/admin/orcl/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /export/home/oracle/app/oracle
/admin/orcl/udump
SQL>
$ pwd
/export/home/oracle/app/oracle/admin/orcl/bdump
$ ls -l alert_orcl.log
-rw-rw-r-- 1 oracle oinstall 123604 Dec 29 09:00 alert_orcl.log
$ file alert_orcl.log
alert_orcl.log: ascii text
$ more alert_orcl.log
Tue Nov 22 07:19:25 2005
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
KCCDEBUG_LEVEL = 0
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
Dynamic strands is set to TRUE
Running with 2 shared and 18 private strand(s). Zero-copy redo is FALSE
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 10.1.0.3.0.
System parameters with non-default values:
processes = 150
sga_target = 289406976
control_files = /export/home/oracle/app/oracle/oradata/orcl/control
01.ctl, /export/home/oracle/app/oracle/oradata/orcl/control02.ctl, /export/home/
oracle/app/oracle/oradata/orcl/control03.ctl
--More--(0%)
#explain plan 명령어
SQL 문이 처리되는 구문분석 단계에서 서버 프로세스에 의해 해당 SQL 문이 어떻게 실행되는 것이 가장 빠른지를 결정하는데, 이 결과를 실행계획(explain plan)이라 한다.
이 결과를 확인할 때 사용하는 튜닝 도구가 explain plan 명령어이다.
explain plan 명령어를 사용하여 확인할 수 있는 내용은 다음과 같다.
• DB에서 SQL문이 실행될 때의 상태 정보
• 실행 경로에 대한 정보
• SQL문의 WHERE절과 FROM절의 변화에 대한 정보
EXPLAIN PLAN 명령어를 실행하기 전에 분석결과를 저장하기 위한 plan_table을 생성해야 한다. plan_table은 utlxplan.sql 스크립트를 사용하여 생성한다.
EXPLAIN PLAN 명령어의 구문은 다음과 같이 두가지이다.
방법1 방법2
EXPLAIN PLAN
[INTO 테이블명]
FOR
SQL문;
EXPLAIN PLAN
[INTO 테이블명]
SET STATEMENT_ID=식별자 FOR
SQL문;
statement_id절은 여러명의 사용자가 동시에 분석 작업을 하는 경우 분석된 결과를 구분하기 위한 식별자로 30자까지 정의할 수 있다.
또 분석결과를 다른 테이블에 저장하기 위해 INTO 절을 사용하기도 한다.
#SET AUTOTRACE ON 명령어
set autotrace on 명령어는 PLAN_TABLE을 생성한 후, 한 번만 설정하면 SQL문이 실행될 때마다 실행계획을 화면에 출력해 준다.
SET AUTOTRACE의 문법은 다음과 같다.
-SET AUTOTRACE [ON|OFF|TRACEONLY][EXPLAIN][STATISTICS]
이 명령어 역시 사용하기 전에 PLAN_TABLE이 생성되어야 하고, PLUSTRACE 권한이 있어야 한다. 분석하고자하는 SQL문을 실행하면 화면에 SQL문의 결과가 표시되고, 이어서 실행계획이 출력된다.
#SQL*Trace와 TKPROF
SQL*Trace는 응용 프로그램 튜닝에 자주 사용되는 기능으로 SQL문의 실행계획과 더불어 시스템을 튜닝하는데 필요한 실행소요 시간과 디스크나 메모리로부터 읽은 데이터 불록 수 등에 대한 정보를 제공한다.
SQL*Trace에 의해 생성되는 결과는 바이너리 형태의 파일이며, 생성된 파일은 TKPROF 유틸리티를 사용하면 텍스트 형태로 변환하여 참조할 수 있다. EXPLAIN PLAN과 병행하여 사용하면 좋다.
#뷰와 유틸리티
데이터 사전 뷰와 동적 성능 뷰
동적인 문제 해결 정보와 성능 향상에 대한 데이터 사전 뷰로, 인스턴스가 시작된 이해 집적된 통계정보등을 제공한다. v$fixed_table 뷰를 사용하면, 모든 동적 성능 테이블, 뷰 등을 확인할 수 있다.
시스템 관련 통계
instance/DB Disk
v$px_process_sysstat v$filestat
v$process v$dbfile
v$waitstat v$tempfile
v$sysgtem_event v$tempstat
memory contention
v$buffer_pool_statistics v$lock
v$db_object_cache v$rollname
v$librarycache v$rollstat
v$rowcache v$waitstat
v$sgastat v$latch
세션 관련 통계정보
user/session
v$lock
v$open_cursor
v$process
v$sort_usage
v$session
v$sesstat
v$transaction
v$session_event
v$session_wait
v$px_sesstat
v$px_session
v$session_object_cache
시스템 관련 통계정보
시스템 전반에 대한 통계정보 목록은 v$statname 뷰에 분류되어 있으며, 오라클 서버는 v$sysstat 뷰를 사용하여 분석된 통계정보를 나타낼 수 있다.
세션 관련 통계정보
세션 데이터는 접속된 이후 게속 쌓이게 되고 로그인한 각 사용자에 대한 세션 정보를 볼 수 있다. 현재 세션에 대한 통계정보는 v$mystat 뷰를 사용하여 확인할 수 있다.
유틸리티
utlbstat.sql과 utlestat.sql 스크립트를 사용하여 정해진 기간동안 데이터베이스의 사용에 관련된 정보를 확인하거나 보고서를 출력하도록 하는 등의 일을 할 수 있다.
일반적으로 튜닝 초기 단계에서 필요한 데이터를 수집하기 뒤해서 사용하며, 매개변수 파일에서 timed_statisitics=TRUE로 설정하거나, 또는 다음과 같이 alter system 문을 사용하여 timed_statisitics 속성을 TRUE로 설정한다.
ALTER SYSTEM SET timed_statistics=TRUE
utlbstat.sql과 utlestat.sql 스크립트를 실행하면 라이브러리 캐시, 시스템, latch, 데이터 사전 캐시, 데이터 파일과 테이블 스페이스에 관한 I/O 등의 통계정보를 수집할 수 있다.
utlbstat.sql과 utlestat.sql 스크립트를 실행한 결과는 REPORT.TXT 파일로 저장한다.
SQL> show parameter timed_statistics;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
SQL>
--------------------------------------------------------------------------------
#statspack 패키지
지금까지 데이터베이스의 성능 분석에는 주로 DBA_*****, V$*****와 같은 자료 사전과동적 성능 뷰 등을 참조하였는데, 이러한 결과들은 항상 자료사전을 참조하는 순간의 결과만을 제공한다.
하지만 statspack 패키지는 일정한 시간동안 데이터베이스의 각 구조에 대한 튜닝 결과를 제공한다.
1) 매개변수 파일 initDB명.ora의 매개변수 TIMED_STATSTICS=TRUE로 설정한다.
2) STATSPACK 패키지는 spcreate.sql 스크립트를 실행하여 생성한다.
하지만 statspack 패키지는 일정한 시간동안 데이터베이스의 각 구조에 대한 튜닝 결과를 제공한다.
실행과정은 다음과 같다.
1) 매개변수 파일 initDB명.ora의 매개변수 TIMED_STATSTICS=TRUE로 설정한다.
2) STATSPACK 패키지는 spcreate.sql 스크립트를 실행하여 생성한다.
이 스크립트가 실행되면, STATSPACK테이블, 제약조건, STATSPACK 패키지등을 소유하는 PERFSTAT라는 사용자가 생성된다.
3) STATSPACK 패키지를 생성한 사용자 PERFSTAT로 접속한다.
4) 데이터베이스에 대한 정보를 수집하는 두가지 방법중 하나를 사용한다.
방법1) 통계정보를 수집하기 위해 STATSPACK.SNAP 프로시저를 실행한다.
이방법은 현재 시점의 튜닝 정보의 성능 정보를 수집한다.
방법2) SPAUTO.SQL 스크립트를 실행한다.
이 방법은 주기적으로 튜닝 정보를 수집하는 경우에 유용하며,
SPAUTO.SQL 스크립트의 DBMS_JOBS 패키지의 시간 매개변수 값을 변경하면
지정된 시간마다 누적된 튜닝 분석결과를 수집해 준다.
5) 4단계에서 분석한 튜닝 결과를 화면과 운영체제의 파일로 생성하기 위해 SPREPORT.SQL 스크립트를 실행한다. 스크립트 실행 후 기본적으로 생성되는 파일이름은 REPORT.TXT이다.
사용방법은 다음과 같다.
INIT<DB명>.ORA 파일에 TIMED_STATISTICS = TRUE
또는 show parameter timed_statistics로 TRUE인지 확인함
SQL> START rdbms/admin/spcreate.sql
SQL> CONNECT perstat/perstat
SQL> START rdbms/admin/spauto.sql
또는 execute rdbms/admin/statspack.snap
SQL> START rdbms/admin/spreport.sql
#SQL 튜닝
SQL 튜닝은 DB의 분석과 생성 단계에서 생성 되어 있는 테이블의 데이터를 빠르게 검색할 수 있도록 사용자의 SQL 문을 조율하는 것으로, SQL 튜닝의 목적은 최소의 자원을 효율적으로 사용하여 원하는 결과를 얻도록 하는 것이다.
SQL 튜닝을 위해서는 SQL 문이 어떻게 수행되는지, SQL 문을 연산하는 과정에서 어떤 자원이 필요한지 이해해야 한다.
SQL 문을 처리하기 위해 사용되는 구성 요소들의 기능은 다음과 같다.
• parser : SQL 문의 구조와 의미를 분석
- 구문구조분석(syntax analyse) : SQL 문이 바르게 작성되었는지 검사
- 의미 분석(semantic analysis) : DB 객체나 참조된 객체가 올바른지 검사
• Optimizer : SQL 문을 분석하여 가장 빠르게 실행할 수 있는 방법을 결정해주는 프로세서
- rule-based optimizer : 미리 정해진 규칙에 따라 가장 빠른 실행 방법을 결정
- cost-based optimizer : 실제로 가장 빠른 방법을 비용을 게산하여 선택하는 방법
• row source generator : 옵티마이저로부터 최적의 계획을 받아 SQL문에 대한 실행계획을 출력,
실행계획은 트리형태의 구조화된 행들의 집합을 의미함
• SQL실행엔진 : SQL 문과 관련된 실행계획을 수행하는 구성요소로 질의에 대한 결과를 생성한다.
row source generator에 의해 생성된 각 행들이 SQL 실행엔진에 의해 실행된다.
1) 옵티마이저 : 옵티마이저는 select, update, insert, delete아 같은 SQL 명령에 대한 최적의 실행계획을 선택하는 역할을 하는 프로세스이다. • PLAN_TABLE 옵티마이저는 PLAN_TABLE이 있으면 sql문이 실행될 때마다 우선실행계획을 작성하여 PLAN_TABLE에 저장한다.
• 실행계획 SQL문은 EXPLAIN PLAN 명령을 사용하여 SQL문을 분석하고 해석하여 실행계획을 수립하고 실행계획 테이블에 저장한다.
• 옵티마이저 모드 비용기반과 규칙기반이 있으며, 세가지 방법을 사용하여 모드를 지정한다.
• analyze 명령어 비용기반 방식에 사용할 통계 데이터를 모아주기 위해 사용되는 튜닝도구로 테이블과 인덱스에 대한 통계정보를 수집한다.
• DBMS_STATS 패키지 스키마나 DB에 있는 모든 스키마 객체에 대한 통계뿐만 아니라 클러스터를 제외한 인덱스, 테이블, 컬럼과 파티션에 대한 통게정보 수집및 통계정보에 대한 검색, 수정, 삭제, 익스포트, 임포트등이 가능하다.
• 힌트 select, update, delete 문 사용시 옵티마이저의 파싱을 원하는 쪽으로 유도할 때 사용한다.
#서버 튜닝
server tuning은 최적의 성능을 얻기 위해 DB 버퍼 캐시, 로그 버퍼와 같은 메모리 영역과 기타 프로세스 영역의 크기를 적당하게 할당하여 성능을 향상시키는 방법을 말한다.
서버튜닝을 통하여 성능을 향상시킬 수 있는 내용은 다음과 같다.
• DB 메모리 영역의 튜닝을 통한 성능 향상
• 물리적 디스크로부터 입출력 수행에 따른 성능 저하 개선
• 백그라운드 프로세스의 활성하를 통한 DB 성능 개선
공유 풀 영역튜닝 공유 풀은 사용자가 실행한 SQL문의 구문 분석 정보가 저장되는 영역으로 라이브러리 캐시 영역과 데이터 사전 캐시로 구성되어 있다.
데이터버퍼 캐시 영역튜닝 데이터 파일로부터 읽혀진 데이터가 저장되는 메모리 영역으로 이영역의 크기는 initDB명.ora파일에 DB_CACHE_SIZE에 의해 결정된다. 데이터 버퍼 캐시 영역 튜닝의 목적은 버퍼 캐시의 상태를 파악하여 캐시 히트율을 높이는 것이다.
리두로그 버퍼 튜닝 사용자가 DB에 접속한 후 DML 작업을 실행하면 트랜젝션에 의해 만들어진 모든 변경 정보가 리두 로그 버퍼 영역에 저장된다. 로그버퍼 영역의 크기는 initDB명.ora파일에 LOG_BUFFER 매개변수에 의해 설정된다.
#I/O 튜닝
오라클 서버의 튜닝은 첫 번째 주안점이 메모리 영역이었다면, 두번째 주안점은 물리적 영역 이다. 이 물리적 영역에 대한 튜닝이 I/O 튜닝이다. 실제로 응용 프로그램은 물리적 읽기/쓰기에 많은 시간이 소비되므로 I/O에 대한 튜닝 작업은 매우 중요하다. 따라서 I/O에 대한 분석을 통해서 그 성능을 높이는 것이 I/O 튜닝의 목적이다.
디스크의 물리적 구조에 대한 분석 디스크의 물리적 구조에 따른 성능 저하
• 디스크 경합
• 디스크 동시성
• 디스크 I/O 튜닝을 위한 분석
I/O 튜닝하기 I/O 튜닝은 SQL튜닝이다. 메모리 튜닝이 잘 되어 있다면, 디스크로의 요청에 대한 처리를 받아 디스크들을 분산시키는 것이 그 목적이다.
• 데이터 파일과 리두 로그 파일 분리
• 테이블과 인덱스 분리
• 파일 스트라이핑
• 전체 테이블 스캔
• 체크 포인트 튜닝
• 멀티 DBWR 프로세스
#소팅 영역의 튜닝
사용자가 작성하는 SQL 문은 대부분 실행시 분류(sorting)작업이 발생하는데, 소팅 작업은 별도의 시간과 공간이 추가로 필요하기 대문에 소팅 작업이 발생하지 않을 때보다 성능이 떨어질 수 밖에 없다
사용자의 SQL 문에서 소팅 작업이 발생하는 경우는 다음과 같다.
• 인덱스를 생성하는 경우(create index 문)
• 인덱스가 있는 테이블에 병렬로 데이터를 입력하는 경우(insert into ... parallel)
• ORDER BY, GROUP BY. DISTINCT 절을 사용하는 경우
• UNION, INTERSECT, MINUS 연결 연산자를 사용하는 경우
• 인덱스가 없는 두 개의 테이블을 조인하는 경우,
소팅할 작업의 크기가 SORT_AREA_SIZE 매개변수보다 작을 경우 메모리에서 소팅 작업을 하고(이상적), 설정된 매개변수의 크기보다 큰 경우는 다음과 같이 작업한다.
1) 데이터를 작은 단위로 나누고, 각 단위별로 소팅한다.
2) 소팅되는 부분만 메모리에 (SORT_AREA_SIZE 크기 만큼)올라오고 나머지는 임시 테이블스페이스에 보관된다.
3) 소팅된 데이터들이 결합된다. 만일 소팅된 데이터들을 결합하는 경우 SORT_AREA_SIZE 크기가 충분하지 않으면 여러 번으로 나누어 결합한다.
#메모리 영역
소팅 작업은 전용 서버 환경에서는 서버 프로세스에 있는 PGA 영역의 SORT AREA 영역에서 소팅 작업이 실행된다.
이 영역의 크기는 initDB명.ora 파일에 SIZE_AREA_SIZE 매개변수를 설정하거나, ALTER SESSION 또는 ALTER SYSTEM DEFFERED 명령문으로 변경할 수 있다. 또 소팅 작업 후에도 SORT_AREA_SIZE 영역이 PGA 영역에 계속 할당되어 있기 때문에 메모리의 낭비를 초래할 수 있으므로 SORT_AREA_RETAINED_SIZE 매개변수를 사용하여 SORT_AREA_SIZE 영역을 축소시킬 수 있다. 전용 서버인 경우는 SORT_AREA_RETAINED_SIZE크기= SORT_AREA_SIZE 크기로 설정한다.
#디스크 영역
시스템이나 DB를 위한 메모리 영역은 크기가 제한되어 있기 때문에 소팅 작업을 실행해야 할 모든 데이터를 메모리에 저장할 수 없다. 그래서 필요한 영역이 임시 테이블스페이스이다. 메모리 영역인 SORT_AREA_SIZE 영역에서 1차 소팅 작업이 실행되고 작업이 완료되지 못하면 디스크 영역에 생성되어 있는 임시 테이블스페이스에 소팅된 내용을 저장하게 된다. 이러한 연속적인 작업을 통해 대용량 데이터에 대한 소팅 작업을 실행한다.
#소팅 영역에 대한 튜닝
DB가 생성되면 기본적으로 임시 테이블스페이스가 생성되고, 사용자가 소팅하려는 데이터가 너무 커서 임시 테이블스페이스 공간만으로는 모든 소팅 작업이 이루어질 수 없을 때 SQL문의 성능이 저하된다.
V$SYSSTAT, V$SORT_SEGMENT, V$SORT_USAGE 데이터 사전을 사용하면 이 영역에 대한 분석 결과를 조회하여 튜닝 여부를 확인할 수 있다
V$SYSSTAT 뷰는 메모리 영역과 디스크 영역에서의 소팅 작업수에 대한 정보를 조회하거나, 디스크 소팅 비율 등을 조화할 수 있다
SQL> select * from v$sysstat
2 where name like '%sorts%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------ ---------- ---------- ----------
310 sorts (memory) 64 275233 2091983730
311 sorts (disk) 64 0 2533123502
312 sorts (rows) 64 42558393 3757672740
SQL>
디스크 영역에서의 소팅과 메모리 영역에서의 소팅 비율이 5% 이하인 것이 이상적이다. 만약 5%가 넘는다면 디스크를 사용한 소팅의 횟수가 많다는 것을 의미하므로 SORT_AREA_SIZE 크기를 늘려야 한다.
SQL> select disk.value Disk, mem.value mem,
2 (disk.value/mem.value)*100 Ratio
3 from v$sysstat mem, v$sysstat disk
4 where mem.name='sorts (memory)'
5 and disk.name='sorts (disk)';
DISK MEM RATIO
---------- ---------- ----------
0 275349 0
SQL>
또 V$SORT_USAGE 뷰를 참조하여 세션별로 임시 테이블스페이스를 얼마나 사용하고 있는지 분석할 수 도 있다
'ORACLE' 카테고리의 다른 글
| 오라클 startup 순서 (0) | 2022.11.07 |
|---|---|
| DML, DDL, DCL, TCL+ RR날짜형식 (0) | 2022.11.07 |
| Oralce DB 수동 생성 (0) | 2022.11.07 |
| select 작성 및 해석 권장 순서 (0) | 2022.11.07 |
| oracle(오라클) 쿼리 모음 (0) | 2021.06.28 |
댓글