SQL> set linesize 200;
SQL>
SQL>
SQL> select sid,serial#,username from v$session where username='SYS';
SQL>
SQL>
SQL> select sid,serial#,username from v$session where username='SYS';
SID SERIAL# USERNAME
---------- ---------- ------------------------------
109 10403 SYS
---------- ---------- ------------------------------
109 10403 SYS
SQL>
SQL>
SQL> set autotrace on;
SQL>
SQL>
SQL> select sid,serial#,username from v$session where username='SYS';
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
---------- ---------- ------------------------------
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 |
--------------------------------------------------------------------------------------------
| 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")
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
---------- ------------------------------ ---------- ------------ ----------
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 |
--------------------------------------------------------------------------------------------
| 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")
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了
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24862808/viewspace-717371/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24862808/viewspace-717371/