Performance Query

What are the queries that are running?

select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
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
/

Get the rows fetched, if there is difference it means processing is happening

select b.name, a.value vlu
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and sid =&sid
and a.value != 0
and b.name like '%row%'


Get the sql_hash_value
select sql_hash_value from v$session where sid='&sid';
SQL> select sql_hash_value from v$session where sid='&sid';
Enter value for sid: 1075
old 1: select sql_hash_value from v$session where sid='&sid'
new 1: select sql_hash_value from v$session where sid='1075'
SQL_HASH_VALUE
--------------
928832585

Get the sql_Text
SQL> select sql_text v$sql from v$sql where hash_value =&Enter_Hash_Value;
Enter value for enter_hash_value: 928832585


Get the explain_plan
set lines 190
col XMS_PLAN_STEP format a40
set pages 100
select
case when access_predicates is not null then 'A' else ' ' end ||
case when filter_predicates is not null then 'F' else ' ' end xms_pred,
id xms_id,
lpad(' ',depth*1,' ')||operation || ' ' || options xms_plan_step,
object_name xms_object_name,
cost xms_opt_cost,
cardinality xms_opt_card,
bytes xms_opt_bytes,
optimizer xms_optimizer
from
v$sql_plan
where
hash_value in (&SQL_HASH_VALUE)
and to_char(child_number) like '%';

Time Remaining to complete the current task

set lines 150
col username format a20
col opname format a30
col target format a40
select sid,opname,target,
to_char(start_time,'DD-MON-YY HH24:MI') START_TIME,
time_remaining/60 "Time Remaining in Mins",username
from v$session_longops where time_remaining>1 order by time_remaining
/

what sessions are active this SQL will help

select sid,
to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,
username,
type,
status,
process,
sql_address,
sql_hash_value
from v$session
where username is not null


Work remaining

select V1.sid, V1.serial#, V2.USERNAME, V2.OSUSER, substr(V1.opname,1,10),
to_char(V1.start_time, 'HH24:MI:SS') AS Started, (V1.SOFAR/V1.TOTALWORK)*100 AS
Pct_completed
FROM V$SESSION_LONGOPS V1, V$SESSION V2
WHERE V1.SID= V2.SID AND V1.SERIAL#=V2.SERIAL#
AND (SOFAR/TOTALWORK)*100 < 100
AND TOTALWORK > 0
/
n Memory usage

SELECT username, value/(1024*1024) "Current session memory MB", sess.sid,sess.status
FROM v$session sess, v$sesstat stat, v$statname name

WHERE sess.sid = stat.sid
AND stat.statistic# = name.statistic#
AND name.name like '%memory%'
and username = 'APPS'
-- and sess.status='ACTIVE'
order by 2,4 asc

Script – Monitor sessions with high Physical Reads

set linesize 120
col osuser format a10
col username format 10
select
OSUSER osuser,
username,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS;

Script – List SQL being executed by a particular SID





col sql_text format a100 heading "Current SQL"
select q.sql_text
from v$session s
, v$sql q
WHERE s.sql_address = q.address
and s.sql_hash_value + DECODE
(SIGN(s.sql_hash_value), -1, POWER( 2, 32), 0) = q.hash_value
AND s.sid=&1;
more scripts
http://gavinsoorma.com/category/scripts/
The following select will show all transactions that started more then half an 
hour ago (1/48 of a day) 


select t.start_time, t.used_ublk, s.username, s.sid, s.serial#, r.segment_id, r.segment_name
  from v$transaction t, v$session s, dba_rollback_segs r
  where t.ses_addr = s.saddr 
    and t.xidusn = r.segment_id
    and t.start_date < sysdate-1/48;