RE: Oracle Tuning Question

Yeah u are right. The script below would only run for currently executing queries but that served to resolve my problem as i could run it while the users were running the applications. One thing more i found that more than one query in v$sqlarea matched the address and hash_value in v$session for the same user and machine, a phenomena which i could not fully understand. However it would be an interesting problem to trace a SQL statement history by machine/terminal uptil last machine/terminal who ran it. Anyone with fresh ideas?
Murtaza.

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).