노스웨일즈 김씨 시조의 블로그

이번에 Production system에서 NullPointerException으로 API call이 failed 되는 이슈가 발생했다.
Review 하던 중에 Maximum Open Cursor도 함께 발생해서, 느낌은 Maximum Open Cursor 때문에 NullPointerException도 발생한 것 같은데 들어주는 사람이 없네.. -.-

어쨋든, 이슈는 이슈고, DBA 얘기로는 Cursor 개수 Count는 Session 단위라고 하니, 해당 이슈가 다시 발생할 개연성은 많이 줄은 듯 하다. ( 특히나 얼마전에 Upgrade 작업으로 DB Session들이 한 번 Restart 됐으니. )

그래도 일단은 Cursor Monitoring은 해야 할 듯 해서 Article을 찾아 보니 아래 Article이 괜찮아 보인다.

http://www.orafaq.com/node/758

현재 OPen Cursor 개수를 확인하는 Query

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current';

현재 최대 Open Cursor 개수와 Maximum Cursor Limitation Check

select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# 
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;

근간에 Project를 진행하면서 Multi Row로 저장된 데이터를 한 Field로 처리할 방법을 Search하다 알게된 listagg 함수,
GROUP BY 와 함께 사용하면 같은 Key Field 값을 가진 ROW 데이터를 합칠 수 있다.

SELECT KEYFIELD, listagg( field_name, ',') within group (order by field_name)
FROM table
GROUP BY KEYFIELD


워낙 이런 Case가 많아서, 자주 사용하기 시작했는데,
오늘 listagg 함수를 사용한 Query에서 ORA-01489 에러가 발생..

찾아 보니... Oracle에서 문자열 Concatenate를 4000 byte 까지 밖에 지원하지 않는 것이다.
크.. 언제적 제약사항이 아직도 발목을 잡는 건지..

Googling을 해 보았더니.. StackOverflow에 관련 내용도 있고,
여러 내용을 리뷰해 보니, Query Level에서 이 문제를 해결할 방법은 아래와 같이 XML TYPE으로 변경해서 Aggregation 하는 방법 밖에는 없다.
http://stackoverflow.com/questions/14864055/listagg-function-and-ora-01489-result-of-string-concatenation-is-too-long

select tm.product_id, 
       rtrim(extract(xmlagg(xmlelement(e, tm.book_id || '(' || tm.score || '),')), 
               '/E/text()').getclobval(), ',')
  from tl_product_match tm
 where tm.book_id is not null 
 group by tm.product_id;

문제는.. XML로 변환 하고, Aggregation 한후 정규식으로 XML TAG를 제거 하고, 이를 다시 CLOB으로 변경하니, Performance가 엄청 좋지 않다는 점..

지금 Project는 Java Program 단에서 해결해 낼 수 있을 것 같긴 한데..
Oracle도 예전 코드들 때문에 이런 제약사항에 시달리는 걸 보면..
너무 앞장서서 나가는 것도 생각 만큼 좋지는 않은 듯...

DB Migration 중에 TEMP 파일을 많이 사용하는 쿼리를 돌리게 되서 TEMP 파일을 늘려 놨더니 , 이젠 서버 공간이 부족해서 파일들을 삭제 해야 할 일이 생겼다.

DB 내렸다 올려야 하나 생각하고 좀 귀찮았는데..
TEMP 파일은 생각 보다 손 쉽게 삭제 할 수 있었다.

SQL> ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;

출처는 아래 페이지에서..

http://www.orafaq.com/node/2


그동안에 API에서 CONCURRENT 관련 이슈가 많이 생겨서 RATE CONTROL FEATURE를 개발했다.
개발하고 나니, 어떤 CLIENT가 얼마가 쓰고 있는 지를 현재 모르고 있다는..
그 전에 CLIENT 들 한테 INFORM을 해야 하는데...
다행히 LOG 분석툴에 DATA는 쌓여 있는데... 동시 사용하는 건지 체크를 어떻게 해야 하나 고민이 되었다.

일단 LOG 데이터를 하나씩 읽어서 시간대가 겹치는 LOG가 있는지를 확인한 후 LOG 가 있을 경우 동시 사용자 정보로 넣는 코드를 생각해 보았는데...
내가 생각해 보아도 너무 무식한 방법인 것이다.

MATT하고도 잠시 상의해 보고 했는데.. MATT도 딱히 생각나는 IDEA가 없는 듯 하다.

결국 GOOGLEING을 했더니.. 나랑 비슷한 이슈를 가졌던 사람들이 있고 QUERY로 해결 할 수 있는 방법이 떡하니 있는 것이 아닌가..

http://stackoverflow.com/questions/2037618/overlapping-time-in-sql-server

http://docs.oracle.com/cd/B28359_01/appdev.111/b28396/long_vt.htm#i1007490

내 경우는...  Index가 날짜 FIELD에 맞추어 생성이 안되어 있어서 값이 RETURN되지 않아서
INDEX 생성  시키면서 그냥 데이터 하나씩 읽어서 중복 데이터 CHECK하는 원래 생각대로 개발 하였다.

역시 세계는 넓고 개발자는 많다는 거...
그나저나 이렇게 인터넷 세상이 발달한 때에 개발자로 산다는 게 얼마다 다행인지..

Stored Procedure가 성능 면에서는 일반 Query 보다 앞선 다는 걸 알면서도 선뜻 사용하기가 주저해진다. 익숙하지 않는 부분이 하나 일 것이고, 디버깅도 쉽지 않기 때문일 것이다.

뭐... 요즘처럼 JDBC 이외에 ORM 까지 사용하는 세상에 Stored Procedure는 정말 찬밥 이기 쉬운 듯 하다.

하지만... 장점도 있는 것이... DB dependent 한 Code를 Stored Procedure로 숨길 수도 있는데.. 이 부분은 Product를 개발하는 입장에서는 정말 매력적인 장점이 아닐까 생각 된다.

뭐.. 어쩌다가 요즘 혼자 개발하다가 갑자기 Stored Procedure를 쓸 일이 생기니 좀 당황 스러웠다.

생각해 보면 예전에 DBA가 SQL*PLUS에서 Stored Procedure를 어떻게 실행하는 지 물어 보기도 했으니.. 그리 부끄러워 할 일은 아닌 듯 하다.

일단 Stored Procedure를 실행 할려면 아래 처럼 하면 된다.

var rc refcursor;

execute ( ... , : rc);

print rc;

우리회사는 보통 Cursor를 제일 마지막에 사용하다 보니 위에 처럼 Output Cursor를 마지막에 넣어 주고... print 문으로 Cursor를 찍어 본다.

그리고 개발을 하다 보니 Stored Procedure가 LOOPING을 도는 것 같은 데 확인할 방법이 없다.

뭐.. 이럴 때는 Query를 확인 해야 하는 데 아래와 같이 CURSOR OPEN 하기 전에 Query를 찍으면 도움이 될 듯 하다.

dbms_output.put_line(p_sql);

근데.. 이걸 했는데도 Query가 콘솔에 찍히지 않아 왜 그런가 확인해 봤더니 아래 환경 변수를 ON 시켜 주어야 한다나..

SET SERVEROUTPUT ON

확인해 보니깐..
조건절을 잘못 넣어서 2억개가 넘는 ROW를 CURSOR에 넣고 있었다...-.-

어쩟든 기존에 개발 되어 있던 여러 Stored Procedure를 참조해서 필요한 Stored Procedure를 개발했는데.
참 어처구니 없는 것이... PL/SQL에는 문자열 split 함수가 없다.
인터넷을 뒤지고 다녔더니.. 다들 자기 들 만의 문자열 split 함수를 올려 놨다.
마침 Vinayak이 방에 놀러 왔길 래 사정 얘기를 했더니..
OAS에서 Cursor, Pipe를 이용해 구현한 함수와 사용 예제를 보여주어 덕분에 쉽게 해결 됐다.
다음에 또 stored procedure를 개발할 일이 생겼을 떄는 split 함수가 지원 되기를 기원한다.