Re: os users, sessions, and connections.

"DaLoverhino" <DaLoveRhino_at_hotmail.com> wrote in message news:1139274175.910349.269400_at_g14g2000cwa.googlegroups.com...
> I've been reading about the difference between connections and sessions
> on Kyte's site and this group. It's a bit rough for me, so I got a few
> questions. I'm also having a hard time figuring out how it could be
> applied to auditing when the OS user uses an app to do database work,
> and the app may have 'connection pooling'.
>
> Here's a few items:
>
> 1. From what I've read a session is a logged connection. Does that mean
> there's no such thing as a connectionless session?
>
> 2. Can you have one connection to multiple sessions?
>
> 3. If an application has (for example) one connection to the database
> but has two users using the app to do DB work, what does the app do?
> Assume that each user is doing his work as if simultaneously (not
> taking turns to make changes.) Does the program open up two sessions
> and switch between the two?
>
> 4. If only one user is used by the app to do the db changes of many OS
> users, how would I know which OS user did what?
>
> Plus, if you have a book you know that really points this stuff out,
> that would be great too.
>
> thanks.
>

A program can create a process that attaches to the Oracle instance, this is a connection and appears in v$process.

The same program can then use that one process to carry several sessions - each one appears in v$session.

The simplest way to see this is to use SQL*Plus to connect to an account that can query v$session, and do the following (paddr is the address of the process through which a session is connected).

select sid, serial#, paddr, username from v$session where username = {me}

set autotrace on

select sid, serial#, paddr, username from v$session where username = {me}

set autotrace off

An example results I got from running this as 'SYS' are:

SQL> select sid, serial#, paddr, username from v$session where username = 'SYS';        SID SERIAL# PADDR USERNAME

---------- ---------- -------- ------------------------------
        10      44880 6C23FCCC SYS

1 row selected.

SQL> set autotrace on
SQL> select sid, serial#, paddr, username from v$session where username = 'SYS';        SID SERIAL# PADDR USERNAME

---------- ---------- -------- ------------------------------
         9       4168 6C23FCCC SYS
        10      44880 6C23FCCC SYS

2 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 FIXED TABLE (FULL) OF 'X$KSUSE' Statistics

          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        591  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> set autotrace off
SQL> As you can see, SQL*Plus implements autotrace by creating a second session (that connects as SYS) to query the session stats for the first session. The second row comes into existence when you enable autotrace, and uses the same process (paddr) as the first.

As far as auditing, triggers, context etc. are concerned, the second session is isolated from the first, and operates under the account name reported. (In my case SYS).

Part of your confusion probably lies in the fact that there are two mechanisms that are called 'connection pooling'. one of them is the above - a single connection is shared by many sessions (like car-pooling). This is useful for an application operating over an INTRA-net, where you have a known client base, and each individual has a database account that can be identified.

However, very few people do this. The commoner interpretation of 'connection pooling' is used to describe how a program creates many connections, with a single session operating on each connection, all sessions using an 'application account'. An incoming request is simply routed to whichever connection/session happens to be free. In this context, auditing etc. is the application's problem - the database doesn't know that all the different queries are coming from different individuals; everything is operating under a single database account.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 2nd Feb 2006