some questions
Brain.Xu, August 13, 2002 - 12:18 am UTC
Hi,tom:
you said"I can have a session via a shared server whereby unless I'm active -- I don't have a process (many sessions share a process)." i think that is a procedure of a "user process" idle for v$session "inactive" and when a user process contact with server process,it's active.right?
i puzzled about "session" concept, I understand process concept(Oracle use server process process user's request or job.it's a really existed things),but don't see difference about it and session so.
Thanks,Tom!
August 13, 2002 - 8:30 am UTC
I do not understand the text in the first paragraph.
In shared server - multi-threaded server -- a user has a session. A session is your login, it means you can do SQL, you are connected and authenticated to the database. You have a row in v$session.
When you are not doing SQL, you are inactive and you will NOT be associated with any process. In shared server, there might be 10 shared servers (just like a dedicated server) but you might have 1000 sessions -- all of the sessions will be sharing these processes. It is like a connection pool.
When a shared server session becomes active, it is associated with some process while active. when it is done being active, it is dis-associated from that process. It only uses a process for the duration of the request/call to the database.
A session is your session.
A process is something you might use from time to time during your session.
Your session does not have a process (in general). A process does not have sessions. A process is a resource used by sessions over time.
Excellent Answer
Leo, August 13, 2002 - 10:14 am UTC
thank you for your answer!
Brain.Xu, August 15, 2002 - 9:04 pm UTC
A reader, December 24, 2002 - 1:17 pm UTC
Where will the PGA in MTS process?
Is it in dispatcher??
December 24, 2002 - 1:39 pm UTC
shared server -- pga = PROCESS global area, process specific memory. It's in the dedicated or shared servers.
A reader, January 31, 2003 - 12:49 pm UTC
Hi Tom,
I am running one sql statement from sql*plus, after fetching some records it hangs.The command column in v$session is 3. I open couple other sessions and when I run same sql statement same thing is happning. If I check from v$session command shows 3 for three sessions(I.e select statement).
I started query at 11:00 AM and I am running below sql at 12:30PM
It seems all three session is hanging for the same query.
Why it is hanging ??
I check v$process table and column
LATCHWAIT shows me value '8431269C' for all three session.
Is it waiting for latch ?
Here is the outcome of my query
1 select sid,paddr,latchwait,status,c.name from v$session a,v$process b,
2 v$latch c,
3 v$latch_children d
4 where a.paddr = b.addr
5 and b.latchwait = d.addr
6 and c.latch# = d.latch#
7 and c.level# = d.level#
8* and a.command = 3
SQL> /
SID PADDR LATCHWAI STATUS NAME
---------- -------- -------- -------- ------------------------------------------------
24 8409A39C 8431269C ACTIVE cache buffers chains
27 8409A69C 8431269C ACTIVE cache buffers chains
I have never seen case like this .
Please advice.
Thanks
January 31, 2003 - 12:54 pm UTC
consider opening a tar i would suggest.
A reader, January 31, 2003 - 2:34 pm UTC
SQL> SELECT ADDR,PROGRAM,LATCHWAIT FROM V$PROCESS
2 WHERE LATCHWAIT IS NOT NULL;
ADDR PROGRAM LATCHWAI
-------- ------------------------------------------------ --------
84095E9C oracle@mhdev1 (DBW0) 8431269C <<------
8409649C oracle@mhdev1 (CKPT) 8431269C <<------
8409A69C oracle@mhdev1 (TNS V1-V3) 8431269C
8409B59C oracle@mhdev1 (TNS V1-V3) 8431269C
What do you think about DBW0 and CKPT ??
Please explain what is going on here.
Thanks
January 31, 2003 - 3:34 pm UTC
I think you should contact support and open a tar....
olaf, January 29, 2004 - 10:58 am UTC
Tom,
what means NONE for server in v$session:
SQL> select username, server from v$session;
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
CITRIX NONE
CITRIX NONE
SYSTEM SHARE
January 29, 2004 - 1:25 pm UTC
it is a shared server connection that is not currently active.
Multiplexing with Dedicated Server?
Jan, February 03, 2004 - 8:23 am UTC
You wrote:
"I can use connection multi-plexing to have one physical connection to the
database with a single dedicated server (process) and have many sessions going."
What is "Connection Multi-plexing"? Is it "Session Multiplexing"? If yes, then as I understood from docs:
Oracle9i Net Services
part "Session Multiplexing"
this option is available only for Shared Server mode. What is the true?
February 03, 2004 - 8:31 am UTC
multi-plexing is just a "computer term"
</code>
http://www.google.com/search?hl=en&lr=&ie=UTF-8&oe=utf-8&q=define%3A+multiplexing&btnG=Google+Search <code>
in my context, it means I can have a single physical connection to a dedicated server but have many sessions active at once. I'm multiplexing my own personal sessions over a single physical circut (connection)
the net guides are talking about multi-plexing ANY session from ANY user through a single server. a much more general case.
multiplexing
Reader, February 03, 2004 - 9:39 am UTC
Tom, as one may have many sessions using a single dedicated server connection in multiplexing, is PGA shared by all of the sessions in multiplexing? Thanks.
February 03, 2004 - 10:07 am UTC
PGA is a process thing, not a session thing.
So yes, the pga is shared by any session using that process.
Shall I kill the extra process in v$process
Ramesh, February 19, 2004 - 12:39 pm UTC
the counts are not matching in V$process and V$session
Shall I kill the extra session which is available in v$process but not available in v$session... Is there any harm to do that ?
Thanks for the Excellant work..
February 19, 2004 - 2:28 pm UTC
no, they do not need to match. don't kill anything willy nilly like that.
zhigang wang, March 16, 2004 - 5:17 am UTC
the following data is query rusult by :
select p.spid,s.sid,t.value/1024/1024 PGA_Mb
from v$process p,v$session s,v$sesstat t
where p.addr=s.addr
and s.sid=t.sid
and t.statstic#=20
adn p.spid=9770;
p.spid s.sid PGA_Mb
9770 107 39.43
9770 138 38.18
9770 432 39.99
9770 392 39.78
==========================
i caught above lines from our Oracle 11i system on Oracle 9.2.0.3, it's not a shared server/MTS, but one shadow process had 4 related session id which statistics of 'pga memory' were different.
it's really confusing me.
March 16, 2004 - 8:01 am UTC
well, thats not the query (typos, wrong column names, etc)
but, add s.server to the query.
and remember a single client can open many sessions on the same connection. (see original answer above!)
In fact, we can see this with a tool as simplistic as SQL plus! Watch:
ops$tkyte@ORA9IR2> select p.spid,p.addr,s.sid,s.server,t.value/1024/1024 PGA_Mb
2 from v$process p,v$session s,v$sesstat t
3 where p.addr=s.paddr
4 and s.sid=t.sid
5 and t.statistic#=20
6 and s.username = user
7 /
SPID ADDR SID SERVER PGA_MB
------------ -------- ---------- --------- ----------
17244 5628FF88 8 DEDICATED .382892609
<b>so, apparently I have one process, one session... but:</b>
ops$tkyte@ORA9IR2><b>
ops$tkyte@ORA9IR2> set autotrace on statistics</b>
ops$tkyte@ORA9IR2> select p.spid,p.addr,s.sid,s.server,t.value/1024/1024 PGA_Mb
2 from v$process p,v$session s,v$sesstat t
3 where p.addr=s.paddr
4 and s.sid=t.sid
5 and t.statistic#=20
6 and s.username = user
7 /
SPID ADDR SID SERVER PGA_MB
------------ -------- ---------- --------- ----------
17244 5628FF88 8 DEDICATED .382892609
17244 5628FF88 9 DEDICATED .507892609
<b>whats that? where did that session come from? SQLPlus opened the session for me in order to autotrace me. It uses another session in order to NOT affect the session it is watching (eg: autotrace runs sql and such -- if it did its work in our session -- autotrace would affect the measurments it is trying to show us. therefore, it just starts a new session on our connection)</b>
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
664 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2 rows processed
ops$tkyte@ORA9IR2> set autotrace off
More Processes than Sessions?
David, March 16, 2004 - 9:25 am UTC
I understand why I have more sessions than processes, but why would I have a database with more processes than sessions?
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
ops$oracle@GPDPROD> select count(*) from v$process;
COUNT(*)
----------
52
ops$oracle@GPDPROD> select count(*) from v$session;
COUNT(*)
----------
16
Thanks.
March 16, 2004 - 9:45 am UTC
There is a connection -- a physical circut from the client to the server.
Then, there are sessions.
You can be connected to Oracle but not have any sessions.
You can have 10 sessions -- but not have ANY processes associated with you.
It is a 1-1, 1-m, m-1, m-m possibility here.
In shared server mode, you could easily have started N sNNN processes (shared servers) but have N-M sessions (set shared servers to 50, have 10 people log in. 10 sessions, 50 processes)
Job queues will be processes
Aq processes will be processes
DBWn is a process
Look at the PROGRAM column in v$process and you'll see what I mean....
Sven Bleckwedel, March 16, 2004 - 9:28 am UTC
Hi Tom,
Could this query be modified to show the PGA consumption for all other sessions/processes ?
Tks in Advance,
Sven
March 16, 2004 - 9:45 am UTC
sure, just drop the "s.username = user"
Multi-Session used in single physical connection by oracle forms
zhigang wang, March 16, 2004 - 9:07 pm UTC
thanks, Tom. as u told us, one single physical connection could start 2 or more sessions. the PGA of the process is summary of all sessions, right? and could u talk about something about how to implement that besides set autotrace, for example, how is it used in Oracle Forms.
March 17, 2004 - 7:07 am UTC
PGA is process global area and there is a single PGA per process, so yes, that is the summary for all sessions using that process (there wasn't 2 separate pga's in my example really)
Forms uses it for creating new sessions separate from the main session (like with run_product) and debugging.
Great stuff
A reader, July 27, 2005 - 9:59 am UTC
PGA_FREEABLE_MEM
Abhishek Kumar, November 21, 2011 - 3:53 pm UTC
Hi Tom,
Can you please help me to understand that what is PGA_FREEABLE_MEM in v$process?
Lets we have:
PGA_USED_MEM = x bytes
PGA_ALLOC_MEM = y bytes
so wil PGA_FREEABLE_MEM = (y - x) bytes ? or PGA_FREEABLE_MEM is calculated by any other algorithm?
Thanks
Abhishek
November 21, 2011 - 6:10 pm UTC
It depends on how contiguous the memory is and what OS you are on. So, let us call this "by any other algorithm"
On most platforms - if your PGA memory is:
UffffffffUffffffffffffUffff
where U = used memory, and f = free memory - you'll only be able to free a tiny bit - the last four f's in this case. If the memory was:
UUUfffffffffffffffffffffff
you'd be able to free a lot. if it was
UffffffffffffffffffffU
you'd be able to free nothing.
PGA_FREEABLE_MEM
Abhishek Kumar, November 21, 2011 - 9:34 pm UTC
Thanks a lot for the explanation. It cleared my doubt about what is freeable memory. :) :)
It seems same mechanism as fragmentation in widows drives, isn't it.
We used to defragment the drives to use fragmented memory.
Can you please also explain that when the memory get freed and what is responsible for it?
(please consider OS as solaris)
Let's we have PGA as below:
UaUaUb
a = [fffff] and b = [fff]
where U = used memory and f = free memory
when memory "a" will get freed?
when memory "b" will get freed?
how will it get freed (Automatically or any component is dedicated to free the memory)?
November 22, 2011 - 8:00 am UTC
well, the memory can be "freed" anytime - it is a question of whether or not it can be freed to the OS (shrink the PGA) or not.
In general, with your example, b would be freeable with respect to both the session (the session would have more free memory it can reuse) as well as the OS - since a heap can, in general, 'shrink'.
In general, a would be freeable with respect to the session - the session could reuse "a" memory the next time it needs some more memory to be used. However, in general, the memory used by "a" would not be released to the OS to be used by another process since a heap is, in general, a contiguous set of memory.
I use in general liberally here - because it truly "depends" - on the OS, on the type of memory being used.
For example - we have "workareas" that we use in sorting, hashing, merging bitmaps and so on. These workareas, allocated when using automatic PGA memory management, are - in general - allocated, used, and deallocated (given right back to the OS). They are not managed in the normal process "heap", but by using special OS calls to allocate some memory temporarily, use it, and give it back. They are not part of this contiguous memory (the program heap).
PGA_FREEABLE_MEM
Abhishek Kumar, November 22, 2011 - 10:20 am UTC
Thanks Tom.
So memory can be freeable with respect to session or OS.
If the memory is freeable to session, only that session can reuse it (Memory "a" as in our example).
If the memory is freeable to OS, it can be used by any session (Memory "b" as in our example).
As you mentioned, workareas are not part of the program heap and special OS calls are used for allocation and deallocation of this memory. So memory reserved for workareas, are not part of "PGA_*_MEM"?
Please correct me if I am wrong.
Thanks,
Abhishek
November 22, 2011 - 1:02 pm UTC
what is pga_*_mem? if * is freeable - I do not believe it is counted in that as we automatically release it when it is not needed anymore (if the OS permits us to do so, if the workareas are allocated "in a special manner" on that OS.
PGA_FREEABLE_MEM
abhishek kumar, November 22, 2011 - 2:08 pm UTC
Hi Tom,
PGA_*_MEM: Here I was refering * with FREEABLE, USED and ALLOC.
Thanks a lot for the explanation. My doubt is pretty clear about PGA_FREEABLE_MEM now :).
Thanks,
Abhishek
session v/s process memory
Girish Singhal, July 16, 2014 - 3:53 pm UTC
So when we say we have multiple processes and multiple sessions being handled by different processes, then suppose we name a session as S1 and process as P1.
So at a particular moment in time would only S1 is attached (serviced by) to P1 when S1/P1 is executing?
Also how the memory gets handled for example the one given above in one the the review question like
UffffUfffUfff
where U is used and f is free.
By handled I mean that if this is the memory structure for S1 (being serviced by P1), when S1 gets decoupled from P1, would the whole memory allocated for P1 gets deallocate and released to OS?