Saturday, April 2, 2016

Currently Running sqls.....

I was working on identifying the currently running sql statements.
Here are interesting facts I found and thought sharing with you all.
Active Sessions:
SELECT      username
         || '('
         || SID
         || ','
         || serial#
         || ') ospid = '
         || process
         || ' command = '
         || command
         || ' program = '
         || program username,
         TO_CHAR (logon_time, ' Day HH24:MI') logon_time,
         TO_CHAR (SYSDATE, ' Day HH24:MI') current_time, sql_address,
         last_call_et, SID, paddr
    FROM v$session
   WHERE status = 'ACTIVE'
     AND RAWTOHEX (sql_address) != '00'
     AND username IS NOT NULL
ORDER BY last_call_et
last_call_et field from v$session;
If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.
Currently running sql's:
SELECT s.sid, s.status, s.process, s.osuser, a.sql_text, p.program
  FROM v$session s, v$sqlarea a, v$process p
WHERE s.PREV_HASH_VALUE = a.hash_value
   AND s.PREV_SQL_ADDR = a.address   AND s.paddr = p.addr
   AND s.status = 'ACTIVE';
More precise way…
select sesion.sid,       sesion.serial#,       sesion.username,
       sesion.sql_id,       sesion.sql_child_number,       optimizer_mode,
       hash_value,       address,       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null;

The above queries will provide the information about all the sql which is currently being executed. With respect to the Oracle Applications if we want to find out what sql being currently executed for a particular request. 
Here is the way.    1) Get the SID from the Request id
                                 2) Get sql from the sid.
The first query will help us getting the sid,serial# from the request id, and other two will fetch the currently running sqls for the given sid. 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select
   'Concurrent request number :'||a.request_id ,
   'Oracle session id(SID)    :'||d.sid,
   'Serial Hash value         :'||d.serial#,
   'OS process id (SPID)      :'||c.spid
from
   applsys.fnd_concurrent_requests a,
   applsys.fnd_concurrent_processes b,
   v$process c, 
   v$session d
where
   a.controlling_manager=b.concurrent_process_id
   and c.pid=b.oracle_process_id   and c.addr=d.paddr
   and a.request_id=&RequestId   and a.phase_code='R';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select sesion.sid,       sesion.serial#,
       sesion.username,       sesion.sql_id,
       sesion.sql_child_number,       optimizer_mode,
       hash_value,       address,       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null
   and sesion.sid=&sid;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT sesion.username, 
       sesion.sql_id,        sesion.sql_child_number,        sql_text
  FROM v$sqltext sqltext, v$session sesion
 WHERE sesion.sql_hash_value = sqltext.hash_value
   AND sesion.sql_address = sqltext.address
   AND sesion.username IS NOT NULL
   AND sesion.SID = &sid;
Here I would like to mention few things about the different view available for the v$sql*
1.V$SQL
2.V$SQLTEXT
3.V$SQLAREA
4.V$SQLTEXT_WITH_NEWLINES
V$SQL: V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.
V$SQLAREA : V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
V$SQLTEXT : This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.
V$SQLTEXT_WITH_NEWLINES : This view is identical to the V$SQLTEXT view except that, to improve legibility, V$SQLTEXT_WITH_NEWLINES does not replace newlines and tabs in the SQL statement with spaces.
V$SQL_PLAN : V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.

In other words….
V$SQL – Will have the multiple copies of the query. (Details)
V$SQLAREA – Is aggregate  of v$sql.  It selects out DISTINCT sql.
V$SQLTEXT  -- is simply a way to see the entire query.  the v$sql and v$sqlarea views only show the first 1000 bytes.  newlines and other control characters are replace with whitespace.
V$SQLTEXT_WITH_NEWLINES is v$sqltext without the whitespace replacement.

So whenever you need to identify the currently running sql you can do this way. And helped you understand  use for various v$sql* views.

~~ Cheers

No comments :

Post a Comment