Tks very much !!!
Sven Bleckwedel, November 21, 2001 - 12:36 pm UTC
Tom,
Your explanation about the dedicated server processes (and also the local= [ yes | no ] ) was very useful to me, to understand what was happening with my server...
Sven
Amit, November 18, 2003 - 5:38 pm UTC
Hi Tom,
One of my net8 ( LOCAL=NO) processes is taking up all of my CPU resource.
On doing top , I get this :
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
15171 oracle 1 20 0 0K 0K run 190.8H 99.02% oracle
On ps -ef , I get this :
oracle 15171 1 99 Nov 10 ? 11446:32 oracleGSO (LOCAL=NO)
Infact, my ps -ef shows a host of other net8 processes but I know that no one is logged in.
My typical users are Forms Client and internal web users.
Is there a way I can figure out why the process didnt get terminated or what is it currently executing ?
as an urgent measure can I kill any thread with LOCAL=No ?
Thanks,
Amit
November 21, 2003 - 11:10 am UTC
someone is logged in -- you just proved it!
have you identified the session in v$session? then alter system kill session.
More info on above
Amit, November 21, 2003 - 3:08 pm UTC
Hi Tom,
I did figure out the session ID. Infact using OEM, I also figured out that this is a web client(PROGRAM=JDBC Thin Client).
The SQL it is executing(still !!! ) is :
SELECT count(*)
FROM s_basic_info a, s_admission_info b, s_application_detail c
WHERE a.student_id = b.student_id
AND a.student_id = c.student_id
AND quarter_apply_enroll = :b2
AND c.file_completed_date IS NOT NULL
AND b.admit_code IS NULL
AND a.major = :b1
and on the statistics side :
Recursive calls = 90232008
recursive cpu usage= 86681075
System uga memory=209240
system uga memory max=209240
Does the above give any hint ?
The above query is part of a PL/SQL procedure which has other queries too, and I might not have used bind variables in all
of them ( I wrote it before I started reading asktom :) )
I can kill this one, but what can I do to avoid its occurring again ? Is my uga memory ok ? If not, how can I increase it.
Thanks
Amit
November 21, 2003 - 5:32 pm UTC
it is almost impossible to NOT use binds correctly in PLSQL -- that is the beauty of that language!
You can use resource profiles in the database to limit the amount of resources a session can use.
PROTOCOL=BEQ
A reader, March 17, 2004 - 11:36 am UTC
Tom,
My understanding on PROTOCOL=BEQ is as follows :
It means it uses a bequeath protocol which will happen always when the client process is running on the same machine as the database server.
1. In your example, however, the server process has been spawned by the listener (LOCAL=NO) and still the PROTOCOL=BEQ. Pls clarify
2. Under what situation we will have LOCAL=YES and still have PROTOCOL not equal to BEQ.
3.What is the meaning of the following :
a. The relation between PROTOCOL and LOCAL
b. Meaning of PROTOCOL=BEQ and Local=NO
c. Meaning of PROTOCOL=BEQ and LOCAL=YES
d. Meaning of PROTOCOL={TCP|xxx} and LOCAL={YES|NO}
e. Meaning of Network Latency and interprocess communication
Thanks much
March 17, 2004 - 3:17 pm UTC
the local=no with beq is "a false positive", it doesn't really happen (there is an open but very low priority issue with that - it has to do with dynamic service registration and such). so, ignore those -- they are not really remote bequeaths.
e) google it, but network latency is a measure of round trip times (how fast can you converse -- "ping"). IPC is interprocess communication -- two processes "talking" to eachother.
LOCAL=NO PROTOCOL=BEQ
A reader, March 17, 2004 - 6:15 pm UTC
Tom,
1. So in one of the example above that shows the LOCAL=YES vs LOCAL=NO, you have LOCAL=NO and PROTOCOL=BEQ and I understand that from your previous reply it is just an example that insists more on difference between LOCAL=NO vs LOCAL=YES rather than the specifics of dynamic registration etc. Is that true?
2. So from the above reply, is it a good conclusion that LOCAL=NO,PROTOCOL=BEQ will happen very very rarely (in scenarios such as dynamic registration as you mentioned)
March 17, 2004 - 6:36 pm UTC
1) yes - it is due to a pmon registering with the listener, blah blah blah -- i didn't really "follow" the entire thread of the bug (there are a couple of permutations that get it actually)
2) well dynamic registration is "the default" these days.... so....
LOCAL=YES PROTOCOL=BEQ
Venkat, February 28, 2005 - 10:17 am UTC
Tom-
Thanks for detailed explainations.
just thought I can post one of the problems I face, which is related to this thread..
I have 10g installed and am trying to connect from SQL*PLUS locally (same machine)
[pkg103]: /home/oracle $ $ORACLE_HOME/bin/sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Feb 28 10:00:34 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL> conn system
Enter password:****
Connected.
SQL> select spid from v$process where addr=(select paddr from
2 v$session where audsid=userenv('sessionid'));
SPID
------------
29285
SQL> ! ps -ef| grep 29285
oracle 29285 1 0 10:00:45 ? 0:00 oracleSID32 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 29774 29162 2 10:04:12 pts/tb 0:00 /usr/bin/sh -c ps
-ef| grep 29285
oracle 29776 29774 0 10:04:12 pts/tb 0:00 grep 29285
The strange behaviour is , I lose the parent process id(PPID) of oracle client connection..it is set to 1 (init)..
and in the same box, I have other 9i Release-2 instances which are showing ppid correctly..
The problem is when pro*c programs connect to the database , it spawns new threads with ppid set to 1 and there is no way to trace back which parent process kicked what child process..
I tested changing the "BEQUEATH_DETACH = YES" in sqlnet.ora but it made no difference.. Just wondering whether is this behavior due to dynamic registration of listener info by PMON (but beq connection should not look into listener.ora , isin't?)
any pointers would be highly appreciated..
and thanks in advance for your time
- Venkat
February 28, 2005 - 10:49 am UTC
looks like something changed -- yes, but you can tie the dedicated server back:
1 select a.spid dedicated_server,
2 b.process clientpid
3 from v$process a, v$session b
4* where a.addr = b.paddr
ops$tkyte@ORA10G> /
DEDICATED_SE CLIENTPID
------------ ------------
24068 24068
24070 24070
24072 24072
24074 24074
24076 24076
24078 24078
24080 24080
24082 24082
24098
24092 24092
24094 24094
24096 24096
25632 25630
24109 24109
14 rows selected.
does that help solve your immediate need?
perfect
Venkat, February 28, 2005 - 11:51 am UTC
Vow!! You are just amazing!! I have been breaking my head for one week and you got it within 5 minutes!!
(fyi: metalink support is still working on this and I am curious to see their response)
extremely helpful as an immediate fix.
Thanks again
======
and by the way, the OS is HP Unix..if you dont mind, pls let me know your suspisions on why this problem occurs..I mean whether
a) os/kernel problem
b) 10g software
c) coexistence of 9i with 10g
the reason why I ask is..your perception/pointers have always exposed me to great new ways of apprach to any problem.
and thanks again for your work.
February 28, 2005 - 12:04 pm UTC
it reproduces on red hat advanced server 3.0 as well.
I'm not sure if it is a bug or not, that would be for support to analyze and decide on.
(oh and that is my "getspid" script, it actually looks like this:
ops$tkyte@ORA9IR2> 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
------------ ------------
25703 25702
for your session.
Bequeath connection
Peter Yates, February 01, 2006 - 7:34 am UTC
Hi Tom
I wonder if you can help me with this problem.
We are converting a third party application from Oracle 7.3 Forms 4.5 to Oracle 9.2. Forms 6i
The application and database are running on the same HP True64 box.
We can connect as a normal user and as an OPS$ user in Oracle 7 but cannot connect as both when running in Oracle 9.
It appears if I set the SID to point to the dbms ORACLE_HOME , I can connect as OPS$.
If I switch the . oraenv to the application (6i) ORACLE_HOME, I cannot connect.
We set the tnsnames connection to bequeath and it works but it slows down alot of our Oracle Reports - after running statspack it appears that with Oracle Reports 6 (not using the reports server) using the bequeath connection creates alot of sqlnet packets and slows our reports dowm. When we switch back to TCP connection , the performance is alot better but we cannot connect as OPS$.
We need to make the Forms 6i work the same as Forns 45 and accept OPS$ and password users.
If you could shed any light on this problem, I would be most grateful.
Thanks for your help.
Cheers, Peter
February 02, 2006 - 3:47 am UTC
in order to use the os authentication you would be using a direct connect to Oracle (no sqlnet). In order to direct connect, we need to have the ORACLE_HOME and ORACLE_SID set to the precise ORACLE_HOME/ORACLE_SID of the database - that is because your process will be taking the home/sid and hashing it together to formulate the key to attach the SGA by. When you set the oracle_home to "not be the database" oracle home, you cannot directly attach to that SGA since the key generated would be wrong.
You would have to have the oracle_home/oracle_sid set to the same sid/home used to start the database to use the direct connect.
Peter Yates, February 02, 2006 - 5:32 pm UTC
Thanks for your prompt reply Tom .. most helpful.
Cheers, Peter
Alexander, February 01, 2011 - 11:54 am UTC
Tom,
Are you able to describe the differences between oracle's dedicated server processes and how other databases work (Sybase, SQLServer)? I noticed they have one OS process to run the instance, and it makes thing more difficult to track down users soaking up resources, things like that.
I assume Oracle's way is better :) Can you explain why? Seems to be a fundamental, architectural difference.
February 01, 2011 - 5:24 pm UTC
well, on windows we look a bit like sqlserver - a single process with threads.
The architecture is driven by the operating system. On windows, using multiple processes would be a really bad idea, windows is not architected for multiple processes - it is a multithreaded thing. Netware was that way - other OS's have been that way. On those OS's - we use a single process with multiple threads.
On Unix - you use multiple processes naturally - it is the way Unix works. Unix did not start as a threaded system (some are moving that way).
Alexander, February 01, 2011 - 8:06 pm UTC
I don't know if I asked that right. If you grep for sybase stuff on unix, you'd see one process for the sybase server basically (maybe a couple others for the backup "server" or whatever)....
But in Oracle, we can see a unix process for every connection, that would be why it's called a dedicated server process right? What benefits does that offer us, what advantages over the others?
February 02, 2011 - 7:24 am UTC
Sybase, whose architecture is from the 1980's - unchanged - whilst Oracle rewrote from the ground up in the late 1980's - dates from the days of single CPU's. Version 5 of Oracle was a single process beast. Version 6 introduced the multiple process architecture.
Once SMP became common in the 1990's - the benefits of multiprocess architectures became evident. Right around the time Sybase as a database company started becoming "not relevant".
When I had to benchmark Oracle7 against Sybase on a single CPU machine - I knew it would be a challenge. On an SMP machine - no sweat.
... But in Oracle, we can see a unix process for every connection, ...
That is true in dedicated server, but not in shared server. We have multiple configurations. The benefit to multiple processes is that Unix in general is built as a multi-process operating system. It was not built as a multithreaded operating system. Windows is.
On unix - multiple processes is in general the way to go.
On windows - a single multithreaded process is the way to go.
If you have software as architecturally old as Sybase and haven't really changed it much - then you might still be stuck back in the days of a single CPU when using a single process was a good choice.