Find the program corresponding to the oracle session based on memory usage

Find out the program corresponding to the oracle session based on the memory usage. Generally, it feels that the oracle database is slow. First, find the process with the largest memory and CPU occupied by the AIX operating system, then, the process ID is used to find the corresponding oracle session and program for optimization. Perform the following operations on the AIX operating system: Memory sorting: ps-ealf | head-1; ps-ealf | sort-rn + 9 | head-50CPU sorting: ps aux | head-1; ps aux | sort-rn + 2 | head-20 maximum resource search: ps aux | sort-rn + 2 | head-20 two dynamic views of Oracle: gv $ process: the process here is at the operating system level, and it is also the entry to debug the oracle database; gv $ session: This is the entry to the oracle session, view the corresponding program, and so on. Note: g in front of v indicates the cluster. If multiple instances exist, you can use the gv $ view to conveniently view the instances where the program runs. Procedure: First, find the address number of the Process corresponding to the PID that occupies the largest resource:

select addr from gv$process where spid in (6423194)           select addr from gv$process where spid in (1880090 )

 

Search for the session corresponding to the process and view the corresponding username, machine, program, and so on.

select * from gv$session  where paddr in (select addr from gv$process where spid in ( 1880090))