미래를 꿈꾸고 성공을 희망하며

온라인 서비스는 멀티프로세스로 인해, 개별 거래의 속도가 느릴지라도 사용자가 느낄 만큼 속도가 느린 경우는 없다. 물론 조회시 where 조건들이 index에 등록되어 있지 않다면, 처음에는 속도가 느린 것을 느끼지 못할지라도 ... 자료가 쌓이면 속도가 느린 것을 느낄 것이다.

그럼 실행 쿼리의 실행시간 기준으로 TOP 10을 조회하여, 해당 where 조건에 항목들에 대해 인덱스를 생성해주면 된다.

인덱스 생성시에 모든 조건을 인덱스로 만들어서는 안된다. 데이터 1건인데, 인덱스가 10개면 실제 11개의 처리가 발생하므로, 속도저하가 발생하고 인덱스 실행 계획이 원하지 예상하지 못하는 방식으로 나와 문제가 될 수 있다.

인덱스는 무조건 5개 이하이다.

왠만하여 공통 인덱스는 모든 업무를 커버할 수 있도록 선정하고, 조회는 인덱스만 잘 설정되어 있다면 문제 없다. 

만약, 배치업무를 수행하는데 느리다면, 다음 사항을 체크해 보자.

1. 조회시 인덱스를 이용하는지?
2. Array 작업을 수행하는지? 
3. 중간중간 commit 을 수행할 수 있는지? 
4. Counter 체크 
5. 통계 정보 생성 
6. 불필요한 과거 정보 삭제
7. 업무 프로세스 변경

1. 조회시 인덱스를 이용하는지? 또는 TABLE FULL SCAN 을 하는지? 만들었다고 생각하지 말고, 체크해보자. 그리고 인덱스는 순차적으로 차례대로 찾기 때문에 항목이 5개인 인덱스는 중간에 3번 항목이 없다면, 결국 1번과 2번 항목만 인덱스를 읽고 나머지는 range_scan을 하기 때문에 데이터량에 따라 항목 조절을 해야 한다.

2. Array 작업을 수행하는지? 대량의 작업은 기본적으로 다량 Fetch, 일괄 Insert(또는 Update)를 수행해야 속도가 빠르다. execute() * 10000 보다는 batch_Execute() * 5 가 몇십배 빠르다.

3. 중간중간 commit 을 수행할 수 있는지? 업무에 따라, 중간에 commit을 할수 있다면, 몇천건 또는 몇만건 단위로 commit을 수행한다면 재작업시 작업량을 줄일 수 있다. 검토 후 중간에 commit을 삽입하자.

4. Counter 체크 : 오라클의 sequence를 사용하지 않고, select max(seq) from tableA; 사용한다면, 당장 변경하라.
* 혹시 개발자 중에서 SELECT TASEQ.nextval from BigTable where rownum =1; 로 개발했다면, 당장 변경하라.
SELECT TASEQ.nextval from dual; 로 실제 수행속도는 테이블의 사이즈에따라 몇백배까지 차이 난다.

5. 통계 정보 생성 : 통계정보가 최신화 되지 않아 plan이 느릴 수 있으므로, Analyze 해보자.

6. 불필요한 과거 정보 삭제 : 데이터의 사이즈에 따라, 성능이 저하될 수 도 있으므로 과거 정보 삭제나 parition 나누자.

7. 업무 프로세스 변경 : DB에서 index 타고, 기본적인 속도개선까지 했다면 프로세스 변경을 고려해 보자. 여기까지 한 후, 하드웨어 증설을 고려하자.

# 추가 설명. sql 구성요소들은 아래와 같은 순서로 실행 된다

1) FROM, WHERE 절을 처리
2) ROWNUM 조건 적용
3) SELECT COLUMN LIST 절을 적용
4) GROUP BY 절을 적용
5) HAVING 절을 적용
6) ORDER BY 절을 적용

그러므로, 4번의 예시처럼 의미없이 대량의 테이블에서 rownum <=1 을 하면, 대량의 자료를 fetch 한후 nextval을 가져오는 불필요한 작업이 발생하므로, dual을 적절히 사용하자.

추가 tip. SMS 제품에서는 Oracle에서 제공하 v$sql, DBA_HIST_SQLSTAT, DBA_HIST_SQLTEXT, DBA_HIST_SNAPSHOT 등의 테이블을 이용하여, TOP N Query(상위 N개의 쿼리)를 제공한다. 이것을 바탕으로 지연 서비스의 쿼리 실행계획 및 실행 단위의 소요시간까지 제공해준다. SMS제품이 제공해 주는 것외에 본인이 DBA 또는 고급 개발자로써, 서비스 품질을 위한다면, 아래의 내용을 참고하여 개선 대상을 찾아 고민해보자.

다만, 아래의 것은 여러분이 문제를 찾기 위한 Query일 뿐, 해결방법은 각양각색이므로 획일적으로 답할 순 없다.

반복하는 쿼리 중에서 총 수행시간이 긴 쿼리 찾기.
(짧은 업무일지라도, 단일 프로세스로 처리되면 문제가 된다. 예를 들어, 30msec * 100만번이면, 300,00초= 6,000분 = 100시간이 된다.)

SELECT * FROM (
SELECT S.SQL_ID, ROUND(SUM(CPU_TIME_DELTA)/100000) CPUTIME, SUM(EXECUTIONS_DELTA) TOTAL_EXECUTED,
DBMS_LOB.SUBSTR(SQL_TEXT,2000,1) SQLTEXT
FROM DBA_HIST_SQLSTAT H, DBA_HIST_SQLTEXT S, DBA_HIST_SNAPSHOT T
WHERE S.SQL_ID = H.SQL_ID
AND H.SNAP_ID = T.SNAP_ID
AND T.BEGIN_INTERVAL_TIME BETWEEN TO_DATE('20190513 09:00:00','YYYYMMDD HH24:MI:SS') AND TO_DATE('20190514 15:00:00','YYYYMMDD HH24:MI:SS')
GROUP BY S.SQL_ID, DBMS_LOB.SUBSTR(SQL_TEXT,2000,1) ORDER BY 3 DESC
)
WHERE rownum < 21 and CPUTIME > 1000 ;

ORACLE서버에서 수행시간이 긴 쿼리 찾기 쿼리
SELECT ROWNUM NO,
PARSING_SCHEMA_NAME,
to_char(ELAPSED_TIME/(1000000 * decode(executions,null,1,0,1,executions)),999999.9999 ) 평균실행시간,
executions 실행횟수,
SQL_TEXT 쿼리 ,
SQL_FULLTEXT
FROM V$SQL
WHERE LAST_ACTIVE_TIME > SYSDATE-(1/24*2)
-- AND LAST_ACTIVE_TIME BETWEEN to_Date('20111226163000','YYYYMMDDHH24MISS') AND to_Date('20111226170000','YYYYMMDDHH24MISS')
-- AND ELAPSED_TIME >= 1 * 1000000 * decode(executions,null,1,0,1,executions)
and PARSING_SCHEMA_NAME = 'ZIPCODE'
ORDER BY 평균실행시간 DESC, 실행횟수 DESC;


SELECT TO_CHAR (SID) sid, serial# serialNumber,
SUBSTR (TO_CHAR (last_call_et), 1, 6) executeSeconds, userName, machine,
b.sql_text sqlText
FROM v$session a, v$sqltext b
WHERE username NOT IN ('SYSTEM', 'SYS')
AND a.TYPE != 'BACKGROUND'
AND a.status = 'ACTIVE'
AND a.sql_address = b.address(+)
AND a.sql_hash_value = b.hash_value(+)
ORDER BY a.last_call_et DESC, a.SID, a.serial#, b.address, b.hash_value, b.piece

현재 실행되고 있는 쿼리 와 실행 시간

SELECT TO_CHAR (SID) sid, serial# serialNumber,
SUBSTR (TO_CHAR (last_call_et), 1, 6) executeSeconds, userName, machine,
b.sql_text sqlText
FROM v$session a, v$sqltext b
WHERE username NOT IN ('SYSTEM', 'SYS')
AND a.TYPE != 'BACKGROUND'
AND a.status = 'ACTIVE'
AND a.sql_address = b.address(+)
AND a.sql_hash_value = b.hash_value(+)
ORDER BY a.last_call_et DESC,
a.SID,
a.serial#,
b.address,
b.hash_value,
b.piece