pajama

14.10.xC4 버전부터 공식적으로 클라이언트에서도 쿼리 플랜을 확인할 수 있는 함수가 제공되는군요. 인포믹스는 과거부터 DB서버에서 set explain 문장을 실행한 후 SQL 문장을 실행하면 생성되는 파일에서 쿼리 플랜을 참조할 수 밖에 없었는데요. 그렇다보니 클라이언트에서는 쿼리 플랜을 볼 방법이 없었습니다.

그래서 11.50버전부터 쿼리 플랜을 시각화하는 explain_sql이라는 루틴이 제공되었는데 IBM Data Studio에서 제한적으로만 쓸 수 있어서 그렇게 많이 사용되지는 않았습니다. 사실 Data Studio는 Db2에 더 최적화 된 느낌이라 인포믹스 DB에 사용하기에는 좀 불편하지요.

그래서 Fernando Nunes씨가 SQL 프로시저를 사용해서 쿼리 플랜을 보는 방법을 제시하기도 했습니다. 사실 이번에 제공되는 루틴과 대동소이합니다. 여러번 루틴을 실행해야하는 번거로움이 있긴 하지만요.

informix-technology.blogspot.com/2012/12/execution-plans-on-client-planos-de.html

어쨌든 많이 늦었지만, 이제라도 쿼리 플랜을 IBM에서 제작된 루틴으로 조회할 수 있다는 점에서 매우 환영할만한 기능입니다. IBM Knowledge Center에서 제공되는 문서의 내용을 따라서 getExplain 함수를 만들어 보았습니다.

> CREATE FUNCTION getExplain(LVARCHAR) RETURNS LVARCHAR(30000) EXTERNAL NAME 'com.informix.judrs.Explain.getExplain(java.lang.String)' LANGUAGE JAVA;
Routine created.
> GRANT EXECUTE ON FUNCTION getExplain(LVARCHAR) TO PUBLIC;
Permission granted.

결과는 LVARCHAR 형식으로 출력되는데, 최대 32KB이니 어지간하면 잘리지 않을 것 같습니다. CLOB 형식으로 해도 괜찮지 않을까 싶습니다.

> execute function getExplain("SELECT * FROM systables a, syscolumns b where a.tabid = b.tabid");
(expression)
              QUERY: (OPTIMIZATION TIMESTAMP: 06-25-2020 22:42:56)
              ------
              SELECT * FROM systables a, syscolumns b where a.tabid = b.tabid
              Estimated Cost: 108
              Estimated # of Rows Returned: 779
                1) informix.a: SEQUENTIAL SCAN
                2) informix.b: INDEX PATH
                  (1) Index Name: informix.column
                      Index Keys: tabid colno
                      Lower Index Filter: informix.a.tabid = informix.b.tabid
              NESTED LOOP JOIN
              ----------
              Procedure: informix.getexplain
              Query statistics:
              -----------------
                Table map :
                ----------------------------
                Internal name     Table name
                ----------------------------
                t1                a
                t2                b
                type     table  rows_prod  est_rows  rows_scan  time       est_cost
                -------------------------------------------------------------------
                scan     t1     1          113       7          00:00.00   14
                type     table  rows_prod  est_rows  rows_scan  time       est_cost
                -------------------------------------------------------------------
                scan     t2     4          786       4          00:00.00   1
                type     rows_prod  est_rows  time       est_cost
                -------------------------------------------------
                nljoin   4          780       00:00.00   109
1 row(s) retrieved.

조금 더 욕심을 부리자면 SQL 문장을 실행하지 않는 AVOID_EXECUTE와 같은 기능을 제공하는 인자도 지정할 수 있다면 좋겠네요.

아래는 getExplain을 실행했을때 online.log에 표시되는 내용입니다.

2020-06-25 22:41:41.70  Booting Language <java> from module <$INFORMIXDIR/extend/krakatoa/lmjava.so>
2020-06-25 22:41:41.70  Loading Module <$INFORMIXDIR/extend/krakatoa/lmjava.so>
2020-06-25 22:41:41.82  cannot extract stack unwind information for /work1/informix/ids1410fc4w1/gls/dll/64-libicudata.so.60
2020-06-25 22:41:41.83  stack dumps out of /work1/informix/ids1410fc4w1/gls/dll/64-libicudata.so.60 may be incomplete
2020-06-25 22:41:41.83  The C Language Module </work1/informix/ids1410fc4w1/extend/krakatoa/lmjava.so> loaded
2020-06-25 22:41:41.83  Loading Module <com.informix.judrs.Explain>
2020-06-25 22:41:41.86  INFO (autoregvp 1) (Execution of [ autoregvp ] dymamically creating VPCLASS jvp)
2020-06-25 22:41:41.957  Dynamically added 1 jvp VP
2020-06-25 22:41:42.859  Got the mutex
2020-06-25 22:41:42.859  LD_LIBRARY_PATH=/work1/informix/ids1410fc4w1/extend/krakatoa/jre/bin/j9vm
2020-06-25 22:41:42.860  VM args[0]= -Xss512k
2020-06-25 22:41:42.860  VM args[1]= -Djava.security.policy=/work1/informix/ids1410fc4w1/tmp/JVM_security
2020-06-25 22:41:42.860  VM args[2]= -Xms16m
2020-06-25 22:41:42.860  VM args[3]= -Xmx16m
2020-06-25 22:41:42.860  VM args[4]= exit
2020-06-25 22:41:42.860  VM args[5]= abort
2020-06-25 22:41:42.860  VM args[6]= -Djava.class.path=/work1/informix/ids1410fc4w1/extend/krakatoa/krakatoa.jar:/work1/informix/ids1410fc4w1/extend/krakatoa
2020-06-25 22:41:43.24  Successfully created Java VM.
2020-06-25 22:41:43.820  Explain file for session 240 : /tmp/informix-explain1554368260018369330.tmp
2020-06-25 22:42:45.158  Explain file for session 240 : /tmp/informix-explain753362435265703881.tmp
2020-06-25 22:42:56.869  Explain file for session 241 : /tmp/informix-explain3777225348977126759.tmp

내용을 살펴보면 java 클래스를 사용한 사용자 함수를 호출한 형태임을 알 수 있습니다. 그리고 set explain 명령을 수행했을 때 처럼 'Explain file for session ...' 이라는 내용이 표시되는데요. 함수가 실행된 직후에는 해당 파일은 삭제되는 것 같습니다.

체감될만한 좋은 기능들이 계속 추가되고 있어서, 앞으로의 변화도 기대되는군요.

data studio를 통해 인포믹스 액세스 플랜을 조회할 때 몇가지 시행착오에 대해 정리했습니다.

추적 파일 옵션을 켜고 파일 내용을 살펴본 것이 도움이 되었습니다.

1. 먼저 인포믹스 서버에 DRDA 프로토콜 연결 엔트리를 작성합니다. (drsoctop)

DRDA 프로토콜 연결이 아닌경우, 액세스 플랜을 조회하면 아래와 유사한 오류가 발생합니다.

[jcc][t4][2030][11211][3.68.61] 연결의 기본 소켓, 소켓 입력 스트림 또는 소켓 출력 스트림에 대한 조작 중에 통신 오류가 

발생했습니다. 오류 위치: Reply.fill() - insufficient data (-1). 메시지: 충분하지 않은 데이터. ERRORCODE=-4499, SQLSTATE=08001

2. Smart blob dbspace가 필요합니다.

원격에서 액세스 플랜을 조회할 때 XML형식의 데이터를 출력하는데 이를 위해 smart blob dbspace가 필요한 모양입니다. 

Invalid default sbspace name (sbspace).. SQLCODE=-9814, SQLSTATE=IX000, DRIVER=3.68.61

IDS_SBPSACE_NOT_CREATED: no message found for the key

3. Data Studio가 한글 환경일때 EXTDIRECTIVES, OPTIMLEVEL 등의 변수값을 영어로 수정해야 합니다.

한글 환경에서는 액세스 플랜 출력에 필요한 일부 변수값이 한글인 경우가 있습니다.

드롭다운 메뉴이기는 하지만 직접 입력하여 수정이 가능합니다.

COLLATION : en_us.8859-1 (한글의 경우 ko_kr.ksc, ko_kr.cp949, ko_kr.utf8)

EXTDIRECTIVES : on/OFF

OPTIMLEVEL : LOW/HIGH

변수가 한글 또는 잘못된 값인 경우 아래와 유사한 오류가 발생합니다.

Invalid values specified for the OPTIMLEVEL environment variable. SQLCODE: -26041, SQLSTATE: IX000

1. IBM Informix Developer’s Handbook (redbook)

2. Use EXPLAIN_SQL with any tool (Fernando Nunes)

3. http://informix-technology.blogspot.kr/2012/12/execution-plans-on-client-planos-de.html