데이터베이스 질문과 답변
by 느훼훼 [SQL Query] [2019.10.17 15:52:04]
각설하고 원하던 증상이 오늘 발생하여 다시 한번 글을 올립니다.
먼저 며칠전 올렸던 글 내용의 링크는 아래와 같습니다.
http://www.gurubee.net/article/82085
다시한번 간략하게 설명하면,
특정 화면에서 조회 버튼을 누르면 아래와 같은 로직으로 실행이 됩니다.
1. delete from sys_myoption where user_id = '유저id'
2. sys_myoption 테이블에 조회한 유저가 등록한 담당 펀드 리스트를 insert
ex) insert into sys_myoption values (imc, fund) select imc, fund from sys_userfund where 블라블라
3. 메인 조회 쿼리 시행( 이부분은 첨부 파일에 있음)
조회 속도가 느려지는 이유는 exists 절에 sys_myoption 테이블을 조회해 오다가 느려지는건데요,
(이 부분은 테스트를 했기에 exists절에 원인이 있는게 확실함.)
(캡쳐파일의) ibk23349라는 유저가 조회 했을 땐 7초 이상이 걸립니다.(며칠전에 10초가 넘었음)
(캡쳐파일의) caprtk라는 유저가 조회 했을 땐 1초 미만이 걸립니다.
두 유저의 차이점은 select * from sys_myoption where user_id = '유저아이디';
를 했을 때 ibk23349유저는 pk로 정렬이 돼서 조회가 됩니다. 그 반면에 caprtk 유저는 pk대로
정렬되지 않고 중구남방으로 비정렬 되어 조회가 됩니다. 차이점은 이거 딱 하나 입니다.
두 유저 다 sys_myoption 테이블에 등록되어있는 펀드 리스트는 13882건 이며 내용도 동일합니다.
특정 화면에서 조회를 했을 때 sys_myoption 테이블에 delete하고 insert하는 과정에서 다른 케이스
로 insert된게 확실하다고 생각되어집니다.
느린 조회 속도를 개선할 수 있는 방법은 있습니다. 이미 그렇게 적용도 해놨구요.
허나 왜 이런 현상이 발생하는지 그 원인이 너무나도 궁금하기에 질문글을 올립니다.
메인 select 쿼리에서 사용되고 있는 테이블의 조건은 유저별로 모두 동일합니다.
건수, 데이터 내용이 다 동일합니다.
1. 실제 프로그램 화면에서 두 유저의 조회 속도 차이가 나나요?
2. 실행계획은 바인드변수 사용해서 보신 건가요?
1. 넵, 툴에서 실행할 때도 그렇고 실제 화면에서 조회 할 때도 그렇고 속도차이가 많이 납니다.
2. 툴에서 바인드 변수가 아닌 실제 값을 넣고 했지만 바인드 변수로 설정하고 툴에서 실행한다해도 그건 무의미 하지 않을까요? 실제 프로그램 내 쿼리에서는 바인드 변수로 처리했고 실제 화면에서도 속도차이가 많이 나니까요. 지금 다시 바인드 변수로 넣고 툴에서 plan을 떠보고 싶은데 지금은 위와 같이 sys_myoption 테이블에 특정 유저로 조회했을 때 pk별로 정렬되어 조회되는 현상이 없어서 plan을 뜰 수가 없네요;;
실제 프로그램에서 돌아가는 쿼리는 바인드변수 처리된 쿼리이지
실제값이 들어간 쿼리가 아닙니다.
실제값을 넣고 수행하는 순간 그 쿼리는 신규쿼리가 됩니다.
SQL이 수행되는 절차를 아주 간단하게 설명드리면
프로그램에서 오라클로 쿼리를 던지면
오라클서버는 해당 쿼리가 이전에 실행한 적이 있는 쿼리인지 라이브러리캐시에서 찾게됩니다.
찾을 때는 sqltext의 해시값을 가지고 찾게 되고요.
따라서 한글자라도 달라지면 신규 쿼리로 인식하게 됩니다.
해당 해시값으로 찾아지면 기존에 만들어놓았던 실행계획을 사용하고
해당 해시값으로 못 찾으면 실행계획을 만듭니다.
느훼훼님이 db툴에서 실제값으로 쿼리를 돌리는 순간 오라클은 신규쿼리로
인식하고 실행계획을 다시 만듭니다.
바인드변수가 아니었으므로 해당 유저아이디 통계정보를 확인합니다.
통계정보로 볼 때 특정 아이디는 데이터량이 많은 것으로 분석되니 해시조인합니다.
통계정보로 볼 때 다른 아이디는 데이터량이 많지 않은 것으로 분석되니 nl 조인합니다.
통계정보 오류로 맞지 않는 실행계획이 만들어지니 느려지는 현상이 생겼습니다.
이것은 실제 프로그램에서 돌아가는 쿼리가 왜 느리고 빠른지 분석하는 방법이 될 수 없습니다.
실제 서버에서 돌아가는 쿼리의 실행계획이 어떠했는지 알려면 trace해보아야 하고요.
실제화면에서도 동일하게 느리고 빨랐다면 심증상은 바인드변수가 사용 안 된 문제인데
그것은 아니라고 하시니.. 실제 어떻게 수행되었는지 trace를 떠보는 수 밖에..
실행계획을 보면 차이점이 보이네요.
1. 빠른 쿼리는 info_job 이 선두 테이블이 되어 sys_myoption 를 Exists(hash join semi) 체크 하네요.
2. 느린 쿼리는 sys_myoption 이 선두 테이블이 되어 info_job 을 NL 조인 하네요.
조건값에 따라 실행계획이 달라진다면?
비용 계산시 컬럼통계가 사용되고 있는걸로 보입니다.
1. 빠른 쿼리에서의 sys_myoption_pk 의 코스트는 107
2. 느린 쿼리에서의 sys_myoption_pk 의 코스트는 3
동일한 건수인데도 다른 실행이 된다면? 통계정보가 정확하지 않다는 거죠.
통계정보를 갱신해 보세요.
table info를 살펴보니 last analyzed가 2019/10/16 22:01:25로 표시되어있습니다.
혹시 다른 방법으로 갱신하는게 있을까요?
테이블 통계 말고 컬럼 통계는 없는지 확인해 보세요.
쿼리 실행계획을 잘못 세우는 경우
대부분 쿼리가 복잡해서 그럽니다.
쿼리를 단순화 하세요.
1. info_fund 가 3 번 사용되는데?
- 서브쿼리 안의 사용은 없어도 되는 부분입니다.
2. sys_myoption 의 PK 조건이 모두 있으므로
- Exists 보다는 조인이 낫습니다.
3. code_imc 는
- 사용되지 않습니다.
- 조인이 필요 없습니다.
마농님 오라클 10버전이고 바인드변수 처리되어있다면
실행계획이 조회할 때마다 달라질 수가 있을까요?
제가 모르는 뭔가 있는 것인지..
나도 잘 모름~ㅋ
지금 주신 실행계획은 별도 상수 처리 실행계획이니
실제 바인드 변수 실행게획과 다르다고 보면 됩니다.
혹시나 대량데이터 delete insert될 때 통계정보 재생성되나 구글링해봐도 별 내용도 안 나오네요;;
1. 서브쿼리 안에 info_fund 테이블은 있어야 합니다. 업무자의 실수(특정 화면에서 데이터 삭제)로 인해 잘못된 정보를 가지고 오면 안되는 상황이 발생할 수 있기에 넣어야만 하는 부분입니다.
2. 음.. 오늘 이 현상이 발생했을 때 조인해서 다시 한번 해볼걸 그랬습니다. 저번주 금요일에는 조인해서 해봤었거든요. 근데 동일한 증상이 나왔었습니다.
3. 맞습니다. 이 부분은 빼야겠네요.
근데 컬럼 통계는 어떻게 확인할 수 있을까요? 구글링 해봤는데도 잘 모르겠습니다;;;
info_fund 테이블이 이미 메인쿼리에 있으므로 서브쿼리의 info_fund 은 불필요합니다.
메인과 서브가 서로 PK 로 조인하므로 같은 행을 바라보게 됩니다.
아~ 그렇네요 ㅎ,.ㅎ;; 말씀하신대로 서브쿼리에 info_fund를 빼고 해보니 7초 이상 걸린게 1초 미만으로 조회가 되네요.
일단은 메인 select 쿼리 자체가 비효율(?) 적으로 코딩이 되어있다는걸 확인하였습니다.
위에서 언급드렸지만 메인 select 쿼리는 exists절을 수정해서 해결을 한 상태지만 마농님께서 말씀해주신 서브쿼리절에서 info_fund를 빼고 메인 info_fund를 조인시켰더니 빠르게 실행되고 있습니다.
허나 아직도 의문인건 메인 쿼리가 잘못 설계되었다면 모든 유저가 다 느린 조회 속도로 결과를 뿌려줘야 하는데 동일건수, 동일 데이터 내용인데도 불구하고 누군 빠르고 누군 느리고 하는 부분은 아직도 의문이네요. select 하는 과정에서 동일 데이터 내용, 건수임에도 불구하고 어떤 식(정렬, 비정렬)으로 들어가있냐에 따라 타 테이블과 조인하는 과정에서 옵티마이저가 다른 해석(?)을 해 빠르냐 느리냐가 결정된다고 생각하고 넘어가야 할 거 같습니다;;
아~ 마농님께서 이미 이 부분에 대해서도 위쪽에 답변을 해주셨군요. 쿼리가 복잡하면 옵티마이저가 실행계획을 잘못세운다...
답변 감사합니다~
1.결과가 정렬되어 나오고 비정렬되어 나오는 건 실행계획에 의한 결과일 뿐 데이터 저장이 정렬/비정렬로 되어있는 게 아닙니다.
2.화면에서는 바인드변수 처리되어있으므로 유저별로 실행계획이 다르게 타지 않습니다.
따라서 화면에서 유저별로 조회되는 속도가 많이 차이가 나는 부분은 다른 이유가 있을듯 하네요.
) 버튼을 클릭하여 작성 하시면 됩니다.