( 재 ) 한국데이터베이스진흥센터 1 KDPC-ET-2003-10 데이터베이스전문인력양성교육 오라클데이터베이스튜닝방법론주종면강사 한국데이터베이스진흥센터 Korea database promotion center 개 요 I. 오라클 DB튜닝 Methodology ------------ 01 page 오라클데이터베이스의구조 --------- 03 page 튜닝 Method와 Methodology ---------- 07 page II. Key To Tuning ------------------------ 튜닝 Methodology ------------------- step - 1 준비단계 ---------------- step - 2 튜닝단계 ---------------- 09 page 10 page 10 page 21 page III. 오라클튜닝 Road Map --------------- 22 page SQL 튜닝 Road Map ----------------- 22 page Server 튜닝 Road Map -------------- 35 page Network 튜닝 Road - Map ------------- 37 page
( 재 ) 한국데이터베이스진흥센터 2 오라클데이터베이스튜닝방법론워크숍 한국데이터베이스진흥센터주종명강사 I. 오라클 DB 튜닝 Methodology
( 재 ) 한국데이터베이스진흥센터 3 데이터베이스의발전방향 1970 년대데이터베이스관리기법의태동시기 1980 년대관계형데이터베이스의상용화 1990 년대데이터베이스의도입및구축시기 2000 년대 보다많은사용자들은보다빠르게데이터를검색할수있는방법을요구 1) Speedup : 보다빠른성능을요구 2) Scaleup : 보다많은사용자에게서비스 3) Sizeup : 보다대용량데이터의저장을요구 성능을저하시키는기업의문제점 1) 결과물위주의개발기법과개발기간 2) 충분하지못한비용 3) 개발자의 RDBMS에대한기술력의한계 4) 적적하지못한 H/W, DBMS의도입 5) 실패에대한두려움
좋은성능을위한요소 1) 관계형데이터베이스에맞는시스템설계 2) 시행착오를줄이고생산성을향상시킬수있는분석 / 설계자 3) SQL 에대한명확한기술을확보한개발자 오라클데이터베이스의구조 ( 재 ) 한국데이터베이스진흥센터 4
( 재 ) 한국데이터베이스진흥센터 5 오라클데이터베이스의구조 메모리 (Instance) Data Buffer Cache SGA Logo Buffer Shared pool Large Pool 서버프로세스 PGA 사용자프로세스 DBWR CKPT LGWR PMON SMON Control files Archived log files Data files Redo log files Parameter file 파일 Password file Select 문의처리과정 1 Instance. 구문분석 (Parse). 실행 (Execute). 인출 (Fetch) 사용자프로세스 서버프로세스 PGA 4 3 Database 1주종면부산 2주영현서울 buffer cache SGA Redo log buffer Shared pool Library cache SELECT * FROM emp ORDER BY ename; Data dictionary cache. 접속일자. 사용자 / 암호 %sqlplus ID ID NAME scott/tiger LOC 2 SELECT -------------------- * FROM 1 emp 주종면부산 ORDER 2 주영현 BY BY ename; 서울 Data files EMP 1 주종면부산 2 주영현서울 Control files Database Redo log files
( 재 ) 한국데이터베이스진흥센터 6 DML 문의처리과정 3 Instance 사용자프로세스. 구문분석 (Parse). 실행 (Execute). 접속일자 Server process 4 2 Database 1주종면110 1주종면100 buffer 1주종면 cache 100 Rollback-block SGA 1Redo 주종면100 log 1주종면110 buffer Shared pool Library cache UPDATE emp SET sal=sal*1.1 WHERE empno=1 Data dictionary DBA_TABLES cache. 사용자 / 암호 %sqlplus scott/tiger UPDATE emp SET sal=sal*1.1 WHERE empno=1; 1 Data files EMP 1 주종면 100 2 주영현 120 RBS01 Control files Database Redo log files COMMIT 문의처리과정 2 Instance SGA Shared pool 서버프로세스 Database 1주종면110 2주영현132 buffer 1cache 주종면100 2 주영현 120 SCN:8014 1주종면100 Redo 1주종면log 110 buffer 2 주영현 120 2 주영현 132 UPDATE emp SET sal=sal*1.1 WHERE empno=1 1 4 사용자프로세스 Sql> commit; Committed. Committed. 7 DBWR 8014 8014 8014 Data files EMP 1주종면110 2주영현132 RBS01 CKPT LGWR 6 3 102 103 Redo SCN:8014 log Control 8014 1주종면 files100 files 1주종면110 5 103 2 주영현 120 2 주영현 132
( 재 ) 한국데이터베이스진흥센터 7 논리적저장구조 /disk1/data SYSTEM.DBF dba_tables Segment RBS.DBF dba_indexes UNDO01 Extents dba_users UNDO02 V$log Block Usaer_tables (db_block_size) UNDO03 Tablespace SYSTEM RBS Physical Storage Database EMP /disk2/data INSA1.DBF aaa001 aaa002 aaa003 aaa004 aaa005 1 주종면 110 2 주영현 132.... INSA.... aaa006 aaa007 aaa008 aaa009 aaa010 DB 생성과저장구조의관계 CREATE DATABASE ora90 LOGFILE GROUP 1 ( /disk1/oradata/redo01.log ) size 10m, GROUP 2 ( /disk2/oradata/redo02.log ) size 10m, GROUP 3 ( /disk3/oradata/redo03.log ) size 10m DATAFILE /disk4/oradata/system01.dbf size 100m UNDO TABLESPACE undo DATAFILE /disk5/oradata/undo01.dbf size 50m DEFAULT TEMPORARY TABLESPACE temp TEMPFILE /disk6/oradata/temp01.dbf size 30m EXTENT MANAGEMENT LOCAL UNIFORM size 1m CHARACTER SET ko16ksc5601 NATIONAL CHARACTER SET ko16ksc5601 SET TIME_ZONE = Korea/Seoul ;
튜닝 Method 와 Methodology 튜닝 Method 비즈니스튜닝 디자인튜닝 SQL 튜닝 서버튜닝 System 튜닝 Network 튜닝 비즈니스-룰튜닝데이터-디자인튜닝응용PG설계의튜닝논리적구조의튜닝실행경로의튜닝 SQL 문장의튜닝메모리구조의튜닝 I/O와물리적구조자원에대한경합 OS와 Network의경합 업무 Process 의간소화를통한성능향상 정규화및역정규화테이블의파티션닝여부로컬 / 글로벌인덱스 App-PG 의구조설계 테이블의저장구조 Foreign-Key 및제약조건의여부 인덱스사용결정인덱스타입분석클러스터의활용 옵티마이저활용조인기법의활용 버퍼캐시영역, 공유- 풀영역, 로그-풀영역 Disk I-O, 저장구조의물리적설계분석 Sort공간,Lock 경합 Undo 공간 ( 재 ) 한국데이터베이스진흥센터 8
( 재 ) 한국데이터베이스진흥센터 9 튜닝 Methodology 준비단계 (Step-1) 분석 / 튜닝단계 (Step-2) 결과단계 (Step-3) 고객 Interview 디자인튜닝 튜닝후자료수집 / 분석 자료수집및분석 (Check-List 작성 ) SQL 튜닝 튜닝결과평가 튜닝계획수립 서버튜닝 산출물작성 고객 Interview System/Network 튜닝 고객 Interview 의문점 / 문제점분석 튜닝대상적용 고객 Interview Key To Tuning CPU Memory I/O Network S/W Design/Architecture DML SQL or Structure Query SQL or Structure Buffer Cache Area Shared Pool Area Sort Area(Temp Segment) Physical Datafile I/O Logfile I/O Archiver-File I/O Undo Segments Locking Network-Traffic
( 재 ) 한국데이터베이스진흥센터 10 Tunner 개발자 (SQL 튜닝 ) DB 데이터베이스관리자 ( 서버튜닝 ) 분석 / 설계자 ( 디자인튜닝 ) 시스템관리자 (O/S 튜닝 ) 네트워크관리자 (Network 튜닝 ) II. KEY TO Tuning
( 재 ) 한국데이터베이스진흥센터 11 튜닝 Methodology Step-1 준비단계
( 재 ) 한국데이터베이스진흥센터 12 고객인터뷰 성능튜닝요청서 요청날자년월일튜닝담당자 요청자 요청부서 성능범위 DB 성능 ( ) 프로그램성능 ( ) H/W 성능 ( ) Network 성능 ( ) 증상내용 기타사항 자료수집및분석 1) 1) 고객으로부터성능성능문제점에대해대해충분한충분한의견을의견을청취하십시오.. 이단계에서는고객의고객의불만과불만과NEED NEED를정확히정확히이해하는것이것이무엇보다중요합니다.. 2) 2) 운영체계,, 데이터베이스,, 프로그램으로부터성능과성능과관련된관련된모든모든정보를수집하십시오..( 운영체계의 ( 상태정보,, Application 의 Trace, Trace, 오라클오라클Alert 파일파일 )) 3) 3) 오라클오라클데이터베이스사용자들의실수에실수에의해의해자주자주발생하는 Mistake 를기반으로 Check-List 를작성하십시오를.. 4) 4) 반복적인시간대에반복적인정보수집을통해통해다양한다양한정보를정보를수집하라.. (( 동적동적성능성능뷰,, Utlbstat 와 Urlestat 스크립트,, STATSPACK 패키지패키지등 )) 5) 5) 고객의고객의NEED NEED와수집된수집된정보를정보를기반으로튜닝튜닝범위를범위를결정하십시오..
( 재 ) 한국데이터베이스진흥센터 13 상태정보수집 1. 운영체계의메모리 /CPU/I-O 상태를 Check 하십시오. 1) 운영체계의 CPU Idle-Time이 0 ~ 10% 는 CPU의사용에문제가발생하여성능저하문제가발생할수있습니다.(Memory는사용자 60%, SYSTEM이 40% 의비율이최적 ) $ vmstat 1 procs memory page disk faults cpu r b w swap free re mf pi po fr de sr s6 sd sd sd in sy cs us sy id 0 0 0 5131208 719288 13 92 30 4 4 0 0 0 13 0 7 553 476 908 2 2 96 0 0 0 3707480 45736 0 7 0 0 0 0 0 0 10 0 2 438 9613 551 0 0 56 0 0 0 3707480 45736 0 7 0 0 0 0 0 0 10 0 2 438 9613 551 0 0 5 2. 오라클데이터베이스의상태정보를 Check 하십시오. 1) background_dump_dest 파라메터가지정하는경로에존재하는 Alert_<SID>.log 파일을참조하여모든로그내용을수집하십시오. $ sqlplus system/manager SQL> show parameter background_dump_dest NAME VALUE -------------------------------------------------------------------- background_dump_dest /oracle9/admin/bdump SQ> exit $ vi /oracle9/admin/bdump/alert_ora9.log Online Redo Log: Thread 1 Group 1 Seq 8 Reading mem 0 Mem# 0 errs 0: C:\ORACLE\ORADATA\ORA90\REDO01.LOG Sun Oct 21 21:05:24 2001 Thread 1 opened at log sequence 9 Current log# 2 seq# 9 mem# 0: C:\ORACLE\ORADATA\ORA90\REDO02.LOG Successful open of redo thread 1. Completed: alter database open Sun Oct 21 23:02:20 2001 create tablespace insa datafile 'c:\oracle\oradata\ora90\insa1.dbf' size 5m Sun Oct 21 23:02:25 2001 Completed: create tablespace insa datafile 'c:\oracle\oradata
( 재 ) 한국데이터베이스진흥센터 14 Check-List 작성 1. 데이터베이스의메모리상태를 Check 하십시오. 1) 공유 - 풀영역의분석 GETHITRATIO -------------------------------.939776536 GETHITRATIO 가 90% 이하인경우튜닝이요구됨 Sum(reload)/sum(pins) -------------------------------.001239704 결과가 1% 이상이면튜닝이요구됨 2) 데이터버퍼캐시영역의분석 SGA DataBuffer Cache Database 1주종면110 2주영현132 buffer 1주종면100 2 cache 2주영현120 3 서버 3 프로세스 1 DBWR 2 RATIO -------------------.98174076 Ratio < 90% 이하이면 db_cache_size 증가 1 Data files EMP 1주종면110 2주영현132
( 재 ) 한국데이터베이스진흥센터 15 3) 로그버퍼영역의분석 SECOND State ------------------------------- 110 Wait Second 가지속적으로증가 log_buffer 증가 NAME VALUE ------------------------------- redo entries 2015 redo buffer alloc 12 Redo buffer alloc 가 redo entries 의 1% 이상이면 log_buffer 증가 2. Database 의 I/O 문제로인한성능문제를 Check 하십시오. DISK1 DISK2 DISK3 System.dbf data1.dbf data2.dbf Index1.dbf undo.dbf Temp.dbf select tablespace_name, file_name, phyrds, phywrts from dba_data_files df,v$,v$filestat fs where df.file_id=.file_id=fs.file# ; TABLE_SPACE NAME PHYS_ PHYS_ PHYS_ PHYS_ PHYS_ PHYS_ READS BLKS_RD RD_TIME WRITES BLKS_WR WRT_TIM ------------------------- ------------------ ------------ -------------- -------------- ------------ --------------- ------------------- UNDO /disk2/rbs.dbf 1200 5243 8324 5259 532 2034 TEMP /disk3/temp.dbf 650 623 1200 634 423 1421 DATA1 /disk1/data1.db 202 986 3210 94 62 1058 DATA2 /disk2/data2.db 62 766 1330 50 46 906 INDEX1 /disk2/index1.dbf 42 121 117 3 3 39 SYSTEM /disk1/system.dbf 740 3553 6779 44 44 1619
( 재 ) 한국데이터베이스진흥센터 16 3. Redo-Log 파일의크기와개수를 Check 하십시오. 1) 너무작은리두-로그파일의개수와크기는성능을저하시키는원인이됩니다. SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER ---------------------------------------------------------------------------------------------------- 1 ONLINE /class01/dba901/dba9i115/log01a.rdo 2 ONLINE /class01/dba901/dba9i115/log02a.rdo 3 ONLINE /class01/dba901/dba9i115/log03a.rdo SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS ---------------------------------------------------------------------------------------------------------------------- 1 1 77 5242880 1 NO INACTIVE 2 1 78 5242880 1 NO CURRENT 3 1 76 5242880 1 NO INACTIVE 2) ALERT 파일을참조해보면 CheckPoint의성능저하현상을확인할수있습니다. $ vi alert_<sid>.log < 생략 > Checkpoint not complete ; unable to allocate file 4. 디스크에서발생하는 Sorting 공간을 Check 하십시오. 대용량데이터에대해분류작업이발생하는 SQL문을실행하면 PGA 영역과 TEMPORARY 테이블스페이스에 SORTING 정보를저장하게됩니다. 이미할당된이공간의크기가너무작으면성능이저하됩니다. Disk Mem Ratio ------------------------------------------ 0 3423 0 > 5 % 이면 SORT_AREA_SIZE 증가 TEMP 세그멘트의추가
5. FreeList, Undo-Segment 의경합을 Check 하십시오. 1) 입력, 삭제작업이빈번하게발생하는테이블의경합관련정보를확인하십시오. SQL> SELECT class, count, time FROM v$waitstat WHERE class = segment header ; CLASS COUNT TIME ------------------------------------------------------------------------- Segment header 120 350 2) UNDO 세그멘트의현재대기상태를확인하십시오. SQL> SELECT class, count, time FROM v$waitstat WHERE class = undo header ; CLASS COUNT TIME ----------------------------------------------------------------- Undo Segment header 34 230 SQL> SELECT 100*sum(waits)/sum(gets) rate, sum(waits), sum(gets) FROM v$rollstat; RATE SUM(WAITS) SUM(GETS) ----------------------------------------------------------------- 0 0 266345 6. Recursive-SQL 의발생빈도를 Check 하십시오. 1) Data-Dictionary 타입의테이블스페이스에생성된테이블의모든익스텐트정보는 SYSTEM 테이블스페이스에기록됩니다. 익스텐트가빈번하게발생하는테이블이어떤타입의테이블스페이스에생성되는지확인하십시오. SQL> SELECT segment_name, max(extent_id), tablespace_name FROM user_extents GROUP BY segment_name, tablespace_name SEGMENT_NAME MAX(EXTENT_ID) TABLESPACE_NAME ------------------------------------------------------------------------------------------------------------------------------ S_INVENTORY 1700 SALES S_INVENTORY_PRODID_WARID_PK 120 SALES S_WAREHOUSE 3500 SALES S_WAREHOUSE_ID_PK 250 SALES SQL> CONNECT system/manager SQL> SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT FROM DBA_TABLESPACES TABLESPACE_NAME EXTENT_MAN --------------------------------------------------------------------- SYSTEM DICTIONARY SALES DICTIONARY Locally Management 테이블스페이스의생성을권장 ( 재 ) 한국데이터베이스진흥센터 17
7. 옵티마이저의상태를 Check 하십시오. 1) 현재오라클서버의기본옵티마이저환경과성능이저하된테이블이마지막분석날자를비교하여최신통계정보를분석하여주십시오. SQL> connect /as sysdba SQL> SHOW PARAMETER optimizer_mode NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_mode string CHOOSE 현재옵티마이저는비용기반옵티마이저 SQL>!date 2003 년 9 월 5 일금요일오전 12 시 48 분 07 초 SQL> ANALYZE TABLE big_emp COMPUTE STATISTICS; Table analyzed. SQL> SELECT TABLE_NAME, LAST_ANALYZED FROM user_tables; TABLE_NAME LAST_ANAL ------------------------------------------------------------ BIG_EMP 05-SEP-02 현재시간보다 1 년전에분석된통계정보 BIG_DEPT 8. 대용량데이터의 Full-Table-Scan 을 Check 하십시오. 1) 성능저하현상이의심가는 SQL문에대해실행계획을확인하십시오. 대용량데이터가저장되어있는테이블에대한 FULL TABLE SCAN은성능저하현상이초래될수밖에없습니다. 인덱스가생성되어있는대도불구하고인덱스를사용하지않는다면 SQL 튜닝이요구됩니다. SQL> SELECT name, value FROM v$sysstat WHERE name LIKE %table scans% ; NAME VALUE ---------------------------------------------------------------- table scans(long tables) 88 이값이지속적인증가를보인다면전체테이블스캔이과다하게발생하는경우 SQL> SET AUTOTRACE ON SQL> SELECT empno, ename, deptno FROM big_emp WHERE substr(ename, 1, 2) = 주 ; Execution Plan ----------------------------------------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer = choose 1 TABLE ACCESS (FULL) OF BIG_EMP ( 재 ) 한국데이터베이스진흥센터 18
( 재 ) 한국데이터베이스진흥센터 19 9. 객체들의논리적, 물리적구조에대한정보를 Check 하십시오. 테이블과인덱스의구조적문제점을분석하십시오.( 행이주 / 행연결현상, 인덱스의밸런싱등 ) SQL> ANALYZE TABLE account COMPUTE STATISTICS; Empty AveRow Pct Pct Chain Distinct Cluster Table Rows Blocks Blocks Length Free Used Count Column Values ----------- ---------- ------- ------ ------ ------- ---- ---- ----- --------------- -------- Unclustered ACCOUNT 28969 425 9 50 10 40 500 NO 28969 START_DATE 44 OPEN_SITE 1 PRIVATE_PASS 0 CONFORM_YN 1 CLOSE_STATUS 2 ACCOUNT_KIND 0 RATE 1 CUSTOMER 28969 CUSTOMER_KIND 3 SQL> ANALYZE INDEX I_account_cust COMPUTE STATISTICS; SQL> ANALYZE INDEX I_account_cust VALIDATE STRUCTURE; Table Pct Cluster Table # Rows Index Space U Column # Dist Free Factor BLevel Leaf BALANC ---------- ------ --------------- ---------- - ------------ ------ ---- ------- ------ ----- ------ ACCOUNT I_ACCOUNT_CUST SYSTEM N CUSTOMER 10 843 1 130 30 SYS_C004736 SYSTEM U NO 10 843 1 175 0 10. 오라클데이터베이스의 CPU 사용상태를 Check 하십시오. 1) SQL 문에대한 CPU 파싱시간이전체파싱시간을초과하지않아야합니다. (CPU 사용에대한 wait-time 은 0 이최적 ) * Wait-Time = ( Parse Time Elapsed - Parse time CPU ) SQL> SELECT * FROM v$sysstat WHERE NAME IN ( parse time cpu, parse time elapsed, parse count ); 2) SQL*TRACE 에의해해당세션에서 CPU 사용상태를확인해보십시오.( 세션별분석 ) OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------------- -------- --------- ------------ ----------- ---------- ----------- --------- Parse 742 2.94 3.28 24 32 0 0 Execute 1904 1.79 1.91 0 45 0 1190 Fetch 1942 87.54 96.72 322 812510 57 2620 ------------- -------- -------- ------------- ----------- ---------- ----------- --------- Total 4588 92.27 101.91 324 812587 57 3810
11. 오라클데이터베이스의 Wait Event 를 Check 하십시오. 1) 데이터베이스구조에대한전체상태정보를 V$SYSTEM_EVENT 를통해분석하십시오. ( 세션별상태정보는 V$SESSION_EVENT 를참조합니다.) Wait Event 튜닝영역발생원인 buffer busy waits free buffer waits Log buffer space Log file switch completion Log file switch (checkpoint) db file scattered read db file sequential read 버퍼캐시튜닝, DBWR 튜닝 버퍼캐시튜닝, DBWR 튜닝, I/O 튜닝 로그버퍼튜닝, I/O 튜닝 I/O 튜닝 SQL 튜닝 1) 대용량테이블에대한삭제작업 ( 문제발생시점에 v$sesion_wait 를참조하여분석 ) 2) FreeList 의경합, Undo Segment Header 의경합 1) DISK Write Time의문제 2) 버퍼-캐시의크기가너무작은경우 1) 로그버퍼의크기가너무작은경우 2) 성능이늦은 IO System 1) V$SQLAREA에서 DISK-Read를많이유발하는문장분석 (Full-Table Scan에서주로발생 ) 2) 성능이늦은 IO System(V$FILESTAT를통해분석 ) Latch free 공유풀영역튜닝 1) 공유풀영역의크기가너무작은경우 2) SQL 문장의 Parse가반복적으로발생하는경우 enqueue LOCK 튜닝 1) V$ENQUEUE_STAT 를통해현상태분석 Undo segment tx slot UNDO 튜닝 1) UNDO 세그멘트에경합이발생하는경우 2) 다음은특정세션에대한상태정보를 V$SESSION_WAIT 를통해분석하십시오. (wait_time 컬럼의값이 > 0 인조건은프로세스가 Busy 상태를나타냅니다.) Idle Wait Event Background Process User Process Parallel Process Shared Process Real App. Cluster Network Traffic Dispatcher timer Lock manager wait for Remote message Pipe get Pmon timer Px idle wait Px deq credit:need buffer Px deq credit:send blk Rdbms ipc message Smon timer SQL*Net message from Client ( 재 ) 한국데이터베이스진흥센터 20
( 재 ) 한국데이터베이스진흥센터 21 12. 데이터베이스와의네트워크접속상태를 Check 하십시오. (Listener 프로세스, Server 프로세스, Dispatcher 의상태확인 ) 1) LISTENER의로그상태확인 ($HOME/network/log/listener.log) LISTENER 프로세스가 BUSY 상태이거나계속적인서비스를할수없는경우에는사용자들의데이터베이스접속시간이지체될수밖에없습니다. $ vi $HOME/network/log/listener.log 28-JAN-2003 19:30:23 * (CONNECT_DATA=(SERVICE_NAME=DBA9I230)(CID=(PROGRAM=C:\Pro gram Files\Oracle\jre\1.1.8\bin\jrew.exe)(HOST=HELLO)(USER=LEE))) * (ADDRESS=(PROTOCOL =tcp)(host=61.99.65.151)(port=3052)) * establish * DBA9I230 * 12514 TNS-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor < 생략 > 11-SEP-2002 17:19:46 * service_register * ora901 * 0 11-SEP-2002 17:19:57 * service_died * ora901 * 12537 2) Dispatcher 의상태확인 SELECT network "Protocol", SUM(busy) / (SUM(busy)+SUM(idle)) "Total Busy Rate" FROM V$DISPATCHER GROUP BY network; Total Busy Rate의비율이 50 % 이상이면 max_dispatchers 의수를증가해야함 3) Dispatcher가응답큐로부터기다리는평균시간확인 SELECT network "Protocol", DECODE(SUM(totalq),0,' 응답없음 ', 지속적으로값이증가하면 SUM(wait)/SUM(totalq) '(1/100초 )') Dispatcher 수증가 FROM V$QUEUE Q, V$DISPATCHER D WHERE Q.type = 'DISPATCHER' AND Q.paddr = D.paddr GROUP BY network; 4) 공유서버의경합확인 SELECT DECODE(totalq, 0, ' 요구없음 ', wait / totalq '(1/100초)') " 요구별평균기다린시간 " FROM V$QUEUE WHERE type = 'COMMON'; 지속적으로값이증가하면 max_servers 의수를증가해야함
( 재 ) 한국데이터베이스진흥센터 22 최적의 Hardware 조건 1. Disk 의특징 (sar, iostat 명령어 ) Size 512M - 36Gig Seek 5-10ms Transfer 5-10ms Thoughput 20-40 I/O seconds per disk Controller throughput at 1750 I/Os per second 2. Speed reading from memory should be 1-10 microseconds. (sar, vmstat 명령어 ) 3. Point-to-point network latencies should be 1-25ms. (sar, netstat 명령어 ) 4. Busy systems:worst case(sar, vmstat 명령어 ) Operational systems - 60%usr 40%sys Decision support systems - 90%usr 10%sys Step-2 튜닝단계
( 재 ) 한국데이터베이스진흥센터 23 III. 오라클튜닝 Road-Map SQL 튜닝 Road-Map
SQL 튜닝 RoadMap SELECT 문 ORA-01467 에러가발생했나요? Y Road-1 (Sort 공간의구조분석 ) N Road 2-1 SELECT 문의성능이저하되고있습니까? Y ( 테이블에대한구조분석 ) Road 2-2 Road-3 (SQL 튜닝 ) ( 인덱스에대한구조분석 ) DML 문 ORA-1555/1562/1560/1628 에러가발생했나요? Y Road-4 (UNDO 공간의구조분석 ) N DML 문의성능이저하되고있습니까? Y Road-5 (Extent 의동적할당현상 ) Road-5-1 ( 사용하지않는인덱스의제거 ) Road 1 Sorting 공간의튜닝 < 원인 > CREATE INDEX, ORDER BY, GROUP BY, DISTINCT, DECODE, UNION, INTERSECT, MINUS와같은문장을통해대용량데이터를분류 (Sorting) 할때Temporary 테이블스페이스가부족할때발생하는에러입니다. < 조치조치1> 1> 사용자의사용자의SQL SQL문에서문에서필요한필요한컬럼컬럼만을분류대상으로분류대상으로설정한다.. SELECT SELECT ** FROM FROM emp emp ORDER ORDER BY BY ename; ename; SELECT SELECT empno, empno, ename ename FROM FROM emp emp ORDER ORDER BY BY ename; ename; ( 재 ) 한국데이터베이스진흥센터 24
< 조치조치2> 2> 사용자의사용자의SQL SQL문에서문에서ORDER BY BY 절에절에사용되는사용되는컬럼에컬럼에대해대해INDEX INDEX를설정한다설정한다.. SELECT SELECT ** FROM FROM emp emp ORDER ORDER BY BY ename; ename; CREATE CREATE INDEX INDEX I_emp_ename I_emp_ename ON ON emp(ename); emp(ename); SELECT SELECT empno, empno, ename ename FROM FROM emp; emp; < 조치조치3> 3> Temporary Temporary 테이블스페이스의테이블스페이스의공간을공간을추가로추가로늘려주어야늘려주어야합니다.. SQL> SQL> ALTER ALTER TABLESPACE TABLESPACE temp temp ADD ADD DATAFILE DATAFILE temp02.dbf temp02.dbf SIZE SIZE 500M; 500M; 또는또는 SQL> SQL> ALTER ALTER DATABASE DATABASE DATAFILE DATAFILE temp01.dbf temp01.dbf RESIZE RESIZE 800M; 800M; < 조치조치4> 4> SORT_AREA_SIZE SORT_AREA_SIZE의값을값을추가로추가로늘려주어야늘려주어야합니다.. SORT_AREA_SIZE SORT_AREA_SIZE = 1000000; 1000000; < 조치조치5> 5> 각사용자별로사용자별로Temporary 테이블스페이스를테이블스페이스를생성하여생성하여Contention 현상을현상을방지한다.. SQL> SQL> CREATE CREATE TABLESPACE TABLESPACE temp1 temp1 DATAFILE DATAFILE temp1_01.dbf temp1_01.dbf SIZE SIZE 500M; 500M; SQL> SQL> ALTER ALTER USER USER scott scott Temporary Temporary TABLESPACE TABLESPACE temp1; temp1; ( 재 ) 한국데이터베이스진흥센터 25
Road 2-1 테이블에대한구조분석 < 원인1> Row-Chaining 및 Row-Migration 현상이발생하면불필요한블록에대한읽기작업이발생하기때문에성능저하현상이발생합니다. ( 과다한 VARCHAR2 타입의사용문제 ) < 조치조치1> 1> ANALYZE ANALYZE 작업을작업을수행한수행한후전체공간의전체공간의30% 30% 이상에서이상에서발생하면발생하면테이블을테이블을재구성한다.. SQL> SQL> EXECUTE EXECUTE dbms_redefinition.start_redef_table( ~~~~~ ~~~~~ )) < 조치조치2> 2> EXPORT EXPORT로해당해당테이블을테이블을백업한백업한후삭제하고삭제하고다시다시IMPORT 한다한다.. (Export (Export 시 extents extents compress compress 옵션을옵션을반드시반드시yes yes로설정해야함설정해야함 )) EXPORT EXPORT scott/tiger scott/tiger COMPRESS=yes COMPRESS=yes < 조치조치3> 3> 해당해당테이블에서테이블에서Row-Chaining 및 Row-Migration Row-Migration 을유발한유발한행만행만삭제삭제후재입력한다입력한다.. SQL> SQL> ANALYZE ANALYZE TABLE TABLE emp emp LIST LIST CHAINED CHAINED ROWS ROWSinto into chained_rows; chained_rows; SQL> SQL> CREATE CREATE emp_temp emp_temp as as select select ** from from emp emp where where rowid rowid in in (select (select rowid rowid from fromchained_rows); SQL> SQL> DELETE DELETE emp emp WHERE WHERE rowid rowid in in (select (select rowid rowid from from chained_rows); chained_rows); SQL> SQL> INSERT INSERT INTO INTO emp emp select select ** from from emp_temp; emp_temp; ( 재 ) 한국데이터베이스진흥센터 26
< 조치조치4> 4> 테이블을테이블을생성할생성할떄충분한충분한PCTFREE PCTFREE와 PCTUSED PCTUSED를할당한다할당한다.. CREATE CREATE TABLE TABLE emp emp (no (no number(4), number(4), ename ename varchar2(10)) varchar2(10)) PCTFREE PCTFREE 30 30 PCTUSED PCTUSED 60; 60; < 원인2> 테이블의구조적설계에문제가발생하면성능이저하될수있습니다. < 조치조치1> 1> 해당해당테이블이테이블이SYSTEM 테이블스페이스에테이블스페이스에저장되어저장되어있는지있는지확인하십시오.. SYSTEM SYSTEM 테이테이블스페이스는블스페이스는자료사전자료사전테이블이테이블이저장되는저장되는공간이므로공간이므로집중적인집중적인Disk I-O I-O가발생하는발생하는공간입니다공간입니다.. SELECT SELECT owner, owner, segment_name, segment_name, tablespace_name tablespace_name FROM FROM dba_segments dba_segments WHERE WHEREtablespace_name = SYSTEM SYSTEM and and owner owner = SCOTT ; SCOTT ; ALTER ALTER TABLE TABLE emp emp MOVE MOVE TABLESPACE TABLESPACE sample; sample; ( 재 ) 한국데이터베이스진흥센터 27
( 재 ) 한국데이터베이스진흥센터 28 < 조치조치2> 2> 해당해당테이블이테이블이저장되는저장되는테이블스페이스를테이블스페이스를Locally Management Management 테이블스페이스로테이블스페이스로생성하십시오생성하십시오.. Data-Dictionary Data-Dictionary 타입의타입의테이블스페이스는테이블스페이스는테이블의테이블의모든모든익스텐트익스텐트정보를 SYSTEM SYSTEM 테이블스페이스에테이블스페이스에저장하기저장하기때문에때문에성능이성능이저하될저하될수있습니다있습니다.. CREATE CREATE TABLESPSCE TABLESPSCE sample sample DATAFILE DATAFILE /disk1/sample1.dbf /disk1/sample1.dbf SIZE SIZE 500m 500m EXTENT EXTENT MANAGEMENT MANAGEMENT LOCAL LOCAL UNIFORM UNIFORM SIZE SIZE 10m; 10m; < 조치조치3> 3> 대용량대용량데이터가데이터가저장되는저장되는컬럼컬럼 (VARCHAR2, (VARCHAR2, LONG, LONG, LONG LONG RAW) RAW) 이있는테이블을이있는테이블을수직수직파티션파티션또는또는수평수평파티션파티션테이블로테이블로분리하십시오.. VARCHAR2 VARCHAR2와 LONG/LONG LONG/LONG RAW RAW 컬럼은컬럼은검색할검색할때불필요한불필요한메모리와메모리와디스크디스크I-O I-O를유발시키기유발시키기때문에때문에성능저하성능저하현상을현상을유발하게유발하게됩니다.. CREATE CREATE TABLE TABLE emp emp CREATE CREATE TABLE TABLE emp_pic emp_pic (empno (empno number, number, ename ename varchar2(15)) varchar2(15)) ;; (empno (empno number, number, pic pic LONG LONG RAW); RAW); CREATE CREATE TABLE TABLE jeon(idate jeon(idatedate, date, no no char(2), char(2), name name v2(20), v2(20), qty qty number) number) Partition Partition By By Range(idate) Range(idate) (Partition (Partition t1 t1 values values less less than(to_date( 2000 )) Tablespace Tablespacechul1999, chul1999, Partition Partition t2 t2 values values less less than(to_date( 2001 )) Tablespace Tablespace chul2000, chul2000, Partition Partition t3 t3 values values less less than(maxvalue) than(maxvalue) Tablespace Tablespacechul2001); chul2001);
Road 2-2 인덱스에대한구조분석 < 원인1> 테이블에대한입력과삭제작업이빈번하게발생하는테이블은해당테이블의밸런싱이깨지는현상으로인해검색할때좋은성능이보장되지않습니다. < 조치조치1> 1> ANALYZE ANALYZE 작업을작업을수행한수행한후인덱스인덱스밸런싱이밸런싱이30% 30% 이상이상깨지면깨지면인덱스를인덱스를재구성한다.. SQL> SQL> ANALYZE ANALYZE INDEX INDEX I_emp_no I_emp_no VALIDATE VALIDATE STRUCTURE; STRUCTURE; SQL> SQL> SELECT SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 Balance Balance FROM FROM index_stats; index_stats; SQL> SQL> ALTER ALTER INDEX INDEX I_emp_no I_emp_no REBUILD; REBUILD; 또는또는 SQL> SQL> ALTER ALTER INDEX INDEX I_emp_no I_emp_no COALESCE; COALESCE; < 원인2> 인덱스의구조적설계에문제가발생하면성능이저하될수있습니다. < 조치조치1> 1> 해당해당인덱스가인덱스가SYSTEM 테이블스페이스에테이블스페이스에저장되어저장되어있는지있는지확인하십시오.. SYSTEM SYSTEM 테이테이블스페이스는블스페이스는자료사전자료사전테이블이테이블이저장되는저장되는공간이므로공간이므로집중적인집중적인Disk I-O I-O가발생하는발생하는공간입니다공간입니다.. SELECT SELECT owner, owner, segment_name, segment_name, tablespace_name tablespace_name FROM FROM dba_segments dba_segments WHERE WHEREtablespace_name = SYSTEM SYSTEM and and owner owner = SCOTT ; SCOTT ; ALTER ALTER INDEX INDEX i_emp i_emp REBUILD REBUILD TABLESPACE TABLESPACE i_sample; i_sample; ( 재 ) 한국데이터베이스진흥센터 29
Road 3 SQL 문의분석 Explain Plan 분석 (Optimizer 결정 ) Full Table Scan? Y 인덱스를사용하지 Y Road 3-1 못하는경우인가? (SQL튜닝) N N Index Scan? Y 대상컬럼의분포도가나쁜가? Y Road 3-2 (Full-Table Scan 의검토 ) N Road 3-3 (Index 타입의검토 ) Road 3-1 SQL 문의튜닝 < 원인 > 사용자의실수로 INDEX SCAN되어야할 SQL문이 FULL TABLE SCAN되는경우가빈번하게발생합니다. INDEX SCAN을하도록SQL문을조정하십시오. < 조치조치1> 1> 1) 1) 인덱스가인덱스가생성된생성된컬럼을컬럼을부정조건 (!=, (!=, <>, <>, not not 등 ) 으로 ) 으로비교하지마십시오마십시오.. 2) 2) IS IS NULL NULL 조건으로조건으로컬럼을컬럼을비교하지비교하지마십시오.. 인덱스는인덱스는NULL NULL값을값을포함하지포함하지않습니다.. 3) 3) 해당해당컬럼을컬럼을표현식,, 함수함수등으로등으로변형시키지변형시키지마십시오.. 4) 4) 해당해당컬럼의컬럼의데이터데이터타입에타입에맞는맞는조건으로조건으로검색하십시오.. 5) 5) 같은같은테이블에테이블에있는있는다른다른컬럼과컬럼과비교하지비교하지마십시오.. 6) 6) LIKE LIKE 연산자를연산자를사용할사용할때와일드와일드카드 (%) (%) 의사용에주의하십시오의주의하십시오.. ( 재 ) 한국데이터베이스진흥센터 30
Road 3-2 Full Table Scan 의성능향상 < 원인 > 분포도가나쁜컬럼에대해서는인덱스를사용하지않는것이인덱스를사용하는것보다좋은성능이기대될수있습니다. 오라클사에서는 FULL TABLE SCAN시에성능향상을위해다음과같은기능을제공합니다. < 조치조치1> 1> Init<DB Init<DB명 >.ora >.ora 파일에파일에DB_FILE_MULTIBLOCK_READ_COUNT 파라메터의파라메터의값을높게높게설정하면설정하면FULL TABLE TABLE SCAN SCAN시에시에한번에한번에지정된지정된블록블록만큼씩만큼씩읽기읽기작업을작업을할수있습니다있습니다.. DB_FILE_MULTIBLOCK_READ_COUNT = 32 32 < 조치조치2> 2> 오라클오라클사에서사에서제공하는제공하는병렬질의 (Parallel (Parallel Query) Query) 옵션을옵션을사용하면여러개의병렬프로세스를세스를통해통해읽기작업을읽기작업을수행할수행할수있기있기때문에때문에FULL TABLE TABLE SCAN SCAN시에시에성능이성능이향상됨.. SQL> SQL> CREATE CREATE TABLE TABLE emp emp (no (no number(5), number(5), ename ename varchar2(10)) varchar2(10)) PARALLEL(DEGREE 3); 3); SQL> SQL> SELECT SELECT ** FROM FROM emp; emp; < 조치조치3> 3> 이미이미인덱스가인덱스가생성되어생성되어있는있는경우에는경우에는임의로임의로인덱스를인덱스를삭제할삭제할수없으므로없으므로HINT HINT문을사용하여사용하여실행경로를실행경로를바꾸어준다.. SQL> SQL> SELECT SELECT /*+ /*+ PARALLEL(degree PARALLEL(degree 3)*/ 3)*/ ** FROM FROM emp; emp; ( 재 ) 한국데이터베이스진흥센터 31
Road 3-3 인덱스타입의검토 INDEX Type 의검토 결합 index 를사용하는가? Y 여러개의컬럼중가장분포도가좋은컬럼을기준으로생성하였는가? N Road3-3-1 ( 인덱스선행컬럼의결정 ) N Y 2 개이상의테이블이조인되는가? N Road3-3-2 ( 인덱스타입의결정 ) Y 분포도가좋은테이블을기준테이블로설정하였는가? N Road3-3-3 ( 조인순서의결정 ) Y Instance 튜닝으로! Road 3-3-1 인덱스선행컬럼의결정 < 원인 > 여러개의컬럼으로결합인덱스를생성할때가장범위가좁은컬럼을기준으로생성하는것이검색시좋은성능을기대할수있습니다.( 부분범위검색유도 ) < 조치조치1> 1> 예를예를들어,, 검색되는검색되는컬럼컬럼중에중에대상대상날자의날자의데이터는데이터는200 200건이고건이고대상대상지역의지역의검색검색데이터는 10 10건건일때날자별 / 지역별 / 지역별인덱스인덱스보다는보다는지역별 / 날자별 / 날자별인덱스가인덱스가검색검색시유리합니다유리합니다.. SQL> SQL> CREATE CREATE INDEX INDEX I_date_loc_emp I_date_loc_emp ON ON emp emp (date, (date, loc) loc) SQL> SQL> CREATE CREATE INDEX INDEX I_loc_date_emp I_loc_date_emp ON ON emp emp (loc, (loc, date) date) SQL> SQL> SELECT SELECT ** FROM FROM emp emp WHERE WHERE date date > 19990101 19990101 AND AND loc loc = 1 ; 1 ; ( 재 ) 한국데이터베이스진흥센터 32
Road 3-3-2 인덱스타입의결정 < 원인 > 검색하고자하는컬럼의데이터속성및크기에따라적절한인덱스를사용하면보다성능향상을기대할수있습니다. 일반적인테이블정보에대한검색시사용된다. 대용량데이터베이스환경에서나쁜분포도에대한컬럼검색시사용된다. 테이블의인덱스컬럼에대해삭제가빈번하게발생할때사용된다. 최신행정보를우선시조회할때사용된다. B*Tree Index BitMap Index Reverse Index Descending Index 검색조건에서계산공식을사용하는경우결과치를인덱스로생성할수있다. Primary Key 를이용하여 Text 컬럼의정보를검색할때사용된다. Function-Based Index Index Organization Index Road 3-3-3 조인순서의결정 < 원인 > 여러개의테이블에서공통컬럼을기준으로논리적결합을할때가장범위가좋은테이블을먼저검색한후범위가나쁜테이블을검색하는것이성능에도움이됩니다. < 조치조치1> 1> 예를예를들어,, A 테이블은테이블은 200 200행,, B 는 100, 100, C 는 10 10 행이행이저장되어저장되어있을있을때 A,B,C A,B,C 순으로순으로조인하는하는것보다는보다는C, C, B, B, A 순으로순으로조인하는조인하는것이것이유리합니다.. SQL> SQL> SELECT SELECT ** FROM FROM A, A, B, B, C WHERE WHERE ~~~~ ~~~~ ;; SQL> SQL> SELECT SELECT ** FROM FROM C, C, B, B, A WHERE WHERE ~~~~ ~~~~ ;; ( 재 ) 한국데이터베이스진흥센터 33
Road 4 UNDO 세그멘트의튜닝 < 원인 > UPDATE, INSERT, DELETE와같은DML문을실행할때사용자의트랜잭션을취소하기위해변경전데이터를 UNDO 세그멘트에저장합니다. 이공간은많은사용자가동시에사용하는공간이기때문에경합이발생하거나또는공간이부족할때 ORA-01562, ORA-01555 에러가발생합니다. < 조치조치1> 1> 대용량대용량데이터를데이터를변경하는변경하는사용자의사용자의애플리케이션에서애플리케이션에서자주자주Commit 문을문을실행할실행할수있도록있도록프로그램프로그램로직을로직을변경하십시오.(.( 변경변경전데이터가데이터가많이많이발생하지발생하지않도록 )) SQL> SQL> UPDATE UPDATE emp emp SET SET sal sal = sal sal ** 1.1 1.1 WHERE WHERE empno empno >= >= 1 AND AND empno empno < 1000; 1000; SQL> SQL> commit; commit; < 조치조치2> 2> Undo Undo 테이블스페이스의테이블스페이스의공간을공간을추가로추가로늘려주어야늘려주어야합니다.. SQL> SQL> ALTER ALTER TABLESPACE TABLESPACE untotbs untotbs ADD ADD DATAFILE DATAFILE undo02.dbf undo02.dbf SIZE SIZE 500M; 500M; 또는또는 SQL> SQL> ALTER ALTER DATABASE DATABASE DATAFILE DATAFILE undo01.dbf undo01.dbf RESIZE RESIZE 800M; 800M; < 조치조치3> 3> 대용량대용량데이터를데이터를처리하는처리하는사용자에게사용자에게별도로별도로큰 undo undo 세그멘트를세그멘트를할당하여할당하여OLTP 업무의업무의사용자의사용자의작업에작업에영향을영향을미치지미치지않게않게하십시오.. SQL> SQL> SET SET TRANSACTION TRANSACTION USE USE ROLLBACK ROLLBACK SEGMENT SEGMENT big_roll; big_roll; SQL> SQL> UPDATE UPDATE emp emp SET SET sal sal = sal sal ** 1.1; 1.1; SQL> SQL> ROLLBACK; ROLLBACK; ( 재 ) 한국데이터베이스진흥센터 34
Road 5 동적익스텐트의할당 < 원인 > 사용자의데이터를저장하는논리적구조들은익스텐트라는요소로구성되어있습니다. 많은데이터를입력할때동적으로익스텐트를할당하는현상이발생하면일시적으로성능이저하될수있습니다. < 조치조치1> 1> DBA DBA는자주입력되는테이블에대해미리익스텐트를할당해두는것이성능에도움이됩니다됩니다.. 전체전체테이블테이블익스텐트에서익스텐트에서프리공간이프리공간이10% 10% 이하이면이하이면미리미리할당할당하십시오.. SQL> SQL> ANALYZE ANALYZE TABLE TABLEemp COMPUTE COMPUTE STATISTICS; STATISTICS; SQL> SQL> SELECT SELECT owner, owner, table_name, table_name, blocks, blocks, empty_blocks empty_blocks FROM FROM dba_tables dba_tables WHERE WHERE empty_blocks empty_blocks //(blocks (blocks + empty_blocks) empty_blocks) > 1; 1; SQL> SQL> ALTER ALTER TABLE TABLE emp emp ALLOCATE ALLOCATE EXTENTS; EXTENTS; Road 5-1 사용하지않는인덱스의분석 < 원인 > 하나의테이블에생성되어있는인덱스수가많으면 DML문을수행할때성능이저하되는현상이발생합니다. 테이블에데이터를변경할때마다모든인덱스를변경해야하기때문입니다. < 조치조치1> 1> 오라클오라클9i 9i 버전부터는버전부터는생성되어생성되어있는있는인덱스인덱스중에중에사용되고사용되고있지있지않는않는INDEX INDEX의존재유무를존재유무를모니터링모니터링할수있습니다있습니다.. 불필요한불필요한인덱스를인덱스를제거하면제거하면DML DML문의문의성능을성능을향상향상시킬시킬수있습니다습니다.. SQL> SQL> ALTER ALTER INDEX INDEX I_emp_ename I_emp_ename MONITORING MONITORING USAGE; USAGE; ( 재 ) 한국데이터베이스진흥센터 35
( 재 ) 한국데이터베이스진흥센터 36 Server 튜닝 Road-Map 서버튜닝 RoadMap 공유 - 풀영역 CPU의과부하현상 메모리튜닝 버퍼 - 캐시영역 데이터파일 I/O 튜닝 아카이브파일 자원경합튜닝 LOCK 경합
( 재 ) 한국데이터베이스진흥센터 37 디스크메모리튜닝버퍼 - 캐시영역 I/O 증가현상 데이터파일 I/O 튜닝 리두 - 로그파일 아카이브파일 UNDO 세그멘트 자원경합튜닝 TEMP 세그멘트 LOCK 경합 메모리의효율저하현상 메모리튜닝 공유 - 풀영역 버퍼 - 캐시영역 로그 - 버퍼영역 자바 - 풀영역 래치와경합 I/O 튜닝 리두로그파일 자원경합튜닝 LOCK 경합
( 재 ) 한국데이터베이스진흥센터 38 Network 튜닝 Road-Map 네트워크튜닝 RoadMap Network의성능저하현상 Dedicated Server Process Server Process 의활성화지연 Process 의경합 Shared Server Process 세션수의제한 시스템의과부하
( 재 ) 한국데이터베이스진흥센터 39 Q & A - 감사합니다 -