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
Received on Sat Jun 17 2000 - 04:06:11 CDT
