Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle Tuning Question

RE: Oracle Tuning Question

From: Chuck Hamilton <chuck_hamilton_at_yahoo.com>
Date: Mon, 19 Jun 2000 06:05:12 -0700 (PDT)
Message-Id: <10533.109701@fatcity.com>


--0-1681692777-961419912=:20986
Content-Type: text/plain; charset=us-ascii

 I don't believe that information is available without writing some sort of script that polls what's running and watches for that SQL. All that's available from V$SQLAREA or V$SQL would be the original parser.

  Ali Murtaza <MURTAZA.ALI_at_ICL.com> wrote: 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.

> ----------
> From: Jared Still[SMTP:jkstill_at_bcbso.com]
> Sent: Wednesday, June 14, 2000 6:48 PM
> To: Ali Murtaza
> Cc: Multiple recipients of list ORACLE-L
> Subject: Re: Oracle Tuning Question
>
>
> Try this:
>
> select
> s.username,
> s.sid,
> s.serial#,
> p.pid ppid,
> s.status,
> s.machine,
> s.osuser,
> substr(s.program,1,20) client_program,
> s.process client_process,
> substr(p.program,1,20) server_program,
> to_char(p.spid) spid,
> to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time,
> -- idle time
> -- days added to hours
> --( trunc(LAST_CALL_ET/86400) * 24 ) || ':' ||
> -- days separately
> substr('0'||trunc(LAST_CALL_ET/86400),-2,2) || ':' ||
> -- hours
> substr('0'||trunc(mod(LAST_CALL_ET,86400)/3600),-2,2) || ':' ||
> -- minutes
> substr('0'||trunc(mod(mod(LAST_CALL_ET,86400),3600)/60),-2,2) || ':' ||
> --seconds
> substr('0'||mod(mod(mod(LAST_CALL_ET,86400),3600),60),-2,2) idle_time
> from v$session s, v$process p
> where s.username is not null
> -- use outer join to show sniped sessions in
> -- v$session that don't have an OS process
> and p.addr(+) = s.paddr
> -- uncomment to see only your own session
> --and userenv('SESSIONID') = s.audsid
> order by username, sid
> /
>
> Jared
>
> On Wed, 14 Jun 2000, Ali Murtaza wrote:
>
> > Hi Listers,
> > In v$sqlarea view, i want to know from which machine or terminal a
> > particular sql statement was last executed. Basically with sql_text
> column i
> > want to know the machine or terminal from where that sql statement was
> > executed. Can somebody please guide me as to which columns to join with
> > which views to get the above required info.
> > 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).
> >
>
>
> Jared Still
> Certified Oracle DBA and Part Time Perl Evangelist ;-)
> Regence BlueCross BlueShield of Oregon
> jkstill_at_bcbso.com - Work - preferred address
> jkstill_at_teleport.com - private
>
>

-- 
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-1681692777-961419912=:20986
Content-Type: text/html; charset=us-ascii

<P> I don't believe that information is available without writing some sort of&nbsp;script that polls what's running and watches for that SQL. All that's available from V$SQLAREA or V$SQL would be the original parser.</P>
<P>&nbsp; <B><I>Ali Murtaza &lt;MURTAZA.ALI_at_ICL.com&gt;</I></B> wrote: <BR>
<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px">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>&gt; ----------<BR>&gt; From: Jared Still[SMTP:jkstill_at_bcbso.com]<BR>&gt; Sent: Wednesday, June 14, 2000 6:48 !
PM<BR>&gt; To: Ali Murtaza<BR>&gt; Cc: Multiple recipients of list ORACLE-L<BR>&gt; Subject: Re: Oracle Tuning Question<BR>&gt; <BR>&gt; <BR>&gt; Try this:<BR>&gt; <BR>&gt; select<BR>&gt; s.username,<BR>&gt; s.sid,<BR>&gt; s.serial#,<BR>&gt; p.pid ppid,<BR>&gt; s.status,<BR>&gt; s.machine,<BR>&gt; s.osuser,<BR>&gt; substr(s.program,1,20) client_program,<BR>&gt; s.process client_process,<BR>&gt; substr(p.program,1,20) server_program,<BR>&gt; to_char(p.spid) spid,<BR>&gt; to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time,<BR>&gt; -- idle time<BR>&gt; -- days added to hours<BR>&gt; --( trunc(LAST_CALL_ET/86400) * 24 ) || ':' ||<BR>&gt; -- days separately<BR>&gt; substr('0'||trunc(LAST_CALL_ET/86400),-2,2) || ':' ||<BR>&gt; -- hours<BR>&gt; substr('0'||trunc(mod(LAST_CALL_ET,86400)/3600),-2,2) || ':' ||<BR>&gt; -- minutes<BR>&gt; substr('0'||trunc(mod(mod(LAST_CALL_ET,86400),3600)/60),-2,2) || ':' ||<BR>&gt; --seconds<BR>&gt; substr('0'||mod(mod(mod(LAST_CALL_ET,86400),3600),6!
0),-2,2) idle_time<BR>&gt; from v$session s, v$process p<BR>&gt; where s.username is not null<BR>&gt; -- use outer join to show sniped sessions in<BR>&gt; -- v$session that don't have an OS process<BR>&gt; and p.addr(+) = s.paddr<BR>&gt; -- uncomment to see only your own session<BR>&gt; --and userenv('SESSIONID') = s.audsid<BR>&gt; order by username, sid<BR>&gt; /<BR>&gt; <BR>&gt; Jared<BR>&gt; <BR>&gt; On Wed, 14 Jun 2000, Ali Murtaza wrote:<BR>&gt; <BR>&gt; &gt; Hi Listers,<BR>&gt; &gt; In v$sqlarea view, i want to know from which machine or terminal a<BR>&gt; &gt; particular sql statement was last executed. Basically with sql_text<BR>&gt; column i<BR>&gt; &gt; want to know the machine or terminal from where that sql statement was<BR>&gt; &gt; executed. Can somebody please guide me as to which columns to join with<BR>&gt; &gt; which views to get the above required info.<BR>&gt; &gt; Thanks,<BR>&gt; &gt; Murtaza<BR>&gt; &gt; -- <BR>&gt; &gt; Author: Ali Murtaza<BR>&gt; &gt; I!
NET: MURTAZA.ALI_at_ICL.com<BR>&gt; &gt; <BR>&gt; &gt; Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>&gt; &gt; San Diego, California -- Public Internet access / Mailing Lists<BR>&gt; &gt; --------------------------------------------------------------------<BR>&gt; &gt; To REMOVE yourself from this mailing list, send an E-Mail message<BR>&gt; &gt; to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>&gt; &gt; the message BODY, include a line containing: UNSUB ORACLE-L<BR>&gt; &gt; (or the name of mailing list you want to be removed from). You may<BR>&gt; &gt; also send the HELP command for other information (like subscribing).<BR>&gt; &gt; <BR>&gt; <BR>&gt; <BR>&gt; Jared Still<BR>&gt; Certified Oracle DBA and Part Time Perl Evangelist ;-)<BR>&gt; Regence BlueCross BlueShield of Oregon<BR>&gt; jkstill_at_bcbso.com - Work - preferred address<BR>&gt; jkstill_at_teleport.com - private<BR>&gt; <BR>&gt; <BR>-- <BR>Author: Ali Murtaza<BR>INET: MURTAZA.AL!
I_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>
Received on Mon Jun 19 2000 - 08:05:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US