RE: Process field on v$session
- From: "QuijadaReina, Julio C" <QuijadJC@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 Apr 2004 10:55:36 -0400
Dennis,
The query you sent is going to be helpful. I noticed one thing from you
example though: 2800 is not a session pid (spid) on the db server. 2800
is Apache running on the application server, which pid gets logged in
v$session.process when a connection is established between the two boxes
(the app server and the db server.)=20
Now, I understand that by using this query I will be able to see what
SQL code is being executed given a spid. However, after playing with
this for a while, I found out that for the any given spid, there can be
several users executing the SQL code. Remember the v$sqlarea view that I
mentioned before? When I run the following query, the users_executing
field shows numbers up in the 50s and 60s. If one spid has as many as 60
users executing the same SQL statement, then it will be even harder to
point out what web client is pounding the database. I know that the SQL
statements run by users connecting through our app server have not given
us performance problems in the past. Now, whether we had these many
users executing this SQL in the past I don't know. Maybe there has been
an increase in the number of users. In the past we didn't care about it
because there was no sluggish effect on our db server. Now that we are
having this problem, we are monitoring our systems more closely.
The following query is what I use (which is very similar to your code):
SELECT
all_users.username,
executions,
users_executing,
command_type,
disk_reads,
sql_text
FROM v$sqlarea a, v$session b, all_users
WHERE disk_reads > 2000
AND a.parsing_user_id =3D b.user#
AND parsing_user_id <> 0
AND parsing_user_id =3D user_id
ORDER BY disk_reads desc
As of right now, the problem seems to have gone away, but I'll keep
monitoring our db server. Thanks for you help!
Julio
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of DENNIS WILLIAMS
Sent: Tuesday, April 13, 2004 5:23 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Process field on v$session
Julio
Here is the SQL query I use, where the spid is the unix process I.D.
that
you mentioned that you already have (replace 19633 with your 2800). This
isn't the best query for this, but I use it a lot and I'm on my way out
the
door for the day. Good luck.
select sql_text
from v$process a, v$session b, v$sqltext c
where spid =3D 19633
and a.addr =3D b.paddr
and b.sql_address =3D c.address
order by address, hash_value, piece
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx=20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of QuijadaReina, Julio C
Sent: Tuesday, April 13, 2004 3:24 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Process field on v$session
Dennis,
Thanks for pointing me to a different direction - I was starting to get
frustrated with this. I hope you pardon my ignorance since I am new to
the Oracle views. But, is this what v$session.command will show me? And
since you mentioned SQL, I've seen a view called v$sqlarea. Is that a
good starting point?
Julio=3D20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of DENNIS WILLIAMS
Sent: Tuesday, April 13, 2004 2:23 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Process field on v$session
Julio
Consider working from the other direction. Try to find the SQL that
is
being executed.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx=3D20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of QuijadaReina, Julio C
Sent: Tuesday, April 13, 2004 12:24 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Process field on v$session
All,
=3D20
For a good chunk of the day now, I've been trying to get all information
I can about this one session that is consuming up to 70 per cent of CPU
on our db server.=3D20
=3D20
Let me give you a little background: We have an application server that
runs a web-based system for class registration. This app server runs
Apache. The task mingler on this app server shows several Apache PIDs
-each db instance has its own separate web service and in turn, each
Apache PID can have several child processes.=3D20
=3D20
Now, in my efforts to track down this CPU-hogging session; on our db
server I see this session as coming from the app server [get this from
v$session.machine.] I can also get some other goodies by joining on
v$process to get the db server PID. I've done pretty good up to this
point, but there is one field on v$session called 'process' of which I
am uncertain. This field shows two numbers separated by a colon (e.g.
2800:2168). A little bit of research indicates that the first number
corresponds to the app server Apache's PID. I have used netstat on my
app server to see if the second number would be a port number. But,
netstat does not show any clients connecting to that port number. Has
anyone figured out what that second number stands for? Is it a client's
identification number of some sort or is it just a random number
generated by Oracle upon establishing a connection? I'll appreciate any
input you may have on this. Thanks in advance!
=3D20
Julio
=3D20
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
