v$session--X$KSUSE s,X$KSLED e欄位對應

panpong發表於2013-02-21

> select * from v$session;

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3733760267

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     1 |  1116 |     0   (0)| 00:00:01 |
|   1 |  NESTED LOOPS            |                 |     1 |  1116 |     0   (0)| 00:00:01 |
|*  2 |   FIXED TABLE FULL       | X$KSUSE         |     1 |   907 |     0   (0)| 00:00:01 |
|*  3 |   FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |   209 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
              BITAND("S"."KSUSEFLG",1)<>0)
   3 - filter("S"."KSUSEOPC"="E"."INDX")

 

> select view_definition from v$fixed_view_definition where view_name='V$SESSION';

VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------
select  SADDR , SID , SERIAL# , AUDSID , PADDR , USER# , USERNAME , COMMAND , OWNERID, TADDR , LOCKWAIT , STATUS , SERVE
R , SCHEMA# , SCHEMANAME ,OSUSER , PROCESS , MACHINE , TERMINAL , PROGRAM , TYPE , SQL_ADDRESS , SQL_HASH_VALUE, SQL_ID,
 SQL_CHILD_NUMBER , PREV_SQL_ADDR , PREV_HASH_VALUE , PREV_SQL_ID, PREV_CHILD_NUMBER , PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTR
Y_SUBPROGRAM_ID, PLSQL_OBJECT_ID, PLSQL_SUBPROGRAM_ID, MODULE , MODULE_HASH , ACTION , ACTION_HASH , CLIENT_INFO , FIXED
_TABLE_SEQUENCE , ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAIT_BLOCK# , ROW_WAIT_ROW# , LOGON_TIME , LAST_CALL_ET , PDML_EN
ABLED , FAILOVER_TYPE , FAILOVER_METHOD , FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS, PQ_STATUS, CUR
RENT_QUEUE_DURATION, CLIENT_IDENTIFIER, BLOCKING_SESSION_STATUS, BLOCKING_INSTANCE,BLOCKING_SESSION,SEQ#, EVENT#,EVENT,P
1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW, P3TEXT,P3,P3RAW,WAIT_CLASS_ID, WAIT_CLASS#,WAIT_CLASS,WAIT_TIME, SECONDS_IN_WAIT,STATE,S
ERVICE_NAME, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS from GV$SESSION where inst_id = USERENV('Instance')


> select view_definition from v$fixed_view_definition where view_name='GV$SESSION';

SELECT s.inst_id, s.addr, s.indx, s.ksuseser, s.ksuudses, s.ksusepro, s.ksuudlui, s.ksuudlna, s.ksuudoct, s.ksusesow,
       decode(s.ksusetrn, hextoraw('00'), NULL, s.ksusetrn), decode(s.ksqpswat, hextoraw('00'), NULL, s.ksqpswat),
       decode(bitand(s.ksuseidl, 11), 1, 'ACTIVE', 0, decode(bitand(s.ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'), 2, 'SNIPED', 3, 'SNIPED',
               'KILLED'), decode(s.ksspatyp, 1, 'DEDICATED', 2, 'SHARED', 3, 'PSEUDO', 'NONE'), s.ksuudsid, s.ksuudsna, s.ksuseunm,
       s.ksusepid, s.ksusemnm, s.ksusetid, s.ksusepnm, decode(bitand(s.ksuseflg, 19), 17, 'BACKGROUND', 1, 'USER', 2, 'RECURSIVE', '?'),
       s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.ksusesch, 65535, to_number(NULL), s.ksusesch), s.ksusepsq, s.ksusepha, s.ksusepsi,
       decode(s.ksusepch, 65535, to_number(NULL), s.ksusepch), decode(s.ksusepeo, 0, to_number(NULL), s.ksusepeo),
       decode(s.ksusepeo, 0, to_number(NULL), s.ksusepes), decode(s.ksusepco, 0, to_number(NULL), s.ksusepco),
       decode(s.ksusepco, 0, to_number(NULL), s.ksusepcs), s.ksuseapp, s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix,
       s.ksuseobj, s.ksusefil, s.ksuseblk, s.ksuseslt, s.ksuseltm, s.ksusectm, decode(bitand(s.ksusepxopt, 12), 0, 'NO', 'YES'),
       decode(s.ksuseft, 2, 'SESSION', 4, 'SELECT', 8, 'TRANSACTIONAL', 'NONE'),
       decode(s.ksusefm, 1, 'BASIC', 2, 'PRECONNECT', 4, 'PREPARSE', 'NONE'), decode(s.ksusefs, 1, 'YES', 'NO'), s.ksusegrp,
       decode(bitand(s.ksusepxopt, 4), 4, 'ENABLED', decode(bitand(s.ksusepxopt, 8), 8, 'FORCED', 'DISABLED')),
       decode(bitand(s.ksusepxopt, 2), 2, 'FORCED', decode(bitand(s.ksusepxopt, 1), 1, 'DISABLED', 'ENABLED')),
       decode(bitand(s.ksusepxopt, 32), 32, 'FORCED', decode(bitand(s.ksusepxopt, 16), 16, 'DISABLED', 'ENABLED')), s.ksusecqd, s.ksuseclid,
       decode(s.ksuseblocker, 4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291,
               'NOT IN WAIT', 'VALID'),
       decode(s.ksuseblocker, 4294967295, to_number(NULL), 4294967294, to_number(NULL), 4294967293, to_number(NULL), 4294967292,
               to_number(NULL), 4294967291, to_number(NULL), bitand(s.ksuseblocker, 2147418112) / 65536),
       decode(s.ksuseblocker, 4294967295, to_number(NULL), 4294967294, to_number(NULL), 4294967293, to_number(NULL), 4294967292,
               to_number(NULL), 4294967291, to_number(NULL), bitand(s.ksuseblocker, 65535)), s.ksuseseq, s.ksuseopc, e.kslednam, e.ksledp1,
       s.ksusep1, s.ksusep1r, e.ksledp2, s.ksusep2, s.ksusep2r, e.ksledp3, s.ksusep3, s.ksusep3r, e.ksledclassid, e.ksledclass#,
       e.ksledclass, decode(s.ksusetim, 0, 0, -1, -1, -2, -2, decode(round(s.ksusetim / 10000), 0, -1, round(s.ksusetim / 10000))),
       s.ksusewtm,
       decode(s.ksusetim, 0, 'WAITING', -2, 'WAITED UNKNOWN TIME', -1, 'WAITED SHORT TIME',
               decode(round(s.ksusetim / 10000), 0, 'WAITED SHORT TIME', 'WAITED KNOWN TIME')), s.ksusesvc,
       decode(bitand(s.ksuseflg2, 32), 32, 'ENABLED', 'DISABLED'), decode(bitand(s.ksuseflg2, 64), 64, 'TRUE', 'FALSE'),
       decode(bitand(s.ksuseflg2, 128), 128, 'TRUE', 'FALSE')
  FROM x$ksuse s, x$ksled e
 WHERE bitand(s.ksspaflg, 1) != 0 AND bitand(s.ksuseflg, 1) != 0 AND s.ksuseopc = e.indx

v$session--X$KSUSE s,X$KSLED e

欄位對應表:

v$session x$ksuse
 
          s.inst_id
SADDR         s.addr
 SID         s.indx
 SERIAL#         s.ksuseser
 AUDSID         s.ksuudses
 PADDR         s.ksusepro
 USER#         s.ksuudlui
 USERNAME         s.ksuudlna
 COMMAND         s.ksuudoct
 OWNERID         s.ksusesow
 TADDR         decode(s.ksusetrn,hextoraw('00'),null,s.ksusetrn)
 LOCKWAIT         decode(s.ksqpswat,hextoraw('00'),null,s.ksqpswat)
 STATUS         decode(bitand(s.ksuseidl,11),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED', 'KILLED')
 SERVER         decode(s.ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE')
 SCHEMA#         s.ksuudsid
 SCHEMANAME         s.ksuudsna
 OSUSER         s.ksuseunm
 PROCESS         s.ksusepid
 MACHINE         s.ksusemnm
 TERMINAL         s.ksusetid  
 PROGRAM         s.ksusepnm
 TYPE         decode(bitand(s.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?')
 SQL_ADDRESS         s.ksusesql
 SQL_HASH_VALUE         s.ksusesqh
 SQL_ID         s.ksusesqi
 SQL_CHILD_NUMBER         decode(s.ksusesch, 65535, to_number(null), s.ksusesch)
 PREV_SQL_ADDR         s.ksusepsq
 PREV_HASH_VALUE         s.ksusepha
 PREV_SQL_ID         s.ksusepsi
 PREV_CHILD_NUMBER         decode(s.ksusepch, 65535, to_number(null), s.ksusepch)
 PLSQL_ENTRY_OBJECT_ID         decode(s.ksusepeo,0,to_number(null),s.ksusepeo)
 PLSQL_ENTRY_SUBPROGRAM_ID         decode(s.ksusepeo,0,to_number(null),s.ksusepes)
 PLSQL_OBJECT_ID         decode(s.ksusepco,0,to_number(null),s.ksusepco)
 PLSQL_SUBPROGRAM_ID         decode(s.ksusepco,0,to_number(null),s.ksusepcs)
 MODULE         s.ksuseapp
 MODULE_HASH         s.ksuseaph
 ACTION         s.ksuseact
 ACTION_HASH         s.ksuseach
 CLIENT_INFO         s.ksusecli
 FIXED_TABLE_SEQUENCE         s.ksusefix
 ROW_WAIT_OBJ#         s.ksuseobj
 ROW_WAIT_FILE#         s.ksusefil
 ROW_WAIT_BLOCK#         s.ksuseblk
 ROW_WAIT_ROW#         s.ksuseslt
 LOGON_TIME         s.ksuseltm
 LAST_CALL_ET         s.ksusectm
 PDML_ENABLED         decode(bitand(s.ksusepxopt, 12),0,'NO','YES')
 FAILOVER_TYPE         decode(s.ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE')
 FAILOVER_METHOD         decode(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE')
 FAILED_OVER         decode(s.ksusefs, 1, 'YES', 'NO')
RESOURCE_CONSUMER_GROUP         s.ksusegrp
 PDML_STATUS         decode(bitand(s.ksusepxopt,4),4,'ENABLED', decode(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED'))
 PDDL_STATUS         decode(bitand(s.ksusepxopt,2),2,'FORCED',decode(bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED'))
 PQ_STATUS         decode(bitand(s.ksusepxopt,32),32,'FORCED',decode(bitand(s.ksusepxopt,16),16,'DISABLED','ENABLED'))
 CURRENT_QUEUE_DURATION         s.ksusecqd
 CLIENT_IDENTIFIER         s.ksuseclid
 BLOCKING_SESSION_STATUS         decode(s.ksuseblocker,4294967295,'UNKNOWN',  4294967294 , 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER',  4294967291,'NOT IN WAIT','VALID')
 BLOCKING_INSTANCE         decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksuseblocker, 2147418112)/65536)
 BLOCKING_SESSION         decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksuseblocker,65535))
 SEQ#         s.ksuseseq
 EVENT#         s.ksuseopc
 EVENT         e.kslednam
 P1TEXT         e.ksledp1
 P1         s.ksusep1
 P1RAW         s.ksusep1r
 P2TEXT         e.ksledp2
 P2         s.ksusep2
 P2RAW         s.ksusep2r
 P3TEXT         e.ksledp3
 P3         s.ksusep3
 P3RAW         s.ksusep3r
 WAIT_CLASS_ID         e.ksledclassid
 WAIT_CLASS#         e.ksledclass#
 WAIT_CLASS         e.ksledclass
 WAIT_TIME         decode(s.ksusetim,0,0,-1,-1,-2,-2, decode(round(s.ksusetim/10000),0,-1,round(s.ksusetim/10000)))
 SECONDS_IN_WAIT         s.ksusewtm
 STATE         decode(s.ksusetim, 0, 'WAITING', -2, 'WAITED UNKNOWN TIME',  -1, 'WAITED SHORT TIME',   decode(round(s.ksusetim/10000),0,'WAITED SHORT TIME','WAITED KNOWN TIME')
 SERVICE_NAME         s.ksusesvc
 SQL_TRACE         decode(bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED')
 SQL_TRACE_WAITS         decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE')
 SQL_TRACE_BINDS         decode(bitand(s.ksuseflg2,128),128,'TRUE','FALSE')

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/16976507/viewspace-754440/,如需轉載,請註明出處,否則將追究法律責任。

相關文章