반응형
출처 : 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;
/
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
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;
/
/*
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>
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
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 [오라클 성능 문제에 대한 통찰 - 조동욱]
반응형
'Database > ORACLE' 카테고리의 다른 글
| 오라클 SR 처리를 위한 사전 준비 (0) | 2018.01.17 |
|---|---|
| ora-00257 archiver error. connect internal only until freed (0) | 2018.01.08 |
| expdp failed with ORA-31641: unable to create dump file ORA-27054: NFS file system where the file is created or resides is not mounted with correct options (0) | 2018.01.08 |
| impdp - ORA-31640 ORA-31693 ORA-19505 ORA-27037 (0) | 2018.01.08 |
| PCTFREE,PCTUSED,FREELIST (0) | 2018.01.05 |