asked on CPU utilization per Oracle Database
Hi,
I have a lot of Oracle databases running on Oracle Linux Server 7.9 with 34 CPUs.
The server is almost 100% cpu used and I would like to know which database is the top cpu consumer
I run the following top command:
top -b -o %CPU -n 1 | head -n 60
example output:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
99815 oracle 20 0 8921672 92800 80900 R 67.9 0.0 41:34.19 oracleDBCGAX061 (LOCAL=NO)
320422 oracle 20 0 6594120 121908 81028 R 58.7 0.0 18:10.64 oracleDBCJAX011 (LOCAL=NO)
229518 oracle 20 0 27.7g 120076 110844 R 55.0 0.0 173:26.64 oracleDBCGAX091 (LOCAL=NO)
237965 oracle 20 0 6593972 120604 80424 R 53.2 0.0 9:02.81 oracleDBCJAX011 (LOCAL=NO)
58155 oracle 20 0 14.0g 88684 83080 R 52.3 0.0 3:30.41 oracleDBCGAX141 (LOCAL=NO)
64922 oracle 20 0 14.0g 88872 83272 R 47.7 0.0 2:35.05 oracleDBCGAX141 (LOCAL=NO)
99810 oracle 20 0 8925840 94368 83260 R 47.7 0.0 42:04.41 oracleDBCGAX061 (LOCAL=NO)
267573 oracle 20 0 6593972 121492 81516 R 46.8 0.0 6:11.84 oracleDBCJAX011 (LOCAL=NO)
383808 oracle 20 0 14.0g 93304 87044 R 46.8 0.0 4:58.72 oracleDBCGAX141 (LOCAL=NO)
37697 oracle 20 0 6594076 122156 81196 R 45.0 0.0 14:25.15 oracleDBCJAX011 (LOCAL=NO)
290197 oracle 20 0 6594340 126044 84672 R 40.4 0.0 7:41.00 oracleDBCJAX011 (LOCAL=NO)
322281 oracle 20 0 6593996 122240 81892 R 40.4 0.0 10:27.74 oracleDBCJAX011 (LOCAL=NO)
322306 oracle 20 0 6594252 120984 82116 R 40.4 0.0 15:51.46 oracleDBCJAX011 (LOCAL=NO)
241737 oracle 20 0 6594324 121220 81632 R 39.4 0.0 19:29.18 oracleDBCJAX011 (LOCAL=NO)
270942 oracle 20 0 12.3g 92816 77392 R 39.4 0.0 13:45.15 oracleDBCGAX011 (LOCAL=NO)
290207 oracle 20 0 6594308 120348 81524 R 39.4 0.0 4:39.72 oracleDBCJAX011 (LOCAL=NO)
243705 oracle 20 0 6557396 87936 77180 R 38.5 0.0 3:38.30 oracleDBCJAX011 (LOCAL=NO)
303768 oracle 20 0 6557420 90556 78744 R 38.5 0.0 11:47.06 oracleDBCJAX011 (LOCAL=NO)
156591 oracle 20 0 14.0g 92600 86336 R 37.6 0.0 0:26.33 oracleDBCGAX141 (LOCAL=NO)
167371 oracle 20 0 27.8g 181940 105736 R 37.6 0.0 2:01.09 oracleDBCGAX091 (LOCAL=NO)
109838 oracle 20 0 14.0g 88844 83236 R 36.7 0.0 1:41.64 oracleDBCGAX141 (LOCAL=NO)
317047 oracle 20 0 12.3g 94652 78208 R 36.7 0.0 10:45.64 oracleDBCGAX011 (LOCAL=NO)
68006 oracle 20 0 6594184 124376 83204 R 35.8 0.0 40:31.55 oracleDBCJAX011 (LOCAL=NO)
51234 oracle 20 0 6594328 124548 83276 R 34.9 0.0 84:07.55 oracleDBCJAX011 (LOCAL=NO)
however this command is not very useful and I would like to group by counting the various databases, in particular in this example I would like a command that provides the following output:
USER COMMAND count
oracle oracleDBCJAX011 13
oracle oracleDBCGAX061 2
oracle oracleDBCGAX091 2
oracle oracleDBCGAX141 5
oracle oracleDBCGAX011 2
Is it possible to modify my top command to get this output?
LinuxUnix OSOracle DatabaseDatabases
Last Comment
Alex [***Alex140181***]
ASKER
Alex, this command not working for me, I get the following output:
I need the COMMAND name displayed in full, it would be better to use the "top" command NOT "ps".
[root@vm005 ~]# ps -u oracle o user,fname | awk -F" " '{A[$1"_"$2]++}END{for (i in A) print i,A[i]}'
oracle_ora_q005 2
oracle_ora_q006 4
oracle_ora_q007 1
oracle_ora_q008 3
oracle_ora_dbw0 6
oracle_ora_dbw1 3
oracle_ora_q009 3
oracle_ora_p000 5
oracle_ora_ppa6 1
oracle_ora_p001 5
oracle_ora_ppa7 6
oracle_ora_p002 5
oracle_ora_p003 5
oracle_ora_p004 4
oracle_ora_p005 4
oracle_ora_p006 3
oracle_ora_p007 3
oracle_ora_p008 3
oracle_ora_mmnl 6
oracle_oracle_1 49
oracle_oracle_2 31
oracle_oracle_3 30
I need the COMMAND name displayed in full, it would be better to use the "top" command NOT "ps".
1) The easy way to do this is to run 1x LXD container containing exactly 1x Oracle database.
Since all containers run in their own private/unique name space, gathering this type of data becomes trivial.
2) In MariaDB/MySQL you can introspect into the system tables to extract some data...
Which gives relative data, with no CPU utilization... and with some math... you might be able to gather this data.
The SQL to extract this information from MariaDB/MySQL used at the core of tracking SQL I/O is something like this...
3) Using LXD to partition each database instance into it's own namespace is by far the most straightforward way to do this + you'll still require writing some code to split up all namespaces on a given machine into a top like display.
4) The https://perlmonks.org/?node_id=11140803 discussion, about writing custom top-esque type tools, might be of use.
If you prefer another language than PERL, then you'd also rewrite or find all the PERL libraries/techniques covered in this discussion.
Since all containers run in their own private/unique name space, gathering this type of data becomes trivial.
2) In MariaDB/MySQL you can introspect into the system tables to extract some data...
# myiotrack | logtop -s 1000000
4688 elements in 9 seconds (520.89 elements/s)
1 3632 403.56/s ja1_ilovevampirenovels wp_options read
2 1056 117.33/s ja1_ilovevampirenovels wp_auto_updates read
Which gives relative data, with no CPU utilization... and with some math... you might be able to gather this data.
The SQL to extract this information from MariaDB/MySQL used at the core of tracking SQL I/O is something like this...
SELECT object_schema as db_name,object_name as table_name,count_read as read_opts, count_write as write_ops,count_insert as insert_opts,count_update as update _opts, count_delete as delete_ops FROM table_io_waits_summary_by_table WHERE count_write > 0 OR count_read > 0 GROUP BY COUNT_WRITE DESC
3) Using LXD to partition each database instance into it's own namespace is by far the most straightforward way to do this + you'll still require writing some code to split up all namespaces on a given machine into a top like display.
4) The https://perlmonks.org/?node_id=11140803 discussion, about writing custom top-esque type tools, might be of use.
If you prefer another language than PERL, then you'd also rewrite or find all the PERL libraries/techniques covered in this discussion.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Open in new window