제목 : 웹 어플리케이션의 쿼리 추적 및 튜닝 포인트 추출
글쓴이: 최기선(guest) 2006/05/11 13:06:11 조회수:8367 줄수:111 |
|
자바로 작성된 웹 어플리케이션 프로그램의 쿼리를 자동으로 추적하고,
해당 쿼리에 대한 통계정보등을 추출하여 튜닝 대상 쿼리로 산정하는 프로그램을 작성하려고 합니다만
경험과 지식이 부족하여 여러분들의 자문을 구합니다.
아래의 설명을 잘 읽으시고, 일부라도 아시는 분은 조언을 해주시면 정말정말 고맙겠습니다.
1. 목적
Web Application Query Tracking을 통한 튜닝 포인트 추출로 성능 테스트 시간 단축 및 비용절감
2. 내용
DBA를 투입하기 어려운 Web기반 소규모 프로젝트에서 Web Application 성능저하의 최대 원인이 되는 쿼리에
대한 통계정보를 집계하여 튜닝 포인트를 추출한다.
3. 기능
1)쿼리 추적(Tracing) 기능
2)쿼리 통계 추출
- Query Plan
- 수행시간
- recursive call
- db block get
- consistent get
- physical reads
- redo size
- bytes set via SQL*Net to client
- bytes received via SQL*Net from client
- SQL*Net roundtrips to/from client
- sorts(memory)
- sorts(disk)
- rows
※ 해당 쿼리의 통계를 이용하여, 튜닝이 필요한 쿼리인지 아닌지를 판단해야 한다.
3)Database Connection 모니터링
- Web Application Server(이하 WAS)의 다운현상의 원인이 될 수 있는 DataBase Connection을
모니터링 하여 Statement나 ResultSet, Connection을 클로즈 하지 않은 부분을 찾아내어,
WAS의 다운현상의 Risk를 줄인다.
4. 대상 시스템
- DB: ORACLE
- 대상언어: java
- 구현방법: WEB Component 형태로 개발하여 배포 (WAR 파일로 묶어서 WAS에 포팅할 수 있는 형태)
- 구현부분
쿼리 추적
Plan 추출모듈
쿼리 수행 통계 추출 모듈
DB환경(Parameter, Cursor) 측정모듈
보고서 작성모듈
★ 5. 질문사항
1) 쿼리 추적과 쿼리 통계를 뽑아낼 수 있는 딕셔너리 정보는?
- 제가 알아본 바로는 아래와 같이 하면 될 것 같은데.. 통계정보는 찾아내기가 힘든 것 같습니다.
SELECT hash_value,
(
SELECT SQL_TEXT FROM (
SELECT * FROM (
select
A.*,
DENSE_RANK() OVER ( PARTITION BY HASH_VALUE ORDER BY LAST_LOAD_TIME DESC) AS RANK
from v$sql A
)
WHERE RANK = 1
) VSQL
where VSQL.hash_value = p.hash_value
and VSQL.address = p.address
AND ROWNUM =1
) AS SQL_TEXT,
child_number,
ID ,
PARENT_ID ,
LPAD('', 2*(depth))||OPERATION||DECODE(OTHER_TAG, NULL, '', '*')|| DECODE(OPTIONS, NULL, '',
' (' ||OPTIONS||')')||DECODE(OBJECT_NAME, NULL, '', 'OF'''||OBJECT_NAME||'''')|| DECODE(OBJECT#,
NULL, '', '(Obj#'||TO_CHAR(OBJECT#)||')')||DECODE(ID, 0, DECODE(OPTIMIZER, NULL, '',
'Optimizer='||OPTIMIZER))||DECODE(COST, NULL, '', ' (Cost='||COST||DECODE(CARDINALITY, NULL,
'', 'Card='||CARDINALITY)||DECODE(BYTES, NULL, '', ' Bytes='||BYTES)||')') SQLPLAN,
OBJECT_NODE,
PARTITION_START ,
PARTITION_STOP,
PARTITION_ID,
CPU_COST,
IO_COST,
TEMP_SPACE,
DISTRIBUTION,
OTHER ,
OBJECT_OWNER
FROM v$sql_plan p
where (p.OBJECT_OWNER = 'SCOTT' OR p.OBJECT_OWNER IS NULL)
START WITH ID=0
--and hash_value = '1977426250'
CONNECT BY PRIOR ID=PARENT_ID
AND PRIOR hash_value=hash_value
AND PRIOR child_number=child_number
ORDER BY hash_value, child_number, ID, POSITION;
2) 데이터 베이스 커넥션을 모니터링 할 수 있는 방법은?
3) 데이터 베이스의 쿼리 수행정보 - V$SQL, V$SQL_PLAN 등 - 테이블이 일정의 시간이 지나면 초기화가 되는
것 같은데.. 그 주기는?
4) 쿼리에 대한 플랜과 통계정보를 이용하여, 튜닝 대상쿼리로 선정할 수 있는 판단 기준은? (판단은
시스템에서 자동으로 해야 합니다. 사람의 개입없이.. )
5) 혹자는 "그런걸 왜 하냐?" 라고 하던데.. 답변자님의 생각은 어떠하신지요?
-- 질문이 어렵다는 생각이 들지만, 고수님들의 답변을 기다리겠습니다..
|
제목 : Re: 똑같은 고민을 3-4년전부터 했었습니다.
글쓴이: 이원영(javaservice) 2006/05/11 14:51:55 조회수:3502 줄수:97 |
|
똑같은 고민을 3-4년전부터 했었습니다. 언급하신 SQL쿼리/DB에 대한 고민을 더 확장하여
성능(Performance)라는 확장된 영역까지를 아울러 고민한 결과로 나온 것이 제니퍼(Jennifer)라는
성능관리솔루션입니다.
1) 쿼리추적기능: 개념적으로는 그러한데, 어떻게 어떤 관점으로 보는 것이 더욱 효율적인
방법인가가 중요합니다. 특히 PreparedStatement로 실행시 BIND변수가 무엇이냐에 따라
심지어 옵티마이제이션이 다이나믹하게 달라진다거나, 조회건수에 따른 차이가 있기에
쿼리에 대한 통계적 관점 뿐만 아니라, 각 개별 SQL쿼리 및 BIND변수 추적은 반드시
필요합니다.
2) 쿼리통계추출
WAS를 통해 DB와 연동될 경우, WAS에서 DB로의 호출에 따른 현재 수행중인 SQL/BIND변수
어떤 어플리케이션 어떤 SQL을 호출하고 있는지/호출하였는지, 해당 SQL은 어떤 애플리케이션
에 의해 사용되고 있는지/사용되었는지, 그것의 개별 응답시간, 호출건수, 호출빈도,
통계적 기법에 의한 가장 영향도 높은 SQL은 무엇이고 어떠한 우선순위로 튜닝 접근을
해야 하는 지를 알 수 있습니다.
반면, 언급해 두신 해당 SQL수행결과에 의한 데이타베이스쪽의 CPU time, physical reads
등등의 항목은 기본적으로 WAS tier에서는 알수 없습니다. 제니퍼의 경우는 데이타베이스
모니터링 툴(맥스게이지, DBMATE)와 연동하여 각 사용자 호출에 따른 각 개별 모든 SQL
쿼리 및 그 수행에 따른 DB모니터링툴로부터 그러한 DB수행 일량(Work) 데이타를 가져와 보여줍니다.
각 개별 SQL쿼리보다는 단위를 트렌젝션 단위로 하는 것이 매우 중요한데, 예를 들어,
한번의 클릭에 의한 하나의 트렌젝션이 시작되었을 때, 짧은 단타성의 SQL쿼리가 반복적으로
호출될 경우, 비록 개별 SQL수행에 따른 DB측 영향은 대단히 미미하나, 지나칠 정도로
recurrsivce 호출이 일어나면, 결국 그 트렌젝션 전체는 응답이 저하되고 튜닝의 대상이
됩니다. 이 경우, 단지 DB쪽 모니터링 툴에서는 이같은 반복호출을 감지하지 못합니다.
그러나, 제니퍼에서는 트렌젝션단위로 호출된 모든 SQL에 대한 총집계시킨 DB일량 데이타를
보여줌으로써 위와 같은 경우도 감지해 낼 수 있는 것입니다.
제니퍼가 DB모니터링제품간에 정보를 주고받으면서 제공하는 항목으로는 다음과 같은 것이
있습니다.
SID,module,action,cpu,PGA memory,logical reads,physical reads,block changes,redo entries,executions,
wait info,sql text
3) JDBC 리소스 미반환 경우.
제니퍼의 기본적인 기능이고, 단순하지만 툴이 없으면 찾기가 어려우며, 그 결과는 시스템
장애로 이어질 만큼 critical한 결과를 야기한다는 것이 참 허탈하고 아이러니합니다.
중요한 것은, 몇개가 그러한 미반환이 생겼느냐가 아니라, 어디서 반환을 제대로 하지 않았느냐를
보여주는 것이 중요한데, 오묘(?)한 것 중 하나는, 어디서 생성한 것인지는 명확하지만,
어디서 반환해야 하는데, 하지 않았느냐는 논리적으로 잘 알 수 없다는 데 있습니다.
그러나, 어디서 생성했느냐에 해당하는 STACKTRACE를 보여주되, Java StackTrace는 성능부하가
심하기에 기술적인 고민을 해결해 내어야 합니다. 그러한 고민의 결과로 제니퍼엔 성능저하
없이 STACK을 추출하는 LWST(Light Weight Stack Trace)라는 기법을 이용하고 있습니다.
5.1) 쿼리추적과 딕셔너리정보는 DB쪽에서 뽑혀져 나오는 수치인데, 물론 그 만큼의 의미는 있으며
전통적으로 오랜 역사(?)를 갖고 있는 DB모니터링 시장이 성숙해 온 그 방향이기도 합니다.
SQL쿼리수행으로 통해 성능정보를 가져오는 DB모니터링 제품은 상용/비상용 제품등 실로
다양하며, 엑셈의 멕스게이지 경우 Oracle Shared Memory를 직접 access하여 가져옴으로써
성능저하를 극복하기도 해 왔습니다.
그러나, 이러한 DB쪽만의 모니터링은 실제 운영되는 시스템의 종합적인 SQL쿼리/모니터링이라는
원론적인 관점에서 그 뭔가가 부족한데, 그것은 첫째, 그 SQL을 호출하는/호출한 애플리케이션이
무엇인지를 알수 없다는 것이 그들의 아킬레스건입니다. 두번째로는 앞서 언급한 짧은 단타성
SQL쿼리가 반복적으로 많을 경우 그 SQL쿼리는 TOP 10 리스트에서 배제된다는 것입니다.
세번째로는 DB쪽에서는 절대로 쿼리실행시 사용된 BIND변수를 알수 없다는 것입니다.
따라서, WAS쪽에서 어떤 SQL을 호출하는지, 각종 통계 및 SQL BiND변수 등의 정보를 함께
보아야할 필요성은 너무나 큽니다.
5.2) 데이타베이스 커넥션 모니터링하는 방법은?
제니퍼는 JDBC Connection 객체를 추적하여 몇개의 Connection이 현재 어떤 애플리케이션에게
할당되었는지, 현재 JDBC쿼리를 수행중인지, 그 개수는 몇개인지, 수행후 rs.next()를 통해
FETCH하고 있는지, FETCH된 건수는 몇개인지등을 Wrapping 기법 및 LWST Tracing기법을
하이브리드로 병행하여 보여줍니다. 아마, 보고자 했던 것을 모두 보실 수 있을 것입니다.
5.3) V$SQL, V$SQL_PLAN ...음... 이 부분은 DBA 전문가에게 여쭤보아야 겠네요. 저는 모르는
부분이라 죄송합니다.
5.4) 플렌과 통계정보는 튜닝대상을 선정하기 위한 하나의 관점을 제시하는 것은 분명합니다.
그러나, 그 판단의 또하나의 변수는 횟수(빈도)입니다. 어떤 SQL쿼리가 어떤 연유에서건
10초가 걸렸다고 했을 때, 그것인 하루에 단 한번밖에 불려지지 않는다면 굳이 튜닝대상이
될 필요가 없습니다. 반면 어떤 SQL쿼리가 비록 0.5초 밖에 걸리지 않더라도, 모든 서비스 중
그 쿼리가 전체의 95%라면 이야기가 달라집니다. 그렇다면 그 관계를 어떻게 수식으로 표현
할 수 있을까요? 이 부분에 대한 고민은 사실 DB튜닝분야에는 지난 10-15년간 여러학자들이
논문으로 발표되었고, 수많은 컨설턴트에 의해 발전해 온 것이 사실입니다.
그러나, DB관점에서가 아니라, DB를 사용하는 CLIENT, WAS입장에서의 고민은 그다지 되어
있지 않았고, 이 부분에서 나름대로 고민했던 부분이 "웹기반시스템 하에서의 성능이론"이란
자료를 만들었습니다. 핵심적인 단어는 "임계성능부등식"이라 할 수 있습니다.
제니퍼의 경우는 응답시간분포그래프(X-View)를 통해 모든 개별 트렌젝션의 응답시간,
그리고, SQL수행시간의 분포, CPU 시간, FETCH 시간의 분포그래프로 변화시켜가며 보여주기에
직관적으로 어떤 애플리케이션, 어떤 SQL쿼리에 의해, 얼마나 영향을 받고 있는지를 확인할
수 있습니다.
5.5) 왜 그런걸 하냐구요? 그건 적절한 질문이라 할 수 없다고 보여집니다. 10개월짜리 프로젝트
잘 해 놓고, 서비스오픈첫날부터 석달간 제대로 서비스한번 못한 경험이 숱하게 있으실 텐데...
사실 대세적인 입장은 "반드시 해야 되는 것은 맞는데, 현실적으로 방법이 없었지 않느냐"가
그동안의 입장이었습니다. 그러나, 이젠 제대로 된 방법으로, 효과적인 방법으로 그것을 할 수
있는 시대가 왔고, 그 선두에 저희들이 있다고 자부합니다.
제니퍼는 단순히 시장에서 요구하는 기능을 갖다 붙이는 것이 아니라, "진정 이렇게 봐야
하는 것 아냐?"라는 끊임없는 원론적인 해답을 제시하려고 노력해 왔으며 앞으로도 그러할
것입니다.
PS: 제니퍼란?
http://www.javaservice.net/~java/bbs/read.cgi?m=resource&b=jscnews&c=r_p&n=1127241452
자바서비스넷 이원영
Phone: 010-6239-6498
E-mail: NOSPAM_lwy@javaservice.com
MSN: NOSPAM_javaservice@hanmail.net
|
제목 : Re: 관리툴 있습니다
글쓴이: 손님(guest) 2006/05/13 22:00:30 조회수:2827 줄수:14 |
|
보아하니 오라클에 국한해서 말씀하신다면
9i 부터는 기본적으로 엔트라이즈 관리자내에 분석 통계툴이 있습니다
물리적/논리적 자원, 쿼리 튜닝등등 ORACLE 에서 추출가능한
정보를 모두 잘 표현해 주고 있으니 괜히 고생하지 마시고 있는걸 사용하세요.
참고로 SQL 튜닝시 오라클 옵티마이저가 종종 삽질 하는 경우가 있으므로
수작업이 필수입니다. ㅎㅎ
인덱스만 잘이용하면 만사형통인것을 ....
개인적인 생각으로 튜닝시 DB CLIENT (WAS를 포함하여 )와
DB와 분리해서 생각하시는게 좋을듯합니다.
|
|
