태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

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

오라클 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

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

Write a comment

티스토리 툴바