Home » RDBMS Server » Performance Tuning » Need to know Sql ID and text (HP-UX,Oracle 10g)
Need to know Sql ID and text [message #544589] Wed, 22 February 2012 13:12 Go to next message
pokhraj_d
Messages: 117
Registered: December 2007
Senior Member
Hi All,

At my prod instance one sql query ran for about 9 hours.
Now the sql query completed with success.

Suddenly our ops team want to know the which sql query was running for 9 hours.

As the query got success no record is being found at session level.

I know the SID.
How Do I get the SQL query ?

Thanks-
P




Re: Need to know Sql ID and text [message #544590 is a reply to message #544589] Wed, 22 February 2012 13:17 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>At my prod instance one sql query ran for about 9 hours.
How do you know this to be true without knowing the SQL itself?

The actual SQL may not be still resident within the DB.

You could query V$SQL.ELAPSED_TIME looking for appropriate large value
Re: Need to know Sql ID and text [message #544591 is a reply to message #544589] Wed, 22 February 2012 13:21 Go to previous messageGo to next message
pokhraj_d
Messages: 117
Registered: December 2007
Senior Member
Hi,

Coz ops team informed that one peoplesoft process is running long time and completed after 9 hours.

It means at the process background the sql query runing for 9 hours.



regards-
P

[Updated on: Wed, 22 February 2012 13:22]

Report message to a moderator

Re: Need to know Sql ID and text [message #544595 is a reply to message #544591] Wed, 22 February 2012 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It means at the process background the sql query runing for 9 hours.

Only if the process is built of one query.

If you have bought the option, query DBA_HIST_ACTIVE_SESS_HISTORY.

Regards
Michel
Re: Need to know Sql ID and text [message #547458 is a reply to message #544595] Wed, 14 March 2012 06:18 Go to previous message
orarule
Messages: 11
Registered: March 2012
Junior Member
ASH report would also show under elapsed time SQL. Uses the table the above poster pointed out.

[Updated on: Wed, 14 March 2012 06:18]

Report message to a moderator

Previous Topic: Oracle sequence performance
Next Topic: join condition in index
Goto Forum:
  


Current Time: Sat Aug 30 21:32:24 CDT 2014

Total time taken to generate the page: 0.13842 seconds