Sunday, October 28, 2012

6 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

6 comments:

  1. Bluehost is ultimately the best web-hosting provider for any hosting plans you might require.

    ReplyDelete
  2. Find the best essays on is my friend's profile page. session replay

    ReplyDelete
  3. Below you will understand what is important, the idea provides one of the links with an exciting site: https://www.pageview.com/

    ReplyDelete
  4. Cartier watches boast a large variety replica watches of design and functionality, replica calibre de cartier watches yet maintain replica cartier tank mc watches the utmost quality and sophistication in every series. Crafted from the finest steel, rose gold, yellow gold, and white gold, and utlizing the highest grade of diamonds worthy of the Cartier name, Cartier has dominated the industry of elegant watches. Mens and ladies watches alike are assured attention to detail internally and externally, and the namesake itself is a milestone in the world for which the standard of reputation alone is reminiscent of the product.

    ReplyDelete
  5. The selected model is represented on charts using Tableau, which proves to be a very efficient tool. The model is then experimented and then applied to solve the problem. data science course in hyderabad

    ReplyDelete

 

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