On a linux/Unix server with multiple database instances running , if one of the databases has a process which is CPU intensive, we want to  identify the database which is the owner of  that process.

Currently, I have two databases – AMER and EURO running on the same server.
To simulate the environment, I needed to run a process in a database which consumes a lot of CPU. Since hash joins consume CPU, I wrote the following query and checked its explain plan

SQL> explain plan for
sELECT /*+ USE_HASH(l h) */ * FROM oe.orders h,oe.order_items l WHERE l.order_id = h.order_id;

select * from table(dbms_xplan.display);<

PLAN_TABLE_OUTPUT
————————————————————————————-
Plan hash value: 864676608

———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 665 | 36575 | 409 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 665 | 36575 | 409 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| ORDERS | 105 | 3885 | 204 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| ORDER_ITEMS | 665 | 11970 | 204 (0)| 00:00:01 |
———————————————————————

The explain clearly shows that hash join will be used. I executed this query repeatedly against EURO database using the following script :

SQL>conn / as sysdba

begin
for i in 1..100000000 loop
execute immediate 'sELECT /*+ USE_HASH(l h) */ * FROM oe.orders h,oe.order_items l WHERE l.order_id = h.order_id';
end loop;
end;
/

In another terminal, I issued “top” linux command to find out top CPU consuming processes. This is the output I got

 [root@node1 ~]# top

top – 21:54:39 up 2 days, 22:08, 6 users, load average: 4.75, 4.29, 4.16
Tasks: 244 total, 4 running, 240 sleeping, 0 stopped, 0 zombie
Cpu(s): 44.6%us, 44.9%sy, 0.0%ni, 0.0%id, 0.0%wa, 9.8%hi, 0.7%si, 0.0%st
Mem: 1667320k total, 1654112k used, 13208k free, 130188k buffers
Swap: 8193140k total, 643728k used, 7549412k free, 1133656k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14305 oracle 20 0 632m 66m 55m R 26.1 4.1 0:03.25 oracle
14665 root 20 0 22772 1548 1216 R 23.8 0.1 0:42.47 perl
21119 oracle 20 0 194m 3152 1500 D 15.2 0.2 164:15.69 mgr
142 root 20 0 0 0 0 R 13.5 0.0 132:57.93 kjournald
19272 oracle 20 0 166m 2292 1700 S 6.3 0.1 52:30.99 extract
3685 root 20 0 5924 544 488 D 5.6 0.0 52:14.51 syslogd

Here, It can be seen that the process with PID 14305 owned by oracle user is consuming most of the CPU.
But we don’t know which database does this process belong to. Let’s try to see the status of process with PID 14305

#[root@node1 ~]# ps -p 14305 -f

UID PID PPID C STIME TTY TIME CMD
oracle 14305 9918 2 21:44 ? 00:00:18 oracleeuro (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

It can be seen that process is running against euro database. Since local connection was made to the database, LOCAL= YES is present in the connection string.

— Now I will run the same script after making remote connection to euro database.

SQL>conn sys/oracle@euro as sysdba

begin
for i in 1..100000000 loop
execute immediate 'sELECT /*+ USE_HASH(l h) */ * FROM oe.orders h,oe.order_items l WHERE l.order_id = h.order_id';
end loop;
end;
/

In another terminal, I issued “top” linux command to find out top CPU consuming processes.

[root@node1 ~]# top

This is the output I got :
top – 22:35:47 up 2 days, 22:49, 6 users, load average: 4.24, 3.16, 3.34
Tasks: 236 total, 4 running, 232 sleeping, 0 stopped, 0 zombie
Cpu(s): 34.4%us, 46.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 17.5%hi, 2.1%si, 0.0%st
Mem: 1667320k total, 1650820k used, 16500k free, 132964k buffers
Swap: 8193140k total, 604000k used, 7589140k free, 1128896k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
16789 oracle 20 0 626m 19m 17m R 32.6 1.2 0:39.00 oracle
21119 oracle 20 0 194m 3152 1500 R 23.4 0.2 172:30.19 mgr
142 root 20 0 0 0 0 R 21.3 0.0 140:19.24 kjournald
3685 root 20 0 5924 544 488 D 8.6 0.0 55:11.53 syslogd
19272 oracle 20 0 166m 2292 1700 S 8.3 0.1 55:32.88 extract
4704 root 20 0 109m 10m 3720 S 1.5 0.6 3:54.93 Xorg
7503 oracle 20 0 284m 11m 4432 S 1.2 0.7 1:09.44 gnome-terminal
4909 oracle 20 0 268m 2628 2328 S 0.6 0.2 15:52.64 ocssd.bin
4535 root 19 -1 112m 608 520 S 0.3 0.0 0:23.08 modclusterd

[root@node1 ~]# ps -p 16789 -f

UID PID PPID C STIME TTY TIME CMD
oracle 16789 1 33 22:33 ? 00:00:54 oracleeuro (LOCAL=NO)

Here again, It can be seen that process is running against euro database. Since remote connection was made, LOCAL=NO can be seen in the connection string

I hope this post was useful. Your comments and sugegstions are always welcome!

————————————————————————————————-

Home

Tuning Index

——————