반응형


출처 : http://ukja.tistory.com/232 에서 퍼온 자료입니다.



특정 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;
/


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


SYSTEM> grant execute on print_table to public;

SYSTEM> create public synonym print_table for scott.print_table;



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


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



출처: http://ukja.tistory.com/232 [오라클 성능 문제에 대한 통찰 - 조동욱]

출처: http://ukja.tistory.com/232 [오라클 성능 문제에 대한 통찰 - 조동욱]

반응형

+ Recent posts