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%';
Comments
Post a Comment