데이터베이스 질문과 답변
by 김태원 [Oracle Tuning] 조회속도 조회속도개선 운영시스템조회속도 [2015.02.09 10:46:42]
지금 겪고 있는 현상과 비슷한 경우를 과거에 경험해보았는데,
그때와 지금의 차이점이라 하면... 이곳 여건상 WAS를 비롯한 운영 시스템의 전반적인 부분에 대해서
확인이 불가하다는 것에 있습니다.(이게 가장 큰 문제일것 같네요;;;)
우선 겪고 있는 증상은 다음과 같습니다.
화면 개발을 위해서 조회쿼리를 하나 짰습니다.
기본적으로 각 테이블간 릴레이션이 원만하지 않아 부득이 크게 연관없는 테이블들을 join 하게 되었습니다.
아무튼 쿼리는 잘 작동하고 인덱스도 적절히 사용하여 개발하게 되었습니다.
테스트로 우선 개발계 db에 붙어서(db툴로 pl/sql developer사용) 조회해본 결과 만족할만한 속도가 나옵니다.
실행계획 역시 원하던대로 나오구요.
개발계 시스템에서 직접 조회를 해본 결과 db에 직접 붙어서 조회했을 때와 마찬가지로 만족할만한 속도가 나옵니다.
화면도 개발되었고, 이제 운영계에 붙어 확인해볼 차례이지요.
운영계 db에 붙어서 조회해본 결과 역시 만족할만한 속도가 나오고,
실행계획 역시 개발계와 똑같습니다.
이제 운영계 시스템에 직접 붙어서 조회를 해봅니다.
조회가 잘 됩니다. 속도도 개발계와 다름없이 나오고요.
그런데... 운영계에서는 잦은 빈도로 타임아웃이 걸립니다. 위에 잘 나옵니다. 했던 것은 처음에 한번 그랬던것이구요...
통계를 내보지 않았지만, 조회가 정상적인 경우, 타임아웃이 걸리는 경우, 조회속도가 느린 경우가
불특정하게 뒤범벅이 되더군요. 1:1:1 정도라고 생각되기도 하구요.
실제 시스템에서 돌아갈 때 실행계획이 의도치 않게 되는 경우가 있어서(과거 경험)
인덱스 힌트줘서 원하는 실행계획이 나오도록 했습니다.
힌트 문제는 최초에 아무 생각없이 올렸을 때 처음부터 타임아웃이 걸려서 원인을 모르다가 힌트를 주어서
찾은 것이구요.(이 덕분에 조회할때마다 타임아웃 걸리던 것이 1/3 정도는 정상적으로 조회가 되게 된것 같습니다.)
예전에 이와 비슷한 문제가 생겼을 때는... WAS 나 오라클 커넥션 및 DAO쪽 클래스쪽에서 문제가 있었던 것으로 기억합니다. 실제 운영계와 개발계의 환경이 다르게 세팅된 경우가 가장 많았죠.
헌데 지금... 여기서 그걸 확인하는 것은 일단 불가한 상황이구요...(이게 제일 안타깝네요;;)
혹여나 하는 심정으로 이곳에도 글을 올려봅니다.
제가 나름 이곳저곳 어드바이스를 구해서 쿼리튜닝 및 인덱스 생성 등은 문제없이 했다고는 생각되지만,
그래도 지금 확인할 수 있는, 그리고 개선의 여지가 있는 것이 쿼리 뿐이라서... 이곳에 전반적인 증상을 써서 어드바이스를 구해봅니다. ㅠ.ㅜ;
보통은 최초조회시 속도가 느린 경우가 많더군요. 두번째부터 조회가 잘 되는 경우는 최초 조회시 기본적으로 쿼리가 느리고
그 다음부터는 조회된 데이터를 메모리에 올려둔 상태라 조회가 빨리 되는거였죠.
헌데 지금은 화면을 열고 최초 조회시 잘 되는 경우도 있고, 타임아웃 걸리는 경우도 있고;; 오랜 시간 후에 결과가 나오는 경우도 있습니다.
또 어떤때는 처음에 잘되다가 갑자기 타임아웃 걸리는 경우 등등도 있구요...
지금도 원인을 찾기 위해서 위에 테스트 방법은 계속 같은 조건에서 실행해보는 중입니다.
기본적으로 쿼리가 잘못되지 않았을까 라는 의구심이 들어 개발계에서 테스트 할 때도 운영계와 같은 데이터 하에서 진행하고요.(매달 운영계db를 개발계로 복원해서 사용중입니다.)
속도가 오래 걸리는 경우는 고작 데이터 300건 나오는데도 몇분씩 걸리고, 300건을 조회하지 못해서 타임아웃 되는 경우도 있습니다.
이와 비슷한 증상을 경험해보신 분들이 계시면... 도움좀... 주시면 감사하겠습니다. ㅠ.ㅜ;
추가로 더 필요한 정보가 있으시다면 리플로 더 알려드리겠습니다.
이곳 환경은... JBOSS에 오라클 11g 가우스라는 그리드 툴 사용하는 정도입니다.
감사합니다. __)
AWR REPORT 를 뽑아서, Top 쿼리 확인 후에
아래 SQL 로 성능에 대한 흐름을 살펴 보세요
SELECT SN.SNAP_ID, SN.INSTANCE_NUMBER, MODULE, ACTION, SQL_PROFILE, PARSING_SCHEMA_NAME, SN.BEGIN_INTERVAL_TIME, SQ.SQL_ID, SQ.PLAN_HASH_VALUE
, NVL(EXECUTIONS_DELTA,0) EXEC
, ROUND((ELAPSED_TIME_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))/1000000) "AVG_ELPASED_TIME(S)"
, ROUND((CPU_TIME_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))/1000000) "AVG_CPU_TIME(S)"
, ROUND(( BUFFER_GETS_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))) "AVG_LOGICAL_IO"
, ROUND(( BUFFER_GETS_DELTA)) "Total_LOGICAL_IO"
, ROUND(( DISK_READS_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))) "AVG_PHYSICAL_IO"
, ROUND(( ROWS_PROCESSED_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))) "AVG_ROWS_PROCESSED"
, ROUND(( APWAIT_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))/1000) "AVG_APWAIT(ms)"
, ROUND(( IOWAIT_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))/1000) "AVG_IOWAIT(ms)"
, ROUND(( CLWAIT_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))/1000) "AVG_CLWAIT(ms)"
, ROUND(( CCWAIT_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))/1000) "AVG_CCWAIT(ms)"
FROM DBA_HIST_SQLSTAT SQ, DBA_HIST_SNAPSHOT SN
WHERE SQ.SQL_ID = '6t39v6xayzvm3' -- 원하는 SQL_ID 입력
AND SQ.SNAP_ID = SN.SNAP_ID
AND SQ.INSTANCE_NUMBER = SN.INSTANCE_NUMBER
AND SN.BEGIN_INTERVAL_TIME BETWEEN SYSDATE - 31
AND SYSDATE - 1/24
ORDER BY SQ.INSTANCE_NUMBER, SQ.SNAP_ID,SN.BEGIN_INTERVAL_TIME ;
구루비 사이트의 AWR 관련된 사항 읽으면서 REPORT를 뽑으려고 하는데...
DBA_HIST_WR_CONTROL 등 이와 같은 테이블이나 뷰가 없다고 나오네요...
이곳 운영계 접속 계정에 권한이 충분치 않은듯 합니다. ㅠ.ㅜ
글을 읽어보니 테스트 DB(개발용)에서는 전부 정상적이며, 실제 Real OP DB로 해당 SQL 을 Upload 후 실행시 느리거나, Time Out 이 걸린다는 내용이신거 같습니다.
개발용 DB에서의 테스트 시는 SQL을 발로 짠다고 해도 잘나오는 경우가 대부분이며(Data 자체가 적기 때문), 또한 접속된 Session 또한 거의 개발자외엔 전무하기때문에 잘 나올 수 밖에 없는 경우입니다.
Setup 이나 기타 설정에 의한 Critical 한 속도 차이는 그다지 크지 않을 거 같구요.
사실 확실한 방법은 동일한 조건(Real DB 쪽 관련 Data를 테스트 DB로 이관 및 다중 세션에서의 처리)으로 테스트하시느게 중요하실 것 같습니다. 제 경험상 거의 SELECT Query 일 경우 잘못된 SQL 작성으로 저런 경우가 많은것 같습니다.(단, 제 경험은 굉장히 일천합니다..^_^;;)
또한 , SELECT 범위에 들어가는 TABLE 들에 대해서 빈번한 DML 처리가 되는지도 확인하셔야 할 것 같아보입니다. 권한이 필요하겠지요.
만능구글에서 검색하시면 여러 증상 및 해결방안 들이 나올 겁니다. 정확한 DB VERSION으로 검색해보세요.
) 버튼을 클릭하여 작성 하시면 됩니다.