RE: Oracle Tuning Question

If you run this script, it will show you the machine name.

Jared

On Sat, 17 Jun 2000, Ali Murtaza 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
> >
> >
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon Received on Mon Jun 19 2000 - 00:04:26 CDT