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