oracle一個process對應多個session測試

SQL> set linesize 200;
SQL>
SQL>
SQL> select sid,serial#,username from v$session where username='SYS';

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       109      10403 SYS

SQL>
SQL>
SQL> set autotrace on;
SQL>       
SQL>
SQL> select sid,serial#,username from v$session where username='SYS';

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        54        760 SYS
       109      10403 SYS

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

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

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

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

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        710  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>

 

 

SQL>  select a.sid,a.username,a.serial#, b.spid,b.pid from v$session a, v$process b  where a.paddr=b.addr and a.username='SYS';

       SID USERNAME                          SERIAL# SPID                PID
---------- ------------------------------ ---------- ------------ ----------
       109 SYS                                 10403 8344                 44
        54 SYS                                   760 8344                 44

Execution Plan
----------------------------------------------------------
Plan hash value: 3145835683

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     1 |   167 |     1 (100)| 00:00:01 |
|   1 |  NESTED LOOPS            |                 |     1 |   167 |     1 (100)| 00:00:01 |
|*  2 |   HASH JOIN              |                 |     1 |   154 |     1 (100)| 00:00:01 |
|*  3 |    FIXED TABLE FULL      | X$KSUSE         |     1 |   101 |     0   (0)| 00:00:01 |
|*  4 |    FIXED TABLE FULL      | X$KSUPR         |     1 |    53 |     0   (0)| 00:00:01 |
|*  5 |   FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    13 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("S"."KSUSEPRO"="ADDR")
   3 - filter("S"."KSUUDLNA"='SYS' AND "S"."INST_ID"=USERENV('INSTANCE') AND
              BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)
   4 - filter("INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0)
   5 - filter("S"."KSUSEOPC"="E"."INDX")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        841  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>

在另一視窗，執行 ps -ef |grep  8344 |grep -v grep

就可以看到一個process 對應多個 session了