Virtual Circuits – The essay part 1

As you may know from Shared Server architecture, one components of Shared Server is not a process but an abstraction of the user session and it is called Virtual Circuit. All the communication between the Dispatcher and Shared Server is made by passing the ownership of Virtual Circuit from Dispatcher to Shared Server and vice-versa.

Part 1.1 – v$circuit

To really understand that abstraction of the user session to the database through dispatchers and servers Oracle gives us a view called v$circuit.
This view contains information that can be used to correlacte the circuit itself with the dispatcher or the shared server.

SQL> select circuit, dispatcher, server, waiter, saddr, status from v$circuit;

CIRCUIT DISPATCHER SERVER WAITER SADDR STATUS
---------------- ---------------- ---------------- ---------------- ---------------- ----------------
0000000071D9F750 0000000075498608 00 00 000000007572DB78 NORMAL
0000000071DA0468 00000000754996B8 00 00 00000000757DA788 NORMAL
0000000071DA1180 000000007549A768 000000007549D978 00 0000000075704048 NORMAL
0000000071DA1E98 000000007549A768 00 00 0000000075706FF0 NORMAL
0000000071DA2BB0 00000000754996B8 00 00 00000000756F81A8 NORMAL
0000000071DA38C8 00000000754996B8 00 00 00000000756E3410 NORMAL
0000000071DA45E0 0000000075498608 00 00 000000007572ABD0 NORMAL
0000000071DA52F8 000000007549A768 00 00 0000000075712E90 NORMAL
0000000071DA6010 00000000754996B8 000000007549B818 00 000000007571BD88 NORMAL
0000000071DA6D28 00000000754996B8 00 00 0000000075709F98 NORMAL
0000000071DA7A40 0000000075498608 000000007549C8C8 000000007549C8C8 00000000756DD4C0 NORMAL
CIRCUIT DISPATCHER SERVER WAITER SADDR STATUS
---------------- ---------------- ---------------- ---------------- ---------------- ----------------
0000000071DA8758 000000007549A768 00 00 00000000756E0468 NORMAL

All the first 5 columns (circuit, dispatcher, server, waiter and saddr) represents a process address that you can link with v$process or v$session (in case of saddr) easily (except circuit cause they are not considered an Oracle process). One virtual circuit represents exactly one client connection. You can learn more in my previous post about Virtual Circuit waits.

You can now understand that some circuit is associated with a certain dispatcher, and some circuits are “in work” already with shared servers (SSERV column not null). On QUEUE column, you see “where are the circuit right now” where NONE is representing an idle circuit and COMMON representing the Common Queue, where it stays until a shared server picks up.
The column STATUS shows the status of circuit itself. NORMAL means that is a normal circuit inside a local database and not from a remote database. Another version of the query more complete to get the session id:

SQL> select v.circuit, p.pname dispatcher, p2.pname sserver, s.sid, 
s.serial#,v.status,v.queue
from v$circuit v, v$process p, v$process p2, v$session s
where v.dispatcher=p.addr
and v.server=p2.addr (+)
and v.saddr = s.saddr;


CIRCUIT DISPA SSERV SID SERIAL# STATUS QUEUE
---------------- ----- ----- ---------- ---------- ---------------- ----------------
0000000071D9F750 D000 81 1 NORMAL NONE
0000000071DA45E0 D000 82 241 NORMAL NONE
0000000071DA7A40 D000 S001 108 8455 NORMAL SERVER
0000000071DA0468 D001 23 19 NORMAL NONE
0000000071DA2BB0 D001 99 1 NORMAL NONE
0000000071DA38C8 D001 106 1071 NORMAL NONE
0000000071DA6D28 D001 93 315 NORMAL NONE
0000000071DA6010 D001 S002 87 95 NORMAL SERVER
0000000071DA1E98 D002 94 1 NORMAL NONE
0000000071DA52F8 D002 90 51 NORMAL NONE
0000000071DA8758 D002 107 8075 NORMAL NONE
CIRCUIT DISPA SSERV SID SERIAL# STATUS QUEUE
---------------- ----- ----- ---------- ---------- ---------------- ----------------
0000000071DA9470 D002 109 10163 NORMAL NONE
0000000071DA1180 D002 S003 95 1 NORMAL SERVER

You can also get more information related with work made my a specified circuit for example, how many bytes that have gone through and the total number of messages:

SQL> select circuit, messages, bytes, s.sid, s.serial# from v$circuit v, v$session s where v.saddr = s.saddr; 

CIRCUIT MESSAGES BYTES SID SERIAL#
---------------- ---------- ---------- ---------- ----------
0000000071D9F750 29736 2928666 81 1
0000000071DA0468 23384 9445256 23 19
0000000071DA1180 2817 846867 95 1
0000000071DA1E98 68106 4026800 94 1
0000000071DA2BB0 30 9132 99 1
0000000071DA38C8 11647 5393486 106 1071
0000000071DA45E0 14441 6457773 82 241
0000000071DA52F8 8484 958868 90 51
0000000071DA6010 17322 4315382 87 95
0000000071DA6D28 153 64840 93 315
0000000071DA7A40 739 336320 108 8455
CIRCUIT MESSAGES BYTES SID SERIAL#
---------------- ---------- ---------- ---------- ----------
0000000071DA8758 828 98413 107 8075
0000000071DA9470 154 25172 109 10163

Part 1.2 – Where is my virtual circuit

The whole job of a virtual circuits costs you SGA memory and more precisely shared pool memory.If you check v$fixed_table you will find that v$sgastat is a view that querys (apart from other tables) x$ksmss. This particular x$ksmss gives you the shared pool stats, including what is the subpool used for allocating virtual circuits by the column KSMDSIDX. On this particular example it is using subpool 1.

SQL> select * from v$sgastat where name = 'VIRTUAL CIRCUITS';

POOL NAME BYTES
------------ -------------------------- ----------
shared pool VIRTUAL CIRCUITS 828760

SQL> select * from x$ksmss where ksmssnam like '%VIRTUAL%';

ADDR INDX INST_ID KSMSSLEN KSMSSNAM KSMDSIDX
---------------- ---------- ---------- ---------- -------------------------- ----------
00007F59B3F09E68 50 1 828760 VIRTUAL CIRCUITS 1

You can actually manage the total number of virtual circuits allowed for your connections. Of course, any change on CIRCUITS parameter will probably change your shared pool memory allocation at system startup. I really recommend no changes on this parameter except for testing purposes or you can run into a really odd results. After changing this parameter let’s bounce the instance.

SQL> alter system set circuits = 2 scope=both;
System altered.

SQL> select * from v$sgastat where name = 'VIRTUAL CIRCUITS';

POOL NAME BYTES
------------ -------------------------- ----------
shared pool VIRTUAL CIRCUITS 7520

As you may see, you shared pool memory usage for virtual circuit allocation has dropped to 7520 bytes. If you set your CIRCUITS parameter too low you will find this in the alert.log, and your clients cannot connect to database:

 TNS-00513: Destination host unreachable 

There is much to say regarding this topic, mainly in Virtual Circuit related wait events but that will be another post. Thank you.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s