SQL to find the SQL,SID , long running , CPU, I/O
Script – Find SQL being executed by a OS Process ID (PID)
prompt "Please Enter The UNIX Process ID"
set pagesize 50000
set linesize 30000
set long 500000
set head off
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;
script used to find out cpu used by this session
SELECT substr(a.sid,1,5) "Sid",
substr(a.process,1,7) "Process",
nvl(a.USERNAME,'ORACLE PROC') "user",
v.value "CPU used by this session"
FROM v$statname s, v$sesstat v, v$session a
WHERE s.name = 'CPU used by this session'
and v.statistic#=s.statistic#
AND v.sid = a.sid
and v.value > 0
ORDER BY v.value DESC;
Top 10 cpu heavy SQLs
set pagesize 999
set linesize 150
col cpu_time format 9999999999 heading "Cputime"
col sql_text format a80 heading "Sqltext"
select cpu_time,sql_text from v$sqlarea where rownum<10 order by cpu_time desc
Top 10 I/O heavy SQLs
set pagesize 60
set linesize 140
col reads_per_exec format 99999999 heading "Reads Per Exec"
col disk_reads format 99999999 heading "Disk Reads"
col executions format 999999999 heading "Executions"
col sql_text format a70 heading "Sql Text"
select cast(disk_reads/(executions+1) as integer) as reads_per_exec,disk_reads,executions,sql_text
from V$SQLAREA where disk_reads/(executions+1) > 1 and executions > 1
and rownum<10
order by disk_reads/(executions+1) desc;
Displays the SQL statements for currently running processes
SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SELECT s.sid,
s.status "Status",
p.spid "Process",
s.schemaname "Schema Name",
s.osuser "OS User",
Substr(a.sql_text,1,120) "SQL Text",
s.program "Program"
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr;
Displays a list of SQL statements that are using the most resources
@top_sql (number
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT *
FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= &&1;
This one shows SQL that is currently "ACTIVE":-
select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
/
This shows locks. Sometimes things are going slow, but it's because it is blocked waiting for a lock:
select
object_name,
object_type,
session_id,
type, -- Type or system/user lock
lmode, -- lock mode in which session holds lock
request,
block,
ctime -- Time since current mode was granted
from
v$locked_object, all_objects, v$lock
where
v$locked_object.object_id = all_objects.object_id AND
v$lock.id1 = all_objects.object_id AND
v$lock.sid = v$locked_object.session_id
order by
session_id, ctime desc, object_name
/
This is a good one for finding long operations (e.g. full table scans). If it is because of lots of short operations, nothing will show up.
COLUMN percent FORMAT 999.99
SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1
/
it will give you queries currently running for more than 60 seconds.
Note that it prints multiple lines per running query if the SQL has multiple lines.
Look at the sid,serial# to see what belongs together.
select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s
join v$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece
column username format 'a10'
column osuser format 'a10'
column module format 'a16'
column program_name format 'a20'
column program format 'a20'
column machine format 'a20'
column action format 'a20'
column sid format '9999'
column serial# format '99999'
column spid format '99999'
set linesize 200
set pagesize 30
select
a.sid,a.serial#,a.username,a.osuser,c.start_time,
b.spid,a.status,a.machine,
a.action,a.module,a.program
from
v$session a, v$process b, v$transaction c,
v$sqlarea s
Where
a.paddr = b.addr
and a.saddr = c.ses_addr
and a.sql_address = s.address (+)
and to_date(c.start_time,'mm/dd/yy hh24:mi:ss') <= sysdate - (15/1440) -- running for 15 minutes
order by c.start_time
/
Step 2: desc v$session
Step 3:select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR from v$session where sid='xxxx' //(enter the sid value)
Step 4: select sql_text from v$sqltext where address='XXXXXXXX';
Step 5: select piece, sql_text from v$sqltext where address='XXXXXX' order by piece;
he following query will give the current running query with status in Oracle 10g.
Code :
SELECT a.USERNAME, b.sql_text, a.STATUS FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.SQL_ID = b.SQL_ID;
While the following query will give the result in Oracle 9i.
Code :
SELECT a.USERNAME, b.sql_text, a.STATUS FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.SQL_ID = b.SQL_ID;
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time
FROM V$Session
WHERE
Status='ACTIVE' AND
UserName IS NOT NULL;
select s.username, s.sid, s.serial#,t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid = '&sid';
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time
FROM V$Session
WHERE
Status='ACTIVE' AND
UserName IS NOT NULL;
How to Find Locks on Objects
ISSUE:
-----
ORA-00054: resource busy and acquire with NOWAIT specified
Locked sessions on Oracle Database Objects :-
-----------------------------------------------
Query 1:
-------
set pages 50000 lines 32767
SELECT s.inst_id,s.sid || ',' || s.serial# sess_id,
oracle_username || ' (' || s.osuser || ')' os_username,
owner || '.' || object_name,object_type,object_id,
DECODE (l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS,
DECODE (v.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lmode)) LOCK_MODE,
a.sql_text
FROM gv$locked_object v,
dba_objects d,
gv$lock l,
gv$session s
gv$sqlarea a
WHERE v.object_id = d.object_id
AND v.object_id = l.id1
AND v.session_id = s.sid
ORDER BY oracle_username, session_id;
Query 2: (More Information)
-------
set pages 50000 lines 32767
select
vlo.object_id, vlo.session_id, vlo.oracle_username, vlo.process
, DECODE(vlo.LOCKED_MODE,
0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE', NULL) LOCK_MODE,
do.owner, do.object_name, do.object_type
, vs.saddr, vs.serial#, vs.paddr, vs.username, vs.ownerid, vs.status, vs.server, vs.schemaname, vs.osuser, vs.machine, vs.program, vs.type, vs.logon_time, vs.last_call_et, vs.blocking_session_status,
vs.event#, vs.event, vs.wait_class#, vs.wait_class, vs.wait_time, vs.seconds_in_wait, vs.state
from gv$locked_object vlo
inner join dba_objects do on (vlo.object_id = do.object_id)
left outer join gv$session vs on (vlo.session_id = vs.sid);
Query 3:
-------
set pages 50000 lines 32767
SELECT
b.inst_id,a.session_id,b.serial#,b.STATUS,b.machine,a.ORACLE_USERNAME,a.OS_USER_NAME,a.LOCKED_MODE,
c.owner,c.object_name,c.object_type,c.object_id,d.sql_text
FROM
gv$locked_object a,
gv$session b,
dba_objects c,
gv$sqlarea d
WHERE b.sid = a.session_id
AND a.object_id = c.object_id
ORDER BY a.oracle_username, a.session_id;
Query 4:
-------
set pages 50000 lines 32767
SELECT s.inst_id,OS_USER_NAME, ORACLE_USERNAME, s.sid, o.object_name,o.object_type, s.serial#, a.sql_text
FROM gv$locked_object l, dba_objects o, gv$session s, gv$sqlarea a
WHERE l.object_id = o.object_id
AND s.SQL_ADDRESS = a.address
AND l.SESSION_ID = s.sid;
Command to kill the session:
---------------------------
ALTER SYSTEM KILL SESSION 'sid, serial#';
ALTER SYSTEM KILL SESSION 'sid, serial#,@<instance_id>'; (RAC)
Script to Kill all the locks
----------------------------
set pages 50000 lines 32767
SELECT 'ALTER SYSTEM KILL SESSION "'||TO_CHAR(s.sid)||','||TO_CHAR(s.serial#)||"';'
AS "Statement to kill"
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.SESSION_ID = s.sid;
CPU usage of the USER
Displays CPU usage for each User. Useful to understand database load by user.
SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.STATISTIC# = sn.STATISTIC#
AND NAME LIKE '%CPU used by this session%'
AND se.SID = ss.SID
AND ss.status = 'ACTIVE'
AND ss.username IS NOT NULL
ORDER BY VALUE DESC;
Long Query progress in database
Show the progress of long running queries.
SELECT a.sid,
a.serial#,
b.username,
opname OPERATION,
target OBJECT,
TRUNC (elapsed_seconds, 5) "ET (s)",
TO_CHAR (start_time, 'HH24:MI:SS') start_time,
ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
FROM v$session_longops a, v$session b
WHERE a.sid = b.sid
AND b.username NOT IN ('SYS', 'SYSTEM')
AND totalwork > 0
ORDER BY elapsed_seconds;
Get current session id, process id, client process id?
This is for those who wants to do some voodoo magic using process ids and session ids.
SELECT b.sid,
b.serial#,
a.spid processid,
b.process clientpid
FROM v$process a, v$session b
WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');
ind Top 10 SQL by reads per execution
SELECT *
FROM ( SELECT ROWNUM,
SUBSTR (a.sql_text, 1, 200) sql_text,
TRUNC (
a.disk_reads / DECODE (a.executions, 0, 1, a.executions))
reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 3 DESC)
WHERE ROWNUM < 10;
Oracle SQL query over the view that shows actual Oracle connections.
SELECT osuser,
username,
machine,
program
FROM v$session
ORDER BY osuser;
prompt "Please Enter The UNIX Process ID"
set pagesize 50000
set linesize 30000
set long 500000
set head off
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;
script used to find out cpu used by this session
SELECT substr(a.sid,1,5) "Sid",
substr(a.process,1,7) "Process",
nvl(a.USERNAME,'ORACLE PROC') "user",
v.value "CPU used by this session"
FROM v$statname s, v$sesstat v, v$session a
WHERE s.name = 'CPU used by this session'
and v.statistic#=s.statistic#
AND v.sid = a.sid
and v.value > 0
ORDER BY v.value DESC;
Top 10 cpu heavy SQLs
set pagesize 999
set linesize 150
col cpu_time format 9999999999 heading "Cputime"
col sql_text format a80 heading "Sqltext"
select cpu_time,sql_text from v$sqlarea where rownum<10 order by cpu_time desc
Top 10 I/O heavy SQLs
set pagesize 60
set linesize 140
col reads_per_exec format 99999999 heading "Reads Per Exec"
col disk_reads format 99999999 heading "Disk Reads"
col executions format 999999999 heading "Executions"
col sql_text format a70 heading "Sql Text"
select cast(disk_reads/(executions+1) as integer) as reads_per_exec,disk_reads,executions,sql_text
from V$SQLAREA where disk_reads/(executions+1) > 1 and executions > 1
and rownum<10
order by disk_reads/(executions+1) desc;
Displays the SQL statements for currently running processes
SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SELECT s.sid,
s.status "Status",
p.spid "Process",
s.schemaname "Schema Name",
s.osuser "OS User",
Substr(a.sql_text,1,120) "SQL Text",
s.program "Program"
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr;
Displays a list of SQL statements that are using the most resources
@top_sql (number
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT *
FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= &&1;
This one shows SQL that is currently "ACTIVE":-
select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
/
This shows locks. Sometimes things are going slow, but it's because it is blocked waiting for a lock:
select
object_name,
object_type,
session_id,
type, -- Type or system/user lock
lmode, -- lock mode in which session holds lock
request,
block,
ctime -- Time since current mode was granted
from
v$locked_object, all_objects, v$lock
where
v$locked_object.object_id = all_objects.object_id AND
v$lock.id1 = all_objects.object_id AND
v$lock.sid = v$locked_object.session_id
order by
session_id, ctime desc, object_name
/
This is a good one for finding long operations (e.g. full table scans). If it is because of lots of short operations, nothing will show up.
COLUMN percent FORMAT 999.99
SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1
/
it will give you queries currently running for more than 60 seconds.
Note that it prints multiple lines per running query if the SQL has multiple lines.
Look at the sid,serial# to see what belongs together.
select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s
join v$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece
column username format 'a10'
column osuser format 'a10'
column module format 'a16'
column program_name format 'a20'
column program format 'a20'
column machine format 'a20'
column action format 'a20'
column sid format '9999'
column serial# format '99999'
column spid format '99999'
set linesize 200
set pagesize 30
select
a.sid,a.serial#,a.username,a.osuser,c.start_time,
b.spid,a.status,a.machine,
a.action,a.module,a.program
from
v$session a, v$process b, v$transaction c,
v$sqlarea s
Where
a.paddr = b.addr
and a.saddr = c.ses_addr
and a.sql_address = s.address (+)
and to_date(c.start_time,'mm/dd/yy hh24:mi:ss') <= sysdate - (15/1440) -- running for 15 minutes
order by c.start_time
/
Step 2: desc v$session
Step 3:select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR from v$session where sid='xxxx' //(enter the sid value)
Step 4: select sql_text from v$sqltext where address='XXXXXXXX';
Step 5: select piece, sql_text from v$sqltext where address='XXXXXX' order by piece;
he following query will give the current running query with status in Oracle 10g.
Code :
SELECT a.USERNAME, b.sql_text, a.STATUS FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.SQL_ID = b.SQL_ID;
While the following query will give the result in Oracle 9i.
Code :
SELECT a.USERNAME, b.sql_text, a.STATUS FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.SQL_ID = b.SQL_ID;
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time
FROM V$Session
WHERE
Status='ACTIVE' AND
UserName IS NOT NULL;
select s.username, s.sid, s.serial#,t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid = '&sid';
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time
FROM V$Session
WHERE
Status='ACTIVE' AND
UserName IS NOT NULL;
How to Find Locks on Objects
ISSUE:
-----
ORA-00054: resource busy and acquire with NOWAIT specified
Locked sessions on Oracle Database Objects :-
-----------------------------------------------
Query 1:
-------
set pages 50000 lines 32767
SELECT s.inst_id,s.sid || ',' || s.serial# sess_id,
oracle_username || ' (' || s.osuser || ')' os_username,
owner || '.' || object_name,object_type,object_id,
DECODE (l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS,
DECODE (v.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lmode)) LOCK_MODE,
a.sql_text
FROM gv$locked_object v,
dba_objects d,
gv$lock l,
gv$session s
gv$sqlarea a
WHERE v.object_id = d.object_id
AND v.object_id = l.id1
AND v.session_id = s.sid
ORDER BY oracle_username, session_id;
Query 2: (More Information)
-------
set pages 50000 lines 32767
select
vlo.object_id, vlo.session_id, vlo.oracle_username, vlo.process
, DECODE(vlo.LOCKED_MODE,
0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE', NULL) LOCK_MODE,
do.owner, do.object_name, do.object_type
, vs.saddr, vs.serial#, vs.paddr, vs.username, vs.ownerid, vs.status, vs.server, vs.schemaname, vs.osuser, vs.machine, vs.program, vs.type, vs.logon_time, vs.last_call_et, vs.blocking_session_status,
vs.event#, vs.event, vs.wait_class#, vs.wait_class, vs.wait_time, vs.seconds_in_wait, vs.state
from gv$locked_object vlo
inner join dba_objects do on (vlo.object_id = do.object_id)
left outer join gv$session vs on (vlo.session_id = vs.sid);
Query 3:
-------
set pages 50000 lines 32767
SELECT
b.inst_id,a.session_id,b.serial#,b.STATUS,b.machine,a.ORACLE_USERNAME,a.OS_USER_NAME,a.LOCKED_MODE,
c.owner,c.object_name,c.object_type,c.object_id,d.sql_text
FROM
gv$locked_object a,
gv$session b,
dba_objects c,
gv$sqlarea d
WHERE b.sid = a.session_id
AND a.object_id = c.object_id
ORDER BY a.oracle_username, a.session_id;
Query 4:
-------
set pages 50000 lines 32767
SELECT s.inst_id,OS_USER_NAME, ORACLE_USERNAME, s.sid, o.object_name,o.object_type, s.serial#, a.sql_text
FROM gv$locked_object l, dba_objects o, gv$session s, gv$sqlarea a
WHERE l.object_id = o.object_id
AND s.SQL_ADDRESS = a.address
AND l.SESSION_ID = s.sid;
Command to kill the session:
---------------------------
ALTER SYSTEM KILL SESSION 'sid, serial#';
ALTER SYSTEM KILL SESSION 'sid, serial#,@<instance_id>'; (RAC)
Script to Kill all the locks
----------------------------
set pages 50000 lines 32767
SELECT 'ALTER SYSTEM KILL SESSION "'||TO_CHAR(s.sid)||','||TO_CHAR(s.serial#)||"';'
AS "Statement to kill"
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.SESSION_ID = s.sid;
CPU usage of the USER
Displays CPU usage for each User. Useful to understand database load by user.
SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.STATISTIC# = sn.STATISTIC#
AND NAME LIKE '%CPU used by this session%'
AND se.SID = ss.SID
AND ss.status = 'ACTIVE'
AND ss.username IS NOT NULL
ORDER BY VALUE DESC;
Long Query progress in database
Show the progress of long running queries.
SELECT a.sid,
a.serial#,
b.username,
opname OPERATION,
target OBJECT,
TRUNC (elapsed_seconds, 5) "ET (s)",
TO_CHAR (start_time, 'HH24:MI:SS') start_time,
ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
FROM v$session_longops a, v$session b
WHERE a.sid = b.sid
AND b.username NOT IN ('SYS', 'SYSTEM')
AND totalwork > 0
ORDER BY elapsed_seconds;
Get current session id, process id, client process id?
This is for those who wants to do some voodoo magic using process ids and session ids.
SELECT b.sid,
b.serial#,
a.spid processid,
b.process clientpid
FROM v$process a, v$session b
WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');
ind Top 10 SQL by reads per execution
SELECT *
FROM ( SELECT ROWNUM,
SUBSTR (a.sql_text, 1, 200) sql_text,
TRUNC (
a.disk_reads / DECODE (a.executions, 0, 1, a.executions))
reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 3 DESC)
WHERE ROWNUM < 10;
Oracle SQL query over the view that shows actual Oracle connections.
SELECT osuser,
username,
machine,
program
FROM v$session
ORDER BY osuser;
I like this query. Got very detailed information on long running sessions.
ReplyDeleteSELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;