Clear, concise.
September 28, 2002 - 11am Central time zone
Reviewer: JW from Colorado front range
Tom, you rock! You are clear, concise, and complete.
Reader
September 28, 2002 - 12pm Central time zone
Reviewer: A reader
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)))
Followup September 28, 2002 - 12pm Central time zone:
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
September 28, 2002 - 3pm Central time zone
Reviewer: A reader
Very Nice explanation! Can you explain why the username is different in the V$PROCESS
September 28, 2002 - 4pm Central time zone
Reviewer: Prince
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
Followup September 28, 2002 - 7pm Central time zone:
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
September 29, 2002 - 10am Central time zone
Reviewer: A reader
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?
Followup September 29, 2002 - 11am Central time zone:
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
September 29, 2002 - 2pm Central time zone
Reviewer: A reader
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!
Followup September 29, 2002 - 3pm Central time zone:
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
http://download-west.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/concepts.htm#1028475
which does not conflict with:
http://download-west.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/concepts.htm#1009622
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.
September 29, 2002 - 11pm Central time zone
Reviewer: Bhagat Singh from Delhi India
This gave the clear cut understanding of the three. That was commendable.
user process and server process
January 30, 2003 - 10pm Central time zone
Reviewer: learningOracle from Houston
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?
What is pseudo program
April 23, 2003 - 2am Central time zone
Reviewer: A reader
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
Followup April 23, 2003 - 7am Central time zone:
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...
April 23, 2003 - 7am Central time zone
Reviewer: A reader
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 ??
Followup April 23, 2003 - 7am Central time zone:
consider it the "init process" of Oracle -- just like there is an init in Unix.
it just is.
i i Sir
April 23, 2003 - 8am Central time zone
Reviewer: A reader
:)
Server Process is an alias of "Oracle" like the background ones ?
May 26, 2003 - 8pm Central time zone
Reviewer: Robert
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
Followup May 27, 2003 - 7am Central time zone:
yes, they are all the same binary.
Follow up question.
May 27, 2003 - 2pm Central time zone
Reviewer: Saibabu Devabhaktuni from Palo Alto, CA USA
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
Followup May 27, 2003 - 5pm Central time zone:
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?
June 9, 2003 - 6pm Central time zone
Reviewer: Subrata from Columbus, Ohio
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
Followup June 9, 2003 - 7pm Central time zone:
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?
June 10, 2003 - 6pm Central time zone
Reviewer: SS from Columbus
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
Followup June 10, 2003 - 8pm Central time zone:
yes you can -- we call it "shared server" or MTS for example
Are connections and process synonymous?
June 10, 2003 - 6pm Central time zone
Reviewer: SS from Columbus
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
June 17, 2003 - 11pm Central time zone
Reviewer: Alvin from Philippines
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 ?
Followup June 18, 2003 - 6pm Central time zone:
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
June 19, 2003 - 12am Central time zone
Reviewer: Alvin from Philippines
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 ?
Followup June 19, 2003 - 7am Central time zone:
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"
June 19, 2003 - 11am Central time zone
Reviewer: kate from PA
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?
Followup June 19, 2003 - 1pm Central time zone:
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
August 3, 2003 - 11am Central time zone
Reviewer: Reader
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.
Followup August 3, 2003 - 12pm Central time zone:
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
August 3, 2003 - 12pm Central time zone
Reviewer: Reader
<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!
August 3, 2003 - 12pm Central time zone
Reviewer: Reader
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.
Followup August 3, 2003 - 2pm Central time zone:
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.
August 4, 2003 - 3am Central time zone
Reviewer: Anthony Reddy from KL, Malaysia
post/wait initialization error
August 4, 2003 - 3am Central time zone
Reviewer: Anthony Reddy from KL, Malaysia
Is this error related to number of processes.
i got this error, when i am creating a database.
Please help.
Followup August 4, 2003 - 9am Central time zone:
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
August 5, 2003 - 4am Central time zone
Reviewer: Anthony Reddy
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.
August 8, 2003 - 3pm Central time zone
Reviewer: A reader
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
Followup August 10, 2003 - 12pm Central time zone:
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.
August 10, 2003 - 1pm Central time zone
Reviewer: A reader
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.
Followup August 10, 2003 - 2pm Central time zone:
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
August 13, 2003 - 7pm Central time zone
Reviewer: Ashok from USA
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
October 2, 2003 - 3pm Central time zone
Reviewer: A reader
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.
Followup October 2, 2003 - 6pm Central time zone:
they are windows clients -- select the program out as well to see what program they are.
October 3, 2003 - 9am Central time zone
Reviewer: A reader
ok, they are windows clients -- but shouldn't there be a corresponding server process on Solaris ?
Shouldn't its pid be there ?
Followup October 3, 2003 - 10am Central time zone:
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.
October 23, 2003 - 2am Central time zone
Reviewer: Alvin from Philippines
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.
Followup October 23, 2003 - 12pm Central time zone:
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
October 28, 2003 - 7am Central time zone
Reviewer: Venkat from India
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
Followup October 28, 2003 - 8am Central time zone:
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
December 29, 2003 - 1pm Central time zone
Reviewer: A reader
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.
Followup December 29, 2003 - 2pm Central time zone:
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
December 29, 2003 - 6pm Central time zone
Reviewer: A reader
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.
Followup December 29, 2003 - 6pm Central time zone:
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
December 30, 2003 - 5pm Central time zone
Reviewer: A reader
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.
Followup December 30, 2003 - 5pm Central time zone:
don't know why they have different names there -- sorry.
AIX processes of Oracle
January 12, 2004 - 7am Central time zone
Reviewer: Himnish Narang from Gurgaon, India
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
Followup January 12, 2004 - 1pm Central time zone:
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.
May 13, 2004 - 12am Central time zone
Reviewer: A reader
Tom,
http://www.dbasupport.com/oracle/ora9i/background_process01.shtml
"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.
Followup May 13, 2004 - 10am Central time zone:
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)
May 15, 2004 - 11am Central time zone
Reviewer: A reader
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.
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 Linux advantage: www.linux.org
*****************************
Thanks.
Rahul
Followup May 15, 2004 - 12pm Central time zone:
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.
May 15, 2004 - 5pm Central time zone
Reviewer: A reader
May 17, 2004 - 6am Central time zone
Reviewer: A reader
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
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 Optimising Oracle Seminar - schedule updated May 1st
*********************
Followup May 17, 2004 - 8am Central time zone:
thanks for the followup, appreciate it.
Checking sessions for connections
May 17, 2004 - 11am Central time zone
Reviewer: Sven Bleckwedel from Santos/SP - Brasil
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
Followup May 17, 2004 - 3pm Central time zone:
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.
May 17, 2004 - 11am Central time zone
Reviewer: A reader
Tom,
How can we find out the number of dirty buffers that are
waiting to be written?
Thanks.
Followup May 17, 2004 - 3pm Central time zone:
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
May 17, 2004 - 3pm Central time zone
Reviewer: A reader
Difference between pid and spid in v$process
May 28, 2004 - 5pm Central time zone
Reviewer: Moorthy Rekapalli from Atlanta GA USA
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.
Followup May 28, 2004 - 8pm Central time zone:
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
July 30, 2004 - 10am Central time zone
Reviewer: Sagi from Oracle World
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
Followup July 30, 2004 - 5pm Central time zone:
it was probably a sqlplus'ism -- i tested (and confirmed), svrmgrl with "connect internal" worked
correctly in this case in 8i
Admirer
August 1, 2004 - 12am Central time zone
Reviewer: A reader
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.
Followup August 1, 2004 - 10am Central time zone:
it was a "product issue". svrmgrl worked, sqlplus did not.
Number of processes in mts
August 4, 2004 - 4am Central time zone
Reviewer: Jitendra Agrawal from New Delhi, India
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
Followup August 4, 2004 - 10am Central time zone:
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
August 5, 2004 - 4am Central time zone
Reviewer: Jitendra Agrawal from New Delhi, India
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)
Followup August 5, 2004 - 12pm Central time zone:
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
August 14, 2004 - 5pm Central time zone
Reviewer: A reader
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!
Followup August 14, 2004 - 7pm Central time zone:
1) sure, yes.
2) connection pooling, ala middle tier speak, is in fact session pooling.
one more question
August 14, 2004 - 6pm Central time zone
Reviewer: A reader
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?
Followup August 14, 2004 - 7pm Central time zone:
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!
August 15, 2004 - 4pm Central time zone
Reviewer: A reader
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?
Followup August 16, 2004 - 8am Central time zone:
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!!!
August 16, 2004 - 9am Central time zone
Reviewer: A reader
Opening multiple sessions
August 16, 2004 - 3pm Central time zone
Reviewer: Peter from Louisville, KY USA
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
Followup August 16, 2004 - 8pm Central time zone:
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
August 24, 2004 - 11am Central time zone
Reviewer: A reader
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!
Followup August 24, 2004 - 11am Central time zone:
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!
August 24, 2004 - 11am Central time zone
Reviewer: A reader
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!
Followup August 24, 2004 - 11am Central time zone:
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!!
August 24, 2004 - 11am Central time zone
Reviewer: A reader
another meaning of pseudo
August 26, 2004 - 9am Central time zone
Reviewer: A reader
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
September 2, 2004 - 12pm Central time zone
Reviewer: A reader
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!
Followup September 2, 2004 - 1pm Central time zone:
yes.
10g r1 v$session process column
September 19, 2004 - 4pm Central time zone
Reviewer: A reader
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!
Followup September 19, 2004 - 5pm Central time zone:
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
process is the client id, sqlplus for example
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
shows you the dedicated/shared server process id you are/were using and your client process id
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!
September 19, 2004 - 5pm Central time zone
Reviewer: A reader
What is actual communication channel?
September 23, 2004 - 1pm Central time zone
Reviewer: Mandell from Victoria, BC Canada
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.
Followup September 24, 2004 - 9am Central time zone:
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?
October 22, 2004 - 6pm Central time zone
Reviewer: Kiran from CA
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
Followup October 23, 2004 - 9am Central time zone:
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
October 27, 2004 - 3pm Central time zone
Reviewer: Sean from NJ, USA
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
inactive session
October 27, 2004 - 6pm Central time zone
Reviewer: Sean from NJ, USA
(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
Followup October 27, 2004 - 6pm Central time zone:
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
October 27, 2004 - 8pm Central time zone
Reviewer: Sean from NJ, USA
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
Followup October 27, 2004 - 10pm Central time zone:
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
November 1, 2004 - 5pm Central time zone
Reviewer: Sean from NJ, USA
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.
Followup November 2, 2004 - 6am Central time zone:
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
November 9, 2004 - 8pm Central time zone
Reviewer: reader from OC, CA
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)
Followup November 9, 2004 - 8pm Central time zone:
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
December 23, 2004 - 5pm Central time zone
Reviewer: A Reader from Herndon, VA USA
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.
Followup December 23, 2004 - 7pm Central time zone:
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
December 24, 2004 - 9am Central time zone
Reviewer: A reader from Herndon, VA USA
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.
Followup December 24, 2004 - 10am Central time zone:
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.
January 31, 2005 - 3pm Central time zone
Reviewer: A reader
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.
Followup January 31, 2005 - 4pm Central time zone:
what does your listener.ora look like? I've only got one on my machines.
January 31, 2005 - 7pm Central time zone
Reviewer: A reader
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))
)
Followup January 31, 2005 - 8pm Central time zone:
if you shut it down and start it up and it keeps happening, contact support -- I've not seen that.
February 1, 2005 - 9am Central time zone
Reviewer: A reader
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
February 9, 2005 - 9am Central time zone
Reviewer: Manish Upadhyay from Madison,WI,USA
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.
Followup February 9, 2005 - 2pm Central time zone:
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..
February 11, 2005 - 10am Central time zone
Reviewer: max
is there any view available to determine whether shared server connections would be accepted or
not?
Followup February 12, 2005 - 7am Central time zone:
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
February 21, 2005 - 2am Central time zone
Reviewer: Premchand from India
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
Followup February 21, 2005 - 10am Central time zone:
"U" doesn't work here? I don't know who "U" is.
have you issued a kill on those sessions?
oracle pseudo processes
February 22, 2005 - 1am Central time zone
Reviewer: Premchand from India
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
Followup February 22, 2005 - 8am Central time zone:
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
April 10, 2005 - 3am Central time zone
Reviewer: Prem
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
Followup April 10, 2005 - 10am Central time zone:
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
April 27, 2005 - 2pm Central time zone
Reviewer: Prem
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
Followup April 27, 2005 - 3pm Central time zone:
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
April 27, 2005 - 4pm Central time zone
Reviewer: Vinayak from NJ, USA
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).
Followup April 27, 2005 - 4pm Central time zone:
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
April 27, 2005 - 6pm Central time zone
Reviewer: Anjo Kolk from Garderen, Netherlands
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
April 28, 2005 - 1am Central time zone
Reviewer: Prem
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
Followup April 28, 2005 - 7am Central time zone:
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
April 28, 2005 - 6am Central time zone
Reviewer: Todor Botev from Germany
Perm,
You can follow the RAM and CPU usage with the Task Manager.
TNS-12500: TNS:listener failed to start a dedicated server process
April 29, 2005 - 4pm Central time zone
Reviewer: Prem
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
Followup April 29, 2005 - 6pm Central time zone:
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
May 8, 2005 - 1pm Central time zone
Reviewer: Kara
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.
Followup May 8, 2005 - 2pm Central time zone:
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
May 8, 2005 - 3pm Central time zone
Reviewer: Kara
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.
Followup May 8, 2005 - 4pm Central time zone:
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
June 16, 2005 - 3am Central time zone
Reviewer: M.Fritz from Germany
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
Followup June 16, 2005 - 3am Central time zone:
why in the world are you shutting down to simply back up???????????
Shutdown
June 16, 2005 - 4am Central time zone
Reviewer: M.Fritz from Germany
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.
Followup June 16, 2005 - 9am Central time zone:
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
June 24, 2005 - 12pm Central time zone
Reviewer: Jim
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,
July 29, 2005 - 2pm Central time zone
Reviewer: sns from austin,tx
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,
Followup July 29, 2005 - 3pm Central time zone:
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,
July 29, 2005 - 3pm Central time zone
Reviewer: sns from austin,tx
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,
Followup July 29, 2005 - 5pm Central time zone:
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
August 3, 2005 - 8am Central time zone
Reviewer: Matthias Rogel from Kaiserslautern, Germany
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?
Followup August 3, 2005 - 11am Central time zone:
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,
August 5, 2005 - 1pm Central time zone
Reviewer: sns from austin,tx
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,
Followup August 5, 2005 - 2pm Central time zone:
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,
August 5, 2005 - 2pm Central time zone
Reviewer: sns from austin,tx
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?
Followup August 5, 2005 - 5pm Central time zone:
the consequence is:
you cannot create process 801
order of invoking processes
September 14, 2005 - 10am Central time zone
Reviewer: Alay from India
Hi Tom,
I want to know that when we startup the database in which order the background processes
invoke?
Followup September 14, 2005 - 10am Central time zone:
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
October 5, 2005 - 1am Central time zone
Reviewer: frank from asean/// malaysia
---------above----plz reply...urgent
Followup October 5, 2005 - 7am Central time zone:
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
December 21, 2005 - 11am Central time zone
Reviewer: Steve Kiteley from UK
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?
Followup December 21, 2005 - 7pm Central time zone:
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?
January 11, 2006 - 11am Central time zone
Reviewer: Shailesh from India
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
Followup January 12, 2006 - 10am Central time zone:
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?
January 18, 2006 - 6am Central time zone
Reviewer: Wang qiang from China
I can't still solve the problem,I don't understand what you said that search metalink
Followup January 18, 2006 - 8am Central time zone:
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
January 18, 2006 - 10am Central time zone
Reviewer: djb from Rome, GA
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
January 18, 2006 - 2pm Central time zone
Reviewer: Tim from PA, USA
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:
http://msdn.microsoft.com/library/en-us/memory/base/large_memory_support.asp
A comment on Tim's comment...
January 18, 2006 - 5pm Central time zone
Reviewer: djb from Rome, GA
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.
January 25, 2006 - 2pm Central time zone
Reviewer: A reader
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.
January 26, 2006 - 2pm Central time zone
Reviewer: A reader
Doh... You have mentioned that many times before.
Thanks.
ora-12500
March 13, 2006 - 6pm Central time zone
Reviewer: Vivek from UK
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
March 13, 2006 - 6pm Central time zone
Reviewer: A reader
In continuation of above thread...
oracle version 9.2.0.3 on AIX
oracle process
March 15, 2006 - 6pm Central time zone
Reviewer: A reader
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
Followup March 16, 2006 - 7am Central time zone:
please utilize support.
Inconsistent data returned by v$session
March 22, 2006 - 1pm Central time zone
Reviewer: JP
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.
Followup March 22, 2006 - 5pm Central time zone:
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
March 22, 2006 - 1pm Central time zone
Reviewer: JP
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
March 27, 2006 - 10am Central time zone
Reviewer: Zahir M from Monroe NJ
Tom ,
What is the difference between Connection Pooling and Session Multiplexing ?
Thanks
Followup March 27, 2006 - 2pm Central time zone:
<quote http://download-east.oracle.com/docs/cd/B19306_01/network.102/b14212/dispatcher.htm#sthref1172 >
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??
April 1, 2006 - 2pm Central time zone
Reviewer: Andrew from Michigan, USA
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
Followup April 1, 2006 - 3pm Central time zone:
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.
August 1, 2006 - 4pm Central time zone
Reviewer: Sean from NJ. USA
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
Followup August 2, 2006 - 10am Central time zone:
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
August 2, 2006 - 11am Central time zone
Reviewer: Michel Cadot from France
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
August 8, 2006 - 8am Central time zone
Reviewer: Arindam Mukherjee from Kolkata, India
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.
Followup August 8, 2006 - 8am Central time zone:
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
August 18, 2006 - 2am Central time zone
Reviewer: A reader
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?
Followup August 18, 2006 - 8am Central time zone:
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
August 20, 2006 - 9am Central time zone
Reviewer: A reader
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 !
Followup August 20, 2006 - 9am Central time zone:
query v$sql_plan and please verify that no one is 'accidently' invoking parallel operations.
max number of processes
September 6, 2006 - 5am Central time zone
Reviewer: A reader
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.
Followup September 6, 2006 - 7am Central time zone:
in your operating system documentation no doubt?
it is setup and limited by the operating system, not us so much.
connections
January 2, 2007 - 2pm Central time zone
Reviewer: Sam
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
January 17, 2007 - 6pm Central time zone
Reviewer: Sam
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
March 17, 2007 - 7am Central time zone
Reviewer: Ajeet
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
Followup March 17, 2007 - 5pm Central time zone:
no
but tell us, what are you trying to do? a load test? dbms_job might be something to look at.
load test -yes
March 20, 2007 - 12am Central time zone
Reviewer: Ajeet
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.
Followup March 20, 2007 - 7am Central time zone:
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
April 4, 2007 - 7am Central time zone
Reviewer: Fernando Sanchez from Madrid, Spain
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.
Followup April 4, 2007 - 10am Central time zone:
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
April 4, 2007 - 11am Central time zone
Reviewer: Fernando Sanchez from Madrid, Spain
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
Followup April 4, 2007 - 11am Central time zone:
why the use of old jdbc drivers?
jdbc Connection close does not release session resources
April 4, 2007 - 11am Central time zone
Reviewer: Fernando Sanchez from Madrid, Spain
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.
Followup April 4, 2007 - 12pm Central time zone:
i would test a more current jdbc release - 9.0 is really quite old.
problem with jdbc connection close
April 9, 2007 - 7am Central time zone
Reviewer: Fernando Sanchez from Madrid, Spain
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.
Followup April 9, 2007 - 10am Central time zone:
you are not upgrading the server jdbc, you are doing the client - the thing that accesses the database.
CPU consumption 100% and machine hangs
May 5, 2007 - 10am Central time zone
Reviewer: Piyush Agarwal from Pune, India
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
Followup May 8, 2007 - 10am Central time zone:
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
May 19, 2007 - 12am Central time zone
Reviewer: Edward from Nashville, TN USA
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.
Followup May 21, 2007 - 10am Central time zone:
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
May 21, 2007 - 1pm Central time zone
Reviewer: Edward Sears from Nashville, TN
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
July 18, 2007 - 10am Central time zone
Reviewer: shailu from NY USA
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
Followup July 18, 2007 - 10am Central time zone:
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
July 18, 2007 - 12pm Central time zone
Reviewer: shailu from NY USA
Hello Tom,
Thanks for you response. If i have no. of processes more then needed. Does it affect the performance of my database?
Followup July 18, 2007 - 12pm Central time zone:
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.
July 19, 2007 - 9am Central time zone
Reviewer: Alexander
Tom,
Is there anything wrong with setting the # of processes really high so you don't have to worry about it?
Followup July 19, 2007 - 11am Central time zone:
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
September 13, 2007 - 5am Central time zone
Reviewer: raman from india
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
Followup September 15, 2007 - 7pm Central time zone:
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
September 17, 2007 - 3am Central time zone
Reviewer: raman from india
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
Followup September 18, 2007 - 2pm Central time zone:
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
September 17, 2007 - 3am Central time zone
Reviewer: raman from India
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
September 19, 2007 - 4am Central time zone
Reviewer: a reader from india
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
Followup September 19, 2007 - 12pm Central time zone:
... 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
December 4, 2007 - 9am Central time zone
Reviewer: Manish from Hyderabad, India
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
June 5, 2008 - 10am Central time zone
Reviewer: Karteek from India
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!
Followup June 5, 2008 - 11am Central time zone:
do not kill oracle processes. stop it.
there are processes not associated with sessions as part of our normal operation, do not kill them.
June 5, 2008 - 11am Central time zone
Reviewer: Karteek from India
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.
Followup June 5, 2008 - 1pm Central time zone:
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://download.oracle.com/docs/cd/B19306_01/network.102/b14212/profile.htm#sthref917
June 6, 2008 - 7am Central time zone
Reviewer: Karteek from India
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!
June 13, 2008 - 1am Central time zone
Reviewer: Karteek from India
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!
Followup June 13, 2008 - 7am Central time zone:
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.
June 13, 2008 - 4pm Central time zone
Reviewer: Steve from USA
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?
Followup June 16, 2008 - 11am Central time zone:
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?
July 16, 2008 - 4pm Central time zone
Reviewer: A reader
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
Followup July 17, 2008 - 11am Central time zone:
shared server connection that is not currently active.
when not active, it is not using any server process.
too many inactive sessions
September 4, 2008 - 11am Central time zone
Reviewer: reader
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!
Followup September 4, 2008 - 1pm Central time zone:
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
September 4, 2008 - 3pm Central time zone
Reviewer: A reader
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!
Followup September 4, 2008 - 4pm Central time zone:
.. 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
September 4, 2008 - 5pm Central time zone
Reviewer: Stew Ashton from Paris, France
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
September 5, 2008 - 2pm Central time zone
Reviewer: a reader
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?
Followup September 5, 2008 - 4pm Central time zone:
based on everything else said here - you have a connection pool going from php, yes.
inactive sessions
September 5, 2008 - 5pm Central time zone
Reviewer: reader
Thank you very much!
OOPS! I should have said "persistent", not "pool"
September 6, 2008 - 6am Central time zone
Reviewer: Stew Ashton from Paris, France
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
September 6, 2008 - 11am Central time zone
Reviewer: reader
Thank you very much Stew for your further explanation. I am clear now! :-D
Max processes parameter
October 3, 2008 - 4am Central time zone
Reviewer: Atul from India
If I set processes parameter to any high value then would it affect any memory being locked etc..
Followup October 3, 2008 - 8am Central time zone:
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 ?
October 11, 2008 - 8am Central time zone
Reviewer: Saju from Livonia, MI USA
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
Followup October 13, 2008 - 2am Central time zone:
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
October 13, 2008 - 9am Central time zone
Reviewer: Saju from Livonia, MI
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.
Followup October 14, 2008 - 4pm Central time zone:
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
October 14, 2008 - 5pm Central time zone
Reviewer: A reader
Single Threading
October 31, 2008 - 11am Central time zone
Reviewer: Saju from Livonia, MI
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.
March 3, 2009 - 10pm Central time zone
Reviewer: A reader
March 11, 2009 - 12pm Central time zone
Reviewer: Raj from US
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
Followup March 12, 2009 - 12pm Central time zone:
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
August 3, 2009 - 9pm Central time zone
Reviewer: Aru from NZ
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?
background processes
August 14, 2009 - 6am Central time zone
Reviewer: Sagar
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?.
Followup August 24, 2009 - 7am Central time zone:
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?
August 20, 2009 - 6am Central time zone
Reviewer: A
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?
Followup August 24, 2009 - 5pm Central time zone:
no, you'd have to snapshot this (statspack/AWR do that) and query the historical snapshot
Checking process size
August 24, 2009 - 8am Central time zone
Reviewer: Sagar
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_
$
Followup August 25, 2009 - 9am Central time zone:
so, analyze these numbers for us, tell us what you think you are seeing.
The command
August 25, 2009 - 6am Central time zone
Reviewer: Sagar
Thanks Tom,
The command used to check process size is also given.
Memory utilisation
August 25, 2009 - 12pm Central time zone
Reviewer: Sagar
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.
Followup August 25, 2009 - 8pm Central time zone:
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...
August 26, 2009 - 3am Central time zone
Reviewer: Sagar
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
October 7, 2009 - 9am Central time zone
Reviewer: A reader
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
|