Sunday, October 28, 2012

2 Connection, Session and UGA

When user use the application ( such as Sqlplus ) connect to the database server. The client application would start the client process. Oracle database would respond with Server processes to process use’s request, such as query.  Each connection can have more than 1 sessions. UGA is session memory. In the dedicate server architecture, UGA store in the PGA. In the shared server architecture, UGA store in the SGA( Large Pool).

  • V$PROCESS contains all the connections in the database instance.
  • V$SESSION contains all the sessions in the database instance.

Description of Figure 15-3 follows

* image from Oracle

Server process and Client process may not be one to one mapping.

Below demo is from Oracle document . It create 2 sessions in one connections.

First, list the current session own by SYS.

SELECT SID, SERIAL#, PADDR,program FROM V$SESSION WHERE USERNAME ='SYS';

image

Enable the autotrace which create the another session. As below screenshot shows the SID 148 and 151 has same PADDR( Process address).

disconnect command only terminate the sessions but not process.

image

open another session and query the process address .As you can see from below screen shot, the PID 27 still exist, but there is no more session ID associate with that process.

SELECT SID, SERIAL#, PADDR,program FROM V$SESSION WHERE USERNAME ='SYS';
SELECT PID,SPID,ADDR,PNAME,USERNAME,PROGRAM FROM V$PROCESS WHERE ADDR = HEXTORAW('5FFDEB88');

image

Dedicated Server Architecture:

Server process and client process is one to one. Each Server process would have its own PGA

Description of Figure 16-6 follows

* image from Oracle

Shared Server Architecture

dispatcher put the user request to the large pools’ request queue. Shared Server processes retrieve the user request from Request Queue as first come first serve. After shared server process complete the request, it put it back to response queue. Dispatcher would pick it up and return the result to the user.

Description of Figure 16-7 follows

* image from Oracle

The advantage for using shared server mode is it reduce the memory requirement for handling large number of user requests.

Show MAX Total UGA memory used and current total UGA memory used

SELECT
  a.value "MAX Total Memory (MB)",
  b.value "Total Memory (MB)"
FROM
  (
    SELECT
      ROUND(SUM(value)/1024/1024) AS value
    FROM
      V$SESSTAT,
      V$STATNAME
    WHERE
      name                    = 'session uga memory max'
    AND V$STATNAME.STATISTIC# = V$SESSTAT.STATISTIC#
  )
  A,
  (
    SELECT
      ROUND(SUM(value)/1024/1024) AS value
    FROM
      V$SESSTAT,
      V$STATNAME
    WHERE
      name                    = 'session uga memory'
    AND V$STATNAME.STATISTIC# = V$SESSTAT.STATISTIC#
  )
  B

 

image

Show the UGA memory usage for all sessions.

SELECT
  a.sid,
  C.USERNAME,
  C.MACHINE ,
  C.program,
  c.osuser,
  c.PROCESS,
  c.LOGON_TIME ,
  a.value
FROM
  V$SESSTAT a,
  V$STATNAME B,
  V$SESSION C
WHERE
  B.name        = 'session uga memory'
AND C.sid       = a.sid
AND a.STATISTIC#=B.STATISTIC#
ORDER BY
  a.value DESC

image

Reference

http://docs.oracle.com/cd/E25054_01/server.1111/e25789/process.htm#i18532

2 comments:

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates