Re: High CPU utilization

Or just run psn from 0x.tools and eyeball which instance's processes are on the CPU state the most. Psn by default replaces any digits in the process name/comm field with a * so it could aggregate similar processes together. This output is from a server with just one DB that's pretty idle:

[tanel_at_linux01 bin]$ psn

Linux Process Snapper v1.2.5 by Tanel Poder [https://0x.tools] Sampling /proc/stat for 5 seconds...
finished.

• Active Threads ================================================

 samples | avg_threads | comm | state

     100 |        *1.00 *| (oracle_*_*li*)    | Running (ON CPU)
       2 |        *0.02* | (ora_p*_*lin*m*)   | Running (ON CPU)
       1 |        0.01 | (ora_ckpt_lin*m) | Disk (Uninterruptible)
       1 |        *0.01* | (ora_dia*_*lin*m*) | Running (ON CPU)
       1 |        *0.01* | (ora_m*_*lin*m*)   | Running (ON CPU)
       1 |        *0.01* | (ora_vkrm_*lin*m*) | Running (ON CPU)
       1 |        *0.01* | (ora_vktm_*lin*m*) | Running (ON CPU)

On Sun, Mar 17, 2024 at 3:24 PM Stefan Koehler <contact_at_soocs.de> wrote:

> Hi Mladen,
>
> > How precisely would you use profiler to answer the question of which
> instance is hogging CPU and memory? Can you please elucidate me?
>
> Krishna wanted to know which instances/databases are utilizing the CPU -
> so you run xcapture (from the 0xtools software suite) as a daemon and you
> get a CSV file that contains all OS processes which are running on CPU or
> waiting on something for every second.
>
> As the Oracle instance name is included in the OS process name you finally
> just grep the CSV file(s) for status "R" and group by the Oracle instance
> name. The 0xtools website also has an example with Oracle and also an
> example for aggregating the CSV data, e.g.
>
> $ head 2020-10-16.21.csv
> TS,PID,TID,USERNAME,ST,COMMAND,SYSCALL,WCHAN,EXE,CMDLINE,KSTACK
> ...
> 2020-10-16
> 21:00:02.075,16762,16762,oracle,R,(ora_m000_lin19c),[running],0,oracle,ora_m000_LIN19C,->do_blockdev_direct_IO()->dio_complete()
> ...
>
> $ cat 2020-10-13.01.csv | awk -F, '{ printf("%2s %-20s %-20s
> %s\n",$5,$4,$7,$10) }' | sort | uniq -c | sort -nbr | head -20
> ...
> 51 R oracle [running] oracleLIN19C
> ...
>
> Of course Krishna could also do this on his own by reading the /proc
> filesystem but why doing it by yourself when there is already a great and
> free software suite for that.
>
> Best Regards
> Stefan Koehler
>
> Independent Oracle performance consultant and researcher
> Website: www.soocs.de
> Twitter: _at_OracleSK
>
> > Mladen Gogala <gogala.mladen_at_gmail.com> hat am 17.03.2024 19:26 CET
> geschrieben:
> >
> > Hi Stefan,
> > I might have misunderstood the 0x.tools. My understanding was that is
> can be used for profiling sessions and doing the event analysis of Oracle
> performance problems. It's a profiler, similar in purpose to gprof. How
> precisely would you use profiler to answer the question of which instance
> is hogging CPU and memory? Can you please elucidate me?
> > Regards
> > --
> > Mladen Gogala
> > Database SME
> > https://dbwhisperer.wordpress.com
> >
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l