1. 실행계획에서 MERGE JOIN(CARTESIAN)이 보인다.
[이슈 내용]
Dictionary 조회 시 속도가 느리며,
실행계획 확인 시 결과 값의 row 수가 많고, 조인조건에 문제가 없음에도 MERGE JOIN CARTESIAN이 확인된다.
[원인]
ORACLE은 QUERY 수행 시, 미리 수집된 통계정보를 참조하여 실행계획을 수립한다.
이 과정에서, 1개의 ROW만 출력될것이라고 예상되는 테이블에 대해서는 JOIN과정의 최소화를 위해 MERGE JOIN CARTESIAN을 수행한다.
익히 알려진 MERGE JOIN 및 CARTESIAN JOIN의 악명과 다르게, 1개의 ROW만 출력되는 경우라면 오히려 NL JOIN & HASH JOIN보다 빠르기 때문에, ORACLE 입장에서는 효율적인 선택을 한 것이다.
문제는 ROW의 수가 많은 테이블임에도 불구하고 1개의 ROW만 있다고 판단하여 수행할 때 발생한다.
[해결 방법]
메인쿼리에 OPT_PARAM('_optimizer_cartesian_enabled' 'FALSE') 힌트를 추가한다.
[영구적 해결 방법]
해당 파리미터는 기동 중에도 변경이 가능하다.
SQL> alter system set "_optimizer_cartesian_enabled" =false scope=both ; System altered.
단, 히든 파라미터의 경우 사용자 임의적용에 의해서 문제 발생 시, ORACLE에 책임을 물을 수 없게 된다.
따라서 해당 파라미터의 영구적인 적용이 필요한 경우, SR 진행 등을 통해 개런티 된 상태에서 적용하는 것이 바람직하다.
[근본적인 해결방법]
1. DBA_, ALL_, USER_ 딕셔너리 뷰 조회 시 이슈가 발생한 경우EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(OPTIONS=>'GATHER') ; 를 수행하여 딕셔너리 통계정보를 갱신한다.
2. V$ 뷰 조회 시 이슈가 발생한 경우DBMS_STATS.GATHER_FIXED_OBJECTS_STATS 패키지를 피크타임 또는 피크타임시간대 직후에 수행한다.
또한 해당 패키지를 수행 후에도 동일한 실행계획이 확인 될 수 있으므로 서비스 품질에 많은 영향을 끼치는 SQL에는 힌트처리하도록 한다.
[참고자료]
Doc ID 549895.1
[힌트 사용 예시]
[AS-IS]
SQL_ID cwxhps73uxj9n, child number 0
-------------------------------------
SELECT P.PGA_USED_MEM / 1024 / 1024, S.SID, S.SERIAL#, S.LAST_CALL_ET, S.LOGON_TIME, S.SQL_EXEC_START, S.PREV_EXEC_START, S.EVENT || ' ' || S.P1TEXT, SQL_TEXT FROM V$SESSION S, V$PROCESS P, V$SQL Q WHERE S.PADDR = P.ADDR AND S.SQL_ID = Q.SQL_ID ...(하략)...
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 |00:00:01.87 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 642 | 8 |00:00:01.87 | | | |
|* 2 | HASH JOIN | | 1 | 1 | 604 | 8 |00:00:01.87 | 755K| 755K| 1053K (0)|
| 3 | NESTED LOOPS | | 1 | 1 | 582 | 8 |00:00:01.87 | | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 2 | 1088 | 1272K|00:00:00.33 | | | |
|* 5 | FIXED TABLE FULL | X$KGLCURSOR_CHILD | 1 | 1 | 536 | 8002 |00:00:00.14 | | | |
| 6 | BUFFER SORT | | 8002 | 249 | 1992 | 1272K|00:00:00.09 | 9216 | 9216 | 8192 (0)|
| 7 | FIXED TABLE FULL | X$KSLWT | 1 | 249 | 1992 | 159 |00:00:00.01 | | | |
|* 8 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1272K| 1 | 38 | 8 |00:00:01.37 | | | |
|* 9 | FIXED TABLE FULL | X$KSUPR | 1 | 230 | 5060 | 169 |00:00:00.01 | | | |
|* 10 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 8 | 1 | 38 | 8 |00:00:00.01 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
[TO-BE]
SQL_ID cwj1uk1k3tdbc, child number 1
-------------------------------------
SELECT /*+ OPT_PARAM('_optimizer_cartesian_enabled' 'FALSE') */ P.PGA_USED_MEM / 1024 / 1024, S.SID, S.SERIAL#, S.LAST_CALL_ET, S.LOGON_TIME, S.SQL_EXEC_START, S.PREV_EXEC_START, S.EVENT || ' ' || S.P1TEXT, SQL_TEXT FROM V$SESSION S, V$PROCESS P, V$SQL Q WHERE S.PADDR = P.ADDR AND S.SQL_ID = Q.SQL_ID ...(하략)...
--------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| A-Rows | A-Time | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 11 |00:00:00.01 | | | | |* 1 | HASH JOIN | | 1 | 1 | 642 | 11 |00:00:00.01 | 748K| 748K| 1212K (0)| | 2 | NESTED LOOPS | | 1 | 1 | 620 | 11 |00:00:00.01 | | | | | 3 | NESTED LOOPS | | 1 | 1 | 84 | 12 |00:00:00.01 | | | | | 4 | NESTED LOOPS | | 1 | 1 | 46 | 12 |00:00:00.01 | | | | | 5 | FIXED TABLE FULL | X$KSLWT | 1 | 249 | 1992 | 159 |00:00:00.01 | | | | |* 6 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 159 | 1 | 38 | 12 |00:00:00.01 | | | | |* 7 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 12 | 1 | 38 | 12 |00:00:00.01 | | | | |* 8 | FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:2) | 12 | 1 | 536 | 11 |00:00:00.01 | | | | |* 9 | FIXED TABLE FULL | X$KSUPR | 1 | 230 | 5060 | 169 |00:00:00.01 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------
2. Multitenant 환경에서 EXTENDED DATA LINK 실행계획이 보인다.
[이슈 내용]
Dictionary 조회 시 속도가 느리거나,
속도가 느리지는 않지만 CPU와 메모리 점유율이 증가한다. Multitenant(CDB/PDB) 환경이며, EXTENDED DATA LINK 실행계획이 확인된다.
[원인]
[해결 방법]
메인쿼리에 opt_param('container_data' 'CURRENT') 힌트를 추가한다.
또는
현재 SQL 세션에 alter session set container_data=CURRENT ; 를 수행한다.
[근본적인 해결방법]
SQL> alter system set container_data=false scope=spfile ; System altered.
[참고자료]
Doc ID 2707499.1
[힌트 사용 예시]
[AS-IS]
select
v.*,
m.comments
from
sys.all_views v,
sys.all_tab_comments m
where
v.owner = : object_owner
and v.view_name = : object_name
and m.owner (+) = : object_owner
and m.table_name (+) = : object_name
...(하략)... ;
select /*+opt_param('_optimizer_cartesian_enabled' 'FALSE') opt_param('container_data' 'CURRENT') */ v.*, m.comments from sys.all_views v, sys.all_tab_comments m where v.owner = : object_owner and v.view_name = : object_name and m.owner (+) = : object_owner and m.table_name (+) = : object_name ...(하략)... ;
