Oracle - CPU used by a database user on per session basis

CPU used by a database user on per session basis on Oracle
DECLARE
v_session_cpu NUMBER;
v_system_cpu NUMBER;
v_cpu_ratio NUMBER;
v_sid NUMBER;
v_serial NUMBER;
v_osuser v$session.osuser%type;
v_username v$session.username%type;
my_exception EXCEPTION;
CURSOR sess IS
SELECT sid,serial#,username
FROM v$session
WHERE username is not null
ORDER BY username;

BEGIN
OPEN sess;
FETCH sess into v_sid,v_serial,v_username;
IF sess%notfound THEN
RAISE no_data_found;
END IF;

LOOP
SELECT value
INTO v_session_cpu
FROM v$sesstat
WHERE sid = v_sid
AND statistic# = 12;

SELECT value
INTO v_system_cpu
FROM v$sysstat
WHERE name = 'CPU used by this session';

v_cpu_ratio := ROUND((v_session_cpu/v_system_cpu)*100,2);

dbms_output.put_line('CPU used by user '||v_username||'('||
''''||v_sid||','||v_serial||''''||') => '||v_cpu_ratio||'%' );

FETCH sess into v_sid,v_serial,v_username;
IF sess%notfound THEN
RAISE my_exception;
END IF;
END LOOP;
CLOSE sess;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No users connected');
close sess;
WHEN my_exception THEN
null;
CLOSE sess;
END;
/

How to tell which IPC resouces belongs to which instance

Chunyu Hu's Weblog - How to tell which IPC resouces belongs to which instance: It's quite common that there are more than 1 Oracle instance on 1 host. For example,we run many standby databases instances on 1 host for cost reason.

Sometimes, we fail to restart some Oracle instance because the IPC resources allocated for it are not cleared up. We can run ipcs to list all the IPC resouces on the host, but the problem is that we have to find only the IPC resources allocated to the specific instance and remove them manually before we restart the specific instance.

sysresv
Oracle provides sysresv to tell out which instance has which IPC resources. Unfortunately, we find sysresv is not reliable many times.

oradebug ipc
SQL>oradebug ipc
On Solaris, if you run this command, it will generate a trace file under the user_dump_dest directory. The trace file will list all the shared memory segment and semaphores. By checking all the in-use IPC resouces for all running Oracle instances and the result from ipcs command, you can know which IPC resouces you can safely to remove.