In Oracle databases running on Linux, you can access the relevant session information from each user processes. In most cases, it is necessary to detect queries that use excessive resources in the operating system. For this determination, the process id of the corresponding process on linux can be matched with the session’s spid information in the database.
For example, as in the example below, a process number 32370 consumes too much cpu. It is necessary to determine the session to which this process belongs and to arrange the related query.
You can use the following query to find out which query is working on this process and what the session information is.
|
1 2 3 4 5 |
SQL> select t.sql_id,t.sql_text from gv$process p , gv$session s , gv$sqlarea t where p.addr=s.paddr and s.sql_address=t.address and s.sql_hash_value=t.hash_value and p.spid = 32370; |
You can get the sql id information that is returned from the query and you can create a tunning task and improve the query if possible.
