------------------------------------------------------------------------
-- Victor Khalturin
--
-- Version 22:54 01-Jun-2022 (001)
--
-- dbt_proutil.sql - Processes Utilization per User
--
-- The DBT_PROUTIL summarize processes utilization in a database per a user.
------------------------------------------------------------------------

SET PAGES 999
SET LINES 200
COL USERNAME FOR A30
COL PCT FOR 990D0

WITH PROCESSES AS (
SELECT NVL(S.USERNAME,'BACKGROUND') USERNAME,
       COUNT(NVL(S.USERNAME,'BACKGROUND')) PROCESSES#
FROM
       V$SESSION S,
       V$PROCESS P
WHERE
       S.PADDR=P.ADDR
GROUP BY
       NVL(S.USERNAME,'BACKGROUND'))
SELECT USERNAME,
       PROCESSES#,
       ROUND((PROCESSES#/SUM(PROCESSES#) OVER ())*100,1) PCT
FROM
       PROCESSES
ORDER BY 2;

------------------------------------------------------------------------
-- Version 22:54 01-Jun-2022 (001)
------------------------------------------------------------------------