How do we see what transaction is currently running on the database?

This queries are tested on oracle 9.2.0.8

Display all the statement which are available in memory?

Select * from v$sqlarea;

Note : If full SQL text is not shown in V$SQLAREA view, because it was larger than 1000 characters, this V$SQLTEXT view should be queried to extract the full SQL. So you need to use sql_text column in v$sql_text view instead of using sql_text column in v$sqlarea.

Display only currently running transactions in database?

The below query is used to find what is actively running(uncommitted transactions) in database. The column query_active_time is time between transaction starting time and commit time. For instance, the transaction started at 10AM and completed at 10.05AM. But user commits the transaction at 10.30PM. So the query_active_time will be 30 Seconds. V$transaction does not show any SELECT statement which are running in the database.

select
osuser,
username,
sid,
serial#,
program,
machine,
sql_text,
start_time,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 run_time,
to_number(sysdate-to_date(c.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 query_active_time
from
v$session a,
v$sqlarea b,
v$transaction c
where
((a.sql_address = b.address
and a.sql_hash_value = b.hash_value )
or ( a.prev_sql_addr = b.address
and a.prev_hash_value = b.hash_value))
and c.ses_addr = a.saddr
--and sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS') > 2/(60*24)


Display all the statement for only connected users?

This query will display all statement in memory only for connected sessions.

select
osuser,
username,
sid,
serial#,
program,
machine,
sql_text,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 run_time
from
v$session a,
v$sqlarea b
where
((a.sql_address = b.address
and a.sql_hash_value = b.hash_value )
or ( a.prev_sql_addr = b.address
and a.prev_hash_value = b.hash_value))
and username is not null


Display all the statement with I/O statistics for connected session?

The below query provides all the transaction which are in memory with I/0 statistics. Sometime, user might be complaining that their query is Stuck. As a DBA, we can check v$sess_io view and see GETS, READS or CHANGES column continue to increase for a session, then you can tell them that the statement is not stuck.

select
a.sid,
a.username,
optimizer_mode,
hash_value,
address,
cpu_time/1000000,
elapsed_time/1000000,
sql_text,
a.sid,
block_gets,
consistent_gets,
physical_reads,
block_changes,
consistent_changes
from
v$session a,
v$sqlarea b,
v$sess_io c
where
a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.sid = c.sid
and a.username is not null


Note : The I/0 statistics(consistent_gets, physical_reads, block_changes) continue to increase for each statement for the session. It would not show the statistics for individual statement.

Display all the long running statement in memory?

This query provides all the long transactions for connected/disconnected sessions.

Select * from v$session_longops;

Display all the long running completed transactions(connected/disconnected sessions)?

Select * from v$session_longops where time_remaining = 0

Display all the long running active transactions?

Select * from v$session_longops where time_remaining > 0

Display all the long running transactions for connected users with I/0 Statistics?

select
a.sid,
a.username,
optimizer_mode,
hash_value,
address,
cpu_time/1000000,
elapsed_time/1000000,
sql_text,
block_gets,
consistent_gets,
physical_reads,
block_changes,
consistent_changes,
d.time_remaining,
message
from
v$session a,
v$sqlarea b,
v$sess_io c,
v$session_longops d
where
a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.sid = c.sid
and a.sql_hash_value = d.sql_hash_value
and a.sql_address = d.sql_address
and a.username is not null

How do we check which SQL is currently taking more CPU's in UNIX?
First we need to run the top command and top command will show the top CPU session.

Here is the top command partial output.

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
27486 oracle 19 0 603M 603M 601M S 16.9 5.2 35:12 1 oracle

27486 is process id which takes 16.9% CPU time. The process id 27486 is equivalent to v$process.spid in database. So we can join v$process, v$session and get the required information.

Here is the query to find out which query is currently taking high CPU.

select
a.sid,
a.username,
optimizer_mode,
hash_value,
address,
cpu_time/1000000,
elapsed_time/1000000,
sql_text,
a.sid,
block_gets,
consistent_gets,
physical_reads,
block_changes,
consistent_changes
from
v$session a,
v$sqlarea b,
v$sess_io c,
v$process d
wherea.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.sid = c.sid
and d.addr = a.paddr
and a.username is not null
and d.spid= 32479

Note : 32479 is taken from Unix TOP output.