RE: Oracle Tuning Question

--0-1189641421-961420339=:10983
Content-Type: text/plain; charset=us-ascii

 That will only give you the info for sql that's currently executing. I thought you wanted to trace back SQL that was previously executed to the last person who ran it. As far as I know, you can only the original parser's oracle user name (not machine or terminal), for SQL in the SGA that's not currently being executed.  

Ali Murtaza <MURTAZA.ALI_at_ICL.com> wrote:

Hi Bradly,
I successfully found the below script at the web site given by u and that solved
my problem. Thanks a lot for ur kind help.

select sa.sql_text,s.username,s.machine,s.program from v$sqlarea sa,v$session s
where s.sql_address=sa.address
and s.sql_hash_value=sa.hash_value
order by sa.buffer_gets desc

Murtaza.

>Ali:
> Have you looked at the scripts offered by dbasupport?
>They should prove useful.
>
>http://www.dbasupport.com/scripts/Administration/Session_Management/

-----Original Message-----
Sent: Saturday, June 17, 2000 5:05 AM
To: Multiple recipients of list ORACLE-L

Hi Jared,
Thank you for this script but I think i was not clear while explaining my problem. Through v$sqlarea view i detected some high resource usage SQL. Now i want to know from which machine or terminal this sql statement is coming from. All end users of the database use the same schema so i want to know from which machine that SQL statement is coming from. I want to know the machine or terminal name so that i can identify which particular application is submitting the high resource SQL statement. By the way that high resource SQL is a cartesian product and our programmers are having difficulty identifying from which application that SQL statement is being submitted. Any help is this regard would be highly appreciated. Thanks,
Murtaza.

-- 
Author: Ali Murtaza
INET: MURTAZA.ALI_at_ICL.com

Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).


---------------------------------
Do You Yahoo!?
Send instant messages with Yahoo! Messenger.
--0-1189641421-961420339=:10983
Content-Type: text/html; charset=us-ascii

<P> That will only give you the&nbsp;info for sql that's currently executing. I thought you wanted to trace back SQL that was previously executed to the last person who ran it. As far as I know, you can only the original parser's oracle user name (not machine or terminal), for SQL in the SGA that's not currently being executed.</P>
<P>&nbsp;</P>
<P><B><I>Ali Murtaza &lt;MURTAZA.ALI_at_ICL.com&gt;</I></B> wrote: <BR></P>
<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px">Hi Bradly,<BR>I successfully found the below script at the web site given by u and that<BR>solved <BR>my problem. Thanks a lot for ur kind help.<BR><BR>select sa.sql_text,s.username,s.machine,s.program<BR>from v$sqlarea sa,v$session s<BR>where s.sql_address=sa.address<BR>and s.sql_hash_value=sa.hash_value<BR>order by sa.buffer_gets desc<BR><BR>Murtaza.<BR><BR>&gt;Ali:<BR>&gt; Have you looked at the scripts offered by dbasupport?<BR>&gt;They should prove useful.<BR>&gt;<BR>&gt;http://www.dbasupport.com/scripts/Administration/Session_Management/<BR><BR><BR><BR>-----Original Message-----<BR>Sent: Saturday, June 17, 2000 5:05 AM<BR>To: Multiple recipients of list ORACLE-L<BR><BR><BR>Hi Jared,<BR>Thank you for this script but I think i was not clear while explaining my<BR>problem. Through v$sqlarea view i detected some high resource usage SQL. Now<BR>i want to know from which machine or terminal!
 this sql statement is coming<BR>from. All end users of the database use the same schema so i want to know<BR>from which machine that SQL statement is coming from. I want to know the<BR>machine or terminal name so that i can identify which particular application<BR>is submitting the high resource SQL statement. By the way that high resource<BR>SQL is a cartesian product and our programmers are having difficulty<BR>identifying from which application that SQL statement is being submitted.<BR>Any help is this regard would be highly appreciated.<BR>Thanks,<BR>Murtaza.<BR><BR>-- <BR>Author: Ali Murtaza<BR>INET: MURTAZA.ALI_at_ICL.com<BR><BR>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, California -- Public Internet access / Mailing Lists<BR>--------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the !
message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).</BLOCKQUOTE><p><br><hr size=1><b>Do You Yahoo!?</b><br>
Send instant messages with Yahoo! Messenger.