Which process is taking up so much CPU???
I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which process is taking up so much CPU???
Prompt
Prompt Show total CPU and statement CPU for current session (requires TIMED
STATISTICS = TRUE)
Prompt
set verify off
col UNAM format a20 word heading 'User'
col STMT format a56 word heading 'Statement'
col RUNT format a08 word heading 'CPU Time'
col ltim format a20 word heading 'Logon Time'
col etim format a20 word heading 'Connect Time'
col PROG format a30 word heading 'Program|Client Terminal Details'
col SID format a10 word heading 'SID/|Serial#'
col DR format 9 heading 'Disk Reads'
col BG format 9 heading 'Buffer Gets'
col EX format 9 heading 'Executions'
col rsecs format 999,999,999.00 heading "CPU time|(seconds)"
Prompt Overall Top CPU for all connected session (used by this session)
Prompt
select nvl(username,'ORACLE PROC')||'
('||ss.sid||')'||DECODE(SS.AUDSID,userenv('SESSIONID'),'**','') UNAM,
ss.program||'-'||ss.terminal||'('||ss.machine||') as '||ss.osuser
PROG,
ltrim(to_char(floor(se1.value*.01/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(se1.value*.01, 36)/60), '09')) || ':'
|| ltrim(to_char(mod(se1.value*.01, 60), '09'))RUNT,se1.value*.01
rsecs
from v$session ss, v$sesstat se1, v$statname sn1
where se1.statistic# = sn1.statistic#
and sn1.name like '%CPU used by this session%'
and se1.sid = ss.sid
-- and ss.username is not null
and se1.value !=0
ORDER BY 3 DESC,1
/
Prompt Overall Top CPU for all connected session (when call started)
Prompt
select nvl(username,'ORACLE PROC')||'
('||ss.sid||')'||DECODE(SS.AUDSID,userenv('SESSIONID'),'**','') UNAM,
ss.program||'-'||ss.terminal||'('||ss.machine||') as '||ss.osuser
PROG,
ltrim(to_char(floor(se1.value*.01/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(se1.value*.01, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(se1.value*.01, 60), '09'))RUNT,se1.value*.01
rsecs
from v$session ss, v$sesstat se1, v$statname sn1
where se1.statistic# = sn1.statistic#
and sn1.name like '%CPU used when call started%'
and se1.sid = ss.sid
-- and ss.username is not null
and se1.value !=0
ORDER BY 3 DESC,1
/
Prompt Top CPU for currently active statements
Prompt
select nvl(username,'ORACLE PROC')||'
('||ss.sid||')'||DECODE(SS.AUDSID,userenv('SESSIONID'),'**','') UNAM,
-- ss.program||'-'||ss.terminal||'('||ss.machine||') as '||ss.osuser
PROG,
ltrim(to_char(floor((se1.value*.01-se2.value*.01)/3600), '09')) ||
':'
|| ltrim(to_char(floor(mod((se1.value*.01-se2.value*.01), 3600)/60),
'09')) || ':'
|| ltrim(to_char(mod((se1.value*.01-se2.value*.01), 60), '09'))
RUNT,
se1.value*.01-se2.value*.01 rsecs,
NVL(SQL.SQL_TEXT,'***NO known SQL***') STMT
from v$session ss, v$sesstat se1, v$sesstat se2, v$statname sn1, v$statname
sn2
, V$SQL SQL
where se1.statistic# = sn1.statistic#
and se2.statistic# = sn2.statistic#
and sn1.name like '%CPU used by this session%'
and sn2.name like '%CPU used when call started%'
and se1.sid = ss.sid
and se2.sid = ss.sid
and ss.username is not null
and se1.value !=0
and se2.value !=0
and SS.SQL_ADDRESS= SQL.ADDRESS --(+)
and SS.SQL_HASH_VALUE = SQL.HASH_VALUE --(+)
ORDER BY 3 DESC,1
/
clear columns
===
I'm not sure where this is from (so can't point to an author)
Mark
-Original Message-
Bernaus Berraondo
Sent: 27 March 2003 15:14
To: Multiple recipients of list ORACLE-L
I usually track our HP-UX 11.0 system with the 'top' command so I can
notice when the system is under slow performance. If that happens, I use
Toad to look for any active Oracle SQL query which may be heavy enough for
degrading the performance.
I think my question is simple, but since I am a newbie on this... how can I
see who is executing an Oracle SQL that is taking all our CPU provided that
I only see his PID with the TOP command? I only see the oracle process, but
I don't know how to get the username and the SQL beside him.
Thank you for your answers!
Fermin.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Fermin Bernaus Berraondo
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Leith
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hos
RE: Which process is taking up so much CPU???
Hey, it could well be one of mine (or something derived from the same source as I started with) - got all the same words, syntax and style as something I call SessionTopCPU and use to be available off my web site. I think the original inspiration was from Dave Ensor, but I no longer use the report and have long since forgotten almost everything about it (ie I won't be of much help in answering questions). _ Tim Onions Head of Oracle and Web Development Speech Machines (A MedQuist Company) ...the speech-to-data Application Service Provider Tel: +44.1684.312364 http://www.speechmachines.com -Original Message- Sent: 27 March 2003 15:59 To: Multiple recipients of list ORACLE-L Prompt Prompt Show total CPU and statement CPU for current session (requires TIMED STATISTICS = TRUE) Prompt set verify off col UNAM format a20 word heading 'User' col STMT format a56 word heading 'Statement' col RUNT format a08 word heading 'CPU Time' col ltim format a20 word heading 'Logon Time' col etim format a20 word heading 'Connect Time' col PROG format a30 word heading 'Program|Client Terminal Details' col SID format a10 word heading 'SID/|Serial#' col DR format 9 heading 'Disk Reads' col BG format 9 heading 'Buffer Gets' col EX format 9 heading 'Executions' col rsecs format 999,999,999.00 heading "CPU time|(seconds)" ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Onions INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which process is taking up so much CPU???
Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which process is taking up so much CPU???
Mark et. al,
Just a caveat based on a personal observation. For
pre-9i databases on some OS platforms(sorry it has
been a while so I can't remember the exact details),
the "CPU used by this session" metric had some
accounting issues and hence reflected much higher
values (by orders of magnitude) than its counterpart -
"CPU used when call started". Usually "CPU used by
this session" is higher than its counterpart, but not
by too much.
On those versions, "CPU used when call started"
provides a more accurate indication of CPU usage. This
was fixed in 9i. Having said that the preferred metric
to be used should be "CPU used by this session", as
this metric gets updated "more often" than the other.
"CPU used when call started" gets updated before "a
call is started" (parse, execute, fetch). Thought this
may be something for folks to keep in mind while using
them in queries.
Cheers,
Gaja
--- Mark Leith <[EMAIL PROTECTED]> wrote:
> Prompt
> Prompt Show total CPU and statement CPU for current
> session (requires TIMED
> STATISTICS = TRUE)
> Prompt
>
> set verify off
> col UNAM format a20 word heading 'User'
> col STMT format a56 word heading 'Statement'
> col RUNT format a08 word heading 'CPU Time'
> col ltim format a20 word heading 'Logon Time'
> col etim format a20 word heading 'Connect Time'
> col PROG format a30 word heading 'Program|Client
> Terminal Details'
> col SID format a10 word heading 'SID/|Serial#'
> col DR format 9 heading 'Disk Reads'
> col BG format 9 heading 'Buffer Gets'
> col EX format 9 heading 'Executions'
> col rsecs format 999,999,999.00 heading "CPU
> time|(seconds)"
>
> Prompt Overall Top CPU for all connected session
> (used by this session)
> Prompt
> select nvl(username,'ORACLE PROC')||'
>
('||ss.sid||')'||DECODE(SS.AUDSID,userenv('SESSIONID'),'**','')
> UNAM,
>
> ss.program||'-'||ss.terminal||'('||ss.machine||') as
> '||ss.osuser
> PROG,
> ltrim(to_char(floor(se1.value*.01/3600),
> '09')) || ':'
>|| ltrim(to_char(floor(mod(se1.value*.01,
> 36)/60), '09')) || ':'
>|| ltrim(to_char(mod(se1.value*.01, 60),
> '09'))RUNT,se1.value*.01
> rsecs
> from v$session ss, v$sesstat se1, v$statname sn1
> where se1.statistic# = sn1.statistic#
>and sn1.name like '%CPU used by this session%'
>and se1.sid = ss.sid
> -- and ss.username is not null
>and se1.value !=0
> ORDER BY 3 DESC,1
> /
> Prompt Overall Top CPU for all connected session
> (when call started)
> Prompt
> select nvl(username,'ORACLE PROC')||'
>
('||ss.sid||')'||DECODE(SS.AUDSID,userenv('SESSIONID'),'**','')
> UNAM,
>
> ss.program||'-'||ss.terminal||'('||ss.machine||') as
> '||ss.osuser
> PROG,
> ltrim(to_char(floor(se1.value*.01/3600),
> '09')) || ':'
>|| ltrim(to_char(floor(mod(se1.value*.01,
> 3600)/60), '09')) || ':'
>|| ltrim(to_char(mod(se1.value*.01, 60),
> '09'))RUNT,se1.value*.01
> rsecs
> from v$session ss, v$sesstat se1, v$statname sn1
> where se1.statistic# = sn1.statistic#
>and sn1.name like '%CPU used when call
> started%'
>and se1.sid = ss.sid
> -- and ss.username is not null
>and se1.value !=0
> ORDER BY 3 DESC,1
> /
>
> Prompt Top CPU for currently active statements
> Prompt
> select nvl(username,'ORACLE PROC')||'
>
('||ss.sid||')'||DECODE(SS.AUDSID,userenv('SESSIONID'),'**','')
> UNAM,
> --
> ss.program||'-'||ss.terminal||'('||ss.machine||') as
> '||ss.osuser
> PROG,
>
>
ltrim(to_char(floor((se1.value*.01-se2.value*.01)/3600),
> '09')) ||
> ':'
>||
>
ltrim(to_char(floor(mod((se1.value*.01-se2.value*.01),
> 3600)/60),
> '09')) || ':'
>||
> ltrim(to_char(mod((se1.value*.01-se2.value*.01),
> 60), '09'))
> RUNT,
> se1.value*.01-se2.value*.01 rsecs,
>NVL(SQL.SQL_TEXT,'***NO known SQL***')
> STMT
> from v$session ss, v$sesstat se1, v$sesstat se2,
> v$statname sn1, v$statname
> sn2
>, V$SQL SQL
> where se1.statistic# = sn1.statistic#
>and se2.statistic# = sn2.statistic#
>and sn1.name like '%CPU used by this session%'
>and sn2.name like '%CPU used when call
> started%'
>and se1.sid = ss.sid
>and se2.sid = ss.sid
>and ss.username is not null
>and se1.value !=0
>and se2.value !=0
>and SS.SQL_ADDRESS= SQL.ADDRESS --(+)
>and SS.SQL_HASH_VALUE = SQL.HASH_VALUE --(+)
> ORDER BY 3 DESC,1
> /
>
> clear columns
>
> ===
>
> I'm not sure where this is from (so can't point to
> an author)
>
> Mark
>
> -Original Message-
> Bernaus Berraondo
> Sent: 27 March 2003 15:14
> To: Multiple recipients of list ORACLE-L
>
>
>
> I usually track our HP-UX 11.0 system with the
> 'top' command so I can
> notice when the system is under slow performance. If
> that happens, I use
> Toad to look for any active Oracle SQL query which
> may be heavy enough for
> degrading the performance.
>
> I think my question
RE: Which process is taking up so much CPU???
Tim, I think you may be right actually! :) I have a sub directory that contains the scripts that you zipped up on your site, this script seemed to have found it's way in to my main "SQL" directory, and as it didn't have any notes on author I plain forgot. It was indeed called SessionTopCPU.sql. All comments/praises to Tim! ;) Welcome back to the list by the way! :) Cheers Mark -Original Message- Sent: 27 March 2003 17:14 To: Multiple recipients of list ORACLE-L Hey, it could well be one of mine (or something derived from the same source as I started with) - got all the same words, syntax and style as something I call SessionTopCPU and use to be available off my web site. I think the original inspiration was from Dave Ensor, but I no longer use the report and have long since forgotten almost everything about it (ie I won't be of much help in answering questions). _ Tim Onions Head of Oracle and Web Development Speech Machines (A MedQuist Company) ...the speech-to-data Application Service Provider Tel: +44.1684.312364 http://www.speechmachines.com -Original Message- Sent: 27 March 2003 15:59 To: Multiple recipients of list ORACLE-L Prompt Prompt Show total CPU and statement CPU for current session (requires TIMED STATISTICS = TRUE) Prompt set verify off col UNAM format a20 word heading 'User' col STMT format a56 word heading 'Statement' col RUNT format a08 word heading 'CPU Time' col ltim format a20 word heading 'Logon Time' col etim format a20 word heading 'Connect Time' col PROG format a30 word heading 'Program|Client Terminal Details' col SID format a10 word heading 'SID/|Serial#' col DR format 9 heading 'Disk Reads' col BG format 9 heading 'Buffer Gets' col EX format 9 heading 'Executions' col rsecs format 999,999,999.00 heading "CPU time|(seconds)" ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Onions INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which process is taking up so much CPU???
Thank you all for your help. I wonder where I should set TIMED STATISTICS = TRUE, if any of you has the time to answer I'd be grateful, but I will look for it in the docs. Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 27 de marzo de 2003 18:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Which process is taking up so much CPU???
Alter system set timed_statistics=true; or/and - init.ora - timed_statistics=true HTH CP [EMAIL PROTECTED] wrote: Thank you all for your help. I wonder where I should set TIMED STATISTICS = TRUE, if any of you has the time to answer I'd be grateful, but I will look for it in the docs. Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 27 de marzo de 2003 18:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: CP INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which process is taking up so much CPU???
Fermin Add this line to your init.ora file. timed_statistics = true Then shutdown, startup your Oracle instance. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 28, 2003 6:24 AM To: Multiple recipients of list ORACLE-L Thank you all for your help. I wonder where I should set TIMED STATISTICS = TRUE, if any of you has the time to answer I'd be grateful, but I will look for it in the docs. Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 27 de marzo de 2003 18:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which process is taking up so much CPU???
On Fri, 28 Mar 2003, DENNIS WILLIAMS wrote: > Fermin >Add this line to your init.ora file. > timed_statistics = true >Then shutdown, startup your Oracle instance. I would hasten to point out that this parameter can be set dynamically using alter system from at least 8.1.x forward. Thus, restarting the instance is unnecessary and only reduces availability. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton > -Original Message- > Sent: Friday, March 28, 2003 6:24 AM > To: Multiple recipients of list ORACLE-L > > I wonder where I should set TIMED STATISTICS = TRUE, if any of you > has the time to answer I'd be grateful, but I will look for it in the docs. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which process is taking up so much CPU???
ALTER SYSTEM SET TIMED_STATISTICS=TRUE; You don't need to bounce the database ;) Best Regards, K Gopalakrishnan -Original Message- WILLIAMS Sent: Friday, March 28, 2003 6:49 AM To: Multiple recipients of list ORACLE-L Fermin Add this line to your init.ora file. timed_statistics = true Then shutdown, startup your Oracle instance. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 28, 2003 6:24 AM To: Multiple recipients of list ORACLE-L Thank you all for your help. I wonder where I should set TIMED STATISTICS = TRUE, if any of you has the time to answer I'd be grateful, but I will look for it in the docs. Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 27 de marzo de 2003 18:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail mess
RE: Which process is taking up so much CPU???
Thanks Jeremiah. My bad. T early on Friday. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 28, 2003 9:19 AM To: Multiple recipients of list ORACLE-L On Fri, 28 Mar 2003, DENNIS WILLIAMS wrote: > Fermin >Add this line to your init.ora file. > timed_statistics = true >Then shutdown, startup your Oracle instance. I would hasten to point out that this parameter can be set dynamically using alter system from at least 8.1.x forward. Thus, restarting the instance is unnecessary and only reduces availability. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton > -Original Message- > Sent: Friday, March 28, 2003 6:24 AM > To: Multiple recipients of list ORACLE-L > > I wonder where I should set TIMED STATISTICS = TRUE, if any of you > has the time to answer I'd be grateful, but I will look for it in the docs. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which process is taking up so much CPU???
It works under Oracle 8.0.6 as well, that's our platform version and I successfully run your queries. Thanks! -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Jeremiah Wilton Enviado el: viernes, 28 de marzo de 2003 16:19 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? On Fri, 28 Mar 2003, DENNIS WILLIAMS wrote: > Fermin >Add this line to your init.ora file. > timed_statistics = true >Then shutdown, startup your Oracle instance. I would hasten to point out that this parameter can be set dynamically using alter system from at least 8.1.x forward. Thus, restarting the instance is unnecessary and only reduces availability. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton > -Original Message- > Sent: Friday, March 28, 2003 6:24 AM > To: Multiple recipients of list ORACLE-L > > I wonder where I should set TIMED STATISTICS = TRUE, if any of you > has the time to answer I'd be grateful, but I will look for it in the docs. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which process is taking up so much CPU???
Dennis, you seem to be have a master in Oracle! please can you help, I can see the SPID column under table v$process, but how do I link it to table v$session so that I actually know which UNIX process it corresponds to. I think there must be another table that links both of them; you talk about the shadow process, where is it or where can I get more info on it. Thank you for your time. Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 27 de marzo de 2003 18:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which process is taking up so much CPU???
I just found that v$session.paddr = v$process.addr is the join that has to be done. Right? -Mensaje original- De: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Enviado el: lunes, 31 de marzo de 2003 18:01 Para: '[EMAIL PROTECTED]' Asunto: RE: Which process is taking up so much CPU??? Dennis, you seem to be have a master in Oracle! please can you help, I can see the SPID column under table v$process, but how do I link it to table v$session so that I actually know which UNIX process it corresponds to. I think there must be another table that links both of them; you talk about the shadow process, where is it or where can I get more info on it. Thank you for your time. Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 27 de marzo de 2003 18:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which process is taking up so much CPU???
Jared - You are right, I do type too many words. Just look how people are misled. I can hear the faint sound of laughter in the air. Fermin - If you've noticed my signature, I'm nowhere near the expert level. For joining v$process and v$session, I join v$process.addr = v$session.paddr. I can't confirm this is correct, but it has provided me the information I've needed to resolve problems. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 31, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Dennis, you seem to be have a master in Oracle! please can you help, I can see the SPID column under table v$process, but how do I link it to table v$session so that I actually know which UNIX process it corresponds to. I think there must be another table that links both of them; you talk about the shadow process, where is it or where can I get more info on it. Thank you for your time. Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 27 de marzo de 2003 18:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which process is taking up so much CPU???
Fermin, The following query might help you set linesize 120 select substr(vs.username,1,10)username, vs.osuser, vs.sid, vs.serial#, vs.LOGON_TIME, to_char(vs.logon_time, 'DD-MON-YY HH24:MI:SS') LOGON_TIME, substr(vs.machine,1,15)machine, vs.process, vp.spid, vs.last_call_et from v$session vs, v$process vp where vs.paddr = vp.addr and vs.username is not null and vs.status = 'ACTIVE' / Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Mon, 31 Mar 2003 07:54:03 -0800 Dennis, you seem to be have a master in Oracle! please can you help, I can see the SPID column under table v$process, but how do I link it to table v$session so that I actually know which UNIX process it corresponds to. I think there must be another table that links both of them; you talk about the shadow process, where is it or where can I get more info on it. Thank you for your time. Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 27 de marzo de 2003 18:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
