Re: 100% CPU utilization, urgent

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Mon, 20 Jan 2003 12:54:33 -0800
Message-ID: <F001.0053513F.20030120125433@fatcity.com>

> Hussain Ahmed Qadri wrote:
>
> Thanks for the script, I would like to know how would I interpret the
> VALUES column, I mean what does it stand for. If the value of CPU used
> for a particular SID is 2000, what does that mean? Is it the time, in
> 1/100 th of seconds of the total CPU time? Can you please help me
> understand this?
>
> Thanks and regards
>
> Hussain
>
> -----Original Message-----
> From: Thomas Day [mailto:tday6_at_csc.com]
> Sent: Monday, January 20, 2003 7:00 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: 100% CPU utilization, urgent
>
> Create the view dba_nt_threads and query it, then run the monitor CPU
> per
> session. These are not my scripts --- I'm pretty sure that they were
> posted here by others --- but I did not capture the information on who
>
> originally wrote them. My apologies. HTH
>
> --cr_dba_nt_threads.sql
> -- run as sys
> create or replace view
> dba_NT_threads
> as
> select
> p.spid "ID_THREAD",
> p.background "BACKGROUND",
> b.name "NAME",
> s.sid "SID",
> s.serial# "SERIAL#",
> s.username "USERNAME",
> s.status "STATUS",
> s.osuser "OSUSER",
> s.program "PROGRAM"
> from
> v$process p,
> v$bgprocess b,
> v$session s
> where
> s.paddr = p.addr
> and
> b.paddr(+) = p.addr;
> create public synonym dba_nt_threads for dba_nt_threads;
> create public synonym threads for dba_nt_threads;
>

If all NT monitoring tools display threads id in hex and Oracle in decimal, if I were you, NT folks, my natural laziness would incite me to create the following function :

create or replace function dec2hex(n in number) return varchar2
is

  v_result      varchar2(20) := '';
  v_characters  varchar2(16) := '0123456789abcdef';
  n_remain      number;
  n_pos         number;

begin
  n_remain := n;
  while (n_remain > 0)
  loop

    n_pos := mod(n_remain, 16);
    v_result := substr(v_characters, n_pos + 1, 1) || v_result;
    n_remain := trunc(n_remain / 16); 

  end loop;
  if (v_result = '')
  then
    return '0';
  else
    return(v_result);
  end if;
end;
/

and, instead of selecting p.spid in the query above, query

   substr(dec2hex(to_number(p.spid)), 1, 9)

I hate converting between decimal and hexadecimal :-).

-- 
HTH,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).

Received on Mon Jan 20 2003 - 14:54:33 CST