ORACLE DBA Usefull Basic performance related Queries

How to Finding oracle locked blocking objects:

set pages 200

set lines 200

select (select username from v$session where sid=a.sid) blocker, a.sid, ' is blocking ' "IS BLOCKING", (select username from v$session where sid=b.sid) blockee,b.sid from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;

How to find Table lock

select a.sid, a.serial# from v$session a, v$locked_object b, dba_objects c where b.object_id = c.object_id and a.sid = b.session_id and OBJECT_NAME='<Table_Name>'; 

How to identify Client IDENTIFIED in particular session:

set lines 200

set pages 200

col USERNAME for a10

col MODULE for a25

col ACTION for a25

col PROGRAM for a18

col CLIENT_IDENTIFIER for a15

select sid,serial#,USERNAME,CLIENT_IDENTIFIER,status,LAST_CALL_ET/60,SQL_HASH_VALUE,module,action,program from v$session where sid='&n';

 

How to identify SQL_HASH_VALUE in SID:

set lines 200

set pages 200

col MODULE for a30

col ACTION for a35

col PROGRAM for a18

select sid,serial#,status,LAST_CALL_ET/60,SQL_HASH_VALUE,module,action,program from v$session where sid='&n';

 

How to find SQL TEXT:

select sql_text from v$sql where hash_value='<HASH_VALUE>'; 

How to get pid from sid?

select b.spid,a.sid, a.serial#,a.username, a.osuser

from v$session a, v$process b

where a.paddr= b.addr

and b.spid='6514'

 

How to find sqlid?

1) select * from v$session where sid='749'

2) select a.sid,a.program,b.sql_text

from v$session a, v$sqltext b

where a.sql_hash_value = b.hash_value

and a.sid='749'

order by a.sid,hash_value,piece;

 

How to kill the particular session:

ALTER SYSTEM KILL SESSION 'sid,serial#';

 

Finding Long Running queries

 

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

 

How to find query progressing/not

User below query and give SID and execute 2 time if values are changing query is progressing

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%';