Useful shortcuts for vi editor

Tuesday 11 April 2017

How to find and investigate a process in top command output?

Total connection by machine:
SQL> select machine, count(*) from v$session group by machine order by 2;

Total number of database user who connected:
SQL> select count(1) "NO. Of DB Users Connected", to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') sys_time from v$session where username is NOT NULL;

Define a process(pid) from top:
17831 ora11g .. ora_m000_ALPERDB  

Get detailed info from database:
SQL> select * from v$process where spid = 17831;
-> M000 means MMON Slave Process
-> e.g. pid -> 16, sid -> 555

SQL>
SELECT s.username, osuser, process machine, p.terminal, type, sid, s.serial#, s.program
FROM v$session s, v$process p
WHERE p.pid = 16
AND s.paddr = p.addr;

SQL> select * from gv$session where sid = 555;

Get sql from below code related to example process if it has SQL:
SQL>
SELECT 
      s.sid
     ,s.CLIENT_INFO
     ,s.MACHINE
     ,s.PROGRAM
     ,s.TYPE
     ,s.logon_time
     ,s.osuser
     ,sq.sorts
     ,sq.DISK_READS
     ,sq.BUFFER_GETS
     ,sq.ROWS_PROCESSED
     ,sq.SQLTYPE
     ,sq.SQL_TEXT
 FROM gv$session s    
    , gv$sql sq
WHERE s.SQL_HASH_VALUE = sq.HASH_VALUE
  --AND s.inst_id= 1
  AND s.sid = 555 
  AND sq.inst_id= s.inst_id