-
whos running what
I want to know who is slowing the database.
Someone is running the query from hell.
I know wich query, but i dont know who is running it.
Could someone give me a query to find top bad sql statement and to fing who is running it?
I have statspack and some query i use to find bad sql statement.
select a.disk_reads,a.buffer_gets, B.SQL_TEXT
from v$sqlarea a,v$sqltext B
where A.disk_reads > 10000
AND A.ADDRESS = B.ADDRESS
order by A.disk_reads desc, b.piece;
This give me top sql statement to tune. But i dont know who.
I want to tell that user to stop, cuse hes slowing the entire database.
-
Check the V$SESSION_LONGOPS.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Join V$SQLTEXT to V$SESSION (V$SQLTEXT.ADRESS = v$SESSION.SQL_ADDRESS) to see which session is running which SQL statement.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
This showsql from Tom which I ran while running 2 sessions gives me the 'Shoot to kill' liscense...
Code:
C:\WINNT\system32>sqlplus
SQL*Plus: Release 9.2.0.3.0 - Production on Tue Sep 30 21:29:27 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: appdev@nick817
Enter password:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
appdev@NICK817.TARRY.LOCAL> column status format a10
appdev@NICK817.TARRY.LOCAL> set feedback off
appdev@NICK817.TARRY.LOCAL> set serveroutput on
appdev@NICK817.TARRY.LOCAL>
appdev@NICK817.TARRY.LOCAL> select username, sid, serial#, process, status
2 from v$session
3 where username is not null
4 /
USERNAME SID SERIAL# PROCESS STATUS
------------------------------ ---------- ---------- --------- ----------
SCOTT 8 31 3548:1896 INACTIVE
APPDEV 9 23 964:1776 ACTIVE
appdev@NICK817.TARRY.LOCAL>
appdev@NICK817.TARRY.LOCAL> column username format a20
appdev@NICK817.TARRY.LOCAL> column sql_text format a55 word_wrapped
appdev@NICK817.TARRY.LOCAL>
appdev@NICK817.TARRY.LOCAL> set serveroutput on size 1000000
appdev@NICK817.TARRY.LOCAL> declare
2 x number;
3 begin
4 for x in
5 ( select username||'('||sid||','||serial#||
6 ') ospid = ' || process ||
7 ' program = ' || program username,
8 to_char(LOGON_TIME,' Day HH24:MI') logon_time,
9 to_char(sysdate,' Day HH24:MI') current_time,
10 sql_address, LAST_CALL_ET
11 from v$session
12 where status = 'ACTIVE'
13 and rawtohex(sql_address) <> '00'
14 and username is not null order by last_call_et )
15 loop
16 for y in ( select max(decode(piece,0,sql_text,null)) ||
17 max(decode(piece,1,sql_text,null)) ||
18 max(decode(piece,2,sql_text,null)) ||
19 max(decode(piece,3,sql_text,null))
20 sql_text
21 from v$sqltext_with_newlines
22 where address = x.sql_address
23 and piece < 4)
24 loop
25 if ( y.sql_text not like '%listener.get_cmd%' and
26 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
27 then
28 dbms_output.put_line( '--------------------' );
29 dbms_output.put_line( x.username );
30 dbms_output.put_line( x.logon_time || ' ' ||
31 x.current_time||
32 ' last et = ' ||
33 x.LAST_CALL_ET);
34 dbms_output.put_line(
35 substr( y.sql_text, 1, 250 ) );
36 end if;
37 end loop;
38 end loop;
39 end;
40 /
--------------------
APPDEV(9,23) ospid = 964:1776 program = sqlplus.exe
Tuesday 21:29 Tuesday 21:29 last et = 0
SELECT USERNAME || '(' || SID || ',' || SERIAL# || ') ospid = ' || PROCESS || ' program = '
|| PROGRAM USERNAME,TO_CHAR(LOGON_TIME,' Day HH24:MI') LOGON_TIME,TO_CHAR(SYSDATE,' Day HH24:MI')
CURRENT_TIME,SQL_ADDRESS,LAST_CALL_ET FROM V$SESSI
appdev@NICK817.TARRY.LOCAL>
appdev@NICK817.TARRY.LOCAL>
appdev@NICK817.TARRY.LOCAL>
appdev@NICK817.TARRY.LOCAL> /
--------------------
APPDEV(9,23) ospid = 964:1776 program = sqlplus.exe
Tuesday 21:29 Tuesday 21:30 last et = 0
SELECT USERNAME || '(' || SID || ',' || SERIAL# || ') ospid = ' || PROCESS || ' program = '
|| PROGRAM USERNAME,TO_CHAR(LOGON_TIME,' Day HH24:MI') LOGON_TIME,TO_CHAR(SYSDATE,' Day HH24:MI')
CURRENT_TIME,SQL_ADDRESS,LAST_CALL_ET FROM V$SESSI
appdev@NICK817.TARRY.LOCAL>
appdev@NICK817.TARRY.LOCAL>
appdev@NICK817.TARRY.LOCAL> select username||'('||sid||','||serial#||')' username,
2 module,
3 action,
4 client_info
5 from v$session
6 where module||action||client_info is not null;
USERNAME MODULE
-------------------- ------------------------------------------------
ACTION CLIENT_INFO
-------------------------------- ----------------------------------------------------------------
SCOTT(8,31) SQL*Plus
APPDEV(9,23) SQL*Plus
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|