오라클 DBMS 를 운영하다보면, 잘 실행되던 SQL 쿼리가 이상하게 가끔씩 느려지는 경우를 경험하게 됩니다.
이유는 Plan(실행계획) 이 바뀌기 때문입니다. 정확하게 말하자면, 하나의 SQL에 대해서 Plan 이 하나가 아닙니다.
이게 Oracle 11g 부터 추가된 Adaptive Cursor Sharing 기능 때문입니다.
데이터를 조회할때 데이터에 따라 대상건수가 너무 크게 다른 경우가 있기 때문에 하나의 Plan 만으로 실행하는 것은 너무 비효율적입니다.
예를 들어,
위 테이블에 대해 select * from ACS_TEST where col1 = :Var1; 과 같은 SQL Query 가 실행되는 경우,
Var1 = 'A' 로 조회할때는 Index 를 타는 것이 좋고,
Var1 = 'C' 로 조회할 때는 Index 를 타는 것보다는 Full Table Scan 하는 것이 좋습니다.
'C' 인 경우가 거의 테이블 전부인데, 어차피 테이블 거의 대부분의 데이터를 읽어와야 하는 마당에 굳이 Index 를 거쳐가면서 왔다리 갔다리 할 이유가 없는 것이죠~
위의 경우처럼, SQL문장은 1개 인데, 들어오는 변수값의 분포도에 따라 Plan 을 여러개 유지하면서 필요에 따라 적정한 Plan 을 사용할 수 있도록 한 개념이 Adaptive Cursor Sharing 입니다.
아주 훌륭한 기능이기는 한데, 위처럼 SQL문이 단순하지가 않고 아주 많은 Join 과 복잡한 조건으로 실행되는 SQL문인 경우, 게다가 통계정보도 정확하지 않은 경우... 이상하게 동작하는 경우가 있습니다.
이런 경우, 해당 SQL문에대해서 Plan 을 고정해버리고 싶은 경우가 있을 수 있습니다.
이게 가능합니다. SPM (SQL Plan Management) 라는 기능입니다.
SPM 기능에 대해 정리된 많은 웹페이지들이 있습니다. SPM 은 DBMS_SPM 패키지를 호출함으로써 SQL 커맨드로 이용 가능합니다.
이 SPM 기능을 좀더 쉽게 사용할 수 있도록 프로그램을 만들어봤습니다.
즉, 현재 DBMS 에서 1) 실행되고 있는 SQL 들의 목록을 확인하고, 2) 해당 SQL 들에 대한 Plan 수를 확인할 수 있고, 3) Plan 을 고정할 수 있는 프로그램입니다.
위에 SQL_ID 가 0sj3rfz0cxhf1 인 SQL은 Plan수가 2 인것을 확인할 수 있습니다. 클릭하면 아래쪽에 Child Cursor 가 2개 나오는데, plan_hash_value 가 다른 것을 확인할 수 있습니다. 서로 다른 2개의 Plan 이 있다는 의미입니다.
* 프로그램 다운로드
- v1.0
> 윈도우즈용
> 리눅스용
* 설치/실행방법 :
- 별도의 Install 이나 setup 과정은 없습니다. 윈도우즈에서는 exe 파일을 바로 실행하면 됩니다.
- 리눅스의 경우는 아래 "리눅스에서 jar 파일 실행하는 방법"을 참고하면 됩니다.
- 이 블로그를 통해서 다운받는 경우 바이러스 걱정은 안해도 됩니다.
윈도우즈10 에서 처음 실행할때 등록되지 않은 프로그램 경고가 한번 뜨는데 무시하고 넘어가면 됩니다.
- 이 프로그램은 실행하려면 PC 에 Java 가 설치되어 있어야 합니다.
Java 다운로드는 여기에서 받을 수 있습니다. ==> >> http://www.java.com <<
* 리눅스에서 jar 파일 실행하는 방법 :
Linux 에서 실행할 때는 SQLArea.jar 파일을 다운로드 받아서 아래처럼 실행합니다.
GUI 인터페이스를 사용하기 때문에 MobaXterm 같은 X-Window 툴이 필요합니다.
MobaXTerm 은 인터넷에서 다운로드 받을 수 있는 무료툴입니다.
* 사용 방법 :
간략한 화면 설명과 프로그램 사용방법을 아래에 적어봤습니다.
1) 접속
DB에 접속하기 위한 정보는 상단에 "Connect" 부분과 "User/PW" 부분에 넣어주면 됩니다.
Connect 부분은 Oracle Easy Connect 방식으로 입력하면 됩니다.
(IP Address) : (Port) / (Service명)
(Port) 가 1521 포트를 사용하는 경우는 생략할 수 있습니다.
Easy Connect 에 대한 자세한 설명은 여길 참조하세요 ==> >>오라클 Easy Connect DB접속 방법<<
User/PW 는 접속하는 DB의 사용자 계정입니다.
보통 system 계정 정보를 입력해주면 됩니다.
[접속테스트] 버튼을 눌러서 접속이 잘 되는지 확인합니다. 접속이 성공하면 하단에 "success" 라고 나옵니다.
2) 실행중인 SQL 조회
[조회] 버튼을 누르면 Oracle DB 내의 Library Cache 영역에서 실행중인 SQL 정보를 가져와서 화면에 출력해줍니다.
메인 그리드에서 SQL을 클릭하면 아래쪽 그리드에 해당 SQL 에 대한 세부 Child Cursor 정보가 표시됩니다.
메인 그리드는 v$sqlarea 에서 정보를 가져와서 표시하고, 서브 그리드는 v$sql 뷰에서 정보를 가져옵니다.
3) Plan 조회 / Plan 고정
화면 우측 하단에 버튼들이 모여 있는데, ▲ 표시가 붙어있는 버튼들은 상단 메인그리드에 대한 버튼들이고,
◀ 표시가 붙어있는 버튼들은 하단 서브 그리드에 대한 버튼들입니다.
[▲Plan] 버튼을 누르면 선택된 SQL 에 연결된 모든 Child Cursor 의 Plan 정보를 한꺼번에 보여줍니다.
[◀Plan] 버튼을 누르면 선택된 Child Cursor 의 Plan 만 보여줍니다.
[▲SQL문(full)] 버튼을 눌러서 전체 SQL문장을 확인할 수 있습니다.
[▲SQL변수] 버튼을 누르면 해당 SQL에서 사용한 Bind 변수가 있는 경우 표시해줍니다.
[◀Plan고정] 버튼을 누르면 왼쪽 서브그리드에서 선택된 plan 으로 실행계획을 고정해버립니다.
※ 내부적으로 dbms_spm.load_plans_from_cursor_cache() 함수를 호출합니다.
fixed='YES' 로 호출하기 때문에, 이후로 해당 SQL 은 무조건 해당 Plan 으로만 실행되게 됩니다.
※ 변수값의 분포도와 상관없이 Plan 이 고정되어 버리기 때문에, 변수값에 따라 Adaptive Cursor Sharing 이 필요한 경우는 이렇게 고정하면 안됩니다. 사용할 때 주의가 필요합니다.
이렇게 고정된 Plan 을 해제하고자 하면, [◀Plan고정해제] 버튼을 눌러주면 됩니다.
Plan고정 / Plan 고정해제 의 경우, 내부적으로 SPM 에 의해 고정하는 것으로,
SQLArea 프로그램에서는 바로 반영되지 않습니다. 해당 SQL이 다시 한번 실행되었을때만 조회됩니다.
고정된 내역을 바로 확인하고자 하는 경우에는 dba_sql_plan_baselines 뷰를 DB에서 직접 조회하면 확인할 수 있습니다.
<< 추가적으로 궁금한 사항은 아래 댓글로 자유롭게 문의바랍니다. >>
