EXADATA X5-2 Performance Test DB Issue - 12C Adaptive Optimization

Symptom

1번노드에서 트래픽 유입 중 특정 시점 이후로 library cache lock 대기 이벤트가 많이 발생 함.

13:57 부터 강제 vip fail-over 로 1번 노드로 전체 트래픽 유입.

fail-over 까지는 정상이나 약 10여분 뒤 14:08 시점 부터 1번 노드에서 library cache lock 을 대기하는 active session 이 약 100여개 정도 급증하면서 SQL처리량 감소.

/* Active Session */
SQL> select inst_id, username, program, module, event, sql_id
     from gv$session where status='ACTIVE'
     order by 1,2,4

   INST_ID USERNAME   PROGRAM                               MODULE                         EVENT                               SQL_ID
---------- ---------- ------------------------------------- ------------------------------ ----------------------------------- -------------
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache: mutex X              bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
....
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache: mutex X              bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache: mutex X              bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      wrc@oratest (TNS V1-V3)               JDBC Thin Client               library cache lock                  bttd0gttbjx9w
         1 P_USR      oracle@exaoradb01.melon.com (O000)                                   class slave wait

Cause

12c New Feature 인 Adaptive Optimization 관련 버그.

My Oracale Support, Bug 19490852 : EXCESSIVE LIBRARY CACHE LOCK

12.2 버전 부터 해결 예정으로 현재(2016년 7월) 최신 버전은 12.1.0.2 로서 버그 패치는 없고 workaround 만 있는 상태.

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

Solution

Adaptive Optimization 기능을 off 하는 workaound 로 해결.

SQL> alter system set optimizer_adaptive_features=false scope=both sid='*';
SQL> alter system set optimizer_dynamic_sampling=0 scope=both sid='*';

적용 후 정상 결과 확인.

Conclusion

New Feature에 대한 사이트 이펙트를 미리 확인해보기 위해서라도 성능테스트를 꼼꼼하게 하자.

추후 12.2 업그레이드 시 Adaptive Optimization관련 사이드이펙트를 확인해 보자.