Lab: configure shared server on oracle 12.1 and see how to verify the shared server connection & manage the connections
Setup:
2 init parameters are involved:
1) DISPATCHERS [specifies the #of dispatchers to start with, also it is possible to set very granular setting for protocol, address, description with more options],
& MAX_DISPATCHERS [used for backward compatibility, used to mention max # of dispatcher at any point it time, but it is overridden by dispatchers]
1) SHARED_SERVER [the min. # of processes to start, 0 or 1 based on DISPATCHER setting, 0 in case no dispatcher]
& MAX_SHARED_SERVER [the max # of shared servers allowed, if not set, either 1/8 of process if free slots available or 2 in case ,# of process < 24 is maximum allowed]
So to start with let us set, dispatchers alone, since the max clause or somehow controlled by this parameter and also shared_server will become 1 as soon as dispatchers is set and then we can evaluate it how busy the shared server is and how many more we need to start (even it automatically starts as long as we dont set the max clause).
1) preserve the current pfile
2) Evaluate what is already there
dispatcher params right now:
dispatchers string (PROTOCOL=TCP) (SERVICE=rmn01t
stXDB)
max_dispatchers integer
shared_server setting right now:
max_shared_servers integer
shared_servers integer 1 <<<< observe, here this parameter isnt set in pfile by default, but the shared_server count increased to 1 as mentioned in oracle param guide.
pfile snipet: [observe no shared_server setting]
*.db_recovery_file_dest_size=4800m
*.db_recovery_file_dest='/oradata_1/fast_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XXXXXDB)'
*.memory_target=1188m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.undo_tablespace='UNDOTBS1'
lsnrctl services:
Service "XXXXXDB" has 1 instance(s).
Instance "XXXX", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: host, pid: 2097>
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=48278))
The command completed successfully
3) Remove or clear the existing default configuration
SQL> select server,count(1) from v$session group by server;
SERVER COUNT(1)
--------- ----------
DEDICATED 32
see here, we dont have any existing shared server session, if there was 1, then we will see SHARED or NONE based on weather the session is actively serviced by the shared server.
SQL> alter system set dispatchers='' scope=both;
System altered.
SQL> select * from v$dispatcher;
no rows selected
SQL>
check listener service after a minute you will see the dispatcher is removed already.
A minute before:
Service "XXXXXDB" has 1 instance(s).
Instance "XXXX", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: host, pid: 2097>
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=48278))
The command completed successfully
A minute later:
Service "XXXXXDB" has 1 instance(s).
Instance "XXXX", status READY, has 0 handler(s) for this service...
The command completed successfully
Since the dispatcher wasnt handling any connection (connected clients) actively, the service died otherwise we will have to use the below command to stop the dispatcher...
ALTER SYSTEM SHUTDOWN IMMEDIATE 'D000'; => to stop accepting new connection and kill the existing connection.
shared server is still alive:
00000000AB97C8C0 2099 S000 oracle@host (S000) <<< the shared server is still left asis, I think it wont go off until the instance is restarted or explictly we set the parameter to 0 [which will be another simple excercise]
4) Setup the fresh one as per the need
alter system set dispatchers='(PROTOCOL=TCP)(DISPATCHERS=3)' scope=both;
before this command was fired:
Service "XXXX" has 1 instance(s).
Instance "XXXX", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
after this command was fired:
Service "XXXX" has 1 instance(s).
Instance "XXXX", status READY, has 4 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: host, pid: 3605>
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=40302))
"D001" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: host, pid: 3607>
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=46767))
"D002" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: host, pid: 3609>
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=60605))
select * from v$dispatcher_config;
CONF_INDX NETWORK DISPATCHERS CONNECTIONS SESSIONS MULT LISTENER SERVICE CON_ID
---------- ------------------------------------------------ ----------- ----------- ---------- ---- -------------------------------------------------------------------------------- ---------- ----------
0 (ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP)) 3 1022 1022 OFF (ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521)) XXXX 0
select * from v$dispatcher;
NAME NETWORK PADDR STATUS ACC MESSAGES BYTES BREAKS OWNED CREATED IDLE BUSY LISTENER CONF_INDX CON_ID
---- ------------------------------------------------ ---------------- ---------------- --- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=40302)) 00000000AB97B760 WAIT YES 0 0 0 0 0 594042 1 0 0 0
D001 (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=46767)) 00000000AB994700 WAIT YES 0 0 0 0 0 594039 0 0 0 0
D002 (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=60605)) 00000000AB995860 WAIT YES 0 0 0 0 0 594035 1 0 0 0
dispatchers are now ready and shared server available as well..
SQL> select addr,spid,pname,program from v$process where pname like 'S%' order by pname;
ADDR SPID PNAME PROGRAM
---------------- ------------------------ ----- ------------------------------------------------
00000000AB97C8C0 2099 S000 oracle@host (S000)
..
5) let us try doing the operation using shared server and query the necessary views
to examine connection/session:
v$circuit
v$session
to examine the common queue - owner of VC & queue statistics:
v$queue
v$reqdist
to examine weather shared server usage is optimal:
v$shared_server
v$shared_server_monitor
Dispatcher monitoring, as per oracle documentation, the work done @ dispatcher is really swift, hence most of the time it is the shared server which struggles.
a) TNS entry with "SERVER=SHARED"
Service "XXXX" has 1 instance(s).
Instance "XXXX", status READY, has 4 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
..
"D002" established:1 refused:0 current:1 max:1022 state:ready <<<<<<<< look D002 has 1 connection established
DISPATCHER <machine: host, pid: 3609>
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=60605))
this is shared connection...
[oracle@host admin]$ sqlplus scott/****@<DB>_shared
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 18 13:47:25 2019 <<< time of shared server
Copyright (c) 1982, 2013, Oracle. All rights reserved.
this is dedicated connection...
[oracle@host ~]$ sqlplus scott/****@<DB>
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 18 14:14:39 2019 <<< time of dedicated server
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Fri Jan 18 2019 13:47:25 +08:00
See the dedicated process list below..
[oracle@host lab18Jan2019]$ ps -ef|grep -i local=no
oracle 7898 1 0 14:14 ? 00:00:00 oracleXXXX (LOCAL=NO) <<< time of dedicated server
so no dedicated process spawned for shared server.
Query 1)
col server for a10
col service_name for a12
col username for a9
col program for a45
col module for a15
set lines 300
set pages 3000
alter session set nls_date_Format='DD/MM/YYYY HH24:MI:SS';
select server,service_name,sid,serial#,username,program,module,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;
14:14:06 SQL> select server,service_name,sid,serial#,username,program,module,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;14:15:59 2 14:15:59 3 14:15:59 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,PROGRAM ,MODULE ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,---------------------------------------------,---------------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX , 40, 657,SCOTT ,sqlplus@host (TNS V1-V3) ,SQL*Plus , ,18/01/2019 14:14:40, ,7896
NONE ,XXXX , 34, 603,SCOTT ,sqlplus@host (TNS V1-V3) ,SQL*Plus , ,18/01/2019 13:47:25, ,7509
14:15:59 SQL>
select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;
14:15:59 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;14:16:21 2 14:16:21 3 14:16:21 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896
NONE ,XXXX , 34, 603,SCOTT , ,18/01/2019 13:47:25, ,7509
14:16:21 SQL>
Notice above the shared server session 34 (SCOTT) - with NONE under server column. It is good that it shows client process ids.
oracle 7509 2762 0 13:47 pts/1 00:00:00 sqlplus << shared server sqlplus conn
oracle 7896 2888 0 14:14 pts/2 00:00:00 sqlplus << dedicated process sqlplus conn
let us check now the shared server views....
Query 2) select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;
14:16:21 SQL> select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;
CIRCUIT ,DISPATCHER ,SERVER ,SADDR ,STATUS ,QUEUE
----------------,----------------,----------,----------------,----------------,----------------
00000000A3A5A4F8,00000000AB995860,00 ,00000000ABC66DA8,NORMAL ,NONE
So there is a session address...
select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;
14:18:22 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;14:18:37 2 14:18:37 3 14:18:37 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
NONE ,XXXX , 34, 603,SCOTT , ,18/01/2019 13:47:25, ,7509
14:18:37 SQL>
Being this session is in idle state, the shared server address is "00" and we get to see the circuit and dispatcher addresses fine.
select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
from v$circuit c
,v$session s
,v$process p1
,v$process p2
where c.saddr=s.saddr
and c.dispatcher=p1.addr
and c.server=p2.addr(+);
14:36:34 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
14:36:44 2 from v$circuit c
14:36:44 3 ,v$session s
14:36:44 4 ,v$process p1
14:36:44 5 ,v$process p2
14:36:44 6 where c.saddr=s.saddr
and c.dispatcher=p1.addr
and c.server=p2.addr(+);
14:36:44 7 14:36:44 8
CIRCUIT ,DISP_,SS_NAME , SID, SERIAL#,STATUS ,QUEUE
----------------,-----,---------,----------,----------,----------------,----------------
00000000A3A5A4F8,D002 ,NO_SERVER, 34, 603,NORMAL ,NONE
So this matches with what we observed.
Query 3) examine queue
SELECT
ksppinm,
ksppstvl
FROM
x$ksppi a,
x$ksppsv b
WHERE
a.indx=b.indx
AND
substr(ksppinm,1,8) like '%_shared_%'
ORDER BY ksppinm
/
14:42:14 SQL> /
KSPPINM ,KSPPSTVL
-----------------------------------,----------
..
_shared_server_load_balance ,0
_shared_server_num_queues ,2 << notice here 2 common queues vs 1 common queue.
11 rows selected.
14:39:36 SQL> select * from v$queue;
PADDR ,TYPE , QUEUED, WAIT, TOTALQ, CON_ID
----------------,----------,----------,----------,----------,----------
00 ,COMMON , 0, 1, 7, 0
00 ,COMMON , 0, 0, 7, 0 << 2 common queue
00000000AB97B760,DISPATCHER, 0, 0, 0, 0
00000000AB994700,DISPATCHER, 0, 0, 0, 0
00000000AB995860,DISPATCHER, 0, 1, 14, 0 << 3 dispatcher queues
select
p.pname,
q.type,
q.totalq,
q.wait,
round(q.wait/nullif(q.totalq,0),2) avg_wait
from
v$queue q,
v$process p
where
p.addr(+) = q.paddr
/
PNAME,TYPE , TOTALQ, WAIT, AVG_WAIT
-----,----------,----------,----------,----------
D000 ,DISPATCHER, 0, 0,
D001 ,DISPATCHER, 0, 0,
D002 ,DISPATCHER, 14, 1, .07
,COMMON , 7, 0, 0
,COMMON , 7, 1, .14
So there are 2 common queue and 3 dispatcher queue. The session is no queue now, staying idle.
Load the server process now...
declare
a varchar2(100);
cursor c1 is
select * from dba_objects;
begin
for i in 1..20
loop
for c1_rec in c1
loop
a:=c1_rec.owner;
end loop;
dbms_output.put_line('exited '||i||' loop');
end loop;
end;
/
With server loaded:
14:52:15 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;14:52:26 2 14:52:26 3 14:52:26 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896
SHARED ,XXXX , 34, 603,SCOTT ,7xr6jha03c1wx,18/01/2019 13:47:25,18/01/2019 14:52:25,7509 <<< NONE to SHARED
14:52:27 SQL> select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;
CIRCUIT ,DISPATCHER ,SERVER ,SADDR ,STATUS ,QUEUE
----------------,----------------,----------,----------------,----------------,----------------
00000000A3A5A4F8,00000000AB995860,00000000AB,00000000ABC66DA8,NORMAL ,SERVER <<< SERVER QUEUE from NONE
, ,97C8C0 , , ,
14:52:36 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;14:52:45 2 14:52:45 3 14:52:45 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
SHARED ,XXXX , 34, 603,SCOTT ,7xr6jha03c1wx,18/01/2019 13:47:25,18/01/2019 14:52:41,7509
14:52:45 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
14:53:01 2 from v$circuit c
14:53:01 3 ,v$session s
14:53:01 4 ,v$process p1
,v$process p2
14:53:01 5 14:53:01 6 where c.saddr=s.saddr
14:53:01 7 and c.dispatcher=p1.addr
14:53:01 8 and c.server=p2.addr(+);
CIRCUIT ,DISP_,SS_NAME , SID, SERIAL#,STATUS ,QUEUE
----------------,-----,---------,----------,----------,----------------,----------------
00000000A3A5A4F8,D002 ,S000 , 34, 603,NORMAL ,SERVER <<<<< S000 shared server is processing the request
14:53:01 SQL> select * from v$queue;
PADDR ,TYPE , QUEUED, WAIT, TOTALQ, CON_ID
----------------,----------,----------,----------,----------,----------
00 ,COMMON , 0, 1, 7, 0
00 ,COMMON , 0, 0, 8, 0
00000000AB97B760,DISPATCHER, 0, 0, 0, 0
00000000AB994700,DISPATCHER, 0, 0, 0, 0
00000000AB995860,DISPATCHER, 0, 1, 14, 0
14:53:31 SQL> select
p.pname,
q.type,
q.totalq,
q.wait,
round(q.wait/nullif(q.totalq,0),2) avg_wait
from
v$queue q,
v$process p
where
p.addr(+) = q.paddr
/14:53:43 2 14:53:43 3 14:53:43 4 14:53:43 5 14:53:43 6 14:53:43 7 14:53:43 8 14:53:43 9 14:53:43 10 14:53:43 11 14:53:43 12
PNAME,TYPE , TOTALQ, WAIT, AVG_WAIT
-----,----------,----------,----------,----------
D000 ,DISPATCHER, 0, 0,
D001 ,DISPATCHER, 0, 0,
D002 ,DISPATCHER, 14, 1, .07
,COMMON , 8, 0, 0
,COMMON , 7, 1, .14
14:53:43 SQL>
Now let us gracefully exit the session.
-----
SQL> disc
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
-----
14:53:43 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;14:56:27 2 14:56:27 3 14:56:27 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896
14:56:27 SQL> select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;
CIRCUIT ,DISPATCHER ,SERVER ,SADDR ,STATUS ,QUEUE
----------------,----------------,----------,----------------,----------------,----------------
00000000A3A5A4F8,00000000AB995860,00 ,00 ,NORMAL ,NONE << CIRCUIT stays but there are no session user connection/session associated
14:56:37 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;14:56:50 2 14:56:50 3 14:56:50 4
no rows selected
14:56:50 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
14:57:01 2 from v$circuit c
14:57:01 3 ,v$session s
14:57:01 4 ,v$process p1
14:57:01 5 ,v$process p2
14:57:01 6 where c.saddr=s.saddr
14:57:01 7 and c.dispatcher=p1.addr
and c.server=p2.addr(+);14:57:01 8
no rows selected
14:57:02 SQL> select * from v$queue;
PADDR ,TYPE , QUEUED, WAIT, TOTALQ, CON_ID
----------------,----------,----------,----------,----------,----------
00 ,COMMON , 0, 1, 8, 0
00 ,COMMON , 0, 0, 8, 0
00000000AB97B760,DISPATCHER, 0, 0, 0, 0
00000000AB994700,DISPATCHER, 0, 0, 0, 0
00000000AB995860,DISPATCHER, 0, 1, 16, 0
14:57:09 SQL> select
p.pname,
q.type,
q.totalq,
q.wait,
round(q.wait/nullif(q.totalq,0),2) avg_wait
from
v$queue q,
v$process p
where
p.addr(+) = q.paddr
/
14:57:21 2 14:57:21 3 14:57:21 4 14:57:21 5 14:57:21 6 14:57:21 7 14:57:21 8 14:57:21 9 14:57:21 10 14:57:21 11 14:57:21 12
PNAME,TYPE , TOTALQ, WAIT, AVG_WAIT
-----,----------,----------,----------,----------
D000 ,DISPATCHER, 0, 0,
D001 ,DISPATCHER, 0, 0,
D002 ,DISPATCHER, 16, 1, .06
,COMMON , 8, 0, 0
,COMMON , 8, 1, .13
14:57:21 SQL>
14:57:21 SQL>
Let us try to kill a session...
Load the server process now... to buy more time the routine is increased to 50
declare
a varchar2(100);
cursor c1 is
select * from dba_objects;
begin
for i in 1..50
loop
for c1_rec in c1
loop
a:=c1_rec.owner;
end loop;
dbms_output.put_line('exited '||i||' loop');
end loop;
end;
/
before kill....
14:57:21 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;15:01:27 2 15:01:27 3 15:01:27 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896
SHARED ,XXXX , 30, 77,SCOTT ,7xr6jha03c1wx,18/01/2019 15:00:27,18/01/2019 15:01:26,8477
15:01:27 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;15:01:37 2 15:01:37 3 15:01:37 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
SHARED ,XXXX , 30, 77,SCOTT ,7xr6jha03c1wx,18/01/2019 15:00:27,18/01/2019 15:01:34,8477
15:01:37 SQL> select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;
CIRCUIT ,DISPATCHER ,SERVER ,SADDR ,STATUS ,QUEUE
----------------,----------------,----------,----------------,----------------,----------------
00000000A3A5A4F8,00000000AB97B760,00000000AB,00000000ABC6ED48,NORMAL ,SERVER
, ,97C8C0 , , ,
15:01:43 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
15:01:50 2 from v$circuit c
15:01:50 3 ,v$session s
15:01:50 4 ,v$process p1
15:01:50 5 ,v$process p2
where c.saddr=s.saddr
15:01:50 6 15:01:50 7 and c.dispatcher=p1.addr
15:01:50 8 and c.server=p2.addr(+);
CIRCUIT ,DISP_,SS_NAME , SID, SERIAL#,STATUS ,QUEUE
----------------,-----,---------,----------,----------,----------------,----------------
00000000A3A5A4F8,D000 ,S000 , 30, 77,NORMAL ,SERVER
15:01:51 SQL> select * from v$queue;
PADDR ,TYPE , QUEUED, WAIT, TOTALQ, CON_ID
----------------,----------,----------,----------,----------,----------
00 ,COMMON , 0, 1, 22, 0
00 ,COMMON , 0, 0, 24, 0
00000000AB97B760,DISPATCHER, 0, 0, 14, 0
00000000AB994700,DISPATCHER, 0, 0, 15, 0
00000000AB995860,DISPATCHER, 0, 1, 16, 0
15:02:00 SQL> select
p.pname,
q.type,
q.totalq,
q.wait,
round(q.wait/nullif(q.totalq,0),2) avg_wait
from
v$queue q,
v$process p
where
p.addr(+) = q.paddr
/
15:02:20 2 15:02:20 3 15:02:20 4 15:02:20 5 15:02:20 6 15:02:20 7 15:02:20 8 15:02:20 9 15:02:20 10 15:02:20 11 15:02:20 12
PNAME,TYPE , TOTALQ, WAIT, AVG_WAIT
-----,----------,----------,----------,----------
D000 ,DISPATCHER, 14, 0, 0
D001 ,DISPATCHER, 15, 0, 0
D002 ,DISPATCHER, 16, 1, .06
,COMMON , 24, 0, 0
,COMMON , 22, 1, .05
so our target is sid 30
alter system kill session '30,77' immediate;
it took few secs before the session is cleared...
15:04:10 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;15:04:40 2 15:04:40 3 15:04:40 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896
SHARED ,XXXX , 30, 77,SCOTT ,7xr6jha03c1wx,18/01/2019 15:00:27,18/01/2019 15:04:36,8477
15:04:40 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process,status,state
15:05:06 2 from v$session
where username='SCOTT'
order by server;15:05:07 3 15:05:07 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS ,STATUS ,STATE
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------,--------,-------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896 ,INACTIVE,WAITING
15:05:07 SQL>
declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2099
Session ID: 30 Serial number: 77
alter system kill session '30,81' immediate;
15:06:09 SQL> alter system kill session '30,81' immediate;
System altered.
15:07:32 SQL> / <<< SEE @ 15:07 it still doing its job
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS ,STATUS ,STATE
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------,--------,-------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896 ,INACTIVE,WAITING
SHARED ,XXXX , 30, 81,SCOTT ,7xr6jha03c1wx,18/01/2019 15:05:53,18/01/2019 15:07:28,8477 ,ACTIVE ,WAITED SHORT TIME
15:07:33 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;15:08:15 2 15:08:15 3 15:08:15 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
SHARED ,XXXX , 30, 81,SCOTT ,7xr6jha03c1wx,18/01/2019 15:05:53,18/01/2019 15:08:13,8477
15:08:16 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
15:08:38 2 from v$circuit c
15:08:38 3 ,v$session s
,v$process p1
15:08:38 4 15:08:38 5 ,v$process p2
15:08:38 6 where c.saddr=s.saddr
and c.dispatcher=p1.addr
and c.server=p2.addr(+);15:08:38 7 15:08:38 8
CIRCUIT ,DISP_,SS_NAME , SID, SERIAL#,STATUS ,QUEUE
----------------,-----,---------,----------,----------,----------------,----------------
00000000A3A5A4F8,D002 ,S000 , 30, 81,EOF ,SERVER << the status show EOF - End of File communication, but the shared server is still doing the job!!!!!!!
15:08:38 SQL>
select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process,status,state
from v$session
where username='SCOTT'
order by server;
15:10:01 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process,status,state
from v$session
where username='SCOTT'
order by server;15:10:24 2 15:10:24 3 15:10:24 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS ,STATUS ,STATE
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------,--------,-------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896 ,INACTIVE,WAITING
SHARED ,XXXX , 30, 81,SCOTT ,7xr6jha03c1wx,18/01/2019 15:05:53,18/01/2019 15:10:21,8477 ,ACTIVE ,WAITED SHORT TIME
15:10:25 SQL>
Still active and my sqlplus session still alive!!!!!!
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS ,STATUS ,STATE
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------,--------,-------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896 ,INACTIVE,WAITING
15:11:00 SQL>
declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2099
Session ID: 30 Serial number: 81
Finally @ 15:11 it is removed, it took nearly 5 mins to clear the session.
Thanks - here we close this lab excercise. Next time will see how does shared server impact memory.
Setup:
2 init parameters are involved:
1) DISPATCHERS [specifies the #of dispatchers to start with, also it is possible to set very granular setting for protocol, address, description with more options],
& MAX_DISPATCHERS [used for backward compatibility, used to mention max # of dispatcher at any point it time, but it is overridden by dispatchers]
1) SHARED_SERVER [the min. # of processes to start, 0 or 1 based on DISPATCHER setting, 0 in case no dispatcher]
& MAX_SHARED_SERVER [the max # of shared servers allowed, if not set, either 1/8 of process if free slots available or 2 in case ,# of process < 24 is maximum allowed]
So to start with let us set, dispatchers alone, since the max clause or somehow controlled by this parameter and also shared_server will become 1 as soon as dispatchers is set and then we can evaluate it how busy the shared server is and how many more we need to start (even it automatically starts as long as we dont set the max clause).
1) preserve the current pfile
2) Evaluate what is already there
dispatcher params right now:
dispatchers string (PROTOCOL=TCP) (SERVICE=rmn01t
stXDB)
max_dispatchers integer
shared_server setting right now:
max_shared_servers integer
shared_servers integer 1 <<<< observe, here this parameter isnt set in pfile by default, but the shared_server count increased to 1 as mentioned in oracle param guide.
pfile snipet: [observe no shared_server setting]
*.db_recovery_file_dest_size=4800m
*.db_recovery_file_dest='/oradata_1/fast_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XXXXXDB)'
*.memory_target=1188m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.undo_tablespace='UNDOTBS1'
lsnrctl services:
Service "XXXXXDB" has 1 instance(s).
Instance "XXXX", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: host, pid: 2097>
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=48278))
The command completed successfully
3) Remove or clear the existing default configuration
SQL> select server,count(1) from v$session group by server;
SERVER COUNT(1)
--------- ----------
DEDICATED 32
see here, we dont have any existing shared server session, if there was 1, then we will see SHARED or NONE based on weather the session is actively serviced by the shared server.
SQL> alter system set dispatchers='' scope=both;
System altered.
SQL> select * from v$dispatcher;
no rows selected
SQL>
check listener service after a minute you will see the dispatcher is removed already.
A minute before:
Service "XXXXXDB" has 1 instance(s).
Instance "XXXX", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: host, pid: 2097>
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=48278))
The command completed successfully
A minute later:
Service "XXXXXDB" has 1 instance(s).
Instance "XXXX", status READY, has 0 handler(s) for this service...
The command completed successfully
Since the dispatcher wasnt handling any connection (connected clients) actively, the service died otherwise we will have to use the below command to stop the dispatcher...
ALTER SYSTEM SHUTDOWN IMMEDIATE 'D000'; => to stop accepting new connection and kill the existing connection.
shared server is still alive:
00000000AB97C8C0 2099 S000 oracle@host (S000) <<< the shared server is still left asis, I think it wont go off until the instance is restarted or explictly we set the parameter to 0 [which will be another simple excercise]
4) Setup the fresh one as per the need
alter system set dispatchers='(PROTOCOL=TCP)(DISPATCHERS=3)' scope=both;
before this command was fired:
Service "XXXX" has 1 instance(s).
Instance "XXXX", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
after this command was fired:
Service "XXXX" has 1 instance(s).
Instance "XXXX", status READY, has 4 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: host, pid: 3605>
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=40302))
"D001" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: host, pid: 3607>
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=46767))
"D002" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: host, pid: 3609>
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=60605))
select * from v$dispatcher_config;
CONF_INDX NETWORK DISPATCHERS CONNECTIONS SESSIONS MULT LISTENER SERVICE CON_ID
---------- ------------------------------------------------ ----------- ----------- ---------- ---- -------------------------------------------------------------------------------- ---------- ----------
0 (ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP)) 3 1022 1022 OFF (ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521)) XXXX 0
select * from v$dispatcher;
NAME NETWORK PADDR STATUS ACC MESSAGES BYTES BREAKS OWNED CREATED IDLE BUSY LISTENER CONF_INDX CON_ID
---- ------------------------------------------------ ---------------- ---------------- --- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=40302)) 00000000AB97B760 WAIT YES 0 0 0 0 0 594042 1 0 0 0
D001 (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=46767)) 00000000AB994700 WAIT YES 0 0 0 0 0 594039 0 0 0 0
D002 (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=60605)) 00000000AB995860 WAIT YES 0 0 0 0 0 594035 1 0 0 0
dispatchers are now ready and shared server available as well..
SQL> select addr,spid,pname,program from v$process where pname like 'S%' order by pname;
ADDR SPID PNAME PROGRAM
---------------- ------------------------ ----- ------------------------------------------------
00000000AB97C8C0 2099 S000 oracle@host (S000)
..
5) let us try doing the operation using shared server and query the necessary views
to examine connection/session:
v$circuit
v$session
to examine the common queue - owner of VC & queue statistics:
v$queue
v$reqdist
to examine weather shared server usage is optimal:
v$shared_server
v$shared_server_monitor
Dispatcher monitoring, as per oracle documentation, the work done @ dispatcher is really swift, hence most of the time it is the shared server which struggles.
a) TNS entry with "SERVER=SHARED"
Service "XXXX" has 1 instance(s).
Instance "XXXX", status READY, has 4 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
..
"D002" established:1 refused:0 current:1 max:1022 state:ready <<<<<<<< look D002 has 1 connection established
DISPATCHER <machine: host, pid: 3609>
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=60605))
this is shared connection...
[oracle@host admin]$ sqlplus scott/****@<DB>_shared
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 18 13:47:25 2019 <<< time of shared server
Copyright (c) 1982, 2013, Oracle. All rights reserved.
this is dedicated connection...
[oracle@host ~]$ sqlplus scott/****@<DB>
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 18 14:14:39 2019 <<< time of dedicated server
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Fri Jan 18 2019 13:47:25 +08:00
See the dedicated process list below..
[oracle@host lab18Jan2019]$ ps -ef|grep -i local=no
oracle 7898 1 0 14:14 ? 00:00:00 oracleXXXX (LOCAL=NO) <<< time of dedicated server
so no dedicated process spawned for shared server.
Query 1)
col server for a10
col service_name for a12
col username for a9
col program for a45
col module for a15
set lines 300
set pages 3000
alter session set nls_date_Format='DD/MM/YYYY HH24:MI:SS';
select server,service_name,sid,serial#,username,program,module,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;
14:14:06 SQL> select server,service_name,sid,serial#,username,program,module,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;14:15:59 2 14:15:59 3 14:15:59 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,PROGRAM ,MODULE ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,---------------------------------------------,---------------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX , 40, 657,SCOTT ,sqlplus@host (TNS V1-V3) ,SQL*Plus , ,18/01/2019 14:14:40, ,7896
NONE ,XXXX , 34, 603,SCOTT ,sqlplus@host (TNS V1-V3) ,SQL*Plus , ,18/01/2019 13:47:25, ,7509
14:15:59 SQL>
select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;
14:15:59 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;14:16:21 2 14:16:21 3 14:16:21 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896
NONE ,XXXX , 34, 603,SCOTT , ,18/01/2019 13:47:25, ,7509
14:16:21 SQL>
Notice above the shared server session 34 (SCOTT) - with NONE under server column. It is good that it shows client process ids.
oracle 7509 2762 0 13:47 pts/1 00:00:00 sqlplus << shared server sqlplus conn
oracle 7896 2888 0 14:14 pts/2 00:00:00 sqlplus << dedicated process sqlplus conn
let us check now the shared server views....
Query 2) select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;
14:16:21 SQL> select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;
CIRCUIT ,DISPATCHER ,SERVER ,SADDR ,STATUS ,QUEUE
----------------,----------------,----------,----------------,----------------,----------------
00000000A3A5A4F8,00000000AB995860,00 ,00000000ABC66DA8,NORMAL ,NONE
So there is a session address...
select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;
14:18:22 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;14:18:37 2 14:18:37 3 14:18:37 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
NONE ,XXXX , 34, 603,SCOTT , ,18/01/2019 13:47:25, ,7509
14:18:37 SQL>
Being this session is in idle state, the shared server address is "00" and we get to see the circuit and dispatcher addresses fine.
select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
from v$circuit c
,v$session s
,v$process p1
,v$process p2
where c.saddr=s.saddr
and c.dispatcher=p1.addr
and c.server=p2.addr(+);
14:36:34 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
14:36:44 2 from v$circuit c
14:36:44 3 ,v$session s
14:36:44 4 ,v$process p1
14:36:44 5 ,v$process p2
14:36:44 6 where c.saddr=s.saddr
and c.dispatcher=p1.addr
and c.server=p2.addr(+);
14:36:44 7 14:36:44 8
CIRCUIT ,DISP_,SS_NAME , SID, SERIAL#,STATUS ,QUEUE
----------------,-----,---------,----------,----------,----------------,----------------
00000000A3A5A4F8,D002 ,NO_SERVER, 34, 603,NORMAL ,NONE
So this matches with what we observed.
Query 3) examine queue
SELECT
ksppinm,
ksppstvl
FROM
x$ksppi a,
x$ksppsv b
WHERE
a.indx=b.indx
AND
substr(ksppinm,1,8) like '%_shared_%'
ORDER BY ksppinm
/
14:42:14 SQL> /
KSPPINM ,KSPPSTVL
-----------------------------------,----------
..
_shared_server_load_balance ,0
_shared_server_num_queues ,2 << notice here 2 common queues vs 1 common queue.
11 rows selected.
14:39:36 SQL> select * from v$queue;
PADDR ,TYPE , QUEUED, WAIT, TOTALQ, CON_ID
----------------,----------,----------,----------,----------,----------
00 ,COMMON , 0, 1, 7, 0
00 ,COMMON , 0, 0, 7, 0 << 2 common queue
00000000AB97B760,DISPATCHER, 0, 0, 0, 0
00000000AB994700,DISPATCHER, 0, 0, 0, 0
00000000AB995860,DISPATCHER, 0, 1, 14, 0 << 3 dispatcher queues
select
p.pname,
q.type,
q.totalq,
q.wait,
round(q.wait/nullif(q.totalq,0),2) avg_wait
from
v$queue q,
v$process p
where
p.addr(+) = q.paddr
/
PNAME,TYPE , TOTALQ, WAIT, AVG_WAIT
-----,----------,----------,----------,----------
D000 ,DISPATCHER, 0, 0,
D001 ,DISPATCHER, 0, 0,
D002 ,DISPATCHER, 14, 1, .07
,COMMON , 7, 0, 0
,COMMON , 7, 1, .14
So there are 2 common queue and 3 dispatcher queue. The session is no queue now, staying idle.
Load the server process now...
declare
a varchar2(100);
cursor c1 is
select * from dba_objects;
begin
for i in 1..20
loop
for c1_rec in c1
loop
a:=c1_rec.owner;
end loop;
dbms_output.put_line('exited '||i||' loop');
end loop;
end;
/
With server loaded:
14:52:15 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;14:52:26 2 14:52:26 3 14:52:26 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896
SHARED ,XXXX , 34, 603,SCOTT ,7xr6jha03c1wx,18/01/2019 13:47:25,18/01/2019 14:52:25,7509 <<< NONE to SHARED
14:52:27 SQL> select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;
CIRCUIT ,DISPATCHER ,SERVER ,SADDR ,STATUS ,QUEUE
----------------,----------------,----------,----------------,----------------,----------------
00000000A3A5A4F8,00000000AB995860,00000000AB,00000000ABC66DA8,NORMAL ,SERVER <<< SERVER QUEUE from NONE
, ,97C8C0 , , ,
14:52:36 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;14:52:45 2 14:52:45 3 14:52:45 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
SHARED ,XXXX , 34, 603,SCOTT ,7xr6jha03c1wx,18/01/2019 13:47:25,18/01/2019 14:52:41,7509
14:52:45 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
14:53:01 2 from v$circuit c
14:53:01 3 ,v$session s
14:53:01 4 ,v$process p1
,v$process p2
14:53:01 5 14:53:01 6 where c.saddr=s.saddr
14:53:01 7 and c.dispatcher=p1.addr
14:53:01 8 and c.server=p2.addr(+);
CIRCUIT ,DISP_,SS_NAME , SID, SERIAL#,STATUS ,QUEUE
----------------,-----,---------,----------,----------,----------------,----------------
00000000A3A5A4F8,D002 ,S000 , 34, 603,NORMAL ,SERVER <<<<< S000 shared server is processing the request
14:53:01 SQL> select * from v$queue;
PADDR ,TYPE , QUEUED, WAIT, TOTALQ, CON_ID
----------------,----------,----------,----------,----------,----------
00 ,COMMON , 0, 1, 7, 0
00 ,COMMON , 0, 0, 8, 0
00000000AB97B760,DISPATCHER, 0, 0, 0, 0
00000000AB994700,DISPATCHER, 0, 0, 0, 0
00000000AB995860,DISPATCHER, 0, 1, 14, 0
14:53:31 SQL> select
p.pname,
q.type,
q.totalq,
q.wait,
round(q.wait/nullif(q.totalq,0),2) avg_wait
from
v$queue q,
v$process p
where
p.addr(+) = q.paddr
/14:53:43 2 14:53:43 3 14:53:43 4 14:53:43 5 14:53:43 6 14:53:43 7 14:53:43 8 14:53:43 9 14:53:43 10 14:53:43 11 14:53:43 12
PNAME,TYPE , TOTALQ, WAIT, AVG_WAIT
-----,----------,----------,----------,----------
D000 ,DISPATCHER, 0, 0,
D001 ,DISPATCHER, 0, 0,
D002 ,DISPATCHER, 14, 1, .07
,COMMON , 8, 0, 0
,COMMON , 7, 1, .14
14:53:43 SQL>
Now let us gracefully exit the session.
-----
SQL> disc
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
-----
14:53:43 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;14:56:27 2 14:56:27 3 14:56:27 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896
14:56:27 SQL> select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;
CIRCUIT ,DISPATCHER ,SERVER ,SADDR ,STATUS ,QUEUE
----------------,----------------,----------,----------------,----------------,----------------
00000000A3A5A4F8,00000000AB995860,00 ,00 ,NORMAL ,NONE << CIRCUIT stays but there are no session user connection/session associated
14:56:37 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;14:56:50 2 14:56:50 3 14:56:50 4
no rows selected
14:56:50 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
14:57:01 2 from v$circuit c
14:57:01 3 ,v$session s
14:57:01 4 ,v$process p1
14:57:01 5 ,v$process p2
14:57:01 6 where c.saddr=s.saddr
14:57:01 7 and c.dispatcher=p1.addr
and c.server=p2.addr(+);14:57:01 8
no rows selected
14:57:02 SQL> select * from v$queue;
PADDR ,TYPE , QUEUED, WAIT, TOTALQ, CON_ID
----------------,----------,----------,----------,----------,----------
00 ,COMMON , 0, 1, 8, 0
00 ,COMMON , 0, 0, 8, 0
00000000AB97B760,DISPATCHER, 0, 0, 0, 0
00000000AB994700,DISPATCHER, 0, 0, 0, 0
00000000AB995860,DISPATCHER, 0, 1, 16, 0
14:57:09 SQL> select
p.pname,
q.type,
q.totalq,
q.wait,
round(q.wait/nullif(q.totalq,0),2) avg_wait
from
v$queue q,
v$process p
where
p.addr(+) = q.paddr
/
14:57:21 2 14:57:21 3 14:57:21 4 14:57:21 5 14:57:21 6 14:57:21 7 14:57:21 8 14:57:21 9 14:57:21 10 14:57:21 11 14:57:21 12
PNAME,TYPE , TOTALQ, WAIT, AVG_WAIT
-----,----------,----------,----------,----------
D000 ,DISPATCHER, 0, 0,
D001 ,DISPATCHER, 0, 0,
D002 ,DISPATCHER, 16, 1, .06
,COMMON , 8, 0, 0
,COMMON , 8, 1, .13
14:57:21 SQL>
14:57:21 SQL>
Let us try to kill a session...
Load the server process now... to buy more time the routine is increased to 50
declare
a varchar2(100);
cursor c1 is
select * from dba_objects;
begin
for i in 1..50
loop
for c1_rec in c1
loop
a:=c1_rec.owner;
end loop;
dbms_output.put_line('exited '||i||' loop');
end loop;
end;
/
before kill....
14:57:21 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;15:01:27 2 15:01:27 3 15:01:27 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896
SHARED ,XXXX , 30, 77,SCOTT ,7xr6jha03c1wx,18/01/2019 15:00:27,18/01/2019 15:01:26,8477
15:01:27 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;15:01:37 2 15:01:37 3 15:01:37 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
SHARED ,XXXX , 30, 77,SCOTT ,7xr6jha03c1wx,18/01/2019 15:00:27,18/01/2019 15:01:34,8477
15:01:37 SQL> select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;
CIRCUIT ,DISPATCHER ,SERVER ,SADDR ,STATUS ,QUEUE
----------------,----------------,----------,----------------,----------------,----------------
00000000A3A5A4F8,00000000AB97B760,00000000AB,00000000ABC6ED48,NORMAL ,SERVER
, ,97C8C0 , , ,
15:01:43 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
15:01:50 2 from v$circuit c
15:01:50 3 ,v$session s
15:01:50 4 ,v$process p1
15:01:50 5 ,v$process p2
where c.saddr=s.saddr
15:01:50 6 15:01:50 7 and c.dispatcher=p1.addr
15:01:50 8 and c.server=p2.addr(+);
CIRCUIT ,DISP_,SS_NAME , SID, SERIAL#,STATUS ,QUEUE
----------------,-----,---------,----------,----------,----------------,----------------
00000000A3A5A4F8,D000 ,S000 , 30, 77,NORMAL ,SERVER
15:01:51 SQL> select * from v$queue;
PADDR ,TYPE , QUEUED, WAIT, TOTALQ, CON_ID
----------------,----------,----------,----------,----------,----------
00 ,COMMON , 0, 1, 22, 0
00 ,COMMON , 0, 0, 24, 0
00000000AB97B760,DISPATCHER, 0, 0, 14, 0
00000000AB994700,DISPATCHER, 0, 0, 15, 0
00000000AB995860,DISPATCHER, 0, 1, 16, 0
15:02:00 SQL> select
p.pname,
q.type,
q.totalq,
q.wait,
round(q.wait/nullif(q.totalq,0),2) avg_wait
from
v$queue q,
v$process p
where
p.addr(+) = q.paddr
/
15:02:20 2 15:02:20 3 15:02:20 4 15:02:20 5 15:02:20 6 15:02:20 7 15:02:20 8 15:02:20 9 15:02:20 10 15:02:20 11 15:02:20 12
PNAME,TYPE , TOTALQ, WAIT, AVG_WAIT
-----,----------,----------,----------,----------
D000 ,DISPATCHER, 14, 0, 0
D001 ,DISPATCHER, 15, 0, 0
D002 ,DISPATCHER, 16, 1, .06
,COMMON , 24, 0, 0
,COMMON , 22, 1, .05
so our target is sid 30
alter system kill session '30,77' immediate;
it took few secs before the session is cleared...
15:04:10 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;15:04:40 2 15:04:40 3 15:04:40 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896
SHARED ,XXXX , 30, 77,SCOTT ,7xr6jha03c1wx,18/01/2019 15:00:27,18/01/2019 15:04:36,8477
15:04:40 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process,status,state
15:05:06 2 from v$session
where username='SCOTT'
order by server;15:05:07 3 15:05:07 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS ,STATUS ,STATE
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------,--------,-------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896 ,INACTIVE,WAITING
15:05:07 SQL>
declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2099
Session ID: 30 Serial number: 77
alter system kill session '30,81' immediate;
15:06:09 SQL> alter system kill session '30,81' immediate;
System altered.
15:07:32 SQL> / <<< SEE @ 15:07 it still doing its job
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS ,STATUS ,STATE
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------,--------,-------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896 ,INACTIVE,WAITING
SHARED ,XXXX , 30, 81,SCOTT ,7xr6jha03c1wx,18/01/2019 15:05:53,18/01/2019 15:07:28,8477 ,ACTIVE ,WAITED SHORT TIME
15:07:33 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;15:08:15 2 15:08:15 3 15:08:15 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
SHARED ,XXXX , 30, 81,SCOTT ,7xr6jha03c1wx,18/01/2019 15:05:53,18/01/2019 15:08:13,8477
15:08:16 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
15:08:38 2 from v$circuit c
15:08:38 3 ,v$session s
,v$process p1
15:08:38 4 15:08:38 5 ,v$process p2
15:08:38 6 where c.saddr=s.saddr
and c.dispatcher=p1.addr
and c.server=p2.addr(+);15:08:38 7 15:08:38 8
CIRCUIT ,DISP_,SS_NAME , SID, SERIAL#,STATUS ,QUEUE
----------------,-----,---------,----------,----------,----------------,----------------
00000000A3A5A4F8,D002 ,S000 , 30, 81,EOF ,SERVER << the status show EOF - End of File communication, but the shared server is still doing the job!!!!!!!
15:08:38 SQL>
select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process,status,state
from v$session
where username='SCOTT'
order by server;
15:10:01 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process,status,state
from v$session
where username='SCOTT'
order by server;15:10:24 2 15:10:24 3 15:10:24 4
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS ,STATUS ,STATE
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------,--------,-------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896 ,INACTIVE,WAITING
SHARED ,XXXX , 30, 81,SCOTT ,7xr6jha03c1wx,18/01/2019 15:05:53,18/01/2019 15:10:21,8477 ,ACTIVE ,WAITED SHORT TIME
15:10:25 SQL>
Still active and my sqlplus session still alive!!!!!!
SERVER ,SERVICE_NAME, SID, SERIAL#,USERNAME ,SQL_ID ,LOGON_TIME ,SQL_EXEC_START ,PROCESS ,STATUS ,STATE
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------,--------,-------------------
DEDICATED ,XXXX , 40, 657,SCOTT , ,18/01/2019 14:14:40, ,7896 ,INACTIVE,WAITING
15:11:00 SQL>
declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2099
Session ID: 30 Serial number: 81
Finally @ 15:11 it is removed, it took nearly 5 mins to clear the session.
Thanks - here we close this lab excercise. Next time will see how does shared server impact memory.
No comments:
Post a Comment