To identify SID based on OS PID in the Oracle Database.

Run the following in the first session

-- Session SQL Prompt 

SQL> SET SQLPROMPT "_user'@'SES_1 > "

-- Session ID 

SYS@SES_1 > SELECT DISTINCT SID FROM V$MYSTAT;

       SID
----------
        20

---------------------------------------------------
-- This PL/SQL anonymous block produces CPU Load 
-- on the database. It utilizes only 1 CPU core.
---------------------------------------------------

DECLARE
   l_result NUMBER;
BEGIN
   FOR id IN 1..999999999
   LOOP
      l_result := (id/DBMS_RANDOM.VALUE + id*DBMS_RANDOM.VALUE)/DBMS_RANDOM.VALUE;
   END LOOP;
END;
/

While PL/SQL block is running, issue the top command and find a process with heavy CPU consumption

top - 19:20:43 up  5:37,  2 users,  load average: 1.20, 1.17, 1.16
Tasks: 274 total,   3 running, 210 sleeping,   0 stopped,   0 zombie
%Cpu0  :  0.0 us,  0.3 sy,  0.0 ni, 99.0 id,  0.0 wa,  0.7 hi,  0.0 si,  0.0 st
%Cpu1  : 99.0 us,  0.0 sy,  0.0 ni,  0.0 id,  0.0 wa,  1.0 hi,  0.0 si,  0.0 st
KiB Mem : 10196328 total,  3421432 free,  1426072 used,  5348824 buff/cache
KiB Swap: 16777212 total, 16777212 free,        0 used.  4841204 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
20040 oracle    20   0 3621772 112408 106244 R  98.3  1.1  29:34.28 oracle_20040_ts
  820 root      20   0  274484   7500   6364 S   0.3  0.1   0:15.14 vmtoolsd
 2141 oracle    20   0 1329044 112992  68352 S   0.3  1.1   1:03.13 ohasd.bin
 2364 oracle    20   0  499116  35060  26020 S   0.3  0.3   0:33.10 evmd.bin
 2559 oracle    -2   0 1561204  64588  61292 S   0.3  0.6   2:20.04 asm_vktm_+asm

In this demo, the process 20040 consumes about 100% of CPU1.

In another database session, issue the following query to match the OS PID with a database SID.

SET LONG 200000000
SET LINES 300
SET PAGES 999
COL OSUSER FOR A6
COL DBUSER FOR A6
COL SID FOR 9999
COL SERIAL# FOR 9999999
COL PID FOR A8
COL SQL_ID FOR A15
COL STATUS FOR A10
COL CONNECTED_FROM FOR A20
COL MODULE FOR A10
COL PORT FOR 9999999
COL PROGRAM FOR A20
COL ACTIVE_TIME_SEC FOR 9999999999
COL PARSING_SCHEMA_NAME FOR A20 
COL SQL_FULLTEXT FOR A60

SELECT S.OSUSER,
       S.USERNAME DBUSER,
       S.SID,
       S.SERIAL#,
       P.SPID PID,
       S.SQL_ID,
       S.STATUS,
       S.MACHINE CONNECTED_FROM,
       S.PORT,
       S.PROGRAM,
       S.LAST_CALL_ET ACTIVE_TIME_SEC,
       Q.SQL_FULLTEXT
FROM
       V$SESSION S,
       V$PROCESS P,
       V$SQL Q
WHERE
       S.PADDR=P.ADDR AND
       S.SQL_ID=Q.SQL_ID AND 
       S.SQL_CHILD_NUMBER=Q.CHILD_NUMBER AND
       P.SPID=&PID;

OSUSER DBUSER   SID  SERIAL# PID      SQL_ID          STATUS     CONNECTED_FROM           PORT PROGRAM              ACTIVE_TIME_SEC SQL_FULLTEXT
------ ------ ----- -------- -------- --------------- ---------- -------------------- -------- -------------------- --------------- ------------------------------------------------------------
oracle SYS       20     4233 20040    f1pbykq4q25ms   ACTIVE     dbpilot.vmware.local        0 sqlplus@dbpilot.vmwa             182 DECLARE
                                                                                               re.local (TNS V1-V3)                    l_result NUMBER;
                                                                                                                                    BEGIN
                                                                                                                                       FOR id IN 1..999999999
                                                                                                                                       LOOP
                                                                                                                                          l_result := (id/DBMS_RANDOM.VALUE + id*DBMS_RA
                                                                                                                                    NDOM.VALUE)/DBMS_RANDOM.VALUE;
                                                                                                                                       END LOOP;
                                                                                                                                    END;

In this demo, the process 20040 (PID) is the database session 20 (SID) . The process is executing the SQL with sql_id f1pbykq4q25ms.
The SQL text of the query is the same as what was executed in the first session.

Written At

08 JAN 202220:00

Red Hat Release

7.x x64

Database Release

19.13.0.0.0