OraFAQ Forum: Performance Tuning » Need to know Sql ID and text

Home » RDBMS Server » Performance Tuning » Need to know Sql ID and text (HP-UX,Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need to know Sql ID and text [message #544589]

Wed, 22 February 2012 13:12

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

Report message to a moderator

Re: Need to know Sql ID and text [message #544590 is a reply to message #544589]

Wed, 22 February 2012 13:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Report message to a moderator

Re: Need to know Sql ID and text [message #544591 is a reply to message #544589]

Wed, 22 February 2012 13:21

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

Michel Cadot
Messages: 68446
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

Report message to a moderator

Re: Need to know Sql ID and text [message #547458 is a reply to message #544595]

Wed, 14 March 2012 06:18

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 27 23:01:51 CDT 2023