6 Replies Latest reply: Apr 28, 2010 2:59 PM by mbobak RSS

    Difference between V$SESSION and GV$SESSION

    orauser34
      Hi All,

      I have a couple of questions :

      1. What is the difference between V$SESSION and GV$SESSION?

      2. Is there an Oracle defined function like USERENV('SESSIONID'). to get the AUDSID from GV$SESSION?

      Thanks in advance
        • 1. Re: Difference between V$SESSION and GV$SESSION
          Pinela
          Hi,

          1 - V$session is used on standalone database, an gv$session (g=global) is used mostly on RAC environments, showing all sessions on each node.

          2 - maybe with something like this. select SYS_CONTEXT('USERENV', 'SESSIONID') from dual;

          http://psoug.org/reference/sys_context.html

          BestRegards,
          Pinela.

          Edited by: Pinela on Apr 27, 2010 4:59 PM
          • 2. Re: Difference between V$SESSION and GV$SESSION
            mbobak
            Hi,

            The difference between GV$SESSION and V$SESSION is that, in a RAC environment, GV$SESSION will show you data on all instances in the cluster.

            If you look at the definition of V$SESSION, you can see it's based on GV$SESSION:
            SQL> desc v$fixed_view_definition
             Name                                                                                Null?    Type
             ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
             VIEW_NAME                                                                                    VARCHAR2(30)
             VIEW_DEFINITION                                                                              VARCHAR2(4000)
            
            SQL> select view_definition from v$fixed_view_definition where view_name='V_$SESSION';
            
            no rows selected
            
            SQL> c/V_/V
              1* select view_definition from v$fixed_view_definition where view_name='V$SESSION'
            SQL> /
            
            VIEW_DEFINITION
            ------------------------------------------------------------------------------------------------------------------------------------------------------
            select  SADDR , SID , SERIAL# , AUDSID , PADDR , USER# , USERNAME , COMMAND , OWNERID, TADDR , LOCKWAIT , STATUS , SERVER , SCHEMA# , SCHEMANAME ,OSUS
            ER , PROCESS , MACHINE , PORT ,  TERMINAL , PROGRAM , TYPE , SQL_ADDRESS , SQL_HASH_VALUE, SQL_ID, SQL_CHILD_NUMBER , SQL_EXEC_START, SQL_EXEC_ID, PRE
            V_SQL_ADDR , PREV_HASH_VALUE , PREV_SQL_ID, PREV_CHILD_NUMBER , PREV_EXEC_START , PREV_EXEC_ID , PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID, PLS
            QL_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# , TOP_LEVEL_CALL#,  LOGON_TIME ,  LAST_CALL_ET , PDML_ENABLED , FAILOVER_TYPE , FAILOVER_METHOD , FAILED_OVER, RESOUR
            CE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS, PQ_STATUS, CURRENT_QUEUE_DURATION, CLIENT_IDENTIFIER, BLOCKING_SESSION_STATUS, BLOCKING_INSTANCE, BLOCKIN
            G_SESSION, FINAL_BLOCKING_SESSION_STATUS, FINAL_BLOCKING_INSTANCE, FINAL_BLOCKING_SESSION, SEQ#, EVENT#,EVENT,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW, P3TEXT,
            P3,P3RAW,WAIT_CLASS_ID, WAIT_CLASS#,WAIT_CLASS,WAIT_TIME, SECONDS_IN_WAIT,STATE,WAIT_TIME_MICRO,TIME_REMAINING_MICRO, TIME_SINCE_LAST_WAIT_MICRO,SERVI
            CE_NAME, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS, SQL_TRACE_PLAN_STATS, SESSION_EDITION_ID, CREATOR_ADDR, CREATOR_SERIAL#, ECID  from GV$SESSION w
            here inst_id = USERENV('Instance')
            Whereas the GV$SESSION view is based on X$KSUSE:
            SQL> l
              1* select view_definition from v$fixed_view_definition where view_name='GV$SESSION'
            SQL> /
            
            VIEW_DEFINITION
            ------------------------------------------------------------------------------------------------------------------------------------------------------
            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.k
            susetrn),decode(s.ksqpswat,hextoraw('00'),null,s.ksqpswat),decode(bitand(s.ksuseidl,11),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACH
            ED'),2,'SNIPED',3,'SNIPED', 'KILLED'),decode(s.ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO',4,'POOLED','NONE'),  s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ks
            usepid, s.ksusemnm,s.ksusemnp,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.ksusesesta,  decode(s.ksuseseid, 0, to_number(null), s.ksuseseid),  s.ksusepsq
            , s.ksusepha, s.ksusepsi,  decode(s.ksusepch, 65535, to_number(null), s.ksusepch),  s.ksusepesta,  decode(s.ksusepeid, 0, to_number(null), s.ksusepeid
            ),  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),         d
            ecode(bitand(s.ksusstmbv, power(2,11)), power(2,11), s.ksusepco,                to_number(null))),  decode(s.ksusepcs,0,to_number(null),         decod
            e(bitand(s.ksusstmbv, power(2,11)), power(2,11), s.ksusepcs,                to_number(null))),  s.ksuseapp, s.ksuseaph, s.ksuseact, s.ksuseach, s.ksus
            ecli, s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s.ksuseslt,  s.ksuseorafn, 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,'DISA
            BLED','ENABLED')),  s.ksusecqd, s.ksuseclid,  decode(s.ksuseblocker,4294967295,'UNKNOWN',  4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO H
            OLDER',  4294967291,'NOT IN WAIT','VALID'),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4
            294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksuseblocker, 2147418112)/65536),decode(s.ksuseblocker, 4294967295,to_number(null),429
            4967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksuseblocker, 65535)),  decode(s
            .ksusefblocker,4294967295,'UNKNOWN',  4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER',  4294967291,'NOT IN WAIT','VALID'),decode(s.k
            susefblocker,4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(null
            ),bitand(s.ksusefblocker, 2147418112)/65536),decode(s.ksusefblocker,4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null),
             4294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksusefblocker, 65535)),  w.kslwtseq,w.kslwtevt,e.kslednam,e.ksledp1,w.kslwtp1,w.kslw
            
            VIEW_DEFINITION
            ------------------------------------------------------------------------------------------------------------------------------------------------------
            tp1r, e.ksledp2,w.kslwtp2,w.kslwtp2r,e.ksledp3,w.kslwtp3,w.kslwtp3r, e.ksledclassid,e.ksledclass#,e.ksledclass, decode(w.kslwtinwait,        0,decode(
            bitand(w.kslwtflags,256),                 0,-2,                 decode(round(w.kslwtstime/10000),                        0,-1,
            round(w.kslwtstime/10000))),        0), decode(w.kslwtinwait,0,round((w.kslwtstime+w.kslwtltime)/1000000),  round(w.kslwtstime/1000000)), decode(w.ksl
            wtinwait,1,'WAITING',  decode(bitand(w.kslwtflags,256),0,'WAITED UNKNOWN TIME',   decode(round(w.kslwtstime/10000),0,'WAITED SHORT TIME',    'WAITED K
            NOWN TIME'))),w.kslwtstime, decode(w.kslwtinwait,0,to_number(null),  decode(bitand(w.kslwtflags,64),64,0,w.kslwttrem)), w.kslwtltime,s.ksusesvc, decod
            e(bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE'),d
            
            
            1 row selected.
            And, this is generally true for all GV$ and V$ tables. The GV$ is based on the X$ and has information on all instances in the cluster. The V$ is based on GV$ and shows information for only the current instance. If you're running a non-RAC database, the information is identical, except that the GV$ will have the INST_ID column, which, in the case of non-RAC will always be hardcoded to 1.

            Hope that helps,

            -Mark
            • 3. Re: Difference between V$SESSION and GV$SESSION
              orauser34
              Thank you Pinela and Mark.

              I have a follow up question.

              In an RAC environment, would it be possible for Users connected to different database instances to have the same AUDSID? In other words is AUDSID unique across all Instances of a database?
              • 4. Re: Difference between V$SESSION and GV$SESSION
                mbobak
                Hmmm....I had no idea.....so I did a little digging.

                The short answer is that other than SYSDBA and SYSOPER connections, which all get the same AUDSID, it is unique across the database. AUDSID values are generated from the SYS.AUDSES$ sequence.

                See MetaLink Doc. IDs 123128.1 and 122230.1 for more info.

                -Mark
                • 5. Re: Difference between V$SESSION and GV$SESSION
                  orauser34
                  Thanks Mark
                  • 6. Re: Difference between V$SESSION and GV$SESSION
                    mbobak
                    8 questions, 5 unresolved.

                    Don't forget to close out questions and mark correct responses.