Oracle 12c 이상 - Adaptive Plans

Oracle Adaptive Plans는 데이터베이스 쿼리 성능을 자동으로 최적화하기 위한 새로운 기술로,

쿼리 실행 시간 동안 데이터베이스 엔진이 실행 계획을 자동으로 변경하여 최적의 성능을 내도록 12c부터 지원하는 새로운 기능이다.

 

12c부터 설치시 optimizer_adaptive_plans 파라미터의 defualt은 TRUE로 설정되어 있어, 설정되어 있는지 모르는 경우가 꽤 있다. 시스템 도입시 오라클 dbms를 설치된 그대로 두고, DBA도 사용하는 개발자도 별 신경을 쓰지 않아서 발생하는 해프닝이라 해야 할까? 

 

Oracle에서 Adaptive Plan을 디폴트로 사용하게 하는 이유는 통계정보를 제때 현행화하지 않는 대부분의 시스템에서 Adaptive Plan의 효익이 더 크다고 판단했을 것이다. 

그러나 민감한 금융권 시스템이나 부하가 많은 시스템에선(특히 OLTP) 수동으로 많은 optimizing이 수행되어야 하는 시스템이라면 해당 파라미터를 켜져 있는게 좋을까? 

이런 시스템들은 이미 통계정보가 충실히 update되어 있는 상태로 관리되고 있다고 봐도 무방할 것이고, 그런 민감한 시스템에서 실행계획(explan plan)과 실제 수행이 다르다면 매우 리스크한 상황을 만들 수 있다. 

 

이미 운영 중인 시스템이고, adaptive plan이 적용되는 sql이라면 /*+ NO_ADAPTIVE_PLAN */으로 adaptive plan이 적용되는 것을 방지할 수 있으니 참고하도록 하자. 

SELECT /*+ NO_ADAPTIVE_PLAN */ column1, column2 FROM table_name;

 

그럼 sql에 adaptive paln이 적용되면, 아래 그림과 같이 실행계획(explan plan)에 statistics collector가 출력된다. 

실행계획(explain plan)에 statistics collector가 출현여부로 adaptive plan이 적용되는 sql여부를 판단할 수 있다는 것이다. 

이게 무슨 소린가? 

ORDERS테이블과 PRODUCTS테이블을 조인하는데 왜 HASH와 NESTED LOOPS가 동시에 등장하지?

그리고 왜 또 Prodect 테이블을 왜 2번 액세스하지?

매우 매우 이상하다고 생각해야 12C 이전의 ORACLE DBMS에 대해 아는 사람이다.

 

위 PLAN은 adaptive plan이라 2가지 path 중 하나를 선택해서 실행하겠다는 이야기가 되는 것이다. 

 

adaptive plan이 선택된 path(실제 수행된 결과)를 보고 싶으면, 

DBMS_XPLAN으로 실행결과를 출력하면 아래와 같이 "this is an adaptive plan (rows marked '-' are inactive)'가 출력되는 것을 볼 수 있고, adaptive '-'를 제외하면 된다. 

위에서는 3, 4, 5, 7, 8번 열을 제외하면 adaptive plan이 선택한 실제 수행된 결과가 되는 것이다. 

(HASH JOIN으로 ORDERS, PRODUCTS 테이블 FTS(Full Table Scan))

 

참고로, 데이터베이스 전역에서 Adaptive Plans를 비활성화하려면 다음과 같이 수행한다.

ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS=FALSE SCOPE=BOTH;

 

12c를 거쳐 19c에서는 adaptive plan이 더 좋아졌으리라 믿는다. 

앞으로도 더 좋아지리라 믿는다. 

그러나 이것도 자동기능의 한 부분으로 받아 들이고, 자동으로 쓸 수 없는 시스템은 해당 기능에 대한 숙지하고 있어야할 사항이니 반드시 알아두도록 하자.