Skip to Main Content
  • Questions
  • DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 28, 2002 - 10:19 am UTC

Last updated: July 27, 2020 - 2:22 am UTC

Version: 8.1.7.4

Viewed 100K+ times! This question is

You Asked

Hi Tom

What's the difference between connections, sessions and processes?

I read a note from Metalink about the difference but I simply dont get it!

May you give a brief explanation?

Thank you

and Tom said...

A process is a physical process or thread.

On unix, you can see a process with "ps" for example. It is there.

There are many types of processes in Oracle -- background processes like SMON, PMON, RECO, ARCH, CKPT, EMNn, DBWR, etc..... And user processes like dedicated servers or shared server (multi-threaded server -- aka MTS -- configuration)


A connection is a "physical circuit", a pathway to a database. You can be connected to a database yet have 0 or 1 or MORE sessions going on that connection. We can see that with sqlplus, consider (single user system here, its all about me)


[tkyte@tkyte-pc-isdn tkyte]$ ps -auxww | grep oracleora920
[tkyte@tkyte-pc-isdn tkyte]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 10:36:03 2002

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

idle> !ps -auxww | grep oracleora920
tkyte 19971 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww | grep oracleora920
tkyte 19973 0.0 0.1 1736 564 pts/1 S 10:36 0:00 grep oracleora920

no process, no nothing

idle> connect /
Connected.
idle> !ps -auxww | grep oracleora920
ora920 19974 1.5 2.2 230976 11752 ? S 10:36 0:00 oracleora920 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
tkyte 19975 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww | grep oracleora920
tkyte 19977 0.0 0.1 1736 564 pts/1 S 10:36 0:00 grep oracleora920

got my process now...

idle> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
idle> !ps -auxww | grep oracleora920
ora920 19974 0.6 2.3 230976 11876 ? S 10:36 0:00 oracleora920 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
tkyte 19978 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww | grep oracleora920
tkyte 19980 0.0 0.1 1736 564 pts/1 S 10:36 0:00 grep oracleora920

idle> select * from dual;
SP2-0640: Not connected

still have my process, but no session, the message is a little "misleading". Technically -- I have a connection, I don't have a session


further, autotrace in sqlplus can be used to show that you can have
a) a connection
b) that uses a single process
c) to service two sessions:


ops$tkyte@ORA920.US.ORACLE.COM> select username from v$session where username is not null;

USERNAME
------------------------------
OPS$TKYTE

one session, ME

ops$tkyte@ORA920.US.ORACLE.COM> select username, program from v$process;

USERNAME PROGRAM
--------------- ------------------------------------------------
PSEUDO
ora920 oracle@tkyte-pc-isdn.us.oracle.com (PMON)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (DBW0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (LGWR)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (CKPT)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (SMON)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (RECO)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (CJQ0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (QMN0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (S000)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (D000)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (ARC0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (ARC1)
tkyte oracle@tkyte-pc-isdn.us.oracle.com (TNS V1-V3)

14 rows selected.

you can see all of the backgrounds and my dedicated server...

ops$tkyte@ORA920.US.ORACLE.COM> set autotrace on statistics;

Autotrace for statistics uses ANOTHER session so it can query up the stats for your CURRENT session without impacting the STATS for that session!


ops$tkyte@ORA920.US.ORACLE.COM> select username from v$session where username is not null;

USERNAME
------------------------------
OPS$TKYTE
OPS$TKYTE


see, two sessions but....

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
418 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

ops$tkyte@ORA920.US.ORACLE.COM> select username, program from v$process;

USERNAME PROGRAM
--------------- ------------------------------------------------
PSEUDO
ora920 oracle@tkyte-pc-isdn.us.oracle.com (PMON)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (DBW0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (LGWR)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (CKPT)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (SMON)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (RECO)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (CJQ0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (QMN0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (S000)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (D000)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (ARC0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (ARC1)
tkyte oracle@tkyte-pc-isdn.us.oracle.com (TNS V1-V3)

14 rows selected.

same 14 processes...

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
1095 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)
14 rows processed

ops$tkyte@ORA920.US.ORACLE.COM>


I'll try to put it into a single, simple paragraph:

A connection is a physical circuit between you and the database. A connection might be one of many types -- most popular begin DEDICATED server and SHARED server. Zero, one or more sessions may be established over a given connection to the database as show above with sqlplus. A process will be used by a session to execute statements. Sometimes there is a one to one relationship between CONNECTION->SESSION->PROCESS (eg: a normal dedicated server connection). Sometimes there is a one to many from connection to sessions (eg: like autotrace, one connection, two sessions, one process). A process does not have to be dedicated to a specific connection or session however, for example when using shared server (MTS), your SESSION will grab a process from a pool of processes in order to execute a statement. When the call is over, that process is released back to the pool of processes.






Rating

  (232 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Clear, concise.

JW, September 28, 2002 - 11:00 am UTC

Tom, you rock! You are clear, concise, and complete.

Reader

A reader, September 28, 2002 - 12:33 pm UTC

How come there is still a bequeathe process even after you
idle> disconnect
ora920 19974 0.6 2.3 230976 11876 ? S 10:36 0:00 oracleora920
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))


Tom Kyte
September 28, 2002 - 12:53 pm UTC

I'm still "connected" physically.

I just gave up my session. (that was sort of the point here. A connection may or may not have a process, a process may or may not be associated with a session, etc)



Thanks

A reader, September 28, 2002 - 3:35 pm UTC


Very Nice explanation! Can you explain why the username is different in the V$PROCESS

Prince, September 28, 2002 - 4:39 pm UTC

Oracle 9.2.0.1 on Win NT

SQL> select process, sid, paddr,username from v$session where username is not null ;

PROCESS             SID PADDR    USERNAME
------------ ---------- -------- ------------------------------
1229:785              8 140FFF7C PRINCE

SQL> select username, spid, pid, program from v$process  where addr = '140FFF7C';

USERNAME        SPID                PID PROGRAM
--------------- ------------ ---------- ---------------------------------------------
SYSTEM          290                  11 ORACLE.EXE 

Tom Kyte
September 28, 2002 - 7:26 pm UTC

It is the OS username of the OS userid associated with the process itself.

If I connected dedicated server over sqlnet, it would show "ora920" as the others do. My OS user is tkyte.

bequeath protocol

A reader, September 29, 2002 - 10:26 am UTC

Hi Tom

When is BEQUEATH protocol used? I think is when your client and server are in same node however when I attended oracle net8i course in Oracle University in the notes it says that by default Bequeath is used (even from a remote client).

There is a paragraph which says:

Using the Bequeath Method
• If a dedicated server does not have prespawned server processes, the Bequeath session method is used by default.
• If, because of the operating system or protocol, a connection cannot pass between two different processes on the same machine, this method cannot be used.

There is another method called Redirect session but from the course my feeling is that it only works if there are prespawned server processes (the explanation given in the notes used a case with prespawned server processes)

I am working in Unix and we dont have prespawned server processes, if I connect from a Windows client will I be using Bequeath protocol or redirect?

Tom Kyte
September 29, 2002 - 11:23 am UTC

bequeath most likely.

It is just a "fork" by the listener and "exec" by the forked listener process. The listener "bequeaths" (leaves in its "will" if you like) the connection you established with it to the child process.




in net8 documentation

A reader, September 29, 2002 - 2:42 pm UTC

hi

in net8i doc it states:

Bequeathed Network Sessions Directly to Dedicated Servers:
If the client and server exist on the same machine, a client connection can be bequeathed (passed) directly to a dedicated server process without going through the listener. The application initiating the session spawns a dedicated server process for the connection request using the Bequeath protocol.

So Bequeathed Network Sessions only occurs if server and client are in same node? Because Oracle University net8i notes suggest other thing!

Tom Kyte
September 29, 2002 - 3:27 pm UTC

It should continue on to fill out the entire story (and it does in fact):

If the client and server do not exist on the same machine, a client connection can be bequeated from the listener to the dedicated server instaled. The listener initiates the session by spawing a dedicated server ........


see
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/concepts.htm#1028475

which does not conflict with:
http://docs.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/concepts.htm#1009622 <code>

It is just that the second link doesn't say "oh, and by the way, this isn't the only way this happens"


I guess there is the confusion between the exactness of the "bequeath protocol" and "bequeathing a connection". Playing with fine grained semantics here but... technically -- the "bequeath PROTOCOL" is used only on single servers. We BEQUEATH a connection using either the beq protocol or from a listener.


Say, if you are hooked on semantics --

* bequeath PROTOCOL -- single machine. Your client bequeaths the connection directly to the forked dedicated server.

* bequeathing a connection -- happens over the net with the listener or in a single server without the listener. bequeathing a connection is part of the bequeath protocol.


Bhagat Singh, September 29, 2002 - 11:25 pm UTC

This gave the clear cut understanding of the three. That was commendable.

user process and server process

learningOracle, January 30, 2003 - 10:52 pm UTC

What is the difference between a user process and server process?
Does the ini parameter processes include user, server and background processes?

What processes does the TOP command show?

Tom Kyte
January 31, 2003 - 7:43 am UTC

You might want to check out

the server concepts guide
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/toc.htm http://docs.oracle.com/docs/cd/B10501_01/nav/docindex.htm#index-CON <code>

you'll find a wealth of information. As well, books such as my own "Expert one on one Oracle" cover topics like this in detail.


top shows all processes running on a system -- in unixland, a process is a process is a process.


in Oracle speak there are background processes (those that are started when you start the database like arch, pmon, smon). Then there are server or user or foreground processes (call them what you will) -- dedicated servers -- they run for you and do the queries.

What is pseudo program

A reader, April 23, 2003 - 2:18 am UTC

Hi Tom,

from your example above
[quote]
ops$tkyte@ORA920.US.ORACLE.COM> select username, program from v$process;

USERNAME PROGRAM
--------------- ------------------------------------------------
PSEUDO
ora920 oracle@tkyte-pc-isdn.us.oracle.com (PMON)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (DBW0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (LGWR)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (CKPT)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (SMON)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (RECO)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (CJQ0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (QMN0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (S000)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (D000)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (ARC0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (ARC1)
tkyte oracle@tkyte-pc-isdn.us.oracle.com (TNS V1-V3)
[/quote]

I have seen this PSEUDO many times but do not understand what it is? Its there even when there is no connection or, say a just started database. I'm little confused on this.

Thanks and Regards

Tom Kyte
April 23, 2003 - 7:21 am UTC


You used alter system kill session

Once an inactive session is killed its row in the v$session view is not
removed until you try to use that session again and "ORA-00028 your
session has been killed" is reported, then it is removed. Until
that point, it is marked as killed in the status column and pseudo in the
server column.


But why on a just started database...

A reader, April 23, 2003 - 7:28 am UTC

Hi Tom,

[code]
SYS@ACME.WORLD> startup
ORACLE instance started.

Total System Global Area 101784796 bytes
Fixed Size 453852 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SYS@ACME.WORLD> select username,program from v$process;

USERNAME PROGRAM
--------------- ----------------------------------------------------------------
PSEUDO
SYSTEM ORACLE.EXE
SYSTEM ORACLE.EXE
SYSTEM ORACLE.EXE
SYSTEM ORACLE.EXE
SYSTEM ORACLE.EXE
SYSTEM ORACLE.EXE
SYSTEM ORACLE.EXE

8 rows selected.

SYS@ACME.WORLD> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ACME.WORLD> startup
ORACLE instance started.

Total System Global Area 101784796 bytes
Fixed Size 453852 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SYS@ACME.WORLD> select username,program from v$process;

USERNAME PROGRAM
--------------- ----------------------------------------------------------------
PSEUDO
SYSTEM ORACLE.EXE
SYSTEM ORACLE.EXE
SYSTEM ORACLE.EXE
SYSTEM ORACLE.EXE
SYSTEM ORACLE.EXE
SYSTEM ORACLE.EXE
SYSTEM ORACLE.EXE

8 rows selected.
[/code]

There was no user connected and this pseudo is still there. Why ??

Tom Kyte
April 23, 2003 - 7:41 am UTC

consider it the "init process" of Oracle -- just like there is an init in Unix.

it just is.

i i Sir

A reader, April 23, 2003 - 8:01 am UTC

:)

Server Process is an alias of "Oracle" like the background ones ?

Robert, May 26, 2003 - 8:36 pm UTC

Tom, re-read "Architecture" in your book and have question about the nature of the (decicatd) server process.

Obviously the server process does a lot of things.

Now, say on UNIX, is this process a "clone" of the Oracle program just like the background processes ?
as you said on p55 about the background processes:
"..there is only one binary, named simply oracle. It is just executed many times with different names"

Thanks

Tom Kyte
May 27, 2003 - 7:25 am UTC

yes, they are all the same binary.

Follow up question.

Saibabu Devabhaktuni, May 27, 2003 - 2:02 pm UTC

Hi Tom,

You said setting autotrace for statistics in a session add up another session with the same process id, are there any other circumstances under which a process can serve bunch of sessions( Not MTS)..., i.e. following query should give count more than 1, "select count(*) from v$session where paddr='ORACLE_PROCESS_ADDR".

Is it possible to have more that one process associated with a session at the same time (Not MTS).

Thanks

Tom Kyte
May 27, 2003 - 5:26 pm UTC

sure, forms does it all of the time.

ANY program that wants to can do it. it is somewhat common.

When in forms are multiple sessions opened against a single process?

Subrata, June 09, 2003 - 6:25 pm UTC

Tom, Could you please elaborate how multiple sessions are opened in Forms which are served by a single process? When a new block is opened or when a new query is executed? when?

Thanks

Tom Kyte
June 09, 2003 - 7:13 pm UTC

the debugging and tracing facilities use it. I believe things like "open this form, but in its own session" will use it.

if you program in C or use ntier proxy authentication in java, you would see it is a pretty trivial thing to actually do in Oracle. A connection is a two step -- you "connect", then you establish a session (and a session and a session and a session and so on)

Are connections and process synonymous?

SS, June 10, 2003 - 6:57 pm UTC

A question regarding the excellent very first reply:
Are connections and processes synonymous? (No confusion about the sessions!)? If no, can we have one connection attached to many processes or vice versa? examples?

Thanks

Tom Kyte
June 10, 2003 - 8:09 pm UTC

yes you can -- we call it "shared server" or MTS for example

Are connections and process synonymous?

SS, June 10, 2003 - 6:57 pm UTC

A question regarding the excellent very first reply:
Are connections and processes synonymous? (No confusion about the sessions!)? If no, can we have one connection attached to many processes or vice versa? examples?

Thanks

I'm getting ORA-00020

Alvin, June 17, 2003 - 11:36 pm UTC

ORA-00020 maximum number of processes (string) exceeded

Cause: All process state objects are in use.

Action: Increase the value of the PROCESSES initialization parameter

I got this message last saturday and got it this wednesday. I had to bounce the db because i can connect to internal but not be able to do anything. Shutdown immediate doesn't work. I was compelled to use shutdown abort.

1. Do i attribute this to the number of sessions ?
2. Or its time that i configure the DB to be of MTS ?
3. Does the SPID column in the V$process table equals the number of shared servers ?

Tom Kyte
June 18, 2003 - 6:02 pm UTC

1) yes.
2) don't know, if processes is set to "10" (you set it), probably not. if processes is 10,000 -- maybe. In between - don't know, don't know your hardware. Is is maxed out process wise?

3) no, there will be dedicated servers, shared servers, background processes in there

Alvin, June 19, 2003 - 12:16 am UTC

I set it as processes=415 and it was 315 before.

Base from the ora docs the number of sessions is computed as
(1.1*processes)+5. That means process=415 will give me sessions=461.

But i only get at most 150 users. I dunno how the processes got maxed out.


We're using
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

Hardware wise its

# uname -a
SunOS sunfire01 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Fire-280R

# psrinfo -v
Status of processor 0 as of: 06/19/03 03:13:49
Processor has been on-line since 01/29/03 05:12:46.
The sparcv9 processor operates at 900 MHz,
and has a sparcv9 floating point processor.
Status of processor 1 as of: 06/19/03 03:13:49
Processor has been on-line since 01/29/03 05:12:47.
The sparcv9 processor operates at 900 MHz,
and has a sparcv9 floating point processor.

and it has 2G of memory.

1. Do machines have limits as to the number of processes it can launch ?
2. How would i know if i'm machine bound ?
3. Can i set sessions to time out ? if possible, will it commit before exiting ?

Tom Kyte
June 19, 2003 - 7:59 am UTC

you have unix "processes per user" settings, but there you would get a different error

you need to research this a little further - you should look at the number of sessions you have and the number of processes - if processes far exceeds sessions (v$session v$process) you have a problem somewhere and you need to get support involved to diagnose it.

for example, I saw a case where an 815 thin JDBC connection to an 817 database could under some circumstances leave a v$process entry -- causing us to hit max processes artificially.

"You used alter system kill session"

kate, June 19, 2003 - 11:01 am UTC

Tom,

if a user is hitting the max process limit, using "alter system kill session" doesn't help anything than, does it? since the pseudo process is not removed until potentially a later time.

someone told me when they hit this ora-00020 they couldn't even connect as sysdba. could this be because they weren't local to the db? should they have been able to even when ora-00020 is denying other connections?

Tom Kyte
June 19, 2003 - 1:09 pm UTC

alter system kill session won't work that way. the session stays in v$session marked as killed.

not having a cut and paste of an actual event leaves me at a disadvantage here, they should have been able to connect (i have been) -- I can only assume they tried to connect as sys not sysdba

dedicated server

Reader, August 03, 2003 - 11:45 am UTC

Following is a quote from ch 3 of your next book.

<quote>
that does not mean every single session has a dedicated server, it is possible for a single application, using a single physical connection (dedicated server) to have many sessions concurrently active
<quote>

I always thought each session will have its own dedicated server process. If so, in the above case, will I still see for each clientPID, there will be a corresponding SPID from v$process and v$session.

THANKS.

Tom Kyte
August 03, 2003 - 12:07 pm UTC

In Oracle there are two concepts

o a connection, a physical circut between you the client and the server
o a session, a logical thing

You can have many sessions going from a single client using a single connection.

The connection has the dedicated server.

dedicated server continued

Reader, August 03, 2003 - 12:35 pm UTC

<quote> You can have many sessions going from a single client using a single connection.<quote>

In this case as well, each session will have its own dedicated server process. Right? If you say no, I have to seriously review my understanding of dedicated server concept. Thanks.


got it!

Reader, August 03, 2003 - 12:42 pm UTC

Please ignore my previous stupid question.

<quote>Autotrace for statistics uses ANOTHER session so it can query up the stats for your CURRENT session without impacting the STATS for that session<quote>

understood from your earlier demo in this thread.
Is there any other situation other than autotrace would use another session still with the same process? Thanks. You are simply superb.

Tom Kyte
August 03, 2003 - 2:27 pm UTC

forms does it for debugging all of the time.

ntier proxy authentication in 9i does it (the ability to connect "on behalf of" someone else.

you can code it yourself in OCI.

Anthony Reddy, August 04, 2003 - 3:35 am UTC


post/wait initialization error

Anthony Reddy, August 04, 2003 - 3:47 am UTC

Is this error related to number of processes.
i got this error, when i am creating a database.
Please help.

Tom Kyte
August 04, 2003 - 9:01 am UTC

read your installation guide, that means you haven't configured sufficient system resources like semaphores, shared memory and such at the OS level for the number of databases you wish to have concurrently running.

post/wait initialization error

Anthony Reddy, August 05, 2003 - 4:19 am UTC

Thanks for your reply.

I am using hp-ux 11,

The memory usage is like this:

root:/>swapinfo -tm

AVAIL(Mb) USED(Mb) FREE(Mb) PCT USED
371 133 238 36%



Here are the kernel settings:

root:/>kmtune | grep shm
shmem 1
shmmax 1073741824
shmmni 300
shmseg 120


root:/>kmtune | grep sem
sema 1
semaem 16384
semmap (SEMMNI+2)
semmni 300
semmns 600
semmnu 600
semume 75
semvmx 32767

This is a development server, here we got six instances,
five instances have processes = 100 in the init.ora,
and the lost one have processes = 50 in the init.ora file.


When i try to create the seventh database, i am getting the
above error.

please seggest the required kernel settings.

thanks in advance.



Tom Kyte
August 05, 2003 - 7:24 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:454420165038 <code>

still hesistant to read that documentation eh... oh well.

A reader, August 08, 2003 - 3:56 pm UTC

Hi Tom,

I hope you can help me to understand something.
In a shared server database the parameter processes in the init.ora file should be set in which of the following ways:

1- processes=dedicated connections + shared_servers=340

or

2- processes=dedicated connections + max_shared_sessions=2000

where:
max_shared_sessions=1700
dedicated connections=300
shared_servers=40

We are planning to allow 1700 shared connections and allow 300 dedicated connections. I think the correct way is option #1, because if not it will make more sense to stay in a dedicated mode instead of shared in this case. Am I correct, is option#1 the correct answer? or option #2 is the correct way?

Any help with this it will be really appreciated.

Thanks

Tom Kyte
August 10, 2003 - 12:01 pm UTC

I think you mean max_shared_servers, not _sessions

but you want processes to be based on the max number of os processes/threads you will create which is bounded by the number of dedicated server sessions, the MAX number of shared server processes and the backgrounds

So, you would actually want probably 2,050 processes -- 1700 for shared servers, 300 for dedicated servers and 50 for backgrounds like dbwr, lgwr, job queue processes, etc.

A reader, August 10, 2003 - 1:01 pm UTC

Hi Tom,

Thanks for answering me.
Well I really meant to say max_server_sessions. In the Oracle documentation says that :
<quote>
SHARED_SERVER_SESSIONS
Specifies the total number of shared server user sessions to allow. Setting this parameter enables you to reserve user sessions for dedicated servers
<quote>
That is what confused me, I thought that I should have used this parameter instead of the shared_servers.

1-Taking into account your answer I understand that I should use the max_shared_servers instead of shared_servers (as I put in option#1). So if the parameters settings are going to be the following:
max_shared_servers=100
shared_servers=40
dispatchers=40
max_shared_sessions=1700
dedicated servers =300

is It correct to set the "processes" parameter to 450 which means in my case 100 max_shared_servers + 300 dedicated servers + 50 backgound processes?

and two more question:
2-If I want to have no more that 1700 user shared sessions on the database is it ok to set max_shared_sessions =1700?

3-based on I want to have 300 dedicated servers and no more than 1700 shared users sessions, is it ok to set the parameter sessions=2000 in this case?

Thanks for your help on this.


Tom Kyte
August 10, 2003 - 2:41 pm UTC

there is no init.ora parameter "max_server_sessions"


processes should be set to some number larger then the sum of

o the max number of shared server processes
o the max number of dedicated servers
o the max number of backgrounds (dbwr, lgwr, job queues, etc)




ORA-12519

Ashok, August 13, 2003 - 7:08 pm UTC

Hi,
I was trying to simulate a problem which we are getting in informatica(ora-12519) during batch process .Informatica uses DBlink to connnect to our database.We monitored the session and it's looks like it frequently connects and disconnects during the batcg process and in the middle of the process they are getting ora-12519.

To simulate this
I tried something in powerbuilder
for i = 1 to 200
connect to oracle;
disconnect to oracle
next

In the mean time i tried to connect to oracle with Dbartisan i got the same error(ora-12519).Why is that happening.
I know it's stupid to do frequent disconnects and connects inside the batch process but we cant control the third party tool(Informatica).


process

A reader, October 02, 2003 - 3:38 pm UTC

Why are some processes showing as n:n in the process columns -- why the colon ? (Using 9ir2 on sun) 

SQL> select username, process from v$session;


USERNAME                       PROCESS
------------------------------ ------------
                               1075
                               1079
                               1081
                               1087
                               1093
                               1097
                               1101
API_CONEXAO                    1144:1148
API_CONEXAO                    26340
API_CONEXAO                    1144:1148
SYSTEM                         1544:1832
API_CONEXAO                    3133
API_CONEXAO                    26341

13 rows selected. 

Tom Kyte
October 02, 2003 - 6:47 pm UTC

they are windows clients -- select the program out as well to see what program they are.

A reader, October 03, 2003 - 9:24 am UTC

ok, they are windows clients -- but shouldn't there be a corresponding server process on Solaris ? Shouldn't its pid be there ?

Tom Kyte
October 03, 2003 - 10:09 am UTC

that column is the clients process id, from the client machine.


ops$tkyte@ORA920> select a.spid dedicated_server,
  2             b.process clientpid
  3    from v$process a, v$session b
  4   where a.addr = b.paddr
  5     and b.audsid = userenv('sessionid')
  6  /
 
DEDICATED_SE CLIENTPID
------------ ------------
26581        26580


that gives you the "dedicated server" and client process id's 

Too many sessions.

Alvin, October 23, 2003 - 2:26 am UTC

The system that i'm handling creates 3 oracle sessions per user login in the front end (10 Front end user = 30 oracle users). Only one of them is used and the other 2 sessions just consumes extra processes. I've hit the max process error a couple of times already and resorted to shutdown abort.

What happens if there are many uneccessary idle sessions in a db ? how would affect performance and how do i quantify it ? We're in a dedicated server mode.

I just finished reading the first chapter of your new book and i agree that data independence idea stresses the DB and the DBA to much. What the our front end does is query from user A and user B and it consolidate the result set which to me seems to be the function of the db in the first place. It can easily be work around by grants or other users objects but doing so would entail massive code re-writes so it'll never be feasible for the developmental team.

It's a too taxing proposing some existing oracle technology at times because they always ask me that other DB's should have the same features too. I only know ORACLE.

They're only compelled to take my suggestion when things are already crawling. Another thing also, they didn't normalize the most frequently accessed table they tell me that they heard that 'joins' are slow. So what should've been query big table with small table, they self join the big table.


Tom Kyte
October 23, 2003 - 12:41 pm UTC

we cannot tell they are unnecessary.

you might consider shared server if you have too many processes, the idle ones just will consume UGA memory in the large_pool (SGA) but not a process.

processes

Venkat, October 28, 2003 - 7:57 am UTC

Tom,

We have a requirement saying that there would be 3400 concurrent users in the system. It is internet banking application. It is a 3 tier architecture, weblogic as app. server.

- What value should be set for processes in init.ora?
- What value should be set in the connection pool of weblogic?

If we set a higher value for processes in init.ora, will it consume any resource?

Pl. clarify.

Regards,
Venkat



Tom Kyte
October 28, 2003 - 8:15 am UTC



o no clue
o no idea


3400 concurrent users is virtually *meaningless*


I have 5,000 concurrent users on asktom -- if I measure an hour.

I have 0-15 truely concurrent users on asktom if i measure a point in time.

So, my connection pool would only need to be about 15-20 at best and processes very low.

But there are other systems with 5,000 concurrent users that have 100 truely concurrent users (all hitting the enter key at the same time)

THAT is what you need to ascertain. How many truely concurrent users do you have -- it will not be anywhere near 3,400 (people think, thinktime...)


setting processes higher consumes a small (trivial, not to be concerned about) amount of SGA memory. Given that you need to restart to change it, you what "bigger" in the event you need more.

Shared servers in dedicated mode

A reader, December 29, 2003 - 1:51 pm UTC

Hi Tom,
I have a database in MTS and all the shared servers are showed in dedicated mode. Do you have any idea why this might happen?

SQL_cccc>select sid,substr(username,1,20) usrname,substr(osuser,1,20) osuser,substr(program,1,20) pgram,server,status
2 from v$session where username is not null
3* order by server,status,sid
SQL_cccc>/

SID USRNAME OSUSER PGRAM SERVER STATUS
----- ----------- -------------- ------------------- --------- --------
48 SYS USRA sqlplusw.exe DEDICATED ACTIVE
10 USRT USRAT104 oracle@cccc (S001) DEDICATED INACTIVE
14 USRT USRAT113 oracle@cccc (S000) DEDICATED INACTIVE
15 USRT usrcccc batchp1.exe DEDICATED INACTIVE
18 USRT USRAT111 oracle@cccc (S002) DEDICATED INACTIVE
19 USRT USRJ oracle@cccc (S000) DEDICATED INACTIVE
24 USRT USRAT101 oracle@cccc (S001) DEDICATED INACTIVE
27 USRZ Administrator batchp2.exe DEDICATED INACTIVE
30 USRT USRAT106 oracle@cccc (S000) DEDICATED INACTIVE
37 USRT USRAT117 oracle@cccc (S001) DEDICATED INACTIVE
40 USRT USRAT102 oracle@cccc (S001) DEDICATED INACTIVE
44 USRA USRA sqlplusw.exe DEDICATED INACTIVE
46 USRT USRAT103 oracle@cccc (S000) DEDICATED INACTIVE
52 USRR usrcccc sqlplusw.exe DEDICATED INACTIVE
65 USRT USRAT120 oracle@cccc (S000) DEDICATED INACTIVE
72 USRT USRAT206 oracle@cccc (S000) DEDICATED INACTIVE
73 USRT USRAT122 oracle@cccc (S000) DEDICATED INACTIVE
75 USRT USRAT121 oracle@cccc (S000) DEDICATED INACTIVE
76 USRT USRAT204 oracle@cccc (S000) DEDICATED INACTIVE
77 USRT USRAT123 oracle@cccc (S000) DEDICATED INACTIVE
80 USRT USRAT110 oracle@cccc (S000) DEDICATED INACTIVE
82 USRT USRAT108 oracle@cccc (S000) DEDICATED INACTIVE
87 USRT USRAT112 oracle@cccc (S000) DEDICATED INACTIVE
102 USRT USRDB appc.EXE DEDICATED INACTIVE
114 USRZ usrcccc batchp4.exe DEDICATED INACTIVE
122 USRT USRAD oracle@cccc (S000) DEDICATED INACTIVE
123 USRT USRAT115 oracle@cccc (S001) DEDICATED INACTIVE
124 USRA USRA vms.exe DEDICATED INACTIVE
128 USRT USRAT210 oracle@cccc (S000) DEDICATED INACTIVE
11 USRAT USRAT205 APPF.EXE NONE INACTIVE
16 USRAT USRAT103 APPF.EXE NONE INACTIVE
17 USRAT USRAD APPP.EXE NONE INACTIVE
21 USRAT USRAT116 APPF.EXE NONE INACTIVE
25 USRAT USRAT207 APPF.EXE NONE INACTIVE
28 USRAT USRAT204 APPF.EXE NONE INACTIVE
29 USRAT USRAT122 APPP.EXE NONE INACTIVE
32 USRAT USRAT108 APPP.EXE NONE INACTIVE
Thanks.

Tom Kyte
December 29, 2003 - 2:38 pm UTC

databases do not run in "shared server mode". A database can be configured to accept shared server connections (and yours is, appp.exe is using a shared connection).

regardless of whether a database is configured in shared server mode -- it is ALWAYS configured for dedicated server. The client can request this in the tnsnames.ora entry (server=dedicated or server=shared).

Also, the database registers with a listener and can register a dedicated server connection, as well as shared server connections. then the tnsnames.ora entry would specify which service it wants to use.


Shared servers in dedicated mode

A reader, December 29, 2003 - 6:31 pm UTC

Tom, Thanks for answering me.
In my case the entry in the tnsnames.ora file used to establish a connection to the database has configured "server=shared".
Shouldn't the shared server processes be connected to the database in shared mode due to the configuration in the tnsnames.ora entry?

Thanks for your help.

Tom Kyte
December 29, 2003 - 6:50 pm UTC

you went to all of the machines of course and checked this out right? not just YOUR machine which will do you and you alone?

(USRAT is using shared server there)

Shared servers in dedicated mode

A reader, December 30, 2003 - 5:26 pm UTC

Thanks for answering Tom.

Well, I didn't check all the machines personally. But I did check the tnsnames.ora file in the server and I checked with Systems and they said that the machines have the entry set to "server=shared" in all the machines.
But what took my attention was that the shared servers were connected in dedicated mode, for example:
10 USRT USRAT104 oracle@cccc (S001) DEDICATED INACTIVE.
After you answer me last time I change my query and I added the view v$process in it, and I found out that the sessions with the program column in v$session like "oracle@cccc (S000 or S001)", in the v$process the program column have a value like "oracle@cccc (TNS V1-V3)". After this I checked all the dblinks in the database and I found a couple of dblinks which are using a dedicated entry to the same database using ipc, and that's why I see "oracle@cccc (S000)" in v$session and I thought that the shared servers were connected as dedicated. The only moment when this is happpening is when the applications use these dblinks using the ipc entry. I will talk with the developers to have them change the queries to fix this.
Now I have another question regarding this, do you know why I see "oracle@cccc (S000)" on v$session and "oracle@cccc (TNS V1-V3)" on v$process for the same session, when the shared server needs to use these dblinks? Shouldn't have the program column the same value in both views?

Thanks for all your help on this.

Tom Kyte
December 30, 2003 - 5:42 pm UTC

don't know why they have different names there -- sorry.

AIX processes of Oracle

Himnish Narang, January 12, 2004 - 7:51 am UTC

Tom,

Just need a clarification on TIME field of the command
ps -eaf in Umix. Following is the output of the command :

dbop 207696 499932 0 18:00:13 pts/2 0:00 grep ora_
dbmgr 475302 1 1 22:03:36 - 9:40 ora_lgwr_BOP
dbmgr 494562 1 0 22:03:36 - 0:00 ora_reco_BOP
dbmgr 502564 1 0 22:03:36 - 0:36 ora_ckpt_BOP
dbmgr 512920 1 1 22:03:36 - 18:56 ora_dbw0_BOP
dbmgr 514628 1 1 22:03:36 - 0:03 ora_smon_BOP
dbmgr 526904 1 0 22:03:36 - 1:23 ora_arc0_BOP
dbmgr 527884 1 0 22:03:36 - 0:01 ora_pmon_BOP

In the TIME field for the processes ora_lgwr_BOP and ora_dbw0_BOP the values shown are 9:40 nd 18:56 respectively. The database was brought up at 22:03:36. Why is there this difference in times for which these processes are getting executed?

Thanx.

Himnish



Tom Kyte
January 12, 2004 - 1:24 pm UTC

that shows the consumed cpu time. dbw0 used 18 minutes, 56 seconds of processing time. you had a busier dbwr than you did a lgwr - they are not "a pair", they do their own job. that is all.

A reader, May 13, 2004 - 12:08 am UTC

Tom,


</code> http://www.dbasupport.com/oracle/ora9i/background_process01.shtml <code>

"The DBWn process writes dirty buffer to disk under the following conditions:
1. When a checkpoint is issued. Please see checkpoint process below.

2. When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers.

3. Every 3 seconds
"


The above link mentions dbwr writes every 3 secs, can you please confirm if this is true.

Thanks.





Tom Kyte
May 13, 2004 - 10:17 am UTC

no, it is not true.

dbwr can be controlled by using the log_checkpoint_* parameters, fast_start_* parameters, (or the max dirty target in older releases)

A reader, May 15, 2004 - 11:00 am UTC

Tom,

This is the response i got from the author of the article that i pointed above.


*************
Hi Rahul,
thanks! I very much appreciate your curiosity. Thanks to Tom also for reviewing my article! Below are some links to my opinion.

[]
As per note 147468.1 on metalink:
"Oracle writes the dirty buffers to disk only on certain conditions:
- A shadow process must scan more than one-quarter of the db_block_buffer
parameter.
- Every three seconds.
- When a checkpoint is produced. "

Dirty buffers flushing is done by DWBR process, hence my opinion on the
article.

[]
As per Nitin Vengurlekar note on metalink (91062.1, very old one but good
insight)
"b. DBWR write dirty buffers when idle (indx value 9)
DBWR is set to timeout after three seconds of inactivity. Each timeout will
awaken DBWR to traverse through the buffer headers (scan size equals
2*_db_block_write_batch) to find and write out any current or dirty blocks
(temporary, a.k.a. sort blocks, are skipped) . If there are any buffers in
the dirty list, then this is also considered non-idle activity. This
prevents DBWR from being too idle."

[]
Jonathan Lewis note:
"Oracle decided to keep trickling dirty blocks to disc at a higher rate than
had been effected by the old 3-second idle write rate (every 3 seconds, dbwr
wakes up and writes a few blocks to disc if it has had no other work in the
interval). "

Jonathan also points that new parameters have also been introduced to
control the dirty blocks writing rate.
</code> http://www.jlcomp.demon.co.uk/faq/log_checkpoint.html

[]  
another article on dbasupport.com by David Nishimoto 
SQL Tuning - File I/O Performance 
"The Database Writer (DBWR) writes data from the buffer cache into the data
files. Every three seconds DBWR wakes up to check the dirty list for blocks
to write. "
--------------

Thanks!
amar kumar padhi
Oracle DBA/InterDev
http://amzone.netfirms.com <code>
Linux advantage: www.linux.org
*****************************


Thanks.

Rahul


Tom Kyte
May 15, 2004 - 12:51 pm UTC

those are accomplished (as jpl's note points out) by setting parameters that control it. it'll not just be happening, you have to set it up to happen.

they are controlled by the very parameters i listed above.

A reader, May 15, 2004 - 5:43 pm UTC


A reader, May 17, 2004 - 6:25 am UTC

FYI.

******************
Hi Rahul,
thanks again. As per Toms reply, I sent an e-mail to Jonathan Lewis and some forums regarding the same. The following is the reply I have received from Jonathan.

From: Jonathan Lewis
Date: 05/17/04 11:29:21
To: Amar Padhi
Subject: Re: DBWR and 3 seconds...

Amar,

I've just run a quick test on 8.1.7.4 and 9.2.0.4,
and when the system is idle (but has a number of
dirty buffers), DBWR wake up every three seconds
on a "background timeouts" event. This does not,
necessarily result in some of the dirty buffers being
written to disk.


Regards

Jonathan Lewis

</code> http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
 
http://www.jlcomp.demon.co.uk/seminar.html <code>
Optimising Oracle Seminar - schedule updated May 1st

*********************


Tom Kyte
May 17, 2004 - 8:01 am UTC

thanks for the followup, appreciate it.

Checking sessions for connections

Sven Bleckwedel, May 17, 2004 - 11:00 am UTC

Hi Tom,

Is there any reason for having differences between these queries ?  I put it together (in cntsess.sql script, mentioned below) for accuracy, but noted that the results were different, because sessions obtained from v$resource_limit are 2 sessions more than obtained from v$session.  Could you explain me why or say what was wrong in this approach ?

- Contents of cntsess.sql script:

select username,count(*)
from v$session          
group by username;      
select *
from v$resource_limit
where resource_name in ('processes','sessions');

- Results obtained:

SQL*Plus: Release 9.2.0.1.0 - Production on Seg Mai 17 11:31:47 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.4.0 - Production
With the Partitioning option
JServer Release 9.0.1.4.0 - Production

SQL> @cntsess

USERNAME                         COUNT(*)
------------------------------ ----------
FISCAL                                 30
SYSTEM                                  2
                                        8

RESOURCE_NAME  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
-------------- ------------------- --------------- ---------- ----------
processes                       45             106        200        200
sessions                        42             103        225        225

Looking these results, I found 42 sessions in v$resource_limit that is 2 sessions more thant that was found when obtained from v$session...


Tks in Adv,
Sven
 

Tom Kyte
May 17, 2004 - 3:50 pm UTC

I see the same, looks like it is always over by two. No idea "why", I don't see any bugs/etc filed against it.

at first, i thought it was the dispatcher/shared server, but even when I turn them off, it is still "two" over.

A reader, May 17, 2004 - 11:06 am UTC

Tom,

How can we find out the number of dirty buffers that are
waiting to be written?

Thanks.

Tom Kyte
May 17, 2004 - 3:52 pm UTC

ops$tkyte@ORA9IR2> select dirty, count(*) from v$bh group by dirty;
 
D   COUNT(*)
- ----------
N      12375
Y        144
 
ops$tkyte@ORA9IR2> update big_table.big_table set id=id where rownum < 1000;
 
999 rows updated.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> select dirty, count(*) from v$bh group by dirty;
 
D   COUNT(*)
- ----------
N      12345
Y        174
 
ops$tkyte@ORA9IR2> alter system checkpoint;
 
System altered.
 
ops$tkyte@ORA9IR2>  select dirty, count(*) from v$bh group by dirty;
 
D   COUNT(*)
- ----------
N      12507
Y         12

 

A reader, May 17, 2004 - 3:57 pm UTC


Difference between pid and spid in v$process

Moorthy Rekapalli, May 28, 2004 - 5:56 pm UTC

Tom,

1)
As per Oracle documentation on v$process
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch3138.htm#1119899
pid is "Oracle process identifier" and spid is "Operating system process identifier".  Please shed some light on the exact difference between these two.  After doing "alter session set sql_trace=true;", to find the trace file (which has the O.S. Process Id), I always do

select p.spid
  from v$session s, v$process p
 where s.paddr = p.addr
   and s.audsid = userenv('sessionid');

From this point, I do my tkprof etc...

Recently, I noticed that when you "connect / as sysdba", that session and all background processes like SMON, PMON, DBWR, LGWR etc... have the same audsid.

SQL> connect / as sysdba
Connected.
SQL> select program from v$session where audsid = 0;

PROGRAM
------------------------------------------------
oracle@bhradcDEV0 (PMON)
oracle@bhradcDEV0 (DBW0)
oracle@bhradcDEV0 (LGWR)
oracle@bhradcDEV0 (CKPT)
oracle@bhradcDEV0 (SMON)
oracle@bhradcDEV0 (RECO)
oracle@bhradcDEV0 (ARC0)
oracle@bhradcDEV0 (ARC1)
sqlplus@bhradcDEV0 (TNS V1-V3)
oracle@bhradcDEV0 (LNS1)

10 rows selected.

I thought that userenv('sessionid') will give a unique audsid, from which you can identify your own session.

2)
Please explain the reason for using the combination of (sid, serial#) to identify a session.  I always wondered why Oracle did not use a surrogate key like we generate one for Primary Keys from sequences.  In what circumstances Oracle generates a different serial# under the same sid?

3)
Why Oracle database engine is implemented as Bunch of Processes on UNIX platforms Vs a single exe on Windows.  In Windows implementation - even in dedicated server mode, when you make a connection, it looks like that is a thread. Whereas in UNIX, it will be a seperate process.  I heard that threads are more efficient and did not understand why that model was not implemented in UNIX.

By the way, which part of Oracle that you don't know.  Your depth of knowledge on so many things amazes me.

Thanks in advance,
Moorthy.

 

Tom Kyte
May 28, 2004 - 8:12 pm UTC

1) sys is special.  I've taken to using:

select * from v$session where sid = (select sid from v$mystat where rownum=1);


ops$tkyte@ORA9IR2> l
  1  select a.spid dedicated_server,
  2        b.process clientpid
  3    from v$process a, v$session b
  4   where a.addr = b.paddr
  5*    and b.audsid = userenv('sessionid')
ops$tkyte@ORA9IR2> /
 
DEDICATED_SE CLIENTPID
------------ ------------
4545         4544


that shows the dedicated server process id on the server machine (or shared server).  Then the client pid is the client process id AS TRANSMITTED BY THE CLIENT (eg: if you have a windoze client connecting to your unix database, you'll see funny looking process id's in there -- they are windoze process/threads)

2) it just happens.  the primary key of that "view" is sid/serial#

3) on unix, multi-process is the correct, most efficient, scalable architecture in general (on MVS we are architected for MVS, on netware -- single process like windoze).  On windoze we are done as a single process with threads cause that is what makes that OS happy.


What part of Oracle don't I know -- well, the further you get from the database, I guess it is like sound -- falls off with the square of the distance... 

Admirer

Sagi, July 30, 2004 - 10:36 am UTC

Tom,

I have seen a strange thing today. I got a call from a user complaining not able to connect to the db because of max conn. exceeded.

ORA-00020: maximum number of processes ( number) exceeded

I am aware that they have reached the limit which i have imposed.

But the strang thig here is, I wanted to increase this parameter and therefore was trying to connect "/ as sysdba" on the DB server.

But even for sysdba connection this was throwing error. Isnt this strange. I then got rid of the shared and scemaphore.

The DB is a 8i.

What is your advice on this.

Regards,
Sagi

Tom Kyte
July 30, 2004 - 5:34 pm UTC

it was probably a sqlplus'ism -- i tested (and confirmed), svrmgrl with "connect internal" worked correctly in this case in 8i

Admirer

A reader, August 01, 2004 - 12:25 am UTC

Tom,

You are really genious.

Yeap, I tried from SQLPLUS.

By what I am not able to understand is how does it matter whether it was SQLPLUS or SVRMGRL.

I was connecting with SYSDBA. So it should have worked..

Would appreciate more technical explanation on this.

Thanx in advance.

Regards,
RK.

Tom Kyte
August 01, 2004 - 10:36 am UTC

it was a "product issue". svrmgrl worked, sqlplus did not.

Number of processes in mts

Jitendra Agrawal, August 04, 2004 - 4:39 am UTC

Hi,

I am using shared server (mts). The database was intalled as a dedicated server and later configured in shared server mode.

The initialization parameter 'processes' is set at 130

processes integer 130

I read somewhere :
The LGWR needs to scan all processes to find each process that is waiting for the commit to be written.

30 minutes statspack snapshot give the following wait events
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file sync 13,456 10 3,181 236 1.0
log file parallel write 8,397 0 1,014 121 0.6
control file parallel write 298 0 139 467 0.0
local write wait 32 8 16 508 0.0
buffer busy waits 9 2 8 867 0.0
enqueue 1 0 1 1011 0.0
process startup 7 0 0 20 0.0
latch free 3 1 0 8 0.0
db file sequential read 47 0 0 0 0.0
single-task message 12 0 0 1 0.0
SQL*Net break/reset to clien 10 0 0 1 0.0
db file parallel write 804 804 0 0 0.1
control file sequential read 37 0 0 0 0.0
virtual circuit status 10,939 7 458 42 0.8
SQL*Net message from client 75,742 0 18 0 5.6
SQL*Net message to client 75,749 0 0 0 5.6
-------------------------------------------------------------

Also number of processes from v$process is 19. Number of connections to database are approximately 70.

Will reducing the parametter 'processes' help in reducing log file sync wait? What is the ideal value of 'processes' in shared server environment?

Following is the result for sessions parameter

mts_sessions integer 143
sessions integer 148
shared_server_sessions integer 143


Tom Kyte
August 04, 2004 - 10:05 am UTC

log file sync wait is the wait registered by the client when they post lgwr to commit. it is the time spent waiting for lgwr to actually write the data out.

the way to reduce log file sync waits are:

a) ensure you are not artificially committing before you should (that is, commit less often, when you commit -- you wait. if you do your entire transaction and then commit -- you have done it right. if you are committing each statement (the default in jdbc/odbc for example!!!) not only are you breaking every rule of database management, you are killing performance.

b) make your logs faster, ensure that lgwr has uncontended access to them. make sure there are no unix buffer caches in the way (eg: forcedirectio as a solaris mount operation for example -- varies by OS, putting logs on RAW definitely does that (and since you never need to touch online redo logs -- that they are raw is not meaningful to the DBA's, they should not be afraid of it since they never touch it)




Any relation of the initialization parameter 'processes' with log sync wait

Jitendra Agrawal, August 05, 2004 - 4:46 am UTC

Thanks Tom,

I will review the code to see if I am using some autocommit stuff anywhere and will try to make appropriate changes to reduce number of commits.

You haven't answered my question though !! Is the log sync wait related to the parameter "processes" in anyway?

Secondly, by raw device do you mean to create another partition? (I'm linux)

Tom Kyte
August 05, 2004 - 12:52 pm UTC

nope. processes and log file sync are not related.

raw devices are "unformatted disks that are not mounted as file systems". Your SA should know what they are.

question

A reader, August 14, 2004 - 5:58 pm UTC

1. so to give an analogy that shows difference
between connection and session, can we say that
the connection is the pipe and the session is a
"wire" going through the pipe..You can have many
wires of different colors sharing the pipe (many sessions
each with different authentication (user/passwd))?

2. when you use session pooling, you maintain a pool
of sessions; when you use connection pooling you maintain
a pool of connections. When I imagine this, in the case of
connection pooling, i would think that you would still
authnticate yourself to the database in a mid tier
to get the connection before putting it in your pool. So
how does it differ from session pooling in middle tier
(I mean what steps are taken in creating a connection
for a conneciton pool and what steps are taken in creating
a "session" for a session pool?

thanx!


Tom Kyte
August 14, 2004 - 7:02 pm UTC

1) sure, yes.

2) connection pooling, ala middle tier speak, is in fact session pooling.

one more question

A reader, August 14, 2004 - 6:12 pm UTC

can we create a session without "authnenticating"? If so, how?
and if it is possible, then can we say that in connection
pooling you pool "connections" without authentication whereas
in session pooling you pool "sessions" that are authneticated
and ready to use?


Tom Kyte
August 14, 2004 - 7:02 pm UTC

yes, OCI programs do it all of the time.

but, as stated above -- in middle tier speak the connection pools are pooling "database sessions".

thanx!

A reader, August 15, 2004 - 4:35 pm UTC

From the OCI programmer guide there does seem to be
difference in session pooling and connection pooling:
"When to Use Connection Pooling, Session Pooling, or Neither
-----------------------------------------------------------
If database sessions are not reusable by mid-tier threads (that is, they are stateful)
and the number of back-end server processes may cause scaling problems on the
database, use OCI connection pooling.
If database sessions are reusable by mid-tier threads (that is, they are stateless) and
the number of back-end server processes may cause scaling problems on the
database, use OCI session pooling."

it seems that session pooling is the case when you maintain
stateless sessions in the middle tier. Connection pooling
seems to imply stateful sessions.

Any comments?
what would be an example of a stateful session?


Tom Kyte
August 16, 2004 - 8:21 am UTC

there is in OCI, however I am referring to the connection pool 99.99999% of the people out there would be talking about -- a middle tier java/jdbc connection pool.


oci is potentially much more sophisticated.


in oci, a single physical connection can support multiple sessions.

stateful is totally up to the application -- in a multi-threaded application -- if each thread opens a "session" and needs the state of that session to be "well known" from call to call (eg: it is using a plsql package or something and having the state MAINTAINED is mandatory) -- it would be "stateful"

thank you!!!

A reader, August 16, 2004 - 9:59 am UTC


Opening multiple sessions

Peter, August 16, 2004 - 3:55 pm UTC

Tom,

I have a Pro* C code which I use to extract data. I can do two of the following.

I am accessing an Oracle DB from a Pro*C client application.

1) Fetch the data using a single session and let my application split and parse and process it and do the necessary i/o operations.
2) Open multiple sessions (I basically run multiple instances of my stand alone executable) and process the data and do the necessary i/o operations.

Can you suggest me which one would be the better option to select? Also if I select 2 what should i consider to select the maximum number of sessions on my client. Am a novice in this and your guildance would help me great.

Thanks,
Peter


Tom Kyte
August 16, 2004 - 8:04 pm UTC

depends on your client - a desktop computer with a slow ATA drive -- not going to matter too much (parallel "2" would be about it).

a huge server with plenty of resources -- processes = 2*number of cpu would be a good start.

q on v$session output

A reader, August 24, 2004 - 11:06 am UTC

hello tom
I have 9ir2; I connect as sys and run the following select:
---
sys@ORA92> set head on
sys@ORA92> select type, process, server, username from v$session;

TYPE PROCESS SERVER USERNAME
---------- ------------ --------- ------------------------------
BACKGROUND 23150 DEDICATED
BACKGROUND 23152 DEDICATED
BACKGROUND 23154 DEDICATED
BACKGROUND 23156 DEDICATED
BACKGROUND 23158 DEDICATED
BACKGROUND 23160 DEDICATED
USER 23162 DEDICATED
USER 23164 DEDICATED
USER 23180 DEDICATED SYS

9 rows selected.
---

I don't understand why there are two extra "USER"
rows (with null "username" values). Can you
explain what they imply? I ran this select right after
bouncing my database. There is no one else connected
to this database.

Many thanx!


Tom Kyte
August 24, 2004 - 11:15 am UTC

show parameter job_queue_processes



i'd guess they are job queue processes, you have a cjq0 process (job queue coordinator) and some job processes going. tis normal.


select program out too, that'd tell you more.

thank you!

A reader, August 24, 2004 - 11:31 am UTC

that was a fast response!
Yes you are right! One of them is cjq0 (job queue coordinator) and the other is qmn0 (no idea what -
must be a job.)

Thanx!

Tom Kyte
August 24, 2004 - 11:46 am UTC

qmn0 is the AQ process, aq_tm_processes is the init.ora parameter that controls that.

if you do not use jobs, do not use AQ, you can set both to zero if you like and these will "disappear"

thank you!!

A reader, August 24, 2004 - 11:51 am UTC


another meaning of pseudo

A reader, August 26, 2004 - 9:23 am UTC

The pseudo value also indicates lightweight sessions
created when you use oci connection pooling. You can
confirm it by using the OracleOCIConnectionPool classes
to create connection pool and then get connection from
it. Each time you get a connection, you get a row with
"PSEUDO" under the "server" column in your
query from v$session. This represents a session
created on top of the already existing connection from the
oci connection pool.
Any comments, tom?

process and connection

A reader, September 02, 2004 - 12:10 pm UTC

From the above, can we conclude that in the dedicated
process model, one process always corresponds to one
connection, and may or may not correspond to one session
though, generally it does?

thanx!

Tom Kyte
September 02, 2004 - 1:17 pm UTC

yes.

10g r1 v$session process column

A reader, September 19, 2004 - 4:28 pm UTC

does not seem to show the correct process id.
For example in my system,
after establishing 2 connections I get the following results.

sys@ORA10G> select type, process, server, username
2 from v$session
3 where type != 'BACKGROUND'
4 and username != 'SYS';

TYPE PROCESS SERVER USERNAME
---------- ------------ --------------- ----------
USER 1234 DEDICATED SCOTT
USER 1234 DEDICATED SCOTT

sys@ORA10G> !ps -ef | grep oracleora10g
ora10g 17926 1 0 13:01:43 ? 0:00 oracleora10g (LOCAL=NO)
ora10g 18328 17570 1 13:04:53 pts/5 0:00 /bin/tcsh -c ps -ef | grep oracle
ora10g
ora10g 17922 1 0 13:01:42 ? 0:00 oracleora10g (LOCAL=NO)
ora10g 17571 17570 0 17:12:58 ? 0:01 oracleora10g (DESCRIPTION=(LOCAL=
YES)(ADDRESS=(PROTOCOL=beq)))

In my select I am supposed to see a process id 1234.
Where as if I do a ps I get different process ids.
1234 does not seem to be indicating a process id.
Am I missing something?
Actually, in my other runs with 9i, turns out that the
process column does not always get populated...

Thanx!

Tom Kyte
September 19, 2004 - 5:12 pm UTC

ops$tkyte@ORA10G> select process from v$session where username = user;
 
PROCESS
------------
24491
 
ops$tkyte@ORA10G> !ps -auxww | grep 24491
tkyte    24491  0.3  0.3 17996 6584 pts/2    S    16:52   0:00 sqlplus
tkyte    24494  0.0  0.0  4208  988 pts/2    S    16:52   0:00 /bin/bash -c ps -auxww | grep 24491
tkyte    24496  0.0  0.0   172   12 pts/2    R    16:52   0:00 grep 24491


<b>process is the client id, sqlplus for example</b>

ops$tkyte@ORA10G> select a.spid dedicated_server,
  2             b.process clientpid
  3    from v$process a, v$session b
  4   where a.addr = b.paddr
  5     and b.audsid = userenv('sessionid')
  6  /
 
DEDICATED_SE CLIENTPID
------------ ------------
24493        24491
 
ops$tkyte@ORA10G> !ps -auxww | grep 24493
ora10g   24493  0.1  1.2 245376 26032 ?      S    16:52   0:00 oracleora10g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
tkyte    24497  0.0  0.0  4216  996 pts/2    S    16:53   0:00 /bin/bash -c ps -auxww | grep 24493
tkyte    24499  0.0  0.0  3680  664 pts/2    S    16:53   0:00 grep 24493
 


<b>shows you the dedicated/shared server process id you are/were using and your client process id</b>


Note: client process id is local to the server your client is on -- it is not necessary a process ID on the database server. 

Thanx!

A reader, September 19, 2004 - 5:24 pm UTC


What is actual communication channel?

Mandell, September 23, 2004 - 1:09 pm UTC

I understand how the communication works when going through a listener. I am not clear how it works through bequeath on a local host.

My problem is that I have two Tru64 UNIX servers. On one, a local bequeath session (specifying just the username/password) runs at almost the same speed as a listener session (started by specifying username/password@database). On the other system, the bequeath session takes almost twice as long to process data (i.e. multiple "select * from organization;") and I understand that for some tasks the difference can be up to 8X as long.

I am trying to understand the communications channel so that I can better understand the bottleneck.

Tom Kyte
September 24, 2004 - 9:19 am UTC

bequeath just uses IPC (interprocess communication) on the server - like socketpair() (probably).


with bequeath -- your process (say sqlplus) forks() itself and execs() the Oracle binary -- telling what file descriptors to read from and write to -- socketpair() is the most typical approach for IPC between a parent and direct child.

I would trace it and see what the wait events are -- if you see lots of sqlnet message from client/server -- there is an issue with IPC on that particular patch level of that particular server.

Just fire up sqlplus, use the 10046 level twelve trace -- and if you have 9i, tkprof will be kind enough to display all you need, if you have 8i, you'll need to read the raw trace file (if you have Expert One on One Oracle -- chapter 10 goes through that)

How to collect the following key information?

Kiran, October 22, 2004 - 6:30 pm UTC

Tom - We have a large DB where so many applications access and do both reporting and insert/updates/deletes. There are small - medium - long transactions. We are now planning to develop an application which should collect the following key information from the database.

1) List all the applications connected to the DB? Doesnt have to be active connections. If I pass a start and end time, I would like to find out the app names (may be ip address/port where the application intiated the request is sufficient)
2) For each application, how many transactions?
3) How long each transaction took to process the request?
4) Any details about the transaction? for eaxmple: The query, number records return to the client, or updated/deleted or inserted.
5) If I can get the user id that is being used to connect to the database per application will be good also.

Any other useful information which will allow us to determine whether the location of this master DB is the right place or not or whether we can split the DB and span across various part of the world by user community/application. By looking at the IP/ports, we have another system where we can map these IPs to organization and locations.

If you can give any help on this information adn how to get this info from the oracle db that will be great.

Again, I am not looking for active connections - but if we can have a process in place where I can capture these information while an app make a request that will be fine too. If I can avoid adding more processes to the system then I would prefer not to add that extra workload.

Thanks Sir.
Kiran

Tom Kyte
October 23, 2004 - 9:35 am UTC

1) audit connects

2, 3) unless the "application" records this, statspack will give you aggregate information. we don't really have any concept of "application" in the database itself. we track things by sessions while the session is alive and in the aggregate for the entire system.

your applications need to track this information (and they should -- all of mine do! every page on this site -- i can tell you precisely how long each one took to generate for the last year or so..)

4) see 2, 3 -- this is called code instrumentation. I do it in every application I write.

5) see #1


How to determine max sessions

Sean, October 27, 2004 - 3:20 pm UTC

Hi Tom,

We have a application which uses C++. Every day it generate 30 dead session on database, which means that these session are not active anymore, but still hang on Oracle. Right now, we need to reboot the web server about every five days to kill these sessions (We could kill there sessions by "kill session", but we are not 100 percent sure which one to kill). Sure, developers will migrate C++ code to Java eventually.

We would like to increase max session to say 500, so we can reboot web server less often. We believe there are must be some limit to set max session even for inactive session. I understand that I have to modify Unix kernal parameters.


Here are my questions:
(1) Do I need to change other parameters before I change max session number?
(2) How do I know that the value of max session is too high?


DB info: Oracle 9204 on RedHat Linux.
Server info: 3G memory.


Thanks so much for your help.

Sean


Tom Kyte
October 27, 2004 - 4:31 pm UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/network.920/a96580/profile.htm#486162 <code>

if they are truly dead sessions.

don't know what C++ has to do with it?

inactive session

Sean, October 27, 2004 - 6:29 pm UTC

(1) What developer told me is this:

It is Java web application, but it calls some C++ module and open new Oracle session. If a user logout improperly, the C++ module would hang there, so the Oracle sessions do not die though not active anymore.

(2) Thanks so much for the solution.

Here are questions about expire_time setting:
(a) Is this setting to kill the terminated session? It seems that Oracle should kill the terminated session automatically without this expire_time in sqlnet.

(b) Is this setting to kill inactive session? If the answer is Yes, then how long Oracle would wait before it kills inactive session? I don't want Oracle to kill the inactive session immediate just because it is inactive. It would be ideal if Oracle can kill the session if it is inactive, say, 5 hr.

(3) Some people suggested that we can use shared server so we can reuse these inactive session, but my manager worry the decrease of performance. Will shared server decrease the performance in any way? The manual states that we should use dedicated session for job or rman backup, but it does not say whether shared server would decrease the performance for regular user.

Thanks so much for your help.

Sean


Tom Kyte
October 27, 2004 - 6:40 pm UTC

1/2) well, if the C++ appliation just "hangs out", expire time won't do a thing. This was invented for the BSOD (blue screen of death) where the client just "goes away". you are saying that the client DOESN'T go away, they just go "silent"


you can look at the last_call_et in v$session, it'll tell you how long an inactive session has been inactive -- you can disconnect it.

3) sessions would still stack up. you would just be changing the wall you hit -- moving it from the left to the right.

shared server is a much longer code path than dedicated server.

How to determine the max session without decreasing the performance

Sean, October 27, 2004 - 8:51 pm UTC

Hi Tom,

The solution of using last_call_et is the good solution for us, as least I think.

I would still like to know how to determine the max session for our system without decreasing the performance, so it will be easy for me to present solutions to manager. With most of inactive session, I tend to increase the max session to 500 (Currently, we set max session to 170).

Thanks so much for your help.

Sean



Tom Kyte
October 27, 2004 - 10:12 pm UTC

upping the number of sessions will not really affect performance, it'll increase the size of SGA components, but with the exception of some larger v$ views (and hence perhaps longer query times against them), it won't be a performance thing so much.

How to set max session

Sean, November 01, 2004 - 5:09 pm UTC

Hi Tom,

I understand that session and process is not one to one relationship. Suppose I want to increase session parameters to 300 on dedicated server, how do I determine the value of process parameter roughly?

Thanks so much for your help.





Tom Kyte
November 02, 2004 - 6:43 am UTC

if every session is destined to be a dedicated server (1 to 1) then you want processes to be big enough for all of your sessions plus whatever backgrounds you run (dbwr, job queues, aq, smon, pmon, etc)


Hidden sessions

reader, November 09, 2004 - 8:07 pm UTC

Tom,
I ran this and getting list of sessions and programs.
However first five have no name of program what are they.
And why sessions = ( processes * 1.1 ) + 5
Thanks for your time.

select count(*), s.username, s.program
from v$session s, v$process p
where s.paddr(+) = p.addr
group by s.username, s.program
/

COUNT(*) USERNAME PROGRAM
----- -------------------------- ---------------------
5
1 oracle@dbsu (CJQ0)
1 oracle@dbsu (CKPT) 1 oracle@dbsu (DBW0)
1 oracle@dbsu (DBW1)
1 oracle@dbsu (LGWR)
1 oracle@dbsu (PMON)
1 oracle@dbsu (RECO)
1 oracle@dbsu (SMON)

Tom Kyte
November 09, 2004 - 8:56 pm UTC

they are the backgrounds, the oracle system itself. cjq0 -- job queue coordinator, ckpt -- checkpoint, dbw1 -- database block writer, lgwr -- log writer and so on.

processes vs. sessions

A Reader, December 23, 2004 - 5:12 pm UTC

Tom,

At the beginning of this thread, you gave a very good example to explain one process with two sessions (sqlplus connection and autotrace). Could you please give me more cases that one process could have more than one session?

In our production DB, we occasionally got ORA-00018 error. Our current setting for 'SESSIONS' parameter is 170. I checked with our development team and they said all applications use maximum of 80 connections. I don't know how those extra sessions came from.

I tried to write a servererror database trigger to catch all opened sessions, but it does not work for ORA-00018 and ORA-00020 errors based on Oracle documented bug.

Please help. Thanks as always.


Tom Kyte
December 23, 2004 - 7:58 pm UTC

when it happens, log in and capture the contents of v$session and v$process (sysdba should be able to crunch his way in -- i just tested it, when you get ora-18, you can still sysdba in).


you should be able to use that information to diagnose excatly what was running.

Re: processes vs. sessions

A reader, December 24, 2004 - 9:28 am UTC

Tom,

When the ORA-00018 happened, there was no trace file generated and no error written to alert log file. So we could not catch the momentary event. Then how to handle it?

Thanks for your help.

Tom Kyte
December 24, 2004 - 10:00 am UTC

guess I'd write a monitoring job (dbms_job) that woke up every 15 minutes, counted the number of rows in v$session and if it exceeded say 150 -- log it, email you.

A reader, January 31, 2005 - 3:53 pm UTC

Tom,

I have 10g on Linux x86, why are the so many listener processes running, shouldn't there be only one?

ps -ef | grep tns | grep -v grep
oracle 7690 1 0 Jan28 ? 00:00:00 /u01/oracle/product/10.1.0/db_1/bin/tnslsnr LISTENER -inherit
oracle 7691 7690 0 Jan28 ? 00:00:01 /u01/oracle/product/10.1.0/db_1/bin/tnslsnr LISTENER -inherit
oracle 7692 7691 0 Jan28 ? 00:00:01 /u01/oracle/product/10.1.0/db_1/bin/tnslsnr LISTENER -inherit
oracle 7693 7691 0 Jan28 ? 00:00:00 /u01/oracle/product/10.1.0/db_1/bin/tnslsnr LISTENER -inherit


Thanks.

Tom Kyte
January 31, 2005 - 4:11 pm UTC

what does your listener.ora look like? I've only got one on my machines.

A reader, January 31, 2005 - 7:08 pm UTC

Tom,

Here is mu listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = test10g)
(ORACLE_HOME = /u01/oracle/product/10.1.0/db_1)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
)


Tom Kyte
January 31, 2005 - 8:28 pm UTC

if you shut it down and start it up and it keeps happening, contact support -- I've not seen that.

A reader, February 01, 2005 - 9:56 am UTC

Thanks Tom..

I did that and its still the same and I also noticed the same for ocssd

oracle 1359 1322 0 Jan28 ? 00:00:02 /u01/oracle/product/10.1.0/db_1/bin/ocssd.bin
oracle 1382 1359 0 Jan28 ? 00:00:01 /u01/oracle/product/10.1.0/db_1/bin/ocssd.bin
oracle 1383 1382 0 Jan28 ? 00:00:00 /u01/oracle/product/10.1.0/db_1/bin/ocssd.bin
oracle 1384 1382 0 Jan28 ? 00:00:30 /u01/oracle/product/10.1.0/db_1/bin/ocssd.bin
oracle 1391 1382 0 Jan28 ? 00:00:00 /u01/oracle/product/10.1.0/db_1/bin/ocssd.bin
oracle 1392 1382 0 Jan28 ? 00:00:00 /u01/oracle/product/10.1.0/db_1/bin/ocssd.bin
oracle 1393 1382 0 Jan28 ? 00:00:00 /u01/oracle/product/10.1.0/db_1/bin/ocssd.bin
oracle 1394 1382 0 Jan28 ? 00:00:13 /u01/oracle/product/10.1.0/db_1/bin/ocssd.bin
oracle 1395 1382 0 Jan28 ? 00:00:18 /u01/oracle/product/10.1.0/db_1/bin/ocssd.bin


Will contact support.

memory used by a dedicated session

Manish Upadhyay, February 09, 2005 - 9:59 am UTC

If a user just got connected to the database using dedicated session , how much memory(real and virtual) on the server it uses , assuming unix server.

and how to find this.

Tom Kyte
February 09, 2005 - 2:39 pm UTC

assuming unix, including linux, hpux, aix, solaris (sparc and x86), etc etc etc.

all different
all with different commands
all with different (slightly) architectures

hmm.

anyway, to see the pga memory used (what we've allocated using malloc()) use v$sesstat or v$mystat -- pga memory..


max, February 11, 2005 - 10:07 am UTC

is there any view available to determine whether shared server connections would be accepted or not?

Tom Kyte
February 12, 2005 - 7:45 am UTC

lsnrctl services


would show if it was configured.

v$parameter can show you if it was setup (you could peek at the shared server parameters to see what values they have)

oracle pseudo processes

Premchand, February 21, 2005 - 2:25 am UTC

Hello Tom,



i have small confusion on oracle pseudo processes. in one my production server always when ever i query
sql> select server,count(server) from v$session group by server; im getting the output

SERVER COUNT(SERVER)
--------- -------------
DEDICATED 22
PSEUDO 63

my dout is will these 63 killed/pseudo sessions will still occupied in my memory. reason asking u is this oracle is running on windows 2000 standard server where im facing frequently the follwing error
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNS:protocol adapter error
TNS-00510: Internal limit restriction exceeded
32-bit Windows Error: 8: Exec format error


and the details of PSEUDO connections
USERNAME PROGRAM||''||OSUSER||''||SERVER||''||STATUS||''||MACHINE||''||LOGON_TIME
------------------------------ -----------------------------------------------------------------------------------
FDSYS acwfd PSEUDO KILLED FIN-JNSHAH 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRMS hrmsys PSEUDO KILLED NTMAIN\AMITNAGAR 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05
HRVIEW JDBC Thin Client tomcat4 PSEUDO KILLED real2.alembic.co.in 21-FEB-05




Regards
Premchand.R


Tom Kyte
February 21, 2005 - 10:15 am UTC

"U" doesn't work here? I don't know who "U" is.

have you issued a kill on those sessions?

oracle pseudo processes

Premchand, February 22, 2005 - 1:36 am UTC

Yes Tom, These all are the killed sessions. Will these killed sessions occupied in memory i mean in oracle.exe. Reason I told you some times gives Listener Error.

Thanks & Regards
Premchand

Tom Kyte
February 22, 2005 - 8:35 am UTC

they will take nominal resources on the server (but they do consume a process) - did this happen (the kills) because the middle tier "disappeared", perhaps what you wanted to really do was enable dead client detection so they just "go away", right now, they are waiting for the middle tier that probably disappeared to ask them to do something so they can report back "you have been killed"

oracle pseudo processes

Prem, April 10, 2005 - 3:01 am UTC

Thanks Tom,
All the pseudo sessions are killed sessions. We have profile to kill when if the idle time 2min.

How to calculate how much exact RAM this pseudeo sessions are taking.

Thanks & Rgds
Prem


Tom Kyte
April 10, 2005 - 10:24 am UTC

probably none of any concern as they will be idle and mostly paged out.

an OS utility could tell you, just remember to take the shared memory into account.

32-bit Windows Error: 8: Exec format error

Prem, April 27, 2005 - 2:45 pm UTC

Hello TOM,

From the last three months we are facing in the month ends the following error :
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNS:protocol adapter error
TNS-00510: Internal limit restriction exceeded
32-bit Windows Error: 8: Exec format error

Server Details:
OS : Windows 2000 Standard Server
Oracle : 8.1.7.4.0
RAM : 3.5 GB
SWAP : 5.3 GB
Application: TOMCAT.
TOTAL SGA : 1,164 MB


After I taken this case I found SORT_AREA_SIZE 97MB,after doing certain calculations to reduce the size 3MB by 4 different cycles by reducing the each 25MB size.

Due to this in last month we have not got any one error on 32-bit Windows Error:8/TNS error.

But again this month we are facing the same problem for this the analysis what I have done:

Observations
---------------------
SHARED_POOL_SIZE
CREAET_BITMAP_AREA_SIZE
HASH_AREA_SIZE
SORT_AREA_SIZE

Taken Action
-------------------
1) SHARED_POLL_SIZE :
a) checked in regular interval the SHAREDPOOL free size is showing always 600MB
b) Execute the following script which is from metalink:
The output of this query always give MIN SHAREDPOOL is 3MB to4MB.
Due to this output I reduced 99MB to 75MB.

2) CREAET_BITMAP_AREA_SIZE :
Not using any Bitmap indexes so I reduced from 8MB to 1MB

3) HASH_AREA_SIZE :
Reduced from 6MB to 4MB


Can you guide what else I can do to avoid this error.

Thanks & Regards
Prem


Script from metalink:
-----------------------------
select to_number(value) shared_pool_size,
sum_obj_size,
sum_sql_size,
sum_user_size,
(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
from (select sum(sharable_mem) sum_obj_size
from v$db_object_cache where type <> 'CURSOR'),
(select sum(sharable_mem) sum_sql_size
from v$sqlarea),
(select sum(250 * users_opening) sum_user_size
from v$sqlarea), v$parameter
where name = 'shared_pool_size';

Output:
-------
SHARED_POOL_SIZE SUM_OBJ_SIZE SUM_SQL_SIZE SUM_USER_SIZE MIN_SHARED_POOL
---------------- ------------ ------------ ------------- ---943,718,400 18,084,705 360,091,276 2,250 491,631,700



Tom Kyte
April 27, 2005 - 3:00 pm UTC

are you running out of physical ram when this happens?

what is your current sort area size (it was not clear), how many users

RE: 32-bit Windows Error: 8: Exec format error

Vinayak, April 27, 2005 - 4:03 pm UTC

Prem,

this usually happens on Windows if the SGA size is high and the number of connections to the database is high. In your case, SGA is about 1.1GB, so you may get the above error when the number of connections reaches about 400 or so.
(assuming you are not running any other instance on that server)

Possible Solutions:
Try to decrease the SGA size.

Are you using dedicated servers only? If yes, you may switch to MTS.

Check with your SA is your server is configured with 3G switch. With 3G switch, you can handle more connections than the defailt configuration.

You may also want to read about Intel ESMA (Extended Server Memory Architecture).

Tom Kyte
April 27, 2005 - 4:37 pm UTC

or less than 400 with a big sort area size.

that is why I asked the above questions -- how big and how many.

stack size for a thread on windows

Anjo Kolk, April 27, 2005 - 6:03 pm UTC

While doing benchmarks on windows with large number of users I also remember that the default stacksize of the thread was large and as a result it would limit the number of possible connections. You could actually change the default stack size on windows with some utility to reduce the default stack size (smaller). Can't remember the name of the utility (orapstack or orastack, not sure). Remember that we had to do it though.

32-bit Windows Error: 8: Exec format error

Prem, April 28, 2005 - 1:47 am UTC

The Current SORT_AREA_SIZE is 1.5 MB and the number of users is connecting around 30 to 40 users.

These are using Dedicatated sessions.

TOM your are say "are you running out of RAM" Can you give some inputs to check this.

Anjo Kolk : That is ORASTACK.. before to execute i want to know how to check what is the current stack size of Oracle.exe / Listener.exe etc...

And pls give advice to go ahead with this option.

Thanks & Regds
Prem

Tom Kyte
April 28, 2005 - 7:50 am UTC

use perfmon or any OS tools.

definitely sounds like your batches are running out of ram, they can and will have multiple sort areas allocated and in 8i -- pga tended to grow -- not shrink (in 9i with workareas that all changes)

are your processes doing big hash joins or big sorts, how many sorts might they have active. during the months end is your load INCREASING to close books or something (why is months end special)

RAM usage

Todor Botev, April 28, 2005 - 6:03 am UTC

Perm,
You can follow the RAM and CPU usage with the Task Manager.

TNS-12500: TNS:listener failed to start a dedicated server process

Prem, April 29, 2005 - 4:12 pm UTC

Hello Tom,

1) As you said, how to check processes doing big hash joins or big sorts?

2) The Month ends they finish to close all the fin books and to also to prepared PAYSLIPS.


Can you suggest some to reduce this error.

Thanks & Regards
Prem

Tom Kyte
April 29, 2005 - 6:44 pm UTC

1) v$sql_plan

2) right, so you are running batch, big sorts/hashs, adding processing (more users) and run out of memory.

lower use of memory.

maximum number of processes exceeded

Kara, May 08, 2005 - 1:52 pm UTC

Hi Tom

I am getting this error 'ORA-00020: maximum number of processes exceeded' in the trace file of Oracle 9i R2 on RH Linux. The database basically halts and can't even login as SYSDBA to kill some sessions. Strange enough when I do login as SYSDBA the only thing it gives me is a 'Connected to an idle instance'. That is strange since when I do a ps -ef | grep ora_ at OS prompt, I see my Oracle instance is running? The only way to get around this is to stop the application which is using Oracle, release some oracle processes and login properly to see what's happenning, since I don't want to restart the box. When I stop the application, some oracle processes are immediately free and I can connect as sysdba.

I get this error when there is a runaway job which tries to refresh materialized views or when this job can not complete. Why do you think this happens?

init.ora parameter processes is currently 300.

Do I need to up the processes and what's the limit?

Many thanks for all your help.

Tom Kyte
May 08, 2005 - 2:27 pm UTC

you have hit the max number of processes you told Oracle to spawn, we are listening to you and what you told us to do.

I do not think the "runaway" job -- if it is a materialized view refresh -- is the culprit, that would take a single session (unless you have a huge degree of parallelism, but if you have 300 processes out there -- I cannot imagine it would be that)


So, how many users do you have here? How many sessions are going.

maximum number of processes exceeded

Kara, May 08, 2005 - 3:42 pm UTC

Hi Tom

Thanks for your quick response!

There are as many sessions as processes can give, configuration is dedicated server, since there is the Java Application Server (not Oracle Java Server) doing connection pooling where it grabs 1,2,3... oracle process(es) <-> session(s) and associates that with couple of Java Application Server connections, the so called midlle tier. That relationship is 1 oracle process / session -> many java connections, the connection pooling, done by the Java app server, not Oracle. When the application server boots up it grabs about 70 Oracle Processes straight away, I counted it, I know that for sure. When I run ps -ef command on OS to count the Oracle processes, I see that straight away. Again there are few Oracle instances on this box and only one of them, the one supplying data to the java application on the internet gets stuck once in a while with max processes. So when I 'Select count(*) from v$session' at the time of the peak it is 288 or something.

As with regards to users, users are the Internet and it can get busy, and the pages they are browsing (they are mainly browsing, not updating), can be from the materialized view which is trying to refresh via that job which fails. When I kill the session which runs the job, and it rolls-back, things cool down and go the normal way. I re - run the job after midnight, not busy hours, and completes easy peasy, but not during the day and when lots of changes in the logs (mlog$). Under similar circumstances, I used to get this error in the past I even used to get the 'ORA-01555 Snapshot too old' in the past, but I upped UNDO RETENTION, got bigger UNDO tablespace and the ORA-1555 error seemed to stop.

The specific error is this on the trace file:

error 20 in job queue process
ORA-00020: maximum number of processes (300) exceeded

Is this because the jobs was trying to restart and there were no processes at the time? There are no Oracle errors in the Alert log. Shall I be suspicious of the Java App server , grabbing to many connections, re-instantiating itself 3-4 times and not releasing the connections or something ??


Many thanks for your time.

Tom Kyte
May 08, 2005 - 4:34 pm UTC

then, based on your first paragraph, you need to boot processes.

The refresh job sounds like it is only using a single session, it is not the cause of "out of processes"..

You need more than 300 if you have 280+ concurrent sessions AND want to use job queues and such.

Active call (process/session?) prevents db shutdown

M.Fritz, June 16, 2005 - 3:34 am UTC

Hello,

we are running a 9.2.0.6 database on a HPUX11 server. Since few days the shutdown transactional (for cold backup) cannot finish, although there are no open transactions. The shutdown timeout's after approx. 1 hour:

Wed Jun 15 23:00:39 2005
Shutting down instance: further logons disabled
Shutting down instance (transactional)
All transactions complete. Performing immediate shutdown
License high water mark = 55
Wed Jun 15 23:05:42 2005
Active call for process 1904 user 'oracle' program 'oracle@uls4000 (S000)'
SHUTDOWN: waiting for active calls to complete.
Thu Jun 16 00:01:15 2005
SHUTDOWN: Active sessions prevent database close operation

But there is no such session at this time:

OSUSER PROGRAM TYPE MACHINE TERMINAL SERVER PROCESS MODULE SID USERNAME LOGON_TIME LAST_CALL
------------------------------ ------------------------------------------------ ---------- ---------------------------------------------------------------- ------------------------------ --------- ------------ ------------------------------------------------ ---------- ------------------------------ ------------------- -------------------
oracle oracle@uls4000 (PMON) BACKGROUND uls4000 UNKNOWN DEDICATED 1886 1 15.06.2005 04:15:19 15.06.2005 04:15:15
oracle oracle@uls4000 (DBW0) BACKGROUND uls4000 UNKNOWN DEDICATED 1889 2 15.06.2005 04:15:19 15.06.2005 04:15:15
oracle oracle@uls4000 (LGWR) BACKGROUND uls4000 UNKNOWN DEDICATED 1892 3 15.06.2005 04:15:19 15.06.2005 04:15:15
oracle oracle@uls4000 (CKPT) BACKGROUND uls4000 UNKNOWN DEDICATED 1894 4 15.06.2005 04:15:20 15.06.2005 04:15:15
oracle oracle@uls4000 (SMON) BACKGROUND uls4000 UNKNOWN DEDICATED 1896 5 15.06.2005 04:15:20 15.06.2005 04:15:15
oracle oracle@uls4000 (RECO) BACKGROUND uls4000 UNKNOWN DEDICATED 1898 6 15.06.2005 04:15:20 15.06.2005 04:15:15
oracle oracle@uls4000 (CJQ0) USER uls4000 UNKNOWN DEDICATED 1900 7 15.06.2005 04:15:20 15.06.2005 04:15:15
oracle oracle@uls4000 (QMN0) USER uls4000 UNKNOWN DEDICATED 1902 8 15.06.2005 04:15:20 15.06.2005 04:15:15
oracle sqlplus@uls4000 (TNS V1-V3) USER uls4000 DEDICATED 13377 sqlplus@uls4000 (TNS V1-V3) 12 SYS 15.06.2005 23:00:39 15.06.2005 23:00:39


ps -efl gives following info about the expected process causing the hang:

F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME COMD
1001 S oracle 1904 1 0 154 20 485605c0 17296 a862a0 04:15:20 ? 0:01 ora_s000_TDB

This process is always there, but usually the shutdown transactional works correctly.

The question is, what exactly causes the hang here and how to get it fixed?

Thanks in advance,
M.Fritz

Tom Kyte
June 16, 2005 - 3:45 am UTC

why in the world are you shutting down to simply back up???????????




Shutdown

M.Fritz, June 16, 2005 - 4:40 am UTC

Ok, I know a shutdown for backup is not really neccessary.

But our current policy is to do it that way. And I have to make sure that it works.

So any hints concerning the mentioned problem are appreciated.

Tom Kyte
June 16, 2005 - 9:51 am UTC

unattended shutdowns are the scariest thing on the planet.

when does a lightbulb burn out?
mostly when you turn it on

when does a database refuse to shutdown/startup?
mostly when you shut it down or start it up.

unattended startups/shutdowns -- every time I see them, it involves PAIN. You will get paged, you will have a database that is not available.

I guess the only thing worse is "it is our standard operating procedure" in response ;) That -- I really hate that.


If you want to do "100% sure unattendend shutdowns", you'll use shutdown abort, startup in restricted session and shutdown immediate.

but, it'll fail during startups sometime.

processes and sessions

Jim, June 24, 2005 - 12:14 pm UTC

On my database, PROCESSES is set to 250 and oracle derived the value for SESSIONS to 280. I was wondering if the instance can support only 250 processes, how come 280 sessions possible? I am using dedicated server connections. So, each dedicated server session has a process that can be maximum of 250. right? Can I allow 280 concurrent sessions eventhough processes parameter is set to 250? Thanks for the clarification.

sql to capture session info,

sns, July 29, 2005 - 2:15 pm UTC

We are facing (sometimes) too many processes causing users to unable to login. Our max processes in the database is 800.

I have written a script that runs once in an hour to capture more information that would have caused too many processes.

Please review and kindly advise whether it makes sense.

select
(select nvl(max(sr_no)+1,1) from monitor_sid),
sysdate,
a.sid,
a.program,
a.osuser,
a.username,
a.status,
substr(c.sql_text,1,100),
to_char(logon_time,'dd-mon-yyyy hh24:mi:ss')logontime,
round(a.last_call_et/60,2)minutes,
nvl(b.cnt,1) no_of_parallel_sessions
from v$session a,
( select qcsid sid,count(*)cnt
from v$px_session
group by qcsid
) b,
(select distinct sql_text,hash_value,address
from sys.v_$sql
)c,
v$process d
where a.sid = b.sid(+)
and a.sql_address = c.address(+)
and a.paddr = d.addr
and a.sql_hash_value = c.hash_value(+);

Thanks,

Tom Kyte
July 29, 2005 - 3:04 pm UTC

seems you would want to drive the query from v$process, not v$session.

but, is 800 sufficient for the number of simultaneous users you know you have to support?!?

thanks,

sns, July 29, 2005 - 3:21 pm UTC

Actually I don't know from what table I am driving the query. What is the difference if I drive from v$session? How would the query look like?

About number of processes, I am not sure whether 800 is sufficient or not but my question was whether the script gives sufficient information about number of processes at that point of time.

Thanks,

Tom Kyte
July 29, 2005 - 5:46 pm UTC

you are worried about processes

but you are reporting by sessions, picking up processes if they have them (which they may or may not).

Seems to me, you'd want to look at what processes you have (even those not associated with sessions).

if you want to analyze PROCESSES, one would think you would start with v$process and outer join from THAT table. That is all. You only show processes associated with sessions.

binary 0 in v$session.machine

Matthias Rogel, August 03, 2005 - 8:33 am UTC

Tom,

I notice that on one of my databases
some (not all) of the Windoze-Clients
have a binary 0 at the end of their entry
in v$session.machine

That is
(*)
select distinct machine from sys.v_$session where dump(machine) like '%,0'

gives plenty of client-machines (all windoze)
whereas
(**)
select distinct machine from sys.v_$session where dump(machine) not like '%,0'

gives some client-machines
(some unix, some thin jdbc, but
also windoze with the same program as in (*)
)

haven't a clue where this comes from,
have you?

Tom Kyte
August 03, 2005 - 11:45 am UTC

I believe it was an issue with some clients setting the length of the machine name incorrectly (so it was inclusive of the '\0' C strings use to terminate themselves)

v$process vs v$session,

sns, August 05, 2005 - 1:12 pm UTC

This is SNS from Austin,TX. Well I am continuing my question which I have asked few days ago.

If I rewrite my query using v$process as the driving table (as you suggested), then I would capture all the records from the v$process table.

Say if count(*) from v$process is 300, then it is using 300 process out of 800 (which is the value of the process parameter).
The moment count(*) reaches 800 then there would be an issue when user tries to connect to a new session. Is this a true statement?

In my SQL query if I use v$process as my driving table, then the total output from the query would be 300 in that case. Is this correct?

Thanks,

Tom Kyte
August 05, 2005 - 2:41 pm UTC

the moment count(*) reaches 800 could be caused by

o the job queues starting new processes (you control how many)
o aq doing same
o parallel query starting some
o shared servers increasing
o or a user creating a new dedicated server (there are probably more, just listing some)

I don't know what the correlation between 300 and 800 is though?

process,

sns, August 05, 2005 - 2:45 pm UTC

Sorry for the confusion. I used 300 as an example.

But my actual question is,
When the count(*) from v$process reaches 800 (PROCESS parameter is set to 800), what are the consequences?

Tom Kyte
August 05, 2005 - 5:36 pm UTC

the consequence is:

you cannot create process 801




order of invoking processes

Alay, September 14, 2005 - 10:03 am UTC

Hi Tom,
I want to know that when we startup the database in which order the background processes invoke?

Tom Kyte
September 14, 2005 - 10:14 am UTC

Never watched, never tried to watch, not sure it is something we need to know.

That and I would suspect it to be very very "version dependent".



what is differences between ram client and ram server

frank, October 05, 2005 - 1:25 am UTC

---------above----plz reply...urgent

Tom Kyte
October 05, 2005 - 7:11 am UTC

I believe you mean the really old Oracle Express (OLAP) tools.

There was a client piece
There was a server piece

RAM was the "name" of these pieces.

Connection pooling and sys_context

Steve Kiteley, December 21, 2005 - 11:50 am UTC

Tom,

I'm a little confused about the scope of sys_context. I have recently moved onto a project that is developing a public web based application using connection pooling. The search facility that forms part of this web site involves buiding a complex SQL statement (in the form of a ref_cursor) based on the criteria requested by the user. The code is currently lacking in its use of binding so I was looking to use sys_context where possible to reduce the hard parsing that is currently causing problems. However I haven't worked on a system whereby the users don't log on individually to a system but share the same user (it is a publically accessable website). Can I still use sys_context or do I run the risk of one attempted search overwriting the context settings of another?

Tom Kyte
December 21, 2005 - 7:49 pm UTC

a regular application context is specific to a session and the values are not visible in other sessions - much like a package global variable would be.

Memory fragmentation or Windows limitation?

Shailesh, January 11, 2006 - 11:46 am UTC

Hello,

RDBMS Version: 8.1.7.4.1
Operating System and Version: Windows 2000 Advanced Server

Although single instance can take 2GB of memory on Windows, when Oracle.exe reaches 1.7GB, we start getting this error. Total SGA size is 1.1 GB, 400 sessions and dedicated server mode. So initially we reduced SGA size by reducing DB_BUFFER_CACHE. But again hit same error...

TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNS:protocol adapter error
TNS-00510: Internal limit restriction exceeded
32-bit Windows Error: 8: Exec format error

Now we have inserted /3 GB parameter in boot.ini and also reduced SGA by 100 MB. In long term we are planning to use Multi Threaded server. What else can be done to avoid this error? Is this windows issue of memory fragmentation when application sessions are connected and disconnected frequently? please help us.

Thanks & Regards,

Shailesh

Tom Kyte
January 12, 2006 - 10:42 am UTC

this is just a limit in windows - there is the program stack plus heap that must fit in 2gig (or 3gig with your switch). You are going to hit this over and over - it is a limit.

You can search metalink - but basically, you are working in a small box of memory. shared server isn't going to significantly decrease memory use (it can cut down on the number of PGA's - but you'll have to allocate a large pool now for the UGA memory so you'll have less memory with which to allocate pga's)

this is not fragmentation, this is a limitation of running a 32bit process that needs to use mutiple threads because the OS is one that says don't go multi-process, use threads in a single process.

Memory fragmentation or Windows limitation?

Wang qiang, January 18, 2006 - 6:43 am UTC

I can't still solve the problem,I don't understand what you said that search metalink

Tom Kyte
January 18, 2006 - 8:20 am UTC

You can use metalink, the support site.

It has a search feature.

In there you can search for articles on how to squeeze blood from a stone (to get 32 bit windows to use a little bit more ram).

Metalink Doc ID's

djb, January 18, 2006 - 10:14 am UTC

For Windows memory issues, see the following Metalink documents. I have much experience with this issue (unfortunately can't afford 64-bit machines right now)

225349.1
1036312.6
46001.1 - look for "Configuring the Oracle process to make allocations greater than 2GB" in this one


Oracle Memory on 32-bit Windows system

Tim, January 18, 2006 - 2:50 pm UTC

On 32-bit Windows, when using Oracle 8.1.6 or greater, in addition to investigating the /3GB boot switch, you may also want to investigate the AWE options available to you. This may allow you to go considerably far beyond the 3GB memory limitation which is available using the /3GB switch. AWE allows up to 64GB of memory to be accessed - although significant restrictions with many applications may not make this feasible. The good news - is that Oracle 8.1.6 (and later) appears to "play nicely" in this environment.

Bear in mind - this is a "work around" for using a 32-bit system with the inherent limitations of a 32-bit system. A 64-bit system is a better solution - however, if a 64-bit system is not possible - using AWE on a 32-bit system may be better than nothing.

Reading both Oracle and Windows documentation available would probably be helpful.

Here is a Microsoft Windows link which provides some information:
</code> http://msdn.microsoft.com/library/en-us/memory/base/large_memory_support.asp <code>

A comment on Tim's comment...

djb, January 18, 2006 - 5:24 pm UTC

Tim's comment is true, with a caveat...

The AWE addressing gives you *only* more buffer-cache, kind-of like a RAM-disk (though it doesn't show up as a disk drive).

It doesn't help the session memory at all, so if you have many sessions (in my system, about 300 is about it) it won't help you. It only helps get more of your DB in memory.


A reader, January 25, 2006 - 2:18 pm UTC

Tom,

Lets say in the database setting for processes=150 and we have two users that connect to the database U1 and U2. I would like to dedicated 30 processes to U1 only and 90 to U2 i.e U1 cannot have more than 30 connections and U2 cannot have more than 90 can i do this using profiles or is there some other method?

TIA.

Tom Kyte
January 25, 2006 - 4:44 pm UTC

this is the resource manager, maximum active sessions control.

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#i1010776 <code>

A reader, January 26, 2006 - 2:39 pm UTC

Doh... You have mentioned that many times before.

Thanks.

ora-12500

Vivek, March 13, 2006 - 6:20 pm UTC

Tom,

I am facing ora-12500 and ora-12540 error on my production server.The problem was appeared three months back and i bounce my production DB , after that it happened couple of weeks back and lately over last week end. when i got this problem i can't log on to the DB as "/ as SYSDBA" its throws same error. After few tries ( with time gap), i was able to log on as sysdba. I queried v$session, v$process count which were below the set parameter.

I have set up an OEM event to alert me when the process and session limit reaches 80% for all these occassion i didn't got any alert but Oracle processes at OS level were well above 100 and there were some defunct oracle process, which i was not able to kill.

Could this problem be related to max user processes at OS level. I tried to find that out using "ulimit -a", but
could get much information . could you please guide me. I have also increased my process parameter from 100 to 120.

TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded

Regards
Vivek


ora-12500

A reader, March 13, 2006 - 6:24 pm UTC

In continuation of above thread...
oracle version 9.2.0.3 on AIX


oracle process

A reader, March 15, 2006 - 6:38 pm UTC

Tom,

I am facing ora-12500 and ora-12540 error on my production server.The problem was appeared three months back and i bounce my production DB , after that it happened couple of weeks back and lately over last week end. when i got this
problem i can't log on to the DB as "/ as SYSDBA" its throws same error. After few tries ( with time gap), i was able to log on as sysdba. I queried v$session,
v$process count which were below the set parameter.

I have set up an OEM event to alert me when the process and session limit reaches 80% for all these occassion i didn't got any alert but Oracle processes
at OS level were well above 100 and there were some defunct oracle process, which i was not able to kill.

Could this problem be related to max user processes at OS level. I tried to find that out using "ulimit -a", but
could get much information . could you please guide me. I have also increased my process parameter from 100 to 120.
oracle version is 9.2.0.3 on AIX.

TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded

Regards
Vivek




Tom Kyte
March 16, 2006 - 7:44 am UTC

please utilize support.

Inconsistent data returned by v$session

JP, March 22, 2006 - 1:06 pm UTC

Hi Tom,

Just a short question.. why is it when a select against the v$session gives a null value for the process field when this is executed in a thin-client ? as opposed to executing it in sqlplus directly..

Illusttration (same userid is used for both examples):

SELECT
nvl(PROCESS,'Field is null'),
program
FROM V$SESSION
where audsid = ( select USERENV('SESSIONID') from dual)


Result of sql executed in a java program (the application just executes the SQL) (no value for the process field.

Field is nullJDBC Thin Client

Result of SQL when executed in sqlplus

NVL(PROCESS,' PROGRAM
------------- ---------------------------------------------495712 sqlplus@xxx (TNS V1-V3)

Thanks in advance for your explination.

Tom Kyte
March 22, 2006 - 5:11 pm UTC

clients set the program field, it is entirely "at will", it is part of the thick stuff (our OCI code). But I could set it to most anything I wanted at all by copying your program.exe to foobar.exe.

I would not say v$session is inconsistent here - rather it is quite consistent. What the client says to set - it sets.



Inconsistent data returned by v$session

JP, March 22, 2006 - 1:22 pm UTC

by the way we're on 9.2.0.6.0

here again the Result of SQL when executed in sqlplus


NVL(PROCESS,' PROGRAM
------------- -------------
495712 sqlplus@xxx (TNS V1-V3)

Thanks again.

difference between Connection Pooling and Session Multiplexing

Zahir M, March 27, 2006 - 10:37 am UTC

Tom ,

What is the difference between Connection Pooling and Session Multiplexing ?

Thanks

Tom Kyte
March 27, 2006 - 2:48 pm UTC

<quote </code> http://docs.oracle.com/docs/cd/B19306_01/network.102/b14212/dispatcher.htm#sthref1172 <code>
>
Session multiplexing, available with Oracle Connection Manager, enables multiple client sessions to funnel through a single protocol connection. For example, several user processes can connect to one dispatcher by way of a single connection from Oracle Connection Manager.
</quote>


connection pooling (as defined by typical middle tier application server connection pooling) - one connection to the database, one session established in the database - shared by many application instances one after the other.

session multiplexing - taking MANY connections/sessions and using one physical connection to the database to service them all.

Too many connections??

Andrew, April 01, 2006 - 2:40 pm UTC

As I understand it, before establishing a session one must first establish a connection. Also, one connection may have 0, 1, or more sessions.

If this is the case, and my database is running dedicated server, how can I get 'ORA-30678: too many open connections' error? The number of sessions I am seeing is far below max_sessions.

The only explanation I can find for this error is
"
Cause: An attempt to open a connection failed because too many are already open by this session. The number of allowed connections varies as some may be in use through other components which share the same pool of allowed connections.
Action: Retry after closing some other connection. The number of connections supported is currently not adjustable.
"
Which appears to tell me that I have > 1 connection per session. How can this be? How can I tell what connections a session has open? And how can I tell what the max connections per session is?

Thanks

Tom Kyte
April 01, 2006 - 3:26 pm UTC

what is the circumstances surrounding this specific issue - eg: what is the client, whats happening when you get this.

max session ever reached by the database.

Sean, August 01, 2006 - 4:34 pm UTC

Hi Tom,

Is there a way to know the maximum session which database ever reached? Like “high water mark of sessions”, though I can write a job to query v$session? Oracle 9207. Thanks


Tom Kyte
August 02, 2006 - 10:44 am UTC

the instance - yes, v$license

the database - no... Unless you periodically poll v$license and save it. (since v$license is reset when you stop the instance and create a new instance to mount that database)

Session HWM

Michel Cadot, August 02, 2006 - 11:41 am UTC

You can also look at your alert.log file.
Each time you shutdown the instance, the session HWM is recorded.
Something like:

...
Shutting down instance (immediate)
License high water mark = 13
...

Michel


Trusted Session

Arindam Mukherjee, August 08, 2006 - 8:12 am UTC

Respected Mr. Tom,
From Oracle 9i documents, I have read the following lines.

"Global application context is a type of secure application context that can be shared among trusted sessions."

"only trusted individuals should have the powerful privileges"

"QUERY_REWRITE_INTEGRITY=TRUSTED."

Ques 1. What is the meaning of trusted sessions?
Ques 2. Who are the trusted individual to database.
Ques 3. What does the word mean "TRUSTED" in case of QUERY_REWRITE_INTEGRITY

Sorry!! for these silly questions, but I don't know the meaning of "TRUST" to different context. Please help me.



Tom Kyte
August 08, 2006 - 8:18 am UTC

q1) your sessions, your set of application sessions, your set of connection pool sessions connected as your application account for example

q2) people you trust (simple as that). You give POWERFUL PRIVILEGES to people in trusted positions.

q3) query rewrite integrity trusted:
<quote src = expert one on one Oracle>

TRUSTED: Queries will be rewritten using the constraints that are enforced by Oracle as well as the relationships we have told Oracle about that exist in the data but are not enforced by Oracle. For example, in our initial example we could have created the physical table MY_ALL_OBJECTS_AGGS manually using a parallel, nologging CREATE TABLE AS SELECT (to speed up the building of the summary table). We could have then created the materialized view instructing it to use this pre-built table instead of creating the summary table itself If we wish Oracle to use this pre-built table during a subsequent query rewrite, we must specify a value of TRUSTED. This is because we want Oracle to 'trust' that we have supplied the correct data in the pre-built table – Oracle does not enforce that the data in that table is correct.
</quote>

number of current logins

A reader, August 18, 2006 - 2:35 am UTC

Hi

I need to determine the maximum concurrency during an interval of time. I have statspack enabled every 5 minutes with snap level 0.

I thought I could use this statistic

select max(value)
from stats$sysstat
where snap_id in (x, y)
and name = 'logons current'

however I noticed that the value shown is very different from that in v$resource_limit and count(*) from v$session

For eample rught now:

select * from v$resource_limit;

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
processes 1682 5000
sessions 1591 4916

select * from v$sysstat where name = 'logons current';

STATISTIC# NAME CLASS VALUE
---------- ---------------------------------------------------------------- ---------- ----------
1 logons current 1 2049

select count(*) from v$session;

COUNT(*)
----------
1588

As you can see the statistic is telling me I have right now 2049 sessions but v$session tells I have 1588?

Is there any other way than statspack to accomplish what I need?




Tom Kyte
August 18, 2006 - 8:18 am UTC

sysstat is "point in time"

statspack takes the two points in time and subtracts (eg: copy sysstat at 1am, copy it again at 1:05am, to generate report, take observation at 1:05am and subtract).

but in any case - do you use parallel query? it can cause v$sysstat to become inflated for a period of time.

number of current logins

A reader, August 20, 2006 - 9:40 am UTC

Hi

We dont use Parallel Query in this database.

I dont understand why v$session and v$sysstat reports so diffrently number of current logins. 1588 vs 2049 !



Tom Kyte
August 20, 2006 - 9:59 am UTC

query v$sql_plan and please verify that no one is 'accidently' invoking parallel operations.

max number of processes

A reader, September 06, 2006 - 5:47 am UTC

Hi Tom,

maximum number of processes is OS dependent . i have read it in reference manual. is there a way to know that what is the maximum number of processes different OS (operating systems) support .is there a metrics kind of inforamtion avaliable somehwere.

Tom Kyte
September 06, 2006 - 7:55 am UTC

in your operating system documentation no doubt?

it is setup and limited by the operating system, not us so much.

connections

Sam, January 02, 2007 - 2:28 pm UTC

Tom:

When I call the login page using mod_plsql I am getting a message:

Too many database connections... Try again after a few minutes.

No one is logged in. Is this cause by a speicfic oracle parameter or O/S setup? How do you fix the issue.

Thanks,

processes

Sam, January 17, 2007 - 6:42 pm UTC

Tom:

Our oracle web application is not allowing any connections in sql*plus. DBA tells me there are too many unix processes running. In oracle the count in v$process was 18 while unix shows 150.

DO you now how those unix processes are created and how they are supposed to go away and what is the solution for this? Can thos be caused by many job scheduled or failures. I found that there were 200 jobs that failed due to using the same previous job number in the old instance. DBA did not reset the SYS.JOBSEQ.


ora926 271946 1 0 11:41:19 - 0:03 oraclexxxx (LOCAL=NO)
ora926 312250 1 0 10:34:52 - 0:00 oraclexxxx (LOCAL=NO)
ora926 357988 1 0 09:59:32 - 0:06 oraclexxxx (LOCAL=NO)
ora926 362534 1 0 10:01:20 - 0:00 oraclexxxx (LOCAL=NO)
ora926 385208 1 0 12:13:49 - 0:00 oraclexxxx (LOCAL=NO)
ora920 387290 1 0 Jan 14 - 0:13 ora_smon_xxxx
ora926 387482 1 0 11:47:18 - 0:00 oraclexxxx (LOCAL=NO)
ora926 393060 1 0 10:26:47 - 0:00 oraclexxxx (LOCAL=NO)
ora920 393258 1 0 Jan 14 - 0:54 ora_cjq0_xxxx
ora926 394568 1 0 12:10:02 - 0:00 oraclexxxx (LOCAL=NO)
ora926 446116 1 0 12:33:01 - 0:00 oraclexxxx (LOCAL=NO)
ora926 446822 1 0 10:02:44 - 0:00 oraclexxxx (LOCAL=NO)
ora926 467078 1 0 10:09:40 - 0:00 oraclexxxx (LOCAL=NO)
ora926 471356 1 0 10:21:00 - 0:00 oraclexxxx (LOCAL=NO)
ora926 472992 1 0 11:38:40 - 0:03 oraclexxxx (LOCAL=NO)
ora926 604462 1 0 10:02:59 - 0:00 oraclexxxx (LOCAL=NO)
ora926 608542 1 0 10:06:56 - 0:00 oraclexxxx (LOCAL=NO)

creating connection/sessions from pl/sql script

Ajeet, March 17, 2007 - 7:23 am UTC

Hi Tom

I want to create a number of sessions using a pl/sql block..
that is i want to open say 50 new sessions using a user scott. is it a way to do this using pl/sql or unix shell script.

begin
loop
connect scott/tiger ;
-- this will not happen , but this is just to explain ---
dbms_lock.sleep(1);
end loop;
end;
/

regards
Ajeet
Tom Kyte
March 17, 2007 - 5:21 pm UTC

no

but tell us, what are you trying to do? a load test? dbms_job might be something to look at.

load test -yes

Ajeet, March 20, 2007 - 12:34 am UTC

Hi Tom,

yes , I want to simulate 7000 user load for a program in test environment,so i want to create 7000 user sessions using a program and then collect the relevent data.
i know , we could use simulation tools such as load runner etc,but just want to explore if we can do it using a sql program...or dbms_job.
Tom Kyte
March 20, 2007 - 7:50 am UTC

you'll need to use some external program for so many users. The job queues will not go that high.

I doubt you want to have 7,000 sessions on the server - you would likely use a connection pool in real life and have significantly fewer sessions/processes. For something of this scale, you'll need multiple machines (to drive the test) and will want to simulate your actual production architecture (middle tier and all)

jdbc connection close does not release session resources

Fernando Sanchez, April 04, 2007 - 7:39 am UTC

Hello.

I have a situation I cannot understand and therefore I do not how to solve.

There is a web application that calls (jdbc with a CallableStatement) a stored function (inside a package) in a MTS server (9.2.0.7) on a XP machine.
The java code creates a thread in which a connection is opened and then calls the statement and after that it calls close() for the Connection instance. Afterwards the thread is killed.

I can see that after the above steps end, the memory usage of the oracle.exe process has increased, and it never decreases, I mean if the web application launches the steps again the memory usage of the oracle.exe increases from the point in the last execution had ended.

If the web application is shutdown, then oracle.exe seems to release memory.

But if I call the function from sqlplus I see that if I exit from sqlplus the memory usage of the oracle.exe process decreases. I have also observed that if I disconnect from sqlplus the memory usage of oracle.exe does not decrease unless I connect again.

I need that the oracle.exe memory usage decreases when the web application ends the execution of the stored function. I thought that the close method of the Connection object would do that but I must be worong.

Could you Tom, please, give me an idea of what is happening and how to get our purpose?

Thanks in advance.

Tom Kyte
April 04, 2007 - 10:37 am UTC

are the number of entries in v$session or v$process going up as the web application connects/disconnects

whose jdbc driver are you using, what version

is MTS really "shared server" or Microsoft Transaction Server.

jdbc does not kill sessions

Fernando Sanchez, April 04, 2007 - 11:28 am UTC

Thanks Tom.

- I have supposed (sorry for my ignorance) I am using a MTS server because I only have one oracle.exe process and because I see these parameters:

mts_sessions                         integer     165
session_cached_cursors               integer     0
session_max_open_files               integer     10
sessions                             integer     170


but if do:

sigres@SIGRESBD> select distinct server from v$session;

SERVER
---------
DEDICATED



- The JDBC is "Oracle JDBC Driver version 9.0.2.0.0"

Before launching from the application (the application is already started up):

sigres@SIGRESBD> select count(1) from v$session;

  COUNT(1)
----------
        50

sigres@SIGRESBD> select count(1) from v$process;

  COUNT(1)
----------
        53




After launching from the application there is one more session and one more process:

sigres@SIGRESBD> select count(1) from v$session;

  COUNT(1)
----------
        51

sigres@SIGRESBD> select count(1) from v$process;

  COUNT(1)
----------
        54



Tom Kyte
April 04, 2007 - 11:34 am UTC

why the use of old jdbc drivers?

jdbc Connection close does not release session resources

Fernando Sanchez, April 04, 2007 - 11:57 am UTC

I do not the reason for this, I suppose it is because the driver has not been upgraded since the application was developed (this is a development environment) and the problem had not been important before now.

Thank you.

Tom Kyte
April 04, 2007 - 12:16 pm UTC

i would test a more current jdbc release - 9.0 is really quite old.

problem with jdbc connection close

Fernando Sanchez, April 09, 2007 - 7:44 am UTC

Could you, please, tell me where can I find a guide from upgrading the server jdbc version?

I have been looking for it but I cannot find it.

Thanks.

Tom Kyte
April 09, 2007 - 10:51 am UTC

you are not upgrading the server jdbc, you are doing the client - the thing that accesses the database.


CPU consumption 100% and machine hangs

Piyush Agarwal, May 05, 2007 - 10:11 am UTC

Dear Tom,

I am not a seasonal dba but currently being asked to look into dev server.

Scenario -> One machine with 1.5G ram having oracle 10G Rel 1 with windows xp installed. About 50 developers are working on it. I have enabled 400 processes and increased SGA to about 800MB. High water mark sometimes reaches to 300 or more at times with Java pool-enabled web applications using either Tomcat, Websphere, RAD, etc. Database instance is single although there are multiple instances. there is only one drive C. Ever 3-5 days, machine hangs and at times even login into windows takes huge time. AT that time, we discover that 100% CPU utilization is taking place. Can you please let us know what could be the problem. Is that RAM is over-utilized or because the machine which is not a server but Intel P4 is getting overheated and hence gets slower by day and finally hangs.....Load is currently under control since Java application has resolved open connection problems in their code. Hence, the time to restart server is now more.

Moreover, since developers have to restart their web server or application server many a times , no of connections increases multi-fold. Even at times if I kill the connections manually, it does not help and a difficult task too. Can you please let me know a way that would clear killed sessions as soon as possible without impacting server performance. I have set one parameter in SQLNet.ora file to set 10 mins as polling time using SQLNET.EXPIRE_TIME.

Connection type : Dedicated.

Please help.

Regards,
Piyush
Tom Kyte
May 08, 2007 - 10:17 am UTC

what could be the problem....

hmm, you have a machine with hundreds of users and you are doing development on it. I'll guess.

someone is running a wicked query, it takes a lot of cpu. you do not have a lot of cpu (you don't say how many cpus)

Max connection 250 - Dedicated Server

Edward, May 19, 2007 - 12:15 am UTC

Tom,
I am having a similar issue with windows 2003 32-bit of connections dying around 250. It is only when I try to use indirect buffers and set the db_block_buffers to 12GB. I have 16GB of memory on the machine using /3GB and /PAE option. if i don't use indirect_buffers and have a cache size of 400mb, I can get up to 700 sessions. I don't know why? The SGA is around 800M and PGA is 300M.
Tom Kyte
May 21, 2007 - 10:14 am UTC

define "dying"

32bit windows is going to be quite limiting. That process is very limited as to what memory is can address. And while the indirect buffers can let you have a larger buffer cache, you have to consider the overhead of the data structures the process would need to manage the indirect buffers (you need pointers to this 'extended memory', that takes quite a bit of memory itself from the base process - where the dedicated servers live in windows...)

Sessions

Edward Sears, May 21, 2007 - 1:49 pm UTC

I refer to Dying meaning that any new users that try to login will get the TNS-Listener 12500 error. I found on another forum that I might need to put in AWE_WINDOW_MEMORY and set it lower like 128mb instead of the normal 1GB because it takes it out of the 3GB window, which I didn't know.

Reducing No. of Processes

shailu, July 18, 2007 - 10:35 am UTC

Hello Tom,

Right now in my init.ora processes are 180. If i have to reduce this number to 165, what are the things i should consider before reducing? can you please give some idea what are the pros and cons of changing processes paramter.

Thanks
Tom Kyte
July 18, 2007 - 10:55 am UTC

just make sure 165 is sufficient for your load

one might ask 'why' - 15 is not huge, I would not touch it.

Reducing n. of processes

shailu, July 18, 2007 - 12:07 pm UTC

Hello Tom,
Thanks for you response. If i have no. of processes more then needed. Does it affect the performance of my database?
Tom Kyte
July 18, 2007 - 12:57 pm UTC

not with just 15, it is a very small number.

processes is used to set sizes of other things, data structures in the SGA. However, decreasing by 15 is not going to really materially affect anything, so - it ain't broke, don't touch it.

Alexander, July 19, 2007 - 9:12 am UTC

Tom,

Is there anything wrong with setting the # of processes really high so you don't have to worry about it?
Tom Kyte
July 19, 2007 - 11:10 am UTC

yes, it is used to set other parameters, these parameters are then used to size memory chunks in the SGA.

Some of these structures are arrays that would have to be searched through (the bigger the array, the more time spent doing somethings)

Some of these structures could be large - using more SGA memory than needed.


a reader

raman, September 13, 2007 - 5:51 am UTC

hi tom

i need help document quote

----------------------------------------------------------------------------------------------------------------------------
The DBWn process writes dirty buffer to disk under the following conditions:
1. When a checkpoint is issued. Please see checkpoint process below.
2. When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers.
3. Every 3 seconds. See Followup on DBWn DBWn 3 second write
----------------------------------------------------------------------------------------------------------------------------

checkpoint occurs when log switch occurs ,i have dirtied blocks which are modified but not committed,at the event of checkpoint..
are those non committed dirtied blocks writes to datafile at the event of checkpoint ?

tom as above statment creating confusion

confusion is modified blocks in buffer cache which is not committed,when DBWR is asked to write dirty blocks to disk (i.e checkpoint occurs) which are not committed and as DBWR to asked to write it at disk then is it not data which is not required by user on disk because he did not isse commit means he did not want to on disk permanent but DBWR write it on disk upon checkpoint.As we commit it should be permanent but DBWR will write those uncommitted data to disk which is not required upon checkpoint.

one more confusion
what is threshold number of buffers??

raman
Tom Kyte
September 15, 2007 - 7:23 pm UTC

dbwr writes uncommitted data all of the time...

think about it - a transaction is unlimited in size.
the SGA, not so.

I can modify a lot more data in an update statement than can fit into ram!!!!!!


If you are very interested in the mechanics and want to hear it in my voice - I'll have to suggest peeking at either the older "expert one on one Oracle" or "expert oracle database architecture", I walk through how this stuff works (it takes a few pages)

basically, since UNDO is managed in the database, all we need to do is make sure the undo is flushed to disk before the affected block is, then no matter what - we can ROLL BACK that uncommitted change.

a reader

raman, September 17, 2007 - 3:09 am UTC

thanks tom i have yours book "expert one on one" i read there about checkpoint pg 94.

tom as you said
------------------------------------------------------------------------------------------------------------
basically, since UNDO is managed in the database, all we need to do is make sure the undo is flushed to disk before the affected block is, then no matter what - we can ROLL BACK that uncommitted change.
------------------------------------------------------------------------------------------------------------
i want to rephrase in my word that what i am understanding from yours this above point.please correct me.

it means if checkpoint occurs DBWR checks dirty block in buffer cache (committed or uncommitted) to disk from buffer cache..for uncommitted block it will only clean that dirty block from buffer cache and write the related block before image copy from undo tablespace.

raman
Tom Kyte
September 18, 2007 - 2:06 pm UTC

I do not follow your working exactly...


more simply:

all undo related to a block will be flushed to disk prior to the block itself.

a reader

raman, September 17, 2007 - 3:16 am UTC


sorry tom a little correction

thanks tom i have yours book "expert one on one" i read there about checkpoint pg 94.

tom as you said
------------------------------------------------------------------------------------------------------------
basically, since UNDO is managed in the database, all we need to do is make sure the undo is flushed to disk before the affected block is, then no matter what - we can ROLL BACK that uncommitted change.
------------------------------------------------------------------------------------------------------------
i want to rephrase in my word that what i am understanding from yours this above point.please correct me.

it means if checkpoint occurs DBWR checks dirty block in buffer cache (committed or uncommitted) and write to disk from buffer cache..for uncommitted block it will only clean that dirty block from buffer cache and write the related block before image copy from undo tablespace to disk.

for committed block it will clean that dirty block from buffer cache and write the same block to disk.

raman

a reader

a reader, September 19, 2007 - 4:06 am UTC

t means i am wrong,please correct me if i am wrong,because yours word is 100% correct in the oracle world.

you said.

more simply:

all undo related to a block will be flushed to disk prior to the block itself.


what i understand from yours this point that upon checkpoint or making room in buffer cache oracle will write uncommitted ,committed data to disk from buffer cache and release space for these block from buffer cache,for uncommitted data it will also write to disk from undo tablespace and release space from undo table space in order to get original effect of uncommited modified data.

please correct me about my above statement:(

raman


Tom Kyte
September 19, 2007 - 12:38 pm UTC

... because yours word is 100% correct in the oracle world. ...

No, that is not true, not ever. No one is 100% correct all of the time.


it will NOT release space from the undo tablespace. The undo cannot be released until AFTER the transaction that generated it - commits or rollsback.


Query on Distributed Transactions with Session

Manish, December 04, 2007 - 9:09 am UTC

Tom,

I had a query on the relationship between sessions and transactions in a distributed transaction environment [COM+/MTS].

I have an environment where we are using COM+ for transaction management, though it may not be on different database servers.

My query is: If I am performing multiple operations in a single transaction [Say 2 updates and 1 insert on different tables], is it possible that different sessions can be used to perform this transaction. If that is the case would setting CLIENT_IDENTIFIER set at the beginning of the transaction be available at the server for all the operations.

Would appreciate some guidance on this.

Thanks
-- Manish


Karteek, June 05, 2008 - 10:50 am UTC

Tom,

If there is a process that is not associated with any session, that process can be used by any other session based on Oracle's choice. am I right?

If I am correct, and if I don't want to have more number (due to some issue) of prcesses that are not serving any sessions, I can simply kill them right? (orphan proccesses are there not doing anything for more than a day or two!)

Thanks!

Tom Kyte
June 05, 2008 - 11:01 am UTC

do not kill oracle processes. stop it.

there are processes not associated with sessions as part of our normal operation, do not kill them.

Karteek, June 05, 2008 - 11:08 am UTC

Not even when I have the process ids (like grepping oracleXXX LOCAL=NO)? I mean when I know the client connections clearly.

Because recently we identified over 500 processes (client connections) older than 5 days and there were just 15 sessions.
Tom Kyte
June 05, 2008 - 1:32 pm UTC

Look, I can only answer based on what I am given. I fear that every time I make reasonable assumptions (based on working with Oracle for years) that the other person asking the question has quite a different set of assumptions.

Let us look at what you said:

If there is a process that is not associated with any session, that process can be used by any
other session based on Oracle's choice. am I right?

If I am correct, and if I don't want to have more number (due to some issue) of prcesses that are
not serving any sessions, I can simply kill them right? (orphan proccesses are there not doing
anything for more than a day or two!)



the only answer to that is STOP IT, DON'T DO IT.

there are processes not associated with sessions normally.
It would be a bad idea to kill them.
So, the answer to your question STANDS


Now, if you have lots of Oracle dedicated servers that are "orphaned", you have what is known as "a problem". We need to correct the problem and not just kill things. So, why are these processes "here" - is it because someone else on another system KILLS the client and tcp/ip isn't timing out the connection? (see, killing 'bad', killing processes not the right approach)

Enable dead connection timeouts
http://docs.oracle.com/docs/cd/B19306_01/network.102/b14212/profile.htm#sthref917


Karteek, June 06, 2008 - 7:19 am UTC

I missed enabling timeouts Tom! Thanks for that...yes, I do agree this is most reliable and as well the best solution for handling orphan processes.

Once again thanks for that!

Karteek, June 13, 2008 - 1:54 am UTC

Tom,

what would be causing a process to turn into an orphan or dead process/connection, apart from improper termination of client? or is it the only reason?

On an avg if there are 100 orphan processes found per each day, I think that is not something doing well, right? I shared the number 100 because its something interesting if you can relate this to some of the bad environments you happened to work/know.

Thanks!
Tom Kyte
June 13, 2008 - 7:48 am UTC

sounds like a firewall/network policy probably.

client connects to database.
client doesn't do anything for say 5 minutes.
network policy says "kill any tcp/ip connection that has been idle for 5 minutes"
client is rudely disconnected - but database doesn't know this.


setting up dead client detection would actually get around this policy - if you ping the connection using an interval smaller than the network timeout, these connections will stop timing out.

You would think that if you have 100 or more a day - someone would complain about it, but it might be hidden by a connection pool or something.

Steve, June 13, 2008 - 4:05 pm UTC

Can a single user with a single connection running PL/SQL code and queries spawn more than one session at a time? Under what circumstance might that occur?
Tom Kyte
June 16, 2008 - 11:21 am UTC

spawn unintentionally? No.

can a single CLIENT APPLICATION (for that is what connects to a database) using a SINGLE CONNECTION have more than one SESSION - sure.

Happens all of the time in sqlplus, consider:


ops$tkyte%ORA10GR2> select sid, serial#, paddr, program from v$session where username = 'OPS$TKYTE';

       SID    SERIAL# PADDR    PROGRAM
---------- ---------- -------- ------------------------------------------------
       144        308 29E1B254 sqlplus@localhost.localdomain (TNS V1-V3)

ops$tkyte%ORA10GR2> set autotrace on statistics;
ops$tkyte%ORA10GR2> select sid, serial#, paddr, program from v$session where username = 'OPS$TKYTE';

       SID    SERIAL# PADDR    PROGRAM
---------- ---------- -------- ------------------------------------------------
       144        308 29E1B254 sqlplus@localhost.localdomain (TNS V1-V3)
       158        441 29E1B254 sqlplus@localhost.localdomain (TNS V1-V3)


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


my paddr (process address) for my dedicated server is shown. I have ONE connection (one physical circut) but sqlplus created another session for me. this other session watches the first session in order to accurately print out the statistics for the watched session (if autotrace used a single session, the queries run by autotrace would affect the output of autotrace itself! Hence the queries autotrace runs are run in another session so as to not affect the session being reported on)

The circumstances: when the client application makes it so, the client application does this on purpose.

What is server type none in v$session?

A reader, July 16, 2008 - 4:17 pm UTC

Tom,
I have searched through documentation but cannot find this. In v$session, the server column can have following values:
DEDICATED, SHARED, PSEUDO, NONE. I understand dedicated and shared. What is pseudo and none?

In my database, I see that connections from applications using shared server show up as none under server column in v$session. Oracle 10.2.0.3.

Thanks

Tom Kyte
July 17, 2008 - 11:36 am UTC

shared server connection that is not currently active.

when not active, it is not using any server process.

too many inactive sessions

reader, September 04, 2008 - 11:45 am UTC

Hey Tom:

    Recently I checked the sessions in our database by chance and found something confusing. there are too many inactive sessions in the system. what is the "inactive" mean for a session? Do I need to kill them all?

    SQL> select status, count(*) from v$session group by status;

INACTIVE        251
ACTIVE           27

    Will the inactive session be active later again or they are just hanging?

    by the way, we are using dedicated server and php+apache.

    Thank you very much!

Tom Kyte
September 04, 2008 - 1:45 pm UTC

it means someone connected to the data and isn't doing anything right now.

should you kill them? almost certainly NOT.

Probably, you have connection pooling software in a middle tier. Probably, they are idle right now, the connection pool is caching a bunch of idle connections, no one is hitting enter. If you killed them - when they did hit enter - they would get "dead" connections and people would probably not like you very much.

They are not hanging, they are just not doing anything right now.


If you feel it is too high, go to your middle tier configuration and lower the size of their connection pool caches.

inactive sessions

A reader, September 04, 2008 - 3:49 pm UTC

Hey Tom:

I am not sure if there is a connection pool in the middle tier. when user browse the webpage, the connection requests will be sent to a load balancer, then it directs the request to one of the webserver. And then that webserver connects to database. If there is connection pool, where it would be? PHP doesn't provide connection pool. And Oracle has Database Resident Connection Pool, but it is the new feature of 11g, we are still on 10gR2.

And if there is a connection pool, then when a request is comming in, it will check if there is an existing connection, if yes, then use it. If not, then open a new. when that connection request is done, that connection (kind of like a pipe) is still there, available for use. Is this what you mean?

One thing I am confused is, It is sessions that are inactive in the database, but here we are talking about connection. are they the same thing? I was reading the answer you post in this topic. Basically connection is a electronic pipe, but session is not.

Thank you very much!
Tom Kyte
September 04, 2008 - 4:34 pm UTC

.. I am not sure if there is a connection pool in the middle tier. ...

then find out? ask the middle tier guys. Look at other fields in v$session - like "program" to see if they give you a hint. Look at last_call_et to see how long they've been there.


you have inactive SESSIONS, v$session is about sessions. You might have fewer connections, we can have many sessions on a given connection.

re: inactive sessions

Stew Ashton, September 04, 2008 - 5:38 pm UTC


PHP does too provide a connection pool, or rather the included OCI8 driver does. It's imperfect but better than nothing.

The "program" field in v$session will show the name of the apache process that PHP runs in. From memory, it normally starts with "httpd".

inactive sessions

a reader, September 05, 2008 - 2:07 pm UTC

Thank you Tom and Stew:


Here are the last 5 lines I pulled from the database with this query:

select p.spid ServPID,s.sid sid, s.username, s.machine,to_char(s.LOGON_TIME,'DD_MM_YYYY HH24:MI') logon_time,s.program,s.status, s.LAST_CALL_ET
from v$session s, v$process p
where p.addr = s.paddr
and s.username='SUPWEB'
and s.program like '%http%'
order by s.LAST_CALL_ET,s.machine,s.program,ServPID,sid,logon_time;

We have other users and other programs, but those are fine and not in the consideration.

7538 619 SUPWEB web01.psudo.net 05_09_2008 07:57 httpd@web01.psudo.net (TNS V1-V3) INACTIVE 2309
7502 1973 SUPWEB web01.psudo.net 05_09_2008 07:57 httpd@web01.psudo.net (TNS V1-V3) INACTIVE 2319
1949 1858 SUPWEB web01.psudo.net 04_09_2008 17:17 httpd@web02.psudo.net (TNS V1-V3) INACTIVE 54180
2313 1829 SUPWEB web01.psudo.net 04_09_2008 17:18 httpd@web02.psudo.net (TNS V1-V3) INACTIVE 54188
1280 686 SUPWEB web01.psudo.net 04_09_2008 17:15 httpd@web02.psudo.net (TNS V1-V3) INACTIVE 54258


and I found some other things that might explain why:

Run PHP as a module in a multiprocess web server, which currently only includes Apache. A multiprocess server typically has one process (the parent) which coordinates a set of processes (its children) who actually do the work of serving up web pages. When each request comes in from a client, it is handed off to one of the children that is not already serving another client. This means that when the same client makes a second request to the server, it may be serviced by a different child process than the first time. What a persistent connection does for you in this case it make it so each child process only needs to connect to your SQL server the first time that it serves a page that makes us of such a connection. When another page then requires a connection to the SQL server, it can reuse the connection that child established earlier.

*** it might because the persistent connection of PHP, what do you think Tom?
Tom Kyte
September 05, 2008 - 4:45 pm UTC

based on everything else said here - you have a connection pool going from php, yes.

inactive sessions

reader, September 05, 2008 - 5:15 pm UTC

Thank you very much!

OOPS! I should have said "persistent", not "pool"

Stew Ashton, September 06, 2008 - 6:22 am UTC


Reader, you're right: the proper term here is "persistent connection", not "connection pool". My apologies :(

As you say, each child process can choose to keep its connection around and reuse it ("persistence"). However, processes cannot share connections (no "pooling").

With persistent connections and no pooling, PHP will tend to have numerous connections and sessions, most of which will be inactive at any given time. I think this is exactly what you are seeing.

So we are all agreed, I just wanted to correct my own mistake.

P.S. I am using Chrome, which allows me to resize the box I am now typing in :)

inactive sessions

reader, September 06, 2008 - 11:24 am UTC

Thank you very much Stew for your further explanation. I am clear now! :-D

Max processes parameter

Atul, October 03, 2008 - 4:39 am UTC

If I set processes parameter to any high value then would it affect any memory being locked etc..


Tom Kyte
October 03, 2008 - 8:12 am UTC

processes is used to set other initialization values, and it will consume memory from the SGA - the initialization parameters are used to size arrays and other data structures within the SGA.

It won't 'lock' memory, it'll cause more memory to be allocated (typically not a significant amount).

You don't want to way oversize these structures - there will be overheads associated with managing them - so don't go nuts and set them absurdly high, but don't worry about setting them a little higher then you absolutely need.

dedicated or shared server ?

Saju, October 11, 2008 - 8:19 am UTC

requirement:
two queries to execute in parallel when queries are initiated by two threads in a client process.

client:
a multi-threaded process making ODBC connections to an Oracle 9i database.

session:
- Make ODBC connection
- Execute Query - Fetch Row(s)
- Disconnect ODBC connection

test:
- one client thread makes an ODBC connection and executes a LONG query; (takes about 15-20 seconds)
- when the first query is running; a second client thread makes another ODBC connection and executes a SHORT query; (returns 1 row and should complete instantly)

(OEM Console shows the two ODBC connections as two seperate sessions; which is what I expected)

connection type:
DEDICATED

what I'm seeing:
The SHORT query on thread 2 of my Client Process appears to wait for the LONG query on thread 1 to complete.

env. info:
- SUN Server (spaarc processor) - solaris 9
- Oracle 9i database (resides locally on the server)
- Oracle 9i Client
- unixODBC - with EasySoft's Oracle ODBC driver
- client - uses native threads


Is it possible to get the two queries to execute in parallel ?
Would changing the connection type to SHARED; do me any good ? made these changes in the init.ora (just in case)

shared_servers 10 (default: 1)
max_shared_servers 40 (default: 20)

ps:
When the LONG query is executing on thread 1 of the Client; if I execute the SHORT query with isql (- an ODBC client) that completes without waiting for the LONG query to complete.

TIA

Tom Kyte
October 13, 2008 - 2:46 am UTC

ask 'easysoft' - whoever they are - if they are compiled threadsafe and make the necessary OCI calls to be threadsafe.

Likely answer to both is "what are threads and probably not"

This has nothing to do with the server setup, for a client to be threadsafe, the client must use all API's in a threadsafe manner and it is highly probable that a generic API layer like someones ODBC implementation - would not be threadsafe.

dedicated or shared - followup

Saju, October 13, 2008 - 9:18 am UTC

Thanks Tom...

our client module has been written to be thread safe.

regarding easysoft's oracle ODBC driver using threadsafe OCI calls...hmm...it is a dont know for now but it is something I defintely intend to find out.

not sure if your response had an answer to my original question. which is...

what type of Oracle server (DEDICATED or SHARED) would work better (allow queries to be processed in parallel) with a client that is a single process that is multithreaded.




Tom Kyte
October 14, 2008 - 4:54 pm UTC

I've written many times - you use dedicated servers unless and until you need to use a shared server. That is when the machine cannot support the spawning of another dedicated server process.

If you are running lots of long running queries - shared server is entirely inappropriate as well.

connections

A reader, October 14, 2008 - 5:07 pm UTC


Single Threading

Saju, October 31, 2008 - 11:26 am UTC

Solved the Single Threading Issue !!! It turns out the single threading was not happening at the Oracle Server and not even in the Oracle ODBC Driver but in s/w that sits in between the Application Process and the Oracle Database driver. unixODBC - the ODBC driver manager. unixODBC(2.2.12) defaults to a single threaded mode of operation. Altering the mode to a multi-threaded mode is a simple parameter setting in a configuration file. The Oracle ODBC driver and the application obviously should support multi threaded for all this to work...

Works like Champ !!! Thank You.

A reader, March 03, 2009 - 10:17 pm UTC


Raj, March 11, 2009 - 12:52 pm UTC

Hi tom,

Thanks for this intresting topic.

I want your help on the below questions.


1. How can we get the present number of connections made to a database. i.e number of connections the database has right now.

2. How can we get the connections made to a databse in the past something like in last one month.

3. How can we get the number of sessions made at present

4. How can we get the number of session made in the history (for example last one month)

5. Can we find something like min, max, avg number of connections made to a database on day wise break up from day 1 (Feb1) till date (this task was from our client, I am not clear with that...can we do something like avg, min, max on day wise..)

We use same host for different databases.

Please help to get the solution for above tasks.

Regards,
Raj

Tom Kyte
March 12, 2009 - 12:22 pm UTC

you will either need something like statspack or AWR (in 10g) to track this, then all of this is tracked.

check out v$license to get started.

Sessions on RAC

Aru, August 03, 2009 - 9:28 pm UTC

Hi Tom,
I have a question regarding RAC database's sessions parameter.
In RAC one has say 3 instances, and the sessions parameter is set to 1500 and processes to 1500 as well.
Will this mean that 1500 sessions are allowed per instance or is it a database limit?
Also please can you guide me towards setting the processes as well?
Tom Kyte
August 04, 2009 - 2:01 pm UTC

sessions and processes are per instance.

sessions is derived normally from processes
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams191.htm#REFRN10197

that reference guide is a good guide for setting something like this, once you understand what it does - you'll just know what to set it to for your system.


background processes

Sagar, August 14, 2009 - 6:59 am UTC

On my linux(RHEL) machine the "oracle" binary(10.2.0.3.0) file is reported to be 84M. But all background proesses together take 25% of 16 GB physical RAM. Why do background processes take so much space?.
Tom Kyte
August 24, 2009 - 7:46 am UTC

you are counting the shared pool over and over and over again.

I'm guessing here because you don't tell us how you are measuring anything, but I'll guess 'top' and that'll show you the SGA (shared memory) used over and over and over again.

At what time?

A, August 20, 2009 - 6:13 am UTC

Hello Tom,
We are seeing the MAX_UTILIZATION of Session is reaching its LIMIT_VALUE. We want to find out at time of the day, its reaching the max value. Is there anyway view which keep tracks of the time?
Tom Kyte
August 24, 2009 - 5:10 pm UTC

no, you'd have to snapshot this (statspack/AWR do that) and query the historical snapshot

Checking process size

Sagar, August 24, 2009 - 8:54 am UTC

Thanks Tom,

I am not using top command.

$free -m
total used free shared buffers cached
Mem: 16242 16201 40 0 38 13098
-/+ buffers/cache: 3064 13177
Swap: 20031 1391 18640
$ps -eo %mem,cmd | grep ora_ | sort -r
5.4 ora_smon_ERPT
4.7 ora_dbw0_ERPT
4.6 ora_cjq0_ERPT
3.5 ora_q001_ERPT
3.2 ora_mman_ERPT
2.1 ora_mmon_ERPT
0.3 ora_mmnl_ERPT
0.3 ora_ckpt_ERPT
0.2 ora_reco_ERPT
0.1 ora_qmnc_ERPT
0.1 ora_pmon_ERPT
0.1 ora_lgwr_ERPT
0.0 ora_q000_ERPT
0.0 ora_psp0_ERPT
0.0 grep ora_
$


Tom Kyte
August 25, 2009 - 9:43 am UTC

so, analyze these numbers for us, tell us what you think you are seeing.

The command

Sagar, August 25, 2009 - 6:19 am UTC

Thanks Tom,

The command used to check process size is also given.

Memory utilisation

Sagar, August 25, 2009 - 12:08 pm UTC

Thanks Tom,
The command gives the memory utilization in percentage of total memory for the oracle background processes. The total comes out to be 25 % of total physical RAM of 16 GB.


Tom Kyte
August 25, 2009 - 8:32 pm UTC

and does it or does it not include the SGA over and over. Please explain in gory detail every single number, if you do not know what a number means, leave it out - but if it is relevant to your 25% theorem - think about that theorem...

Sagar, August 26, 2009 - 3:58 am UTC

Thanks Tom,

Thanks for the hint :) . Indeed SGA is included over and over!. Using pmap I was able to see the size of actual "oracle" binary as well as shared libraries including the shared memory segment for each background process.



Processes far exceeds sessions

A reader, October 07, 2009 - 9:13 am UTC

Oracle 9.2.0.8 on Solaris. We use dedicated server exclusivel. count(*) from v$process is around 500. count(*) from v$session is around 175. max processes in init.ora is 1000. You indicated earlier on this page (version 8i) that this situation is cause for concern. What could cause this? Is this a bug in Oracle or a bug in some client software connecting to Oracle? Should we contact Support?

Thanks

sessions = processes + 10% of processes + 5

Johnson Lobo, February 22, 2010 - 12:18 pm UTC

Please explain to me why take 10% of the processes
Tom Kyte
March 01, 2010 - 7:02 am UTC

did you check out the documentation? You got as far as the default setting (1.1*processes)+5 but didn't finish...


http://docs.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams227.htm#REFRN10197

You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.


a process can handle 0, 1 or more sessions at a time.

v$session

Viveka, July 02, 2010 - 6:52 am UTC

1. Does a count of the view v$session give me a count of all the sessions -- including user, background and recursive sessions?

2. If I want to restrict the number of ALL the above type of sessions in my DB to say 800, Can I use the formula sessions=processes*1.1+5 and set processes to 722? (using (800-5)/1.1)

Thanks.
Tom Kyte
July 06, 2010 - 2:39 pm UTC

1)

it gives a count of all sessions, yes.

remember a connection to a database may have

a) zero sessions, just because you are connected does not mean you have a session
b) one session, this is "normal"
c) more than one session, this happens frequently too

and remember a process may have

a) zero sessions associated with it
b) one or more sessions associated with it


the relationship is many to many.

2) see #1, since a single process may have 0, 1 or more sessions associated with it and a session can be associated with 0, 1 or more processes over time...

No, setting processes would not necessarily be sufficient.





Do this, connect to the database using sqlplus, issue "set autotrace on", then select count(*) from v$session where username = user

You'll see two sessions - if you are using dedicated server - you'll only have one process.


If you use shared server, thousands of sessions can use tens of processes.

If you use many sqlnet features - you have the ability to have a complete disconnect between the number of sessions and the number of processes.

Viveka, July 07, 2010 - 12:54 am UTC

This means that I can restrict the number of sessions only via the application ? (I have a third party package running on an application server in another box accessing the DB.)

Thanks.
Tom Kyte
July 08, 2010 - 11:33 am UTC

I should have mentioned

http://docs.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams227.htm#REFRN10197

you can limit sessions directly.

Oracle Process memory

Praveen, August 31, 2010 - 10:02 pm UTC

How to determine the process memory assign to Oracle.
If we assume 10mb allocated to each oracle process does it mean the value of Oracle parameter process *10 gives the total MB required by oracle process?
Tom Kyte
September 09, 2010 - 11:49 am UTC

are you asking to set the pga_aggregate_target?

if so, use the pga aggregate target advisor (stats pack or AWR report)

mfz, September 17, 2010 - 4:47 pm UTC

I am getting "Maximum Processes Exceeded" in the alert log . How to avoid this error ? This database supports a web application which has implemented connection pooling .

I am on 11g Release 2 on Windows 64 bit .

SQL> Select Status , Count(*)  From V$session
  2  where schemaname not in ( 'SYS' , 'SYSMAN' , 'DBSNMP') GROUP BY status ;

STATUS     COUNT(*)
-------- ----------
INACTIVE         72


Select Pname , Username , Program  
from v$process where background is null ;


D000  SYSTEM          ORACLE.EXE (D000)
S000  SYSTEM          ORACLE.EXE (S000)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)
      SYSTEM          ORACLE.EXE (SHAD)

Tom Kyte
September 17, 2010 - 5:06 pm UTC

well, the obvious would be "increase processed, init.ora parameter"

If you are hitting max processes exceeded with just a handful of connections like this - you probably have that set artificially low - but you didn't share your settings with us...

A reader, September 17, 2010 - 9:14 pm UTC

Sorry . It was set to 800

@A reader

Michel CADOT, September 18, 2010 - 1:07 am UTC


What is the exact and complete message you got in alert.log?

Regards
Michel

A reader, September 18, 2010 - 8:27 am UTC

Mike .. Here are the snippets ... Thanks


ORA-20 errors will not be written to the alert log for

.....

kkjcre1p: unable to spawn jobq slave process


Errors in file c:\app\oracle\diag\rdbms\dbdev\dbdev\trace\dbdev_cjq0_3316.trc:the alert log for

@A reader

Michel CADOT, September 18, 2010 - 9:52 am UTC


You most likely reached an OS limit not an Oracle one.
See details in trace file.

Regards
Michel

Tom Kyte
September 20, 2010 - 1:47 pm UTC

agreed, give a synopsis of what is in the trace file

A reader, September 27, 2010 - 10:28 am UTC

Here are the contents of alert log and trace file

Altert Log
===========
....
....
.....

ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
Process J001 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file c:\app\oracle\diag\rdbms\testdb\testdb\trace\testdb_cjq0_9136.trc:
Mon Sep 27 11:17:28 2010
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file c:\app\oracle\diag\rdbms\testdb\testdb\trace\testdb_cjq0_9136.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file c:\app\oracle\diag\rdbms\testdb\testdb\trace\testdb_cjq0_9136.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file c:\app\oracle\diag\rdbms\testdb\testdb\trace\testdb_cjq0_9136.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file c:\app\oracle\diag\rdbms\testdb\testdb\trace\testdb_cjq0_9136.trc:
Mon Sep 27 11:18:09 2010
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file c:\app\oracle\diag\rdbms\testdb\testdb\trace\testdb_cjq0_9136.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file c:\app\oracle\diag\rdbms\testdb\testdb\trace\testdb_cjq0_9136.trc:

TRACE FILE
==========

Trace file
c:\app\oracle\diag\rdbms\testdb\testdb\trace\testdb_cjq0_9136.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Windows NT Version V6.1
CPU : 2 - type 8664, 2 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:659M/3839M, Ph+PgF:3667M/7677M
VM name : VMWare Version (6)
Instance name: testdb
Redo thread mounted by this instance: 1
Oracle process number: 24
Windows thread id: 9136, image: ORACLE.EXE (CJQ0)


*** 2010-09-20 22:00:00.103
*** SESSION ID:(12.5) 2010-09-20 22:00:00.103
*** CLIENT ID:() 2010-09-20 22:00:00.103
*** SERVICE NAME:(SYS$BACKGROUND) 2010-09-20 22:00:00.103
*** MODULE NAME:() 2010-09-20 22:00:00.103
*** ACTION NAME:() 2010-09-20 22:00:00.103


*** TRACE FILE RECREATED AFTER BEING REMOVED ***

Setting Resource Manager plan SCHEDULER[0x3003]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Setting Resource Manager plan SCHEDULER[0x3004]:DEFAULT_MAINTENANCE_PLAN via scheduler window

*** 2010-09-21 22:00:00.508
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Setting Resource Manager plan SCHEDULER[0x3005]:DEFAULT_MAINTENANCE_PLAN via scheduler window

*** 2010-09-22 22:00:00.123
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Setting Resource Manager plan SCHEDULER[0x3006]:DEFAULT_MAINTENANCE_PLAN via scheduler window

*** 2010-09-23 22:00:00.308
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Setting Resource Manager plan SCHEDULER[0x3007]:DEFAULT_MAINTENANCE_PLAN via scheduler window

*** 2010-09-24 22:00:00.189
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Setting Resource Manager plan SCHEDULER[0x3008]:DEFAULT_MAINTENANCE_PLAN via scheduler window

*** 2010-09-25 06:00:00.114
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Setting Resource Manager plan SCHEDULER[0x3009]:DEFAULT_MAINTENANCE_PLAN via scheduler window

*** 2010-09-26 06:00:00.460
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
ORA-00020: No more process state objects available

*** 2010-09-26 20:05:26.315
ORA-00020: No more process state objects available
ORA-00020: No more process state objects available
ORA-00020: No more process state objects available
ORA-00020: No more process state objects available

*** 2010-09-27 11:16:13.493
ORA-00020: No more process state objects available
ORA-00020: No more process state objects available

*** 2010-09-27 11:16:23.618
ORA-00020: No more process state objects available
ORA-00020: No more process state objects available

*** 2010-09-27 11:16:28.837
ORA-00020: No more process state objects available

*** 2010-09-27 11:17:29.024
ORA-00020: No more process state objects available
ORA-00020: No more process state objects available

*** 2010-09-27 11:17:34.165
ORA-00020: No more process state objects available
ORA-00020: No more process state objects available

*** 2010-09-27 11:18:09.322
ORA-00020: No more process state objects available
ORA-00020: No more process state objects available

Tom Kyte
September 27, 2010 - 12:20 pm UTC

how many jobs are you trying to run concurrently, what is job_queue_processes set to.

A reader, September 27, 2010 - 1:29 pm UTC

I was not running any concurrent jobs.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
job_queue_processes                  integer     1000

Tom Kyte
September 27, 2010 - 1:45 pm UTC

sorry, you were. I don't mean "oracle apps" concurrent jobs, I mean dbms_job jobs.


kkjcre1p: unable to spawn jobq slave process


did you have a lot of jobs trying to run concurrently.

A reader, September 27, 2010 - 2:00 pm UTC


Sorry , Tom . I dont get it .

These are my jobs ( including the system defined one).

SQL> col  owner format a10
SQL> col  job_name  format a30
SQL> col  REPEAT_INTERVAL  format a50
SQL> Select  owner , job_name , repeat_interval   from dba_scheduler_jobs ;

OWNER      JOB_NAME                       REPEAT_INTERVAL
---------- ------------------------------ --------------------------------------------------
SYS        XMLDB_NFS_CLEANUP_JOB          Freq=minutely;interval=5
SYS        SM$CLEAN_AUTO_SPLIT_MERGE      freq=daily;byhour=0;byminute=0;bysecond=0
SYS        RSE$CLEAN_RECOVERABLE_SCRIPT   freq=daily;byhour=0;byminute=0;bysecond=0
SYS        FGR$AUTOPURGE_JOB              freq=daily;byhour=0;byminute=0;bysecond=0
SYS        BSLN_MAINTAIN_STATS_JOB
SYS        DRA_REEVALUATE_OPEN_FAILURES
SYS        HM_CREATE_OFFLINE_DICTIONARY
SYS        ORA$AUTOTASK_CLEAN
SYS        FILE_WATCHER
SYS        PURGE_LOG
SYSTEM     ADV_SEGMENTADV_3753295
ORACLE_OCM MGMT_STATS_CONFIG_JOB          freq=monthly;interval=1;bymonthday=1;byhour=01;bym
                                          inute=01;bysecond=01

ORACLE_OCM MGMT_CONFIG_JOB
EXFSYS     RLM$SCHDNEGACTION              FREQ=MINUTELY;INTERVAL=60
EXFSYS     RLM$EVTCLEANUP                 FREQ = HOURLY; INTERVAL = 1
BATUSER    DAT_COPY_JOB                   FREQ=DAILY;BYHOUR=8;BYMINUTE=0
BATATA     DAT_DEPTS                      FREQ=DAILY;BYHOUR=8;BYMINUTE=30

Tom Kyte
September 27, 2010 - 2:51 pm UTC

how about dba_jobs too - did you happen to have something that could have 'accidentally' created a ton of jobs unbeknownst to you

A reader, September 27, 2010 - 3:01 pm UTC

I was not using dbms_job for any of my things .


SQL> Select log_user , schema_user , last_date , next_date  , interval  from dba_jobs;

LOG_USER             SCHEMA_USER          LAST_DATE NEXT_DATE INTERVAL

SYS                  APEX_030200          27-SEP-10 27-SEP-10 sysdate + 8/24
SYS                  APEX_030200          27-SEP-10 27-SEP-10 sysdate + 10/1440
SYS                  SYSMAN               27-SEP-10 27-SEP-10 sysdate + 1 / (24 * 60)



SQL> select * from v$version;

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Tom Kyte
September 27, 2010 - 3:17 pm UTC

the jobs would not be there anymore if there were one off jobs and they got to run eventually.


In any case - this error is basically saying that beginning about


*** 2010-09-26 20:05:26.315


You had exceeded processes - for whatever reason, you had 1000 threads going in your database server at that point and we could not start just one more (to service any of your jobs that were scheduled to run - which you do have, you showed us above you do).

Now, you probably recovered from that by restarting - which makes it not possible to diagnose what these processes were (you cannot query v$process anymore, it was reset). Even if you did not restart - and it just recovered itself - the information in v$process is gone by now.


the v$process you showed us above with 72 entries is not from the time of the failure - you either restarted (probable) or whatever was flooding your system at that time - stopped flooding it.

do you have access to AWR and ASH? If so, it can show you what was going on in your system at that time.

If not, you might want to monitor v$process (as this seems to be ongoing) to watch for a spike (you could create a job that just keeps running that queries and saves the contents of v$process every 15 minutes if the number of rows in it exceeds say 500 or something - so you can analyze what is ultimately flooding you)

DIFFERENCES BETWEEN PROCESSES,...

Bao Han, September 27, 2010 - 3:40 pm UTC

All 3 comcepts are crystal clear to me now. Thanks!

opidcl aborting process ...

A reader, October 14, 2010 - 9:00 am UTC

I am getting these kinds of entries once a week or so .I dont find any adequate in Metalink. Please advise .

I am on 11g r2 on windows 2008 r2 64 bit.


Thu Oct 14 03:21:29 2010
opidcl aborting process unknown ospid (11556) as a result of ORA-2396
Thu Oct 14 03:21:35 2010
opidcl aborting process unknown ospid (9956) as a result of ORA-2396
Thu Oct 14 03:22:00 2010
opidcl aborting process unknown ospid (10084) as a result of ORA-2396
Thu Oct 14 03:22:05 2010
opidcl aborting process unknown ospid (13928) as a result of ORA-2396

Tom Kyte
October 15, 2010 - 7:53 am UTC

02396, 00000, "exceeded maximum idle time, please connect again"


you appear to have a resource profile in place don't you.

opidcl aborting process ...

A reader, October 14, 2010 - 9:03 am UTC

I am getting these kinds of entries once a week or so .I dont find any adequate in Metalink. Please advise .

I am on 11g r2 on windows 2008 r2 64 bit.


Thu Oct 14 03:21:29 2010
opidcl aborting process unknown ospid (11556) as a result of ORA-2396
Thu Oct 14 03:21:35 2010
opidcl aborting process unknown ospid (9956) as a result of ORA-2396
Thu Oct 14 03:22:00 2010
opidcl aborting process unknown ospid (10084) as a result of ORA-2396
Thu Oct 14 03:22:05 2010
opidcl aborting process unknown ospid (13928) as a result of ORA-2396

A reader, October 18, 2010 - 2:12 pm UTC

I have the following profile on few app users . Not sure how this connected this "opidctl" process . What is this process for ? How it is related to the app user?

CREATE PROFILE profusr
LIMIT
connect_time 120
failed_login_attempts 10
idle_time 60
sessions_per_user 20;

Tom Kyte
October 25, 2010 - 10:01 am UTC

you have sessions timing out - they are getting zapped, we are cleaning up.

Maximum dedicated connection in oracle

Vishwanath, February 19, 2011 - 1:36 am UTC

Hi Tom,

What is the limit of dedicated connections we can have in oracle.Is there is any count?

I just want to check the performance of the database by changing the same to shared server.

thx

Vishwanath



Tom Kyte
February 20, 2011 - 12:20 pm UTC

the maximum is physically limited by the OS - how many processes can the OS support in theory.

the maximum is logically limited by reality - how many processes are sensible on your machine/platform?

Dedicated dconnection

Vishwanath, February 21, 2011 - 12:10 am UTC

Thanx

Actually i have 45 dedicated connections and here is th OS
configuration
Windows Server 2008
Processor:Intel 2.27ghz (2 processor)
Ram:24 GB

Database configuration:
SGA:2464M
PGA:1632M

Are above configuration is ok for dedicated connection or it will require shared server configuration.

I will be very very glad if u please explain me in detail..







Tom Kyte
February 22, 2011 - 10:55 am UTC

If all 45 went active simultaneously - what would happen to your little 2 cpu machine?

I don't think it calls for shared server - 45 threads is ok - it is just "what happens if they all go off at the same time".

You might consider backing off the connection pool size and queuing at the application server instead of queuing in the database - the earlier you queue inbound request to ensure you don't kill the server - the better.

Dedicated connection

Vishwanath, February 23, 2011 - 2:16 am UTC


ok! but here is my case an application server cannot be deployed (minimizing hardware cost).

So,is there any other solution for this ?


Tom Kyte
February 24, 2011 - 12:18 pm UTC

re-read what I wrote above - there is no need of a "solution" necessarily. 45 is pretty small - to big for 2 cpus if they all lit up at once of course - but given that this database isn't 'big', it really isn't worth spending too much time on.

Shared Database Link

A reader, March 31, 2011 - 3:46 pm UTC

Hi Tom

Can you please explain the process,sessions,connections in the context of SHARED DATABASE LINK ?

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_admin003.htm#i1008155

The explanation from the Oracle Doc URL above is not quite clear.


Thanks

Tom Kyte
April 12, 2011 - 10:43 am UTC

Say you are using non-shared database links. Then, for every session that opens a database link - there will be a network connect to the other remote database and a process over there to satisfy the request.

Say you are using a shared database link with a shared server connection locally. There would be a network link from the shared server to the other database, and a process over there to deal with it. Everyone that used that shared server however would share that single database link.


Say you are using a shared database link with dedicated server connections locally. There would be a network link from the dedicated server to the other database and the process to handle it over there (so far no savings), but if that dedicated server had say 10 sessions established, all 10 sessions could use that database link, could share it (therein lie the savings)

http://docs.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_admin003.htm#ADMIN12162


session

sam, July 19, 2011 - 11:17 am UTC

Tom:

If a database on average shows 100 sessions (mostly inactive) in the V$SESSION view, does that mean
a new hardware needed to host this database has to support 100 concurrent users at one time, or do you consider the "Active" sessions only.

or do you consider the number of connections that serve the users since these 100 sessions could be served using 10 connections (dedicated server configuarion). I do not know if there is an oracle view that gives you nubmer of connections available or you can see that in unix.

Is there any specific view or things you look for in oracle that can help you spec out the hardware needed to run this database?
Tom Kyte
July 19, 2011 - 11:53 am UTC

if you have 100 sessions established
then
   100 sessions have the potential to try to become simultaneously active
end if


ponder that reality for a minute. What would happen to the server if all sessions tried to become (stress the word try see - they physically cannot but they will try someday) active?


or do you consider the number of connections that serve the users since these
100 sessions could be served using 10 connections (dedicated server
configuarion). I do not know if there is an oracle view that gives you nubmer
of connections available or you can see that in unix.


unless you did special setup, it is probable (almost 100%) that each session is a connection is a dedicated server.

Unless you configured shared server or the application is using a relatively sophisticated api to connect.




To figure out the hardware needed - do math sam. I've tried to tell you to divide and multiply before :)


How much cpu do your transactions consume?
How many do you need to do at a given point in time?

Multiply. Leaving at least 30-40% idle capacity for the most part if you are transactional in nature.


sessions

sam, July 19, 2011 - 12:54 pm UTC

I should have told you that many of those inactive sessions are created by mod_plsql web application which uses connection pooling.

Would this change your statement that each session = one connection or that applies to both client/server and web environment.


Is there a query or view that answers the above questions on how much cpu
does the transactions consume or how many transactions do we have at a given point?
would you run statspack for this or check the V$SESSION or V$STATS.


Currently this is a big expensive ibm machine that supports many groups with 30-40 application installed on it. I only want to take two database applications out and install it on a new dedicated machine much smaller in size than current one.


Tom Kyte
July 19, 2011 - 1:26 pm UTC

I should have told you that many of those inactive sessions are created by
mod_plsql web application which uses connection pooling.


lower the connection pool setting, why do you need 100? can your database do 100?

Sam - I'm having some SERIOUS deja vu here. You and I have already discussed this ad-nauseum. Do we need to do it again? Cannot you just read what I already wrote to you before (it will not change... I will say the same things over and over..)



Would this change your statement that each session = one connection or that
applies to both client/server and web environment.


no, it wouldn't. In mod_plsql - each connection is it's own session, if you are using dedicated server - there will be a dedicated server for each connection/session.


Is there a query or view that answers the above questions on how much cpu
does the transactions consume or how many transactions do we have at a given
point?


Nope - not really. V$LICENSE has a bit of information about concurrency. But if you want to measure how much cpu your concept, your idea of a transaction uses - you would have to record that.




sessions

sam, July 19, 2011 - 4:57 pm UTC

<<lower the connection pool setting, why do you need 100? can your database do 100>>

Why lower it? Yes, the database can handle 100 sessions fine. Performance is great too.
I do not even know the connection pool setting for mod_plsql but most likely it is the default setting used after installation. I am not the administrator for it.

It must be at least 100 since I see 100 session on average in V$SESSION and V$PROCESS.



<<But if you want to measure how much cpu your concept, your idea of a transaction uses - you would have to record that. >>

OK, great but how do i record that? Do you turn on SQL trace, run the tansaction and see how much CPU it consumed on the TKPROF report.

What kind of mertic you measure that tells you OK now i can go now and get a single, dual core or quad core CPU with this speed, etc. and that should meet what my transactions need.


Tom Kyte
July 19, 2011 - 5:43 pm UTC

Sam,

I'm getting tired of repeating myself. I'm going to cut and paste, and if you don't have anything else to say other than "why lower it", please do not respond again, it is getting *boring*


if you have 100 sessions established
then
100 sessions have the potential to try to become simultaneously active
end if


ponder that reality for a minute. What would happen to the server if all sessions tried to become (stress the word try see - they physically cannot but they will try someday) active?




OK, great but how do i record that?

that is what I call instrumentation - in the form of an audit trail for example (that is how I know mine for example, but I use APEX - it is easy to figure many things out - out of the box - if you use a tool that is heavily instrumented)


What kind of mertic you measure that tells you OK now i can go now and get a
single, dual core or quad core CPU with this speed, etc. and that should meet
what my transactions need.



I must have answered that a billion times by now...

benchmark it
numerate it
evaluate the numbers

In other words - do not expect it to come to you in a dream. Do not expect to be able to answer that before having something to measure.




"process" in v$session

Jithin Sarath, October 12, 2011 - 10:39 am UTC

There seem to be quiet a few sessions in my prod DB with process column as "1234". They do not tie back to a valid session on DB server. Are these some kind of fake placeholders for something NULL?
Tom Kyte
October 12, 2011 - 11:57 am UTC

how about displaying some of the other information to give us context.

use print_table to display the first few rows of v$session with that 1234

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1035431863958

Processes & multiple DB instances,

A reader, October 19, 2011 - 10:33 am UTC

Hello,

The error ORA-00020 may indicate to bump up the value of processes parameter of a database instance. However, when a server hosts more than 20 instances (don't ask me why we have 20 instances), then I don't know whether there is a formula on how far we can continue increase the value of the processes parameter. The increase in parameter value for all the 20 instances shouldn't impact the OS. Is there a document or a formula on how to handle the processes?

The OS Linux x86_64 x86_64 x86_64 GNU/Linux. Are any OS related parameters that goes with ORA-00020 error?

Thanks,


Tom Kyte
October 19, 2011 - 6:31 pm UTC

The increase in parameter value for all the 20 instances shouldn't
impact the OS.


of *course* it will. It *has* to. It cannot "not impact the OS". Think about this - the OS is going to be burdened with a ton more busy work.


You should be looking at reducing your processes - using CMAN (connection manager) or shared server or (this is the best way) REDUCING THE SIZE OF YOUR CONNECTION POOLS.


You have 20 instances. You don't say how many cpus (cores) you have - but let's say it is 8.

Now, if all 20 instances decide to suddenly run a query that needs 100% cpu for 30 seconds each - what is going to happen to that machine?

If 8 of the instances decide to do that - what happens to the other 12 instances?

In one instance decided to run 10 of those queries at the same time - what happens to the other 19 instances?

You need to very very carefully use resource management here. I would suggest:

a) in 10g and before, use the resource manager to limit the number of active sessions in each instance to some number LESS THAN the number of cores. This prevents a single instance from killing the machine. In 11g you can and should use instance caging instead (set cpu count to lower than the number of cpus and enable the default resource plan)

b) using shared server with the shared server pool set to a reasonable number to reduce the number of processes on the server. Do this only if the app server folks (not intelligently) demand to have dozens or hundreds of connections to your database. The best place to do the connection pooling would be IN THE CONNECTION POOL - but app server folks never seem to understand that basic concept.

another benefit of the shared server processing will be that session memory is kept in the SGA not the PGA and that will make it so that a single instance cannot have a runaway process that consumes all real memory on the machine, you'll be constrained to the size of the SGA.

follow up,

A reader, October 20, 2011 - 10:19 am UTC

Thanks. However, when I a user tries to login as "oracle" on a Linux server, they get "resource temporarily unavailable". They won't get the error when they login as themselves. Is this related to previous Oracle sessions taking up OS resources and preventing any new oracle ID to login?

I am trying to understand what relationship exists between Oracle processes and any other OS related kernels.

Thanks,

Tom Kyte
October 20, 2011 - 6:14 pm UTC

if you mean

when I log into the OS as a user account X (you are using the account name oracle), they get an error logging into the database using "sqlplus user/password".

however when I log into the OS as user account Y, "sqlplus user/password" works.



If that is what you are saying - then I would say simply "check your oracle_home and oracle_sid - you will find they are different"

compare the environments of these two accounts, sounds like they are trying to access two different databases entirely and one of them is "up" and one of them "is not"

Follow up,

A reader, October 21, 2011 - 2:18 pm UTC

We are not even getting into SQL Plus. The restriction (resource temporarily unavailable) is happening when a user tries to "su - oracle" on Linux.

Thanks,

Tom Kyte
October 21, 2011 - 8:36 pm UTC

then you obviously have something in your login script (.bashrc or whatever shell you are using) that does attempt to get into the database.

I fail to see what this has to do with Oracle - if you are not typing anything to cause the issue - it is something you've put into a script that is being run during login time. Your environment is botched. You or someone else did this to yourselves.



You are getting into sqlplus (or running some program that attempts to log into the database). Review your login scripts.

process limit scenario (cant connect as sysdba)

A reader, January 16, 2012 - 2:01 pm UTC

hi tom,

i am on 10g linux for production
on my development platform - i am on 10g windows.

today i encounter something tricky.
there was some high connection today and i hit my process limit set.
------------

so i wanted to connect as sysdba to view the v$session/v$process to see what's wrong.

but the problem is i can't because the process limit is hit.
i will always endup being this way ->

------
sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jan 17 03:57:48 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected.
SQL> select * From v$session;
select * From v$session
*
ERROR at line 1:
ORA-01012: not logged on
SQL>

----------------

q1) what should i do ? i do not want to just simply restart the db 

-----------------

On my development platform, trying to simulate the scenario, i run this query

select p.program, s.sid, s.program from v$process p join v$session s
on p.addr = s.paddr;

--> there are 25 process return

select * from v$resource_limit where RESOURCE_NAME in ('sessions','processes','transactions');

processes 24 25         25         25
sessions 33 34         35         35
transactions 0 4         38  UNLIMITED

-----

q2) however the current_utilization is 24, but my V$process is 25. which figure should i believe ?

q3) on sessions, the current utilization is 33, but when i select count(*) from v$session, it is 25 only. which figure is correct then ?

Please advise.

Regards,
Noob


A reader, January 17, 2012 - 1:31 pm UTC

hi tom,

happy new year.

saw that you have begun replying the threads,
any chance you might have an idea on the issue above this thread ?

Regards,
Noob
Tom Kyte
January 17, 2012 - 3:38 pm UTC

I don't know how you connected as sysdba if you hit max processes already.

[tkyte@localhost ~]$ $ORACLE_HOME/bin/sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 16:36:59 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-00020: maximum number of processes (30) exceeded


Enter user-name: / as sysdba
ERROR:
ORA-00020: maximum number of processes (30) exceeded


Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[tkyte@localhost ~]$ 
[tkyte@localhost ~]$ 
[tkyte@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 16:37:08 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

idle> connect / as sysdba
ERROR:
ORA-00020: maximum number of processes (30) exceeded





so, I'm not sure what you were really doing.

A reader, January 18, 2012 - 11:36 am UTC

hi tom,

previously i was running shared and dedicated mode so i think some configurations of mine was not right.

i have now set everything to dedicated and remove the shared server and dispatchers value.
------------------------------

select * from v$resource_limit where RESOURCE_NAME in ('sessions','processes','transactions');

processes 20 21         21         21
sessions 27 28         28         28
transactions 0 4         30  UNLIMITED


---------------------------
Different attempts to be the 21st connection

1) SQL> conn szejie/abc123
ERROR:
ORA-00020: maximum number of processes (%s) exceeded


2) SQL> conn szejie/abc123@ora10
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server

3) SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> select * From v$session;
select * From v$session
*
ERROR at line 1:
ORA-01012: not logged on

----------------------------------------

q1) For attempt 2, is this error message normal ?

q2) For attempt 3, i believe i cannot connect as sysdba since process is full.

what should i do ? if i encounter this in production and alot of users cannot connect in and yet 

- i cannot connect in &
- neither could i change the parameter and restart the database as there are alot of users connected as well .

Regards,
Noob

Tom Kyte
January 18, 2012 - 12:32 pm UTC

is this windows or linux.

A reader, January 18, 2012 - 2:17 pm UTC

hi tom,

i am on my testing environment now which is window..

but actually on my linux i am having this problem whereby i cannot make any connection due to the max out process.

regards,
Noob

Tom Kyte
January 18, 2012 - 2:57 pm UTC

right, so lets stick to linux. Ignore the stuff you see on windows, it isn't relevant.

when you hit max processes, you have hit it, you would either have to have a pre-established session or you would have to wait for someone to log off to allow you to get one.

In theory, you could kill a dedicated server or two at the OS level, but I'm not really suggesting you do that.

A reader, January 18, 2012 - 3:01 pm UTC

hi tom,

thanks for the reply !

coming back to think of it, i really wouldn't want to kill any process at OS level (what if it is running an transaction at the moment)..

but on 2nd thoughts again, someone else (prank) can easily max out my db connection and block off all normal users (if he/she know our backend architecture)..

I am thinking of maybe i should always keep one sqlplus session in the background connected as sys just in case anything happen..

Rgds,
Noob


Tom Kyte
January 18, 2012 - 3:43 pm UTC

but on 2nd thoughts again, someone else (prank) can easily max out my db
connection and block off all normal users (if he/she know our backend
architecture).


how? I can see your OWN DEVELOPERS doing this to you if they over configure the connection pool (they should sort of be consulting with you before they make any sort of change like that) - but how could anyone else easily max out your database?

or you could just shared server connect your app servers since people in the middle tier love to create hundreds/thousands of connections - but then only use 20 of them at a time max under normal conditions. and leave yourself as many dedicated server connections as you like.

Session Hung

A reader, February 09, 2012 - 10:26 am UTC

Hi Tom,

We have a situation on our server and do not know how to solve.

We have a database which is on 9.2.0.6 on HP-UX.
The database hangs at a particular time in the evening at 7:00 PM, and we are unable to connect to it.
Neither SYS nor any other schema connections are possible by which we could at least run a hanganalyze or a system state even.

Under such circumstance, the only option remains is to kill the pmon process and terminate the instance. Nothing unusual is seen as of the sessions or any abnormalities from statspack report.

I can just see a DBWR trace file generated at that point.

Can you help us to understand what can we do to resolve this as even SYS user connections are not possibel at that state.


Tom Kyte
February 09, 2012 - 7:32 pm UTC

every night at 7PM

are your datafiles on a SAN by any chance?

Database sessions hung issue

A reader, February 10, 2012 - 4:42 am UTC

Yes, the files are on a SAN.
Tom Kyte
February 10, 2012 - 5:27 pm UTC

betcha there is a backup going on somewhere in your system at 7pm. Ask around.


Funny - second time in my life someone has a 'hung' database at precisely 7pm. Exchange backup going on at 7pm. shared san. totally wiped out the IO - making the database APPEAR hung.

Check it out - you'll find someone is slamming the SAN at seven - good old ssss.


IDLE_TIME vs SQLNET.EXPIRE_TIME / session vs connection

A reader, February 28, 2012 - 1:25 pm UTC

hi tom,

I need to make sure that a connected session does not idle more then 30 min.

or rather a inactive session for more then 30min must not an active connection to the db anymore

as we know

session != connection.
i can have a connection with 0 session.

-------------

my question is

q1) setting IDLE_TIME - does it just terminate the session, or it kill the connection as well ?

when a session hit a IDLE TIME, will it get killed ? and will the dedicated process for it be deallocated ?

q2) setting SQLNET.EXPIRE_TIME -> "Use to specify a specify the time interval, in minutes, to send a probe to verify that client/server connections are active."

what is active connection / inactive connection in relation to active session /inactive session ?

can I have a active connection with an inactive session ? so how does oracle know if a connection is active or not ?


Regards,
Noob
Tom Kyte
February 28, 2012 - 3:30 pm UTC

q1) it will kill sessions

you will see it as "sniped" - until the session becomes active and then the session will get an ora-28 and go away.

q2) that will not terminate a session - it simply makes sure the connection still exists and if it doesn't (because the client just disappeared) it will clean up the orphaned database resources. It will not time out a session and kill it - it will only clean up from abnormally terminated clients.



Question

Kailash, April 24, 2012 - 6:35 am UTC

Tom,
Thanks much for a great explanation. However, I am still not clear on few things that you explained

In your above original example you demonstrated difference between a process, session and connection, you did a "disconnect". Will that not cause the "server" process to be terminated at that point? How does the "ps -ef | grep ..." still show that the "server" process is still alive? (since most of the text books say that the server process will be terminated as soon as the client disconnects)
Tom Kyte
April 24, 2012 - 7:53 am UTC

A connection is a "physical circuit", a pathway to a database. You can be
connected to a database yet have 0 or 1 or MORE sessions going on that
connection.


when you "disconnect" in sqlplus, all you've done is destroy your session - but not your physical circuit.

I tried to indicate that directly in the original text:

idle> select * from dual;
SP2-0640: Not connected

still have my process, but no session, the message is a little "misleading".  
Technically -- I have a connection, I don't have a session




It could be that most text books are not being as technically picky as others - they are probably thinking of a "client application EXITING" as disconnecting. Not really "disconnecting"


1 dedicated process , 1 connection , 2 session

A reader, April 25, 2012 - 2:15 pm UTC

hi tom,

i understand from your previous thread that

- a process is used to service a session
- a connection is a physical link between the session and the process.
- 1 or more session can be establish over a connection
- 1 or more session might use the same process.

in your 1st previous example, with autotrace on, you prove that there will be 2 session served by a single process in a single connection.

---------------------

q1) therefore in a dedicated server environment, when will an additional process be spawn to service a new session ?

how does oracle determine if the new session should be service by a new process or using existing process (like in the autotrace example)

is it only if the new session is establish by a new connection, then a new server process will be spawn ?

q2) i am reading on shared database link on
" http://docs.oracle.com/cd/B19306_01/server.102/b14231/ds_concepts.htm#sthref3878"

"Different users accessing the same schema object through a database link can share a network connection."

comment1) in a dedicated environment, i believe with different users, there will be several dedicated processes spawn. does the phrase means that, all these dedicated processes can shared the same connection to the remote database ?

"When a user needs to establish a connection to a remote server from a particular server process, the process can reuse connections already established to the remote server. The reuse of the connection can occur if the connection was established on the same server process with the same database link, possibly in a different session. In a non-shared database link, a connection is not shared across multiple sessions."

comment2) on the phrase above, it states that process can only reuse connection only if it was established on the same server process.

so isn't it contradicting with comment1) since multiple server processes can shared a connection to the remote database and yet only if the connection is from the same server process ?

maybe i got my concepts wrong.

Hope you could shed some light to this.

Regards,
Noob



Tom Kyte
April 25, 2012 - 3:01 pm UTC

q1) when ever the client tells us to - the client creates sessions. sqlplus created that session explicitly, in its code. The programmer does it.

q2) shared database links allow a large community of users to share a small set of connections to a remote database basically.

c1) yes

c2) no, because a single process can have zero, one or MORE sessions associated with it in dedicated server mode and a single process in shared server services any and all connections to the database.


1 dedicated process , 1 connection , 2 session

A reader, May 04, 2012 - 3:59 pm UTC

hi tom,

with regards to your answer for q1 in the earlier thread
"q1) when ever the client tells us to - the client creates sessions. sqlplus created that session explicitly, in its code. The programmer does it. "

so do you mean that the client will tell oracle
- when to create a dedicated process to service a session
and
- when to use the same dedicated process to service another session
(e.g the autotrace example, 2 session service by 1 dedicated process).

am i right ?

Regards,
Noob

Tom Kyte
May 06, 2012 - 3:10 pm UTC

Yup, you typically need to be programming at the OCI (oracle call interface) level to do that - but yes.

inactive session

A reader, May 13, 2012 - 6:16 am UTC

Hi Tom

Recently we did a single sql MERGE that was to update over 12 million rows in a table. This code was executed using sqlplusw, after about an hour or so we could find that the session went inactive. Also there were no locks once the session was inactive. The sqlplusw went to hanging state(Not Responding). After it was reported that the session was inactive and there were no locks i closed the not responding sqlplusw window.

This was a single sql only and if the process of updating was going on shouldnt this have been in the active status throughout since it was doing work?.

is the session idle time which has caused this problem?

In the database that i work on if i leave the session for some time say around 20 min, and then issue a query like "SELECT * from DUAL, it would say "not connected".

Which is the parameter that i can see to know the exact time interval oracle waits till it says "not connected"



Tom Kyte
May 13, 2012 - 10:27 am UTC

that was a pretty bad idea. If I had 12,000,000 rows to update - I would be finding some other way to do it using DDL. Think about it - you probably have MILLIONS of blocks you'll hit to do that. If a physical IO takes an average of 5ms to perform (pretty average) - every 1,000,000 blocks you need to read would be 50,000 seconds - about 1.3333 hours.... and you have to write them out too (meaning 5ms will be the best you can achieve) and you have to create and write the undo and redo (meaning 5ms - forget it, it won't happen that fast). meaning - this is probably a day long operation!

You never want to update millions of rows - seriously.



as for the timeout issue - talk to your network folks. They have a security setting that times out and kills any open network connection that hasn't see any activity for some period of time. You submitted your merge and would have been waiting on a network connection for hours (if not days). Your network team's rule "killed" that network connection because it saw no traffic on it. Nothing to do with Oracle - everything to do with your sites security policies. Of this I'm pretty much 100% sure. I've seen it about a million times by now....

inactive session

A reader, May 13, 2012 - 11:02 am UTC

thanks tom for your response.

I knew the update was a bad idea, after rereading some of the asktom forum articles. However this particular case was a one off thing, because the update was originally supposed to be have occured as part of the insert(ie the changes of the update were to be created and inserted),somehow the Informtica code messed this one up and had to resort to this.

So, whilst a long update is going on the work is done by the server and is an active session, but since the client is simply waiting for the completion status its considered as idle and this idleness after some time delay violated a network rule which caused the zapping of the connection.

Am i interpreting this correctly?. If so is this same as idle wait time for a session?


Tom Kyte
May 13, 2012 - 11:19 pm UTC

that is what happened.


sqlplus opened a network connection to the database.
It sent and received some data back and forth. (it was active on the network)
Then it sent the UPDATE - and sat there waiting for something to come back (it was inactive on the network).

At some predefined point in time, based on your network security policy, the connection was "zapped" by the network software (firewall) since it appeared inactive.

What they are trying to 'protect' against is someone leaving something like a sqlplus window open and walking away - leaving it open for someone else to type in whatever they want. (or any program that opens a connection to a remote server like telnet, ssh, etc)



Next time, instead of even considering UPDATE, use a CREATE TABLE AS SELECT to select out the updated data... No undo, possible to skip redo, easily parallelized, direct path - bypasses the inefficiencies of the buffer cache...

thanks Tom

George Joseph, May 13, 2012 - 11:52 pm UTC


Connection and Session

Rajeshwaran, Jeyabal, May 20, 2012 - 6:32 am UTC

Tom:

I was reading about connection vs session, from your book Expert Oracle database Architecture 2nd edition, Chapter#5-Oracle process.

1) I dont see that " We can see that we have no sessions—but we still have a process, a physical connection (using the previous ADDR value): " happening in Windows environment, is that only possible in unix/linux and not in windows.

I see everything, except this "We can see that we have no sessions—but we still have a process"

Below is the sql*plus copy of my workings.

<quote src=Expert oracle database architecture 2nd edition>
We can see that we have no sessions—but we still have a process, a physical connection (using the previous ADDR value):
sys%ORA11GR2> select username, program
2 from v$process
3 where addr = hextoraw( '32BC2B84' );
USERNAME PROGRAM
--------------- ------------------------------------------------
tkyte oracle@localhost.localdomain (TNS V1-V3)
So, here we have a connection with no sessions associated with it. We can use the also misnamed
SQL*Plus CONNECT command to create a new session in this existing process (the CONNECT command
might be better named CREATE_SESSION). Using the SQL*Plus instance we disconnected in, we’ll execute
the following:
ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> select username, sid, serial#, server, paddr, status
2 from v$session
3 where username = USER;
USERNAME SID SERIAL# SERVER PADDR STATUS
--------- ---------- ---------- --------- -------- --------
OPS$TKYTE 37 404 DEDICATED 32BC2B84 ACTIVE
</quote>


idle> connect rajesh/oracle
Connected.
rajesh@ORA11GR2> SELECT sid,serial#,paddr,username,server,status
  2  from v$session
  3  where username = USER;

       SID    SERIAL# PADDR    USERNAME                       SERVER    STATUS
---------- ---------- -------- ------------------------------ --------- --------
        20       4838 21C830DC RAJESH                         DEDICATED ACTIVE

rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace on statistics;
rajesh@ORA11GR2>
rajesh@ORA11GR2> SELECT sid,serial#,paddr,username,server,status
  2  from v$session
  3  where username = USER;

       SID    SERIAL# PADDR    USERNAME                       SERVER    STATUS
---------- ---------- -------- ------------------------------ --------- --------
        20       4838 21C830DC RAJESH                         DEDICATED ACTIVE
        23       2084 21C830DC RAJESH                         DEDICATED INACTIVE

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

rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
rajesh@ORA11GR2>
rajesh@ORA11GR2> connect sys/oracle as sysdba
Connected.
sys@ORA11GR2>
sys@ORA11GR2> SELECT sid,serial#,paddr,username,server,status
  2  from v$session
  3  where username = 'RAJESH';

no rows selected

sys@ORA11GR2>
sys@ORA11GR2> select username,program
  2  from v$process
  3  where addr = hextoraw('21C830DC');

no rows selected
sys@ORA11GR2>

You can see here that i don't find any entry in V$process for '21C830DC'. But your example in the book shows that your find an entry in V$process.
sys@ORA11GR2> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

sys@ORA11GR2>
sys@ORA11GR2> connect rajesh/oracle
Connected.
rajesh@ORA11GR2> SELECT sid,serial#,paddr,username,server,status
  2  from v$session
  3  where username = USER;

       SID    SERIAL# PADDR    USERNAME                       SERVER    STATUS
---------- ---------- -------- ------------------------------ --------- --------
        20       4843 21C830DC RAJESH                         DEDICATED ACTIVE

rajesh@ORA11GR2>

Tom Kyte
May 21, 2012 - 8:22 am UTC

windows is single process.

there is a *single* process regardless of the number of connections/etc.


Oracle Sessions

vinod, August 01, 2012 - 8:59 am UTC

Hello Tom,

Is it any thumb rule how much value of sessions/processes parameter we should set
say i have 20 GB RAM,and i have set value of session=300
my sga is 8GB and pga 1.8 GB
if ever i want to increase value of session/processes how to determine how much max value will be supported by this
config

Tom Kyte
August 01, 2012 - 1:00 pm UTC

it is more a function of your CPU count - if you had 300 processes going and they all decided to become active - what would happen to your machine?

watch this:

http://www.youtube.com/watch?v=xNDnVOCdvQ0

Oracle Sessions

vinod, August 02, 2012 - 2:17 am UTC

Thanks Tom,

so its more of cpu/hardware/os dependency
And while increasing number of sessions we should not just
consider oracle parameters or configurations like sga,pga etc

for example if i see total sessions at any point of time of day approaching max no of sessions set for the instance,
should i increase taking into account load on cpu,hardware
or also take into account oracle configurations(any init.para,sga,pga size etc)


Best Regards

Oracle Sessions

vinod, August 02, 2012 - 2:37 am UTC

Hello Tom,

In addition to to what i ask in above message
if i have 10 cpu's ,in one server approximately how much
sessions it can hold smoothly/without getting overloaded

Thanks in advance
Tom Kyte
August 02, 2012 - 9:09 am UTC

with 10 CPU's you could have maybe 9 concurrently active sessions that are using pure CPU (more if some of the sessions are waiting on something like a physical IO - they'll be active but not needing CPU)

you should not just be increasing sessions willy nilly, you should be going to your middle tier and reducing the size of their connection pools.

did you watch the video? did you understand what it was showing you?

Oracle Sessions

vinod, August 03, 2012 - 2:11 am UTC

Thanks Tom again for valuable inputs

one doubt if i have 10 cpus ,as you mentioned it can hold
9 concurrent sessions,but if i query v$session ,it shows much more then 10 sessions ,for example if any point of time in this scenario if i see say 200 sessions only 9 out of this can be active ,rest will be inactive ,and out of this inactive sessions whichever required to use cpu will be waiting for thier cpu turn
Tom Kyte
August 15, 2012 - 4:58 pm UTC

only if you use something like the database resource manager or shared server to make it so that only N sessions can become active. We do not limit it by default

Same sid for two sessions within a connection

Prajjwal Mallik, August 08, 2012 - 2:30 am UTC

Hi Tom,
I expected a different sid when using "pragma autonomous_transaction" from the same connection. However, I was wrong.


SQL> conn u1/u1
Connected.
SQL> column SYS_CONTEXT('USERENV','SID') format a10
SQL> select sid, sys_context('userenv', 'sid') from v$mystat where rownum=1;

       SID SYS_CONTEX
---------- ----------
        15 15

SQL> 
SQL> set serverout on size unl
SQL> declare
  2   pragma autonomous_transaction;
  3  
  4   sid1 int;
  5   sid2 int;
  6  begin
  7   select sid into sid1 from v$mystat where rownum=1;
  8   select sys_context('userenv', 'sid') into sid2 from dual;
  9  
 10   dbms_output.put_line(sid1||','||sid1);
 11  end;
 12  /
15,15                                                                        

PL/SQL procedure successfully completed.




The following might be one of the reasons to justify this behavior that I can think of, but is this a good enough reasoning?

If the sid within "pragma autonomous_transaction" were different, oracle wouldn't have been able to detect this deadlock just by looking at sessions ID(s) cyclic blocking session dependencies. Oracle would have to drill down to connections to detect the deadlock. E.g. If blocked session and blocking session share the same connection, detect a deadlock. This well could have been done. But why do this extra check? What's the problem in having the same sid for both the sessions except for it looks a little odd? Hence, same sid for both the sessions.

SQL> create table test_table(aa int);

Table created.

SQL> insert into test_table select rownum from dual connect by level<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL> update test_table set aa=11 where aa=1;

1 row updated.

SQL> declare
  2     pragma autonomous_transaction;
  3  begin
  4     update test_table set aa=111 where aa=1;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4


Many Thanks!!

Regards,
Prajjwal

Tom Kyte
August 17, 2012 - 12:45 pm UTC

you only have one session, you have two transactions.

do not get session and transaction confused here. there is ONE session, MANY transactions possible.

Same sid for two sessions within a connection

Prajjwal Mallik, August 23, 2012 - 5:13 am UTC

Thank you very much Tom!! That clears my confusion.

A reader, October 25, 2012 - 9:21 am UTC

Tom:

For a new system sizing/load purposes, I was asked to check the number of concurrent users the database has. Right now we have one database account that users/applications use.

When I check V$SESSION I only see 5 "ACTIVE" sessions and 45 "INACTIVE" sessions using the same database account.

Does that mean I have a total of 5 concurrent users or 50 concurrent users?
Do you normally include inactive sessions as a user?


Also, a session could be created by a client/server program or a web connection using mod/Plsql.

Does it make any difference when computing the total number of concurrent users?



Tom Kyte
October 25, 2012 - 9:50 am UTC

you have 50 concurrent users from a license perspective.

you have 5 users actually doing something at the point in time you looked.



session

A reader, October 25, 2012 - 11:12 am UTC

Tom:

Not worried about license - it is processor based.

But according to what you say, then if 1000 users log into a website in 1 DAY (24 hours) and it happens that each user requested a webpage in a differnt timeframe from another user then you really have 1 USER concurrent access.

What is the best way to approximately estimate the number of concurrent users for a database or web application to get some idea about hardware load/performance requirements?
Tom Kyte
October 25, 2012 - 11:37 am UTC

I think you mean "active" users don't you?

connect = count(*) from v$session
active = count(*) from v$session where status = active



You can use AWR (or statspack if you don't have AWR) to derive that information for some period of time.

sessions

sam, October 25, 2012 - 11:49 am UTC

Tom:

I assume the "active" number of users id what is important for system sizing (concurrent users). is it correct or do you look at the total users (inactive and active).

What is strange is i get a count of 10 for active users

but when i koinn V$SESSION and V$PROCESS i only see 2 active session listed.
Tom Kyte
October 28, 2012 - 10:16 pm UTC

no, concurrently connected users take resources too.

You shouldn't have that many more connected users than active users optimally - but most people (for whatever reason, they like buying extra hardware and having unstable systems I guess) have many orders of magnitude more connections than typical active sessions (and if all of the sessions they had connected did try to become active - their machine would meltdown - it probably already has on occassion(s) )

(you and I have had this talk ad-nauseum...)


example or it didn't happen. show your work. who knows how you joined those tables together or what the query looked like.

and remember - these are point in time queries, I would be really surprised if you ran these two queries one after the other and got the same answer. the state of the sessions is constantly changing.

and don't forget - a process may have one or more sessions and a session may belong to zero or one process (and may move from process to process as well)

sessions

sam, October 25, 2012 - 12:55 pm UTC

My query was checking where TYPE='USER'

I see that oracle has all these active running sessions too

PMON
DBW0
LGWR
CKPT
SMOR
REC0
...

I do nto know if these should be included as part of the number of concurrent users count for a databse or not?
Tom Kyte
October 28, 2012 - 10:26 pm UTC

well sam, tell me, do you want to?


I mean, I don't know what you are planning on doing with these numbers. these are certainly processes, they certainly do work.

sessions

A reader, October 29, 2012 - 12:00 pm UTC

Tom:

I am trying to deterine if a new dell server have enough resources to run my 11g oracle database.

Basically, I am migrating a 9iR2 database and oracle web applications running on a large IBM AIX server to an an oracle 11g database on a dell poweredge server running a RHEL 5.7 virtual server. It will have 48 GB RAM and 2 dual core CPUs.

I am allocating 20 GB on VM for the oracle versus 4GB now on the AIX.

I think RAM size, CPU power and BANDWIDTH are the three main factors to determine whether the new machine will be able to support a database with 50 or 100 concurrent connections or not.

Do you agree or not? If not, what factors would you be looking at.
Tom Kyte
October 29, 2012 - 9:01 pm UTC

4 cores is good for 0-8 concurrently active sessions (maybe up to 40 instead of 8 is you do lots of physical IO to spinning magnetic disk).

it depends on how many of your concurrent sessions (which eat RAM and some bit of CPU) are attempting to be concurrently active.

sessions

A reader, October 30, 2012 - 2:14 pm UTC

Tom:

4 cores to support 8 active session seems to be very low number compared to what hardware manufacturers recommend and to what is actually deployed in the field.. You say depending on the activity it may support up to 40 active users.

According to Hardware manufacturers as HP and Dell you can support many more users (1 - 150) with a 2 dual core processor server

http://www.hp.com/cgi-bin/sbso/buyguides/tsg_product_select.cgi


The AIX server we use currently use is powerful (16 processors and 64 GB RAM) but also have 100 instances running on it (DEV, TEST, PROD).

The Dell server will only have couple of databases.
Tom Kyte
October 31, 2012 - 4:00 pm UTC

sam,

please - I've had this conversation with you I don't know how many times.



supporting users is a lot different than supporting concurrently ACTIVE sessions. On asktom.oracle.com - we have 4 cores (it is really apex.oracle.com) we support hundreds of thousands of users

but we only have a small number of concurrently active sessions.

but we've had this connection conversation over and over and over.

watch this video (again)
http://www.youtube.com/watch?v=xNDnVOCdvQ0

sessions

A reader, November 04, 2012 - 4:42 pm UTC

Tom:

I was going to ask about asktom and you beat me to it.

It is good to know you have 4 cores supporting asktom with thousands of users, but how do you nromally calculate this or determine this concurrent active sessions number?

<<<but we only have a small number of concurrently active sessions.>>


I was thinking of running a job every 5 minutes daily for a week to count active sessions in V$session and saving to a log.
Then i can anlayze the log. I am not sure if this a reliable way though because there might be large number of sessions active within that 5 minute window. It might be more accurate to run it every minute.
Tom Kyte
November 05, 2012 - 9:42 am UTC

you need to know about how many people will be hitting the enter key at the same time and about how much cpu each will need.


A reader, November 05, 2012 - 9:51 am UTC

Tom:

<<you need to know about how many people will be hitting the enter key at the same time and about how much cpu each will need. >>

OK, great but what methods did you use to determine that answer for the asktom site? I am curious to know the real number for you site too.

I know it has to be an estimate because you may have one month where no one hits the "ENTER" key at once and another month where suddenly "10" people hit it together. I guess the frequency of number of active session is important too.


Tom Kyte
November 05, 2012 - 9:54 am UTC

we guessed - because the site is APEX.oracle.com - asktom is just one of thousands of applications.

and adjusted over time based on observation.

you want your OLTP system to be running at 66% or less CPU over time - if you start going over that frequently - time for more resources.

session

A reader, November 05, 2012 - 6:42 pm UTC

Tom:

Let me make sure I am reading this correct.

Asktom is running on apex.oracle.com machine which has 2 intel based dual-core cpus (total 4 cores).

The machine is also supporting thousands of other applications and hundreds of thousands of users.

If that is true i am very impressed!

I never had perfromance issue with your site.

that also mean i can do a whole lot with 4 cores.

of course, there are difference between cores too. Some are fast and some might be slow.
Tom Kyte
November 05, 2012 - 7:02 pm UTC

ok, i went back to check to make sure, I was wrong on the number of cores, we have 8:

Architectural Overview – apex.oracle.com

o Home to asktom.oracle.com (aliased)
o HP DL380
o 2 CPUs (quad core) (3.00 GHz)
o 32 GB RAM
o Running Oracle Database Enterprise Edition 11.2.0.3
o Oracle Linux 5 64-bit
o APEX Listener 2.0


o In the last week
   o Total Page Events:          5,111,974
   o Workspaces Approved:              923
   o Workspaces Purged:                425 
o Distinct Users:                    6,541 
o Total Number of Workspaces:       14,155
o Total Number of Applications:     53,044     



o apex.oracle.com runs (among other things):
   o SQL Developer Check For Updates (about 1.1M page views)
   o Oracle Learning Library (about 700K page views)
   o AskTom (about 600K page views)
   o APEX Application Builder (about 400K page views)
   o APEX SQL Workshop (about 330K page views)




we can do a lot with this machine because...


a) efficient code, efficient database code

b) draconian use of the resource manager and profiles to basically say "be efficient or be killed". if someone does something that takes a ton of resources - their sessions are killed, no questions asked. No runaways, ever.

sessions

A reader, November 06, 2012 - 12:06 pm UTC

Tom:

Great info on the machine.

Amazing what 8 cores can really do. I never realized that.
This is a public site and anyone can create a workspace and write crappy code. How do you know it is efficient unless you mean APEX engine generates very efficient code.

But I am curious as to how you kill those sessions or processes that consume large resources. Is this something you set in oracle database or the Oracle Linux OS?

I think i want to do something similar so that when a user does

select * from 1_MILLIOn_RECORD_TABLE

he does not kill all other users with him.

Currently I use application accounts and one database account but we should still be able to kill the session cosuming large CPU and RAM.
Tom Kyte
November 06, 2012 - 12:13 pm UTC

it is all about resource management. You may write crappy code, we won't permit it to run.

resource manager
profiles

all in effect, always has been, always will be.



session

A reader, November 06, 2012 - 6:03 pm UTC

Tom:

profiles is a DB feature but would that work if users are using ONE db account?

resource manager? is that O/S or 11g database feature.
Tom Kyte
November 07, 2012 - 7:39 am UTC

Sam,

why wouldn't it work with "ONE" db account. We set up a rule stating "though shall use no more than this much CPU per call" for example. It is the golden rule - applicable for all (except administrators)

the resource manager has been a feature of the database singe 9i, ask your DBA about it - they would know (if they don't, they should)

http://docs.oracle.com/cd/E11882_01/server.112/e17120/dbrm.htm

Comments on apps using a single, shared db user

José Laurindo Chiappa, November 07, 2012 - 11:08 am UTC

Sam, I suppose you are thinking about an intranet-like application where (differently from us here in asktom), the end-users are know.... If each one needs different resource shares - Joe from Sales must have x% CPU max, Mary from Marketing must have y minutes of connection time, and so on - , in my humble opinion the right way is that each end user would have (must have) your own database user, so is trivial assign profiles and/or the correct resource manager group for them...
If your app is using a single database-user for all end-users for this kind of environment where the end-users are know, to me it´s a failure of implementation and a bad practice , but even then Resource Manager can be used - if some session attribute (such as os_name, say) can be used to "map" the end-user with the desired RM group you could use consumer group mapping rules to automatic switching, OR if no session attribute could be used the RM group could be changed/assigned at a login trigger, OR the created session can be assigned to a group via DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ....
I repeat, to me it´s a mistake to use a single database user in cases where different end-users limits/permissions exists and the end-users are know, but many (ugly) web applications do so, and thus reluctantly we would do this kind of additional "work" ...

how do i see amount of physical connection to the db ?

A reader, January 02, 2013 - 1:46 am UTC

hi tom,

i need to check the amount of "physical" connections to the database

i seen people used the script below

"
select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
--       b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid; "


I understand that 1 connection might service more then 1 session, so i am not sure if checking the v$session would be accurate.

q1) is there anyway to check the amount of connection to the database more accurately for both dedicated/share setup ?

q2) why would be the reason for the sql above to check the v$process table as well ?

can a session not be served by any process at a moment ?

Regards,
Noob

Tom Kyte
January 04, 2013 - 1:48 pm UTC

A connection can have zero one or more sessions associated with it.

A process can be associated with zero, one or more sessions.

A session can use a multitude of processes over time.


In general, counting v$session is sufficient - in *most cases* a session = a connection.

Unless you are doing something that causes multiple sessions per connection

counting non-background processes in a dedicated server only environment would tell you as well.

RE : Process and Sessions

Reader, USA, February 17, 2013 - 11:14 am UTC

Tom,
Thanks much for your great explanations. I am still confused. For dedicated server connfiguration, some text books say "Whenever a new session is created, a server process will be started to service the session".

Below are my questions I need your help to clarify :

#1. The example that you gave at the start of this discussion, you show a scenario of 1 connection -> 2 sessions (one yours and one for autotrace) -> 1 process, is it because you are giving a scenario assuming it is using connection multiplexing?

#2. If there is no connection multiplexing in the example you showed, will a server process be created for each new sessions going thru a single connection or will it always be the case that a single dedicated server process will be created per connection irrespective of the number of sessions going thru that single connection?

This is the URL that I came thru that has the following text :

http://www.datadisk.co.uk/html_docs/oracle/connectivity.htm
..........................
Dedicated Connection
Oracle will create a new process when you log on, this is commonly know as the dedicated server process,
the process will exists as long as the connection exists. For every session a new dedicated server process
will be created. This process will receive SQL and execute it, read data files and look in the database cache
for data. It will also perform update statements and run any PL/SQL code. The flow of this process is seen
below:
..........................

Appreciate if you could clarify the relationship concept between connection, session and process based on above statement
Tom Kyte
February 19, 2013 - 9:38 am UTC

"Whenever a new session is created,
a server process will be started to service the session"


that is "generally" true but it is not a rule, a dedicated server CONNECTION may have zero, one or MORE sessions established in it. The trick is to realize that connection and session are not one to one. They are many to one.


1) that is NOT connection multiplexing ( connection concentration). That is my client having a single connection (a physical circuit to the database) but many sessions.

connect multiplexing would take many connections and turn them into a SINGLE connection and share that connection. You do that with something like connection manager - CMAN. with CMAN you can have 1000 people connect to CMAN and have CMAN connect to the database say 10 times - and CMAN will submit the requests it gets from the 1000 connections over the 10 connections it has. It does what a connection pool is designed to do. It takes a large community if connections and turns them into a small number.

what I demonstrated is NOT that.

2) keep connection and session separate in your mind and this will be easier.

You get connected to the database - that connection can be

a) dedicated server, you get a NEW process for you for your connection, it is yours. there is no session yet.

b) shared server, you get connected to a dispatcher, dispatcher has many people connected to it, you do not create anything new on the server. You have no session yet

c) some other method like DRCP database resident connection pool - we'll ignore this for now as (a) and (b) are by far the most common.



Now, once you are connected - you can start establishing sessions. Sessions are just a "state object" (a data structure) in the data. Your dedicated server could start one or more sessions - they are NOT connections, you already have that, they are just state objects in the database that represent a separate session with its own transactions.



that link is not technically accurate, it covers the most *common* case, but it is not in general correct.


use this instead:

http://docs.oracle.com/cd/E11882_01/server.112/e25789/process.htm#i18532


RE : Connection and Session

Reader, February 19, 2013 - 11:41 am UTC

Tom,
Thanks for the clear explanation. From what you explained (for Dedicated Server connection), here is what I understand.

#1. Most of the time there will be a 1->1 mapping between sessions and connections. When a connection is made, a server process will be started that will interact with the session establised thru this connection

#2. In cases where there is a one-to-many mapping between a connection and a session (1 connection -> many sessions), a single server process will be created for this connection and multiple sessions that go thru this single connection will use the same server process

As always thanks much for your explanation
Tom Kyte
February 25, 2013 - 8:22 am UTC

1) that is the "typical use case", yes.


2) yes

mfz, December 09, 2013 - 4:02 pm UTC

I am getting the following messages in the listener.log .
What is the cause of this and how to fix this ?

I looked at the metalink ( 1453883.1 , 1340831.1 , 1457854.1) . Could not get to find the resolution .
Please advise.


on Dec 09 10:57:39 2013
09-DEC-2013 10:57:39 * service_register_NSGR * 1194
TNS-01194: The listener command did not arrive in a secure transport
Mon Dec 09 10:58:42 2013
09-DEC-2013 10:58:42 * service_register_NSGR * 1194
TNS-01194: The listener command did not arrive in a secure transport

Difference between process session etc

Sukhendu Bhowmick, January 16, 2014 - 11:31 am UTC

Im relaay impressed & enthralled the consise & crisp way of discussing the process session etc with real example clearing the imparetive basic concept. Thanks a lot Sir for sharing the knowledge.

Obtaing SID from AWR for a OSPID.

rajorshi, August 05, 2014 - 8:42 am UTC

Hi Tom
I have situation where a process aborted the instance due to ora-600 error. The alert log gives me the os processid (linux) that aborted the instance but it does not give me the SID. I want the sid so that I can check the AWR tables to ascertain what was going wrong. Is there a view in the AWR that help me map os pid to sid that got generated sometime in the past?

ARFAOUI Marouene, April 07, 2016 - 1:46 pm UTC

So how can we describe this example on an oracle node :

1 - we have over 3100 TCP ESTABLISHED connections over the port 20450 of oracle (Linux : Node 2 OS Side )

2 - 1200 sessions when "select count(*) from v$session ; ( Oracle DB Side)

this two number does not fit no ? is there a problem please ? a big difference non ?

Thank You !
Chris Saxon
April 08, 2016 - 3:25 am UTC

Sorry - what is the significance of port 20450 in this case ?

Vinay, July 25, 2020 - 6:02 am UTC

how to set the open_cursors process, session and transaction parameter in oracle 12c for new database.
And database is available for more than 2000 users.
Connor McDonald
July 27, 2020 - 2:22 am UTC

Setting "processes" to a good value will automatically take care of sessions and transactions in 99% of cases.

"2000 users" doesn't mean a lot because it could mean

2000 *actual* database sessions, or
2000 users sharing a connection pool, or
it could be 2000 requests per day, or
it could be 2000 requests per second

etc

I typically set processes to the lower of

- what my server could handle
- what my highest user load I expect plus (say) 20%

the intent being

- don't let my server get swamped
- if things go more than 20% above the maximum I *ever* expected, then something is wrong, and I want to know about it


open cursors I normally start at the default unless the software I'm installing explictly has a setting, and then monitor system stats from there

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.