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@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@xxxxxxxx> wrote:
Hi Mladen,
How precisely would you use profiler to answer the question of whichinstance 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: @OracleSK
Mladen Gogala <gogala.mladen@xxxxxxxxx> hat am 17.03.2024 19:26 CETgeschrieben:
can be used for profiling sessions and doing the event analysis of Oracle
Hi Stefan,
I might have misunderstood the 0x.tools. My understanding was that is
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
//www.freelists.org/webpage/oracle-l
