Home » RDBMS Server » Backup & Recovery » Need to check session information (Oracle 11g Linux)
Need to check session information [message #485079] Mon, 06 December 2010 01:45 Go to next message
pokhraj_d
Messages: 117
Registered: December 2007
Senior Member
Hi ,
My manager wants to check one report as which Oracle Processess consuming maximum memory.
I have run top command and getting some oracle process consuming maximum memory.(checking PID from to command)

How can I query from the oracle views with respect to PID?

Thanks-
P
Re: Need to check session information [message #485188 is a reply to message #485079] Mon, 06 December 2010 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67769
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v$process.spid, v$session.process

Regards
Michel
Re: Need to check session information [message #485194 is a reply to message #485188] Mon, 06 December 2010 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 26759
Registered: January 2009
Location: SoCal
Senior Member
>I have run top command and getting some oracle process consuming maximum memory
any *NIX has VERY POOR ability to accurately measure/report process memory consumption; due to Shared Global Area.
Every process is reported to contain total amount of SGA plus more.
If you added up the size reported by OS for every Oracle process, the total will likely exceed total RAM.

You may be on a snipe hunt.
http://en.wikipedia.org/wiki/Snipe_hunt
Re: Need to check session information [message #485849 is a reply to message #485079] Fri, 10 December 2010 10:47 Go to previous messageGo to next message
ckbeg
Messages: 2
Registered: December 2010
Location: Kazakhstan, Atyrau
Junior Member
Just execute this script in sqlplus:

SET LINESIZE 80 HEADING OFF FEEDBACK OFF
SELECT
  RPAD('USERNAME : ' || s.username, 80) ||
  RPAD('OSUSER   : ' || s.osuser, 80) ||
  RPAD('PROGRAM  : ' || s.program, 80) ||
  RPAD('SPID     : ' || p.spid, 80) ||
  RPAD('SID      : ' || s.sid, 80) ||
  RPAD('SERIAL#  : ' || s.serial#, 80) ||
  RPAD('MACHINE  : ' || s.machine, 80) ||
  RPAD('TERMINAL : ' || s.terminal, 80) ||
  RPAD('SQL TEXT : ' || q.sql_text, 80)
FROM v$session s
    ,v$process p
    ,v$sql     q
WHERE s.paddr          = p.addr
AND   p.spid           = &PID_FROM_OS
AND   s.sql_address    = q.address
AND   s.sql_hash_value = q.hash_value;
Re: Need to check session information [message #485850 is a reply to message #485849] Fri, 10 December 2010 10:48 Go to previous messageGo to next message
ckbeg
Messages: 2
Registered: December 2010
Location: Kazakhstan, Atyrau
Junior Member
and type your *nix PID for sqlplus prompt.
Re: Need to check session information [message #485851 is a reply to message #485849] Fri, 10 December 2010 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67769
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SET LINESIZE 80 HEADING OFF FEEDBACK OFF
RPAD('USERNAME : ' || s.username, 80)

See SQL*Plus Manual, COLUMN statement to know how get a new line after/before a column value without using this awful trick.

Regards
Michel
Re: Need to check session information [message #486087 is a reply to message #485851] Mon, 13 December 2010 09:20 Go to previous messageGo to next message
najjuizzhere
Messages: 7
Registered: December 2010
Location: BAHRAIN
Junior Member
set line 300 pagesize 60
col mins_in_wait for 999,999.99
col event for a40
col osuser for a20
col username for a10
col program for a35
select a.sid,a.serial#,a.username,a.status,a.osuser,a.program,b.event,b.seconds_in_wait/60 mins_in_wait from v$session a,v$session_wait b
where a.sid=b.sid and
a.username is not null
order by 3,6,7;

Enjoy
Re: Need to check session information [message #486102 is a reply to message #486087] Mon, 13 December 2010 10:36 Go to previous message
Michel Cadot
Messages: 67769
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does this query and especially "b.event,b.seconds_in_wait/60 mins_in_wait" as to do with OP's question:
Quote:
How can I query from the oracle views with respect to PID?


In addition, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: Query Regarding Rman Recovery
Next Topic: how to recover a table after dropping(using time based recovery)
Goto Forum:
  


Current Time: Sun Mar 07 22:11:25 CST 2021