Oracle process using high CPU on the TEC server

Cause

The Oracle process in question will appear as:

oracleTEC (LOCAL=NO)

The process name will actually be oracle<SID>, in the example above, the Oracle SID is 'TEC'. This is the users connection to Oracle, whether it be RIM, or sqlplus, or any other program that logs into Oracle via the Oracle program interface. This process is the Oracle 'User process' and is responsible for manipulating the user's input and communicates with the Oracle server process through the Oracle program interface. This process is also responsible for the display of information requested by the user. The TEC server will spawn at least two connections to the Oracle database via the Framework RIM_Oracle_Agent process which will have a corresponding oracle<SID> process. If the user has sys access to the Oracle database, they can verify which program corresponds to the oracle<SID> process by first running:

ps -ef |grep ora

Mark down the process id's and log into the Oracle database as a user with sysdba privileges and issue:

SELECT a.username,
a.osuser,
a.program,
spid,
sid,
a.serial#
FROM v$session a,
v$process b
WHERE a.paddr = b.addr
AND spid = '&pid';
Enter value for pid:

Once the pid is entered the return will look similar to:

USERNAME OSUSER PROGRAM
------------------------------ ------------------------------ ------------------
TEC nobody RIM_Oracle_Agen@ba

The high CPU utilization for the oracle<SID> process can be caused by the TEC java console refresh interval being set to a very low value, like '1'.

This will cause a select to be run against the TEC databases TEC_V_CONSOLE_LIST table every second. Essentially, any program or user that is running a select, or multiple selects every second will have their corresponding Oracle user process go up in CPU.

Below is a loop that can be set up to in oracle to emulate this type of behavior:

sqlplus@dasbier (TSQL>
Note that the program that is responsible for the oracledasbier process is 'sqlplus'. If the console refresh interval set to 1 second, this means that every second a query, or queries (depending on the event groups) will be run on the TEC_V_CONSOLE_LIST view. Taking Tivoli completely out of the picture, and using the same process id (from the sqlplus connection).
From the sql prompt if I run a very simple select statement in a loop:
SQL> DECLARE
2 i NUMBER := 1;
3 tempvar varchar(255);
4 BEGIN
5 LOOP
6 SELECT count(*) INTO tempvar from tec_t_evt_rep;
7 i := i + 1;
8 EXIT WHEN I > 10000;
9 END LOOP;
10 END;
11 .
SQL> run;

And in another window check the ps output for the oracledasbier process which corresponds to the sqlplus session:

while :
do
ps -ef |grep 34526 | grep -v grep
done

The CPU usage for this process goes way up:
oracle 34526 1 0 12:20:01 - 0:17 oracledasbier (LOCAL=NO)
oracle 34526 1 0 12:20:01 - 0:17 oracledasbier (LOCAL=NO)
(Hit enter in the sqlplus prompt to run the loop)
oracle 34526 1 0 12:20:01 - 0:17 oracledasbier (LOCAL=NO)
oracle 34526 1 7 12:20:01 - 0:17 oracledasbier (LOCAL=NO)
oracle 34526 1 80 12:20:01 - 0:18 oracledasbier (LOCAL=NO)
oracle 34526 1 120 12:20:01 - 0:19 oracledasbier (LOCAL=NO)
oracle 34526 1 60 12:20:01 - 0:20 oracledasbier (LOCAL=NO)
oracle 34526 1 66 12:20:01 - 0:21 oracledasbier (LOCAL=NO)
oracle 34526 1 83 12:20:01 - 0:22 oracledasbier (LOCAL=NO)
oracle 34526 1 65 12:20:01 - 0:22 oracledasbier (LOCAL=NO)
oracle 34526 1 32 12:20:01 - 0:22 oracledasbier (LOCAL=NO)
oracle 34526 1 16 12:20:01 - 0:22 oracledasbier (LOCAL=NO)
oracle 34526 1 4 12:20:01 - 0:22 oracledasbier (LOCAL=NO)
oracle 34526 1 2 12:20:01 - 0:22 oracledasbier (LOCAL=NO)
oracle 34526 1 1 12:20:01 - 0:22 oracledasbier (LOCAL=NO)
oracle 34526 1 0 12:20:01 - 0:22 oracledasbier (LOCAL=NO)
oracle 34526 1 0 12:20:01 - 0:22 oracledasbier (LOCAL=NO)
oracle 34526 1 0 12:20:01 - 0:22 oracledasbier (LOCAL=NO)

Essentially, any program or user that is running a select, or multiple selects every second will have their corresponding Oracle user process go up in CPU.