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