다시 본문을 읽어보니 좀 헷갈리네요. plan을 떠봤다는 부분이 원래 쿼리를 플랜 떠봤다는 말씀이신지 'exists 부분에서 사용되는 임의의 한 테이블을 조회' -> 요 쿼리를 plan 떠봤다는 말씀이신지..

바인드변수로 인한 문제가 아니고, 바인드변수 처리되었고 원 쿼리의 실행계획도 동일하다면

말그대로 exists절에 해당하는 데이터가 A는 빨리 찾을 수 있고 B는 빨리 찾을 수 없다는 말이되네요.

일단 특정화면에서 조회 시 사용되는 select 쿼리는 동일한게 맞습니다.

또한 말씀하신 user_id 부분은 바인드 변수로 받고 있어요. 저희쪽 시스템을 접속하려면 user_id / password를 입력하고

접속해서 사용하는데 접속할 때 사용되는 해당 id가 특정화면 조회 시 사용되는 user_id에 바인드 변수로 들어가게

됩니다.

다른 테이블과 조인이 걸려있는건 맞습니다. 위에 쿼리 중 exists 부분에 조인절이라고 한글로 명시해놓은 부분이 있는데

그 부분에 info_job 테이블과 조인이 걸려있긴 합니다. 허나 유저별로 해당 화면에서 조회했을 때 info_job 테이블과

sys_myoption이라는 두 테이블의 건 수는 동일합니다. 건 수 뿐만이 아니라 조회되는 값도 동일합니다. 

다른부분은 오로지 sys_myoption이라는 테이블을 select 했을 때 B유저는 키 값대로 정렬돼서 조회가 되고

A유저는 중구난방으로 조회가 되는 것만 다릅니다;;;

근데 이것도 저번주 금요일엔 그러했지만 오늘 오전에 와서 원인을 찾아보려 이것저것 알아보는 도중에

sys_myoption 테이블을 A유저와 B유저로 조회했을 때 두 유저 다 정렬이 되지 않은 상태로 select가 되네요;;;

결국은 두 유저 다 풀스캔을 타서 빠른 속도로 조회가 되고 있습니다.

sys_myoption 테이블에 유저별로 insert되는 쿼리가 동일함에도 어제 했을 때와 오늘 할 때의 결과가 다르게 나오네요;

오라클 옵티마이저가 어떻게 해석해서 insert를 하고 있는지 모르겠지만 지금 현 상황이 이렇습니다.

해결방법은 본문에 언급드렸듯이 exists절을 빼면 됩니다. 허나 궁극적으로 왜 이런 현상이 일어나는지를 알고 싶어요.

지금은 plan을 뜰 수도 없는 상황(A유저, B유저 모두 sys_myoption 테이블에 비정렬된 상태로 조회됨) 이라

사진찍어서 이미지파일 용량을 줄이고 올릴 수도 없는 상황입니다;;

엇, 방금 해당 증상이 발생된 user_id가 있어서 플랜 떠놨습니다. 사진찍어서 이미지로 올려보겠습니다. 

이미지 용량을 줄여야 하는데 그게 가능할지 모르겠네요. 회사 내 인터넷 pc에선 보안땜에 안되는게 많아서;;;

압축해서 파일첨부 하였습니다. ㅎ,.ㅎ;;;

sys_myoption 테이블을 A유저와 B유저로 조회했을 때 실행계획이 다르다.. 이 부분은 본문 쿼리 실행속도 차이와 무관해보입니다.

인덱스 스캔을 했다면 결과가 정렬되어 나왔을 것이고 풀스캔했다면 정렬이 되지 않았을 것이고요.

옵티마이저의 통계정보상 A는 데이터가 많다고 판단하고 풀스캔했고 B는 데이터가 적다고 판단하여 인덱스 스캔했다고

보여지고요.

시간이 지나 다시 조회했을 때는 통계정보가 바뀐 상태니 또 달라졌을 것 같네요.

근 하루동안 분석해본 결과 본문에 적혀있는게 주 원인이라고 밖에 생각되어지지 않습니다.

말씀하신 시간대별 조회속도는 주 원인은 확실히 아닌거 같습니다. 저희 시스템을 이용하는 인원은 많아야 20명입니다.

주 원인은 본문에 적어놓은 내용밖에 없을거 같아요. 이게 왜 그러냐면 아래 리플에서 언급드렸습니다만,

동일 user_id로 sys_myoption 테이블에 어떨 땐 키 값대로 정렬돼서 데이터가 들어가있고, 어떨 땐 비정렬돼서 

들어가 있을 때가 있습니다. 정렬이 되어있을 땐 오랜 조회속도, 비정렬이 되어있을 땐 빠른 조회속도를 기록합니다.

설명을 상세히 드리지 않았던 부분은 sys_myoption 테이블의 경우 동일 화면에서 조회할 때 다른 테이블의 있는

정보를 sys_myoption에 insert를 하게 됩니다. 순서로 따지면,

1. delete from sys_myoption where user_id = :user_id

2. insert into sys_myoption (valse 값) select 쿼리절

3. 화면에 뿌려줄 select 절

입니다. sys_myoption테이블에 insert하는 쿼리는 동일한데 어떨 땐 정렬돼서 들어가고 어떨 땐 비정렬돼서 들어갑니다.

이 case가 어떤 것 때문이지만 밣혀내면 될 거 같은데 그 원인을 모르겠어요.

단순히 옵티마이저가 자기 하고 싶은데도 하는건지, 특정 이유가 있는지 그걸 모르겠습니다.

A로 조회했을 때 B로 조회했을 때 둘다 풀스캔으로 조회되었는데

하나는 소트되어나오고 하나는 소트되어 나오지 않았다면

물리공간에 저장된 위치가 영향을 주었을 수 있지만

본문에 말씀하신대로 하나는 풀스캔, 하나는 인덱스스캔으로 조회했다면 저장순서와는 무관합니다.

select /*+full(a)*/ * from sys_myoption a where user_id = 아이디 로 조회해보세요.

둘다 정렬되지 않은 상태로 나올 확률이 큽니다.

sys_myoption은 위 리플에서 언급드렸듯이 해당 화면에서 조회를 할 때 마다 실시간으로 delete후 insert가 되는 부분이라

현재는 유저가 해당 화면을 이용했는지 전부 비정렬돼서 데이터가 들어간 상태라 리플달아주신 힌트절 사용이 불가합니다.

ㅎ,.ㅎ;; 차후에서 정렬돼서 들어가있는 상황이 발생했을 때 한번 해보겠습니다.

본문과 이 본문의 리플에서 언급드렸던 사항으로 해결방법은 있습니다. select 쿼리절에서 exists부분의 

select 쿼리를 바꾸면 됩니다. sys_myoption 테이블에 insert하는 쿼리를 풀어서 그 안에 넣어주면 빨리 되더라구요.

허나 sys_myoption 테이블을 사용했을 때 왜 이런 현상이 발생되는지 너무 궁금합니다;;

유저의 화면 조작이 아닌 옵티마이저가 뭘 하는거 같은데;; ㅠㅠ

사용되는 쿼리 및 plan등을 압축해서 첨부하였습니다.

조회쿼리 일부분 이라는 파일 보면 바인드변수로 실행되지 않고 텍스트로 값이 들어가있는데요.

실제 프로그램에서 바인드변수 처리되는 게 맞나요?

예를 들어 mabatis에서는 #{param명}으로 바인드변수 처리하면 바인드변수로 처리되지만

${param명}으로 처리하면 바인드변수 처리가 아니라 문자 대체가 됩니다.

그리고 빠른 쿼리와 느린 쿼리가 실행계획도 서로 다른 것 같은데...

그것은 화면 조회를 했을 때 사용되는 쿼리가 자동 복사 되는데 그걸 복사에서 오랜지에 붙여넣고 실행시킨 것입니다.

실제 소스엔 user_id = :as_user_id 라고 개발되어있습니다.

복사된 쿼리는 이미 텍스트로 변수가 대체되어있으므로 빠르게 나온 쿼리와 느리게 나온 쿼리 실행계획은

다르게 나올 수 있는 부분이네요. 실행계획 보실 때도 바인드변수로 되어있는 상태로 보셔야할 것 같고요.

바인드변수로 잘 처리된 게 맞다면 A데이터를 찾는 게 더 빨랐다는 것 밖에 이유가 없을 것 같은데요.

exists 절은 데이터 1건만 찾아도 종료되는데 A데이터는 빨리 찾을 수 있었고 B데이터는 A에 비해 찾기가 어려웠던 것 아닐까요.

시간대에 따라 수행속도가 다르다면 다른 이유가 있을 것 같고요.

oracle 10g Enterprise 10.2.0.3.0 입니다.

아;; 죄송합니다. 빨리 조회되는 쿼리의 plan은 제가 해당 쿼리를 개선한 쿼리의 plan이네요;; 아;;; 다시 올리겠습니다.

재등록하였습니다!!!!

1. 아.. plan에 있어서는 정말 죄송하다는 말씀을 드리고 싶습니다. 다른 결과의 plan이어야 하는데 같은 결과로 찍혔습니다; (지난주 금요일과 오늘 오전만해도 다른 결과의 plan을 확인했었는데 구루비에 업로드 하자고 제차 찍었다가 그 현상의 plan이 날아가버렸습니다. 실시간 확인 후 원하는 plan이 찍힐 때 다시 첨부해드리겠습니다 ㅠㅠ)

그리고 현재는 sys_myoption의 데이터값이 비정렬로 다 들어가있어서 보이고자 하는 plan을 찍을 수가 없습니다;;

말씀하신대로 변수 부분을 바인드로 바꾸고 plan을 찍어보니 중간에 세미 해쉬조인이 일어나면서 info_job 테이블이 

풀스캔 되면서 cost가 많이 올라간걸 확인할 수 있었습니다. 제대로 된 plan을 떠서 올려드리고 싶은데 해당 상황이

나오질 않고 있네요;;

2. sys_myoption은 pk와 인덱스가 같구요, user_id, imc, fund 컬럼이 pk 및 인덱스 입니다. 조인이 된 info_job 테이블의

pk 및 인덱스는 imc와 fund 입니다. 두 테이블의 조인도 놓치지 않고 잘 되어 있습니다. 참고로 info_job 테이블의 총 건수는 3870건이구요, sys_myoption의 유저별 건수는 13000여건 입니다. 

금융권에서 일하고 있는데 보안 때문에 파일 첨부하기가 너무 힘드네요. 생각같아서는 캡쳐해서 올려드리고 싶은데 그게 안돼서 휴대폰으로 사진 찍은 다음에 다른 사람한테 카톡보내서 사이즈를 줄인 다음 사내 이메일로는 접속이 가능해 그 이메일을 통해 내려받은 다음 첨부해드리고 있습니다;;; 이 부분 너무 죄송스럽네요.

실행계획은 입력되는 파라메터에 따라 매번 바뀌는 게 아닙니다.

동일한 쿼리가 오전과 지금 실행계획이 다르다는 것은 라이브러리캐시에서 해당 쿼리가 빠지고

뭔가 변경사항(통계정보라든지)이 생긴 후 실행계획이 다시 만들어진 것이고요.

본문에 풀스캔되었다/안 되었다 말씀하신 것은 전체 쿼리에서 어떤 테이블을 풀스캔했다/안했다는 말씀이신지.

첨부해주신 실행계획은 정상일 때의 플랜이라는 말씀 같은데

정작 sys_myoption 테이블은 index 스캔하고 있습니다.

본문에 table full scan할 때가 더 빠르다는 전제와 모순입니다.

실행계획이 시간대별로 바뀌는게 아니구요, 특정 화면에서 조회 버튼 누를 때 마다 위 리플에 언급드린 sys_myoption테이블을 delete 후 insert 한 뒤 메일 select 쿼리에서 쓰고 있습니다. 조회 버튼 누를 때 마다 delete 하고 insert되기 때문에 sys_myoption 테이블에 특정 유저의 데이터가 계속 바뀌는것이지 시간대마다 바뀌는건 아닙니다. 말씀드렸듯이 비정렬 되어 있으냐 정렬되어 있느냐에 따라 메인 select 화면 조회 쿼리 속도가 10초 이상 걸리느냐 3초 내 조회가 되느냐가 갈리고 있어요. plan은 해당 현상이 나왔을 때 다시 새 글로 올린 뒤 첨부하겠습니다. 현재는 그런 현상이 없습니다. sys_myoption 테이블에 유저별로 데이터가 비정렬 되어있거든요. 정렬되어 있어야지만 이런 현상이 발생합니다.

지난주 금요일에 exists빼고 직접 조인 해봤습니다. 허나 증상은 동일하였습니다. 플랜까지는 떠보지 않았으나 10초 이상 걸렸습니다. 

매인 select 쿼리에서 사용되는 테이블은 총 4개 입니다. info_job, info_fund, code_imc, sys_myoption

(pk와 인덱스는 테이블마다 모두 동일)

info_job // imc, fund 건수 약 3800여건

info_fund // imc, fund 건수 약 2600여건

code_imc // imc 건수 약 500여건

sys_myoption // user_id, imc, fund 유저별 건수 약 13000여건, 전체 토탈 건수 약  37만여건 

입니다. 

특별한 특징 없구요 기본적인 키 값 조인만 되면 됩니다.

저도 제 상황이나 너무나 답답스럽습니다. 해당 상황이 특정 case에서 발생하는 것도 아니라 랜덤하게 발생하는 부분이라 제대로 된 plan을 떠서 화질 나쁜 사진이 아닌 캡쳐를 통해 공유하고 싶고 한데 사내 보안으로 인해 인터넷 pc로 파일 전송 자체가 불가하여;;;

말씀하신 것처럼 sys_myoption 테이블에 테이터가 어떻게 들어가있냐에 따라 매인 select 쿼리의 조회속도에 영향을 끼친다고 예상하고 있습니다. 말씀하신것처럼 테이블 alter는 없습니다. 다만 특정 화면에서 조회 할 때 마다 sys_myoption테이블에 delete, insert를 하고 있는 것 뿐입니다. 

정렬된 상태에서 plan을 떴을 때는 cost도 낮고 table full scan도 타지 않는데 실제 조회 속도는 10초를 넘어가구요, 비정렬된 상태에서 plan을 떴을 때는 table full scan으로 인해 cost가 높은데도 불구하고 실제 조회 속도는 3초 내 입니다. 

해당 상황 발생 시 다시한번 글을 제대로 올리도록 하겠습니다. 일부러 그 상황을 발생시키려 노가다 하고 있는 중입니다;;

음.. 많이 답답하시겠어요.

제 생각 다시한번 정리해서 말씀드릴게요.

정확한 데이터를 볼 수 없고 실행계획이라든지 확인할 수 없는 상태의 추측이므로

틀렸을 수 있습니다.

1.데이터 정렬, 비정렬은 해당 테이블을 해당 아이디로 조회하신 것이죠?

바인드변수로 조회하지 않고 해당 유저아이디로 조회하셨을 거구요.

바인드변수로 조회하지 않으면 매번 새로운 쿼리가 실행될 때마다 실행계획을 새로 만듭니다.

바인드변수로 수행될 때는 최초 1회만 실행계획을 만들고요.

* 정렬되어 나왔다는 것은 옵티마이저가 전체 데이터 비중에서 얼마 안 되는 데이터를

탐색한다고 판단한 것이고 그래서 인덱스 스캔했을 것입니다. -> 인덱스 스캔시

인덱스가 정렬된 상태이므로 결과도 정렬된 상태로 출력됩니다.

* 정렬되지 않았다는 것은 옵티마이저가 전체 데이터 비중에서 해당 데이터가 차지하는 양이

많다고 판단하고 테이블 전체를 스캔한 것입니다.(비중이 클 경우 테이블 풀스캔이 더 빠릅니다.)

이 경우에는 읽는 블럭 순서대로 데이터를 가져오므로 정렬되어있지 않습니다.

2.전체 실행계획을 보실 때도 바인드변수로 보시지 않고 유저아이디로 조회하셨을듯하고

이것으로 비교하셨을 것 같고요. 그래서 풀스캔 <-> 인덱스스캔으로 실행계획이 

상황에 따라 계속 다르게 보였을 것 같고요.

3.업로드해주신 실행계획이 어째서 저렇게 나왔는지 알기 어렵네요.

exists절에 있는 sys_myoption 테이블이 단순히 nested loop 조인되어있습니다만..

어째서 nested loop semi 조인이 아닌지 모르겠습니다.

여튼 제 추측으로는 쿼리가 딱 저런 모양이라면

exists절은 일치하는 데이터 1건만 찾으면 다음행으로 진행되므로

상대적으로 데이터가 많았던 A 유저는 exists절에서 데이터를 빨리 찾을 수 있어

조회속도가 빨랐을 것이고

상대적으로 전체 데이터중 일부데이터만 존재했던 B는 exists절에서 데이터를 찾는 속도가

느려 조회속도가 느렸던 게 아닌가 생각됩니다.

단순히 물리적 저장 위치 때문에 속도가 느렸다고 하기에는

해당 데이터들이 이미 메모리영역에 올라와있을 가능성이 커서

그럴 것 같지는 않다는 게 제 생각이고요.

-> 요 부분은 실행계획 이해할 수 없어 제가 짐작한 것인데 이제 이해가 되었습니다. 제 짐작이 틀렸네요.

EXISTS절 조인조건이 PK컬럼 전부여서 일반 조인으로 풀리고 드라이빙테이블이되었군요.

1. 네, 맞습니다. 특정 아이디로 조회하였습니다. 

2. 네, 이 부분도 맞습니다. 바인드 변수가 아닌 유저 아이디로 조회하였습니다.

3. 이 부분은 다릅니다. 유저별로 등록되어있는 건수는 동일합니다. 동일한 업무를 하고 있기 때문입니다. 

너무 죄송스러운게 제대로 된 자료를 올려놓고 질문을 해야하는데 그렇지 못한게 너무 죄송스럽습니다. 지금 짬 날 때 마다 해당상황 만들어보려고 하고 있습니다. 제대로 된 자료로 다시 재질문글 등록하겠습니다.

마농님 혹시 exists절 조인 조건절에 모든 pk 컬럼 조건이 들어오면 semi 조인이 아니라 일반 조인으로 풀릴 수도 있나요?
sys_myoption 테이블이 왜 일반 조인인지 모르겠어요.
--> 테스트해보니 강제로 exists절을 먼저 수행하게 하니 semi조인이 아니라 일반 조인이군요-0-;
그래서 실행계획이 그랬던 거네요. 그럼 제 추측은 failㅠㅠ
--> 혹시 화면에서도 실제로 A유저와 B유저의 해당 쿼리 속도차이가 많이 났나요?
지금 정렬되었다 안 되었다 캡쳐해주신 데이터를 보니 해당 쿼리로 결과가 나온 거네요.
인덱스스캔이 항상 테이블풀스캔보다 빠른 것은 아닙니다.
실제로 B유저의 데이터가 양이 많은데도 불구하고 통계정보의 오류로 인덱스스캔하면 풀스캔보다
속도가 느려집니다. 바인드변수처리된 상태에서 A유저 B유저의 속도차이가 많이 난다면
SYS_MYOPTION 테이블에서 실제 A유저 데이터 ROW수와 B유저 데이터 ROW수를 비교해보세요.

1. info_fund_comm : pk 및 인덱스 imc, fund, imc_conn, fund_conn 총건수 2133 입니다.

2. 넵, 정확하게 말씀드린게 맞습니다. 

3. 이 부분은 정말 죄송합니다. 지금 테스트 서버에서 해당 상황을 만들어보려고 하고 있습니다. 정확한 자료로 다시 재질문 드리겠습니다.

4. 맞습니다. 특정 화면에서 조회 버튼을 누르면 sys_myoption 테이블에 다른 테이블에 담긴 데이터를 특정 조건하에 

delete >> insert 하고 있습니다. 말씀하신것처럼 이건 비효율이 맞는거 같아요. 제가 개발한건 아니고 특정 개발업체에서 패키지 형식으로 만든걸 사용하고 있는데 이렇게 해놨더라구요. 이 부분은 말씀하신거 처럼 개선해야하는게 맞는거 같습니다.

결론적으로 정확하지 않은 plan 스샷이 현재 없어서 제대로 된 질문글이 못 돼 죄송스럽게 생각하고 있습니다.

특정상황을 만든 다음에 다시 재질문글 올리려 합니다. 

업무자별로 관리하는 펀드가 따로 있을 수 있기 때문에 특정 화면에서 업무자별로 등록되어있는 펀드 리스트만 들고오게 하려고 하기 때문입니다. 

예를들어 A업무자는 한국투자증권 펀드리스트, 미래에셋 펀드리스트, B업무자는 동양증권 펀드리스트.. 각 업무자가 담당하는 펀드가 별도 존재할 수 있기에 exists를 사용한 것입니다.