X$KGLOB

• V$SQLAREA 뷰(부모 커서)와 V$SQL 뷰(자식 커서)를 통해 얻을 수 없을 경우
• V$SQL 뷰의 베이스 테이 블인 X$KGLOB 테이블을 사용
• X$KGLOB 테이블을 사용하는 방법

=========================================================================
SQL> -------------------------------------
SQL> -- long_parsing을 수행하는 SQL작성 --
SQL> -------------------------------------
SQL> set echo off
SQL> set termout off
SQL> set pagesize 0
SQL> set heading off
SQL> set verify off
SQL> set feedback off
SQL> set serveroutput on
SQL> set timing off
SQL> set scan off
SQL>
SQL> var v_sql clob;
SQL>
SQL> begin
  2    :v_sql := 'select count(*) from ';
  3
  4    for r in (select t1.table_name
  5                from user_tables t1,
  6                     user_tables t2
  7               where rownum <= 302
  8                 and t1.table_name not like '%$%') loop
  9        :v_sql := :v_sql || r.table_name || ', ';
 10    end loop;
 11  end;
 12  /
SQL>
SQL> spool long_parse.sql
SQL> exec dbms_output.put_line(:v_sql);
select count(*) from 읽을 수 있는 모든 테이블들을 cartesian product...
SQL> spool off
SQL>
SQL> set echo on
SQL> set termout on
SQL> set pagesize 100
SQL> set heading on
SQL> set verify on
SQL> set feedback on
SQL> set serveroutput off
SQL> set timing on
SQL> set scan on
SQL>
SQL> ed long_parse


SQL> ---------------------------
SQL> -- long_parsing.sql 수행 --
SQL> ---------------------------
SQL>
SQL> -- session #1
SQL> exec dbms_application_info.set_client_info('session1');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>
SQL> @long_parse


SQL> ---------------------------
SQL> -- long_parsing.sql 추적 --
SQL> ---------------------------
SQL> -- session #2
SQL> col sid new_value sid1
SQL> select sid from v$session where client_info = 'session1';

       SID
----------
       158

SQL> -- 10g
SQL> col sql_id new_value sql_id
SQL> col sql_text format a30
SQL>
SQL> select /*+ no_query_transformation leading(x) use_nl(s) */
  2         sql_id,
  3         sql_child_number,
  4         logical_read,
  5         substr(sql_text, 1, 60) as sql_text
  6  from (select level
  7          from dual
  8         where WOONG_PKG.SLEEP(1) = 1
  9         connect by level <= 100) x,
 10       (select s.sql_id,
 11               s.sql_child_number,
 12               (select sql_text
 13                  from v$sql x
 14                 where x.sql_id = s.sql_id
 15                   and x.child_number = s.sql_child_number) sql_text,
 16               (select x.value
 17                  from v$sesstat x,
 18                       v$statname n
 19                 where x.sid = s.sid
 20                   and x.statistic# = n.statistic#
 21                   and n .name = 'session_logical_reads') as logical_read
 22          from v$session s
 23         where s.sid = &sid1
 24       );
구  23:        where s.sid = &sid1
신  23:        where s.sid =        158

SQL_ID        SQL_CHILD_NUMBER LOGICAL_READ SQL_TEXT
------------- ---------------- ------------ ------------------------------
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
...

100 개의 행이 선택되었습니다.


SQL> ------------------------------
SQL> -- 안보이는 10g를 위한 방법 --
SQL> ------------------------------
SQL> select kglnaobj
  2  from x$kglob
  3  where kglobt03 = '&sql_id';
구   3: where kglobt03 = '&sql_id'
신   3: where kglobt03 = '88vqr4bx7c8r8'

KGLNAOBJ
--------------------------------------------------------------------------------
select count(*) from     DUAL ,SYSTEM_PRIVILEGE_MAP ,TABLE_PRIVILEGE_MAP ,STMT_A
UDIT_OPTION_MAP      ,AUDIT_ACTIONS
,OGIS_SPATIAL_REFERENCE_SYSTEMS ,OGIS_GEOMETRY_COLUMNS ,SDO_UNITS_OF_MEASURE ,SD
O_PRIME_MERIDIANS ,SDO_ELLIPSOIDS ,SDO_DATUMS ,SDO_COORD_SYS ,SDO_COORD_AXIS_NAM
ES ,SDO_COORD_AXES ,SDO_COORD_REF_SYS ,SDO_COORD_OP_METHODS ,SDO_COORD_OPS ,SDO_
PREFERRED_OPS_SYSTEM ,SDO_PREFERRED_OPS_USER ,SDO_COORD_OP_PATHS ,SDO_COORD_OP_P
ARAMS ,SDO_COORD_OP_PARAM_USE ,SDO_COORD_OP_PARAM_VALS  ,SDO_PROJECTIONS_OLD_SNA
PSHOT ,SDO_ELLIPSOIDS_OLD_SNAPSHOT ,SDO_DATUMS_OLD_SNAPSHOT    ,SDO_XML_SCHEMAS
,MGMT_NOTIFY_QTABLE ,AQ$_MGMT_NOTIFY_QTABLE_S  ,MGMT_VERSIONS ,MGMT_TABLE_SIZES
,MGMT_INDEX_SIZES ,MGMT_REBUILD_INDEXES ,MGMT_LICENSES ,MGMT_AVAILABILITY ,MGMT_
CURRENT_AVAILABILITY ,MGMT_AVAILABILITY_MARKER ,MGMT_MASTER_AGENT ,MGMT_MASTER_C
HANGED_CALLBACK ,MGMT_TARGET_BASELINES ,MGMT_TARGET_BASELINES_DATA ,MGMT_METRICS
,MGMT_METRICS_EXT ,MGMT_TARGET_TYPES 

=========================================================================