오라클 성능 문제에 대한 통찰 - 조동욱 :: Oracle 성능 Troubleshooting을 위해 꼭 알아야 할 프로그래밍 지식

Oracle 성능 Troubleshooting을 위해 꼭 알아야 할 프로그래밍 지식 - Dynamic SQL

Advanced Oracle 2009/07/02 14:21

성능 문제를 분석하기 위해 테스트를 수행하다 보면 반드시 Dynamic SQL을 작성해야할 때가 온다. 그럴 필요를 못느꼈다면 다양한 테스트를 수행할 기회가 별로 없었다고 봐야할 것이다.

가령 아래 예를 보자. 10개의 테이블을 삭제하고 새로 만드는 길고 복잡한 작업도 간단한 PL/SQL과 Dynamic SQL만으로 손쉽게 해결된다.

UKJA@ukja116> -- basic dynamic sql
UKJA@ukja116> begin
2     for idx in 1 .. 10 loop
3         begin
4             execute immediate 'drop table t_' || idx;
5         exception
6             when others then
7                 null;
8         end;
9
10         begin
11             execute immediate
12                 'create table t_' || idx || '(c1 number, c2 number)';
13         exception
14             when others then
15                 null;
16         end;
17     end loop;
18 end;
19 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.42

Execute Immediate나 Open <Cursor> For ... 류의 Dynamic SQL을 흔히 Native Dynamic SQL이라고 부른다. 많은 경우 Native Dynamic SQL만으로도 원하는 작업을 수행할 수 있지만, 가끔 더 복잡한 기능이 필요할 때가 있다.

특정 Query를 수행한 후, 그 결과를 가로 형태가 아닌 세로 형태로 보기 좋게 출력하고 싶다. Tom Kyte가 멋지게 이 작업을 해냈는데, 핵심은 DBMS_SQL 패키지를 이용한 Dynamic SQL에 있다.

create or replace procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
begin
      dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
    dbms_sql.describe_columns
    ( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        dbms_sql.define_column
        (l_theCursor, i, l_columnValue, 4000);
    end loop;

    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value
            ( l_theCursor, i, l_columnValue );
            dbms_output.put_line
            ( rpad( l_descTbl(i).col_name, 30 )
              || ': ' ||
              l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;
exception
    when others then
     raise;
end;
/

약간의 귀찮은 코딩이 필요하지만 그 효과는 입이 딱 벌어질 정도이다. 다음과 같이 아주 보기 편하게 데이터를 출력할 수 있다.

UKJA@ukja116> set serveroutput on
UKJA@ukja116>
UKJA@ukja116> exec print_table('select * from v$session where sid = userenv(''sid'')');
SADDR                         : 35065B10
SID                           : 127
SERIAL#                       : 557
AUDSID                        : 6755975
PADDR                         : 35A94D88
USER#                         : 88
USERNAME                      : UKJA
COMMAND                       : 3
OWNERID                       : 2147483644
TADDR                         :
...
CREATOR_SERIAL#               : 140
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20

참고로, 나는 print_table 함수를 접하는 순간 Toad가 더 이상 불필요하게 되었다.

DBMS_SQL 패키지를 이용한 Dynamic SQL 구현은 너무 강력하다. 아래는 내가 실제로 테스트를 할 때 많이 사용하는 간단한 PL/SQL Script이다. Column이 50개 이상 존재하는 V$SQL_SHARED_CURSOR 뷰에서 값이 'Y'인 것만 뽑아서 예쁘게 출력하고 싶기 때문이다.

-- my shared cursor
/*
declare
c         number;
col_cnt   number;
col_rec   dbms_sql.desc_tab;
col_value varchar2(4000);
ret_val    number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c,
      'select q.sql_text, s.*
      from v$sql_shared_cursor s, v$sql q
      where s.sql_id = q.sql_id
          and s.child_number = q.child_number
          and q.sql_text like ''&1''',
      dbms_sql.native);
dbms_sql.describe_columns(c, col_cnt, col_rec);

for idx in 1 .. col_cnt loop
    dbms_sql.define_column(c, idx, col_value, 4000);
end loop;

ret_val := dbms_sql.execute(c);

while(dbms_sql.fetch_rows(c) > 0) loop
    for idx in 1 .. col_cnt loop
      dbms_sql.column_value(c, idx, col_value);
      if col_rec(idx).col_name in ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS',
                    'CHILD_NUMBER', 'SQL_TEXT') then
        dbms_output.put_line(rpad(col_rec(idx).col_name, 30) ||
                ' = ' || col_value);
      elsif col_value = 'Y' then
        dbms_output.put_line(rpad(col_rec(idx).col_name, 30) ||
                ' = ' || col_value);
      end if;

    end loop;

    dbms_output.put_line('--------------------------------------------------');

   end loop;

dbms_sql.close_cursor(c);

end;
/

만일 이러한 수고없이 V$SQL_SHARED_CURSOR 뷰를 보려고 하면, 곧 좌절하고 말 것이다. 아래 데이터를 보면서 좌절감을 잠깐 맛보자...

UKJA@ukja116> col sql_id new_value v_sql_id
UKJA@ukja116>
UKJA@ukja116> select sql_id from v$sql where sql_text like
    'select /* cursor_share */ * from t1%';

SQL_ID
-------------
2zu6xb9130t89

Elapsed: 00:00:00.07
UKJA@ukja116>
UKJA@ukja116> set serveroutput on
UKJA@ukja116> exec print_table( -
        'select * from v$sql_shared_cursor where sql_id = ''&v_sql_id''');
SQL_ID                        : 2zu6xb9130t89
ADDRESS                       : 2867E250
CHILD_ADDRESS                 : 2FABDDD8
CHILD_NUMBER                  : 2
UNBOUND_CURSOR                : N
SQL_TYPE_MISMATCH             : N
OPTIMIZER_MISMATCH            : N
OUTLINE_MISMATCH              : N
STATS_ROW_MISMATCH            : N
LITERAL_MISMATCH              : N
FORCE_HARD_PARSE              : N
EXPLAIN_PLAN_CURSOR           : N
BUFFERED_DML_MISMATCH         : N
PDML_ENV_MISMATCH             : N
INST_DRTLD_MISMATCH           : N
SLAVE_QC_MISMATCH             : N
TYPECHECK_MISMATCH            : N
AUTH_CHECK_MISMATCH           : N
BIND_MISMATCH                 : N
DESCRIBE_MISMATCH             : N
LANGUAGE_MISMATCH             : N
TRANSLATION_MISMATCH          : N
ROW_LEVEL_SEC_MISMATCH        : N
INSUFF_PRIVS                  : N
INSUFF_PRIVS_REM              : N
REMOTE_TRANS_MISMATCH         : N
LOGMINER_SESSION_MISMATCH     : N
INCOMP_LTRL_MISMATCH          : N
OVERLAP_TIME_MISMATCH         : N
EDITION_MISMATCH              : N
MV_QUERY_GEN_MISMATCH         : N
USER_BIND_PEEK_MISMATCH       : N
TYPCHK_DEP_MISMATCH           : N
NO_TRIGGER_MISMATCH           : N
FLASHBACK_CURSOR              : N
ANYDATA_TRANSFORMATION        : N
INCOMPLETE_CURSOR             : N
TOP_LEVEL_RPI_CURSOR          : N
DIFFERENT_LONG_LENGTH         : N
LOGICAL_STANDBY_APPLY         : N
DIFF_CALL_DURN                : N
BIND_UACS_DIFF                : N
PLSQL_CMP_SWITCHS_DIFF        : N
CURSOR_PARTS_MISMATCH         : N
STB_OBJECT_MISMATCH           : N
CROSSEDITION_TRIGGER_MISMATCH : N
PQ_SLAVE_MISMATCH             : N
TOP_LEVEL_DDL_MISMATCH        : N
MULTI_PX_MISMATCH             : N
BIND_PEEKED_PQ_MISMATCH       : N
MV_REWRITE_MISMATCH           : N
ROLL_INVALID_MISMATCH         : N
OPTIMIZER_MODE_MISMATCH       : N
PX_MISMATCH                   : N
MV_STALEOBJ_MISMATCH          : N
FLASHBACK_TABLE_MISMATCH      : N
LITREP_COMP_MISMATCH          : N
PLSQL_DEBUG                   : N
LOAD_OPTIMIZER_STATS          : N
ACL_MISMATCH                  : N
FLASHBACK_ARCHIVE_MISMATCH    : N
LOCK_USER_SCHEMA_FAILED       : N
REMOTE_MAPPING_MISMATCH       : N
LOAD_RUNTIME_HEAP_FAILED      : N
-----------------

PL/SQL procedure successfully completed.

너무 많은 데이터때문에 분석하는 시간이 즐거운 시간이 아니라 고생스러운(주로 눈이) 시간이 되어 버린다. 하지만 다음과 같이 간단하게 이 문제를 해결할 수 있다.

UKJA@ukja116> @shared_cursor 'select /* cursor_share */%'
SQL_TEXT                       = select /* cursor_share */ * from t1 where c1 =
:b1
SQL_ID                         = 2zu6xb9130t89
ADDRESS                        = 2867E250
CHILD_ADDRESS                  = 2DCB06D0
CHILD_NUMBER                   = 0
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------
SQL_TEXT                       = select /* cursor_share */ * from t1 where c1 =
:b1
SQL_ID                         = 2zu6xb9130t89
ADDRESS                        = 2867E250
CHILD_ADDRESS                  = 2DD0DB6C
CHILD_NUMBER                   = 1
BIND_MISMATCH                  = Y
--------------------------------------------------
SQL_TEXT                       = select /* cursor_share */ * from t1 where c1 =
:b1
SQL_ID                         = 2zu6xb9130t89
ADDRESS                        = 2867E250
CHILD_ADDRESS                  = 2FABDDD8
CHILD_NUMBER                   = 2
--------------------------------------------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

이런 간단하지만 아름다운 결과들이 엔지니어라면 누구나 남을 한번쯤은 놀라게 할 만한 프로그래밍 실력이 필요한 이유이다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

'Advanced Oracle' 카테고리의 다른 글

Oracle 성능 Troubleshooting을 위해 꼭 알아야 할 프로그래밍 지식 - Dynamic SQL (0)	2009/07/02
Oracle 성능 Troubleshooting을 위해 꼭 알아야 할 프로그래밍 지식 - Trace 파일을 SQL로 읽기 (1)	2009/07/01
Oracle 성능 Troubleshooting을 위해 꼭 알아야 할 프로그래밍 지식 - PL/SQL을 이용한 동시성 제어 트릭 (3)	2009/06/30
Oracle 성능 Troubleshooting을 위해 꼭 알아야 할 프로그래밍 지식 - SQL*Plus에서 인자 받기 (2)	2009/06/29
Dynamic Performance View 프로파일링 하기 (0)	2009/06/26

tags : dbms_sql, native dynamic sql

Trackback 0 : Comment 0

Trackback Address :: http://ukja.tistory.com/trackback/232

Write a comment

: name

: password

: homepage secret mode

◀ PREV : [1] : [2] : [3] : [4] : [5] : ... [225] : NEXT ▶

태터데스크 관리자

태터데스크 메시지

욱짜의 블로그

Oracle 성능 Troubleshooting을 위해 꼭 알아야 할 프로그래밍 지식 - Dynamic SQL

'Advanced Oracle' 카테고리의 다른 글

Write a comment