OraFAQ Forum: Oracle Fusion Apps & E-Business Suite » Long running Stored Procedure

Home » Applications » Oracle Fusion Apps & E-Business Suite » Long running Stored Procedure (Oracle 10g R2 /Solaris 64-bit)

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

Long running Stored Procedure [message #483378]

Sun, 21 November 2010 09:44

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Hi All,

We have a concurrent program running for long hours. By using the v$ views, I found that the program was running stored procedure. I was just able to see that it is running. I was unable to drill down as why it is running long. The final view used for drilling was v$sqltext. Can you let me know the steps to rectify the issue?

Regards,
Antony

Report message to a moderator

Re: Long running Stored Procedure [message #483383 is a reply to message #483378]

Sun, 21 November 2010 09:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can you let me know the steps to rectify the issue?
EXACTLY what do you mean by "rectify"?

Match SQL contained in Procedure with SQL existing in V$SQLTEXT.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Long running Stored Procedure [message #483386 is a reply to message #483378]

Sun, 21 November 2010 09:59

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

You need to retrieve the complete SQL statement (which may be several rows in v$sqltext.) Then use the sql_id (which you will also have from v$sqltext) of the statement with the function dbms_xplan.display_cursor to retrieve trhe execution plan. If you post both the statement and the execution plan here, someone may be able to help. With the information so far, no-one can advise.

Report message to a moderator

Re: Long running Stored Procedure [message #483394 is a reply to message #483386]

Sun, 21 November 2010 12:29

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

One example I can show is

Code: [Select all] [Show/ hide]

SQL> select sql_text from v$sqltext where sql_id='82hxvr8kxuzjq';

SQL_TEXT
----------------------------------------------------------------
BEGIN dbms_stats.gather_database_stats; END;

SQL>

How can I drill down from here ?

Report message to a moderator

Re: Long running Stored Procedure [message #483396 is a reply to message #483394]

Sun, 21 November 2010 12:43

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Do you know what this procedure dbms_stats.gather_database_stats does? Gathering stats should be done at off peak hours as they generally take huge resources.It takes time depending on database size , sampling, parallelism option , CPUs etc.

Regards
Ved

[Updated on: Sun, 21 November 2010 12:47]

Report message to a moderator

Re: Long running Stored Procedure [message #483397 is a reply to message #483396]

Sun, 21 November 2010 12:49

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Yes this gathers the statistics of all the objects in the database.

My doubt is, when I execute some other programmer's procedure also, I get a similar output where the procedure name is given with some parameters. How can i know, what is happening the Oracle server right now with this procedure?

Report message to a moderator

Re: Long running Stored Procedure [message #483398 is a reply to message #483396]

Sun, 21 November 2010 12:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How can I drill down from here ?
What additional details do you hope/desire to obtain?

Was this procedure invoked by local user or by Oracle default SCHEDULER job?

Report message to a moderator

Re: Long running Stored Procedure [message #483399 is a reply to message #483398]

Sun, 21 November 2010 12:58

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

What would that procedure do at that point of time? Will it do any DML in any of the tables? Or will transfer data from one table to another? Or is it running for a long time due to some invalid indexes? How do I identify that?

This might be a basic question. But I am beginner for performance tuning. Please advise.

Report message to a moderator

Re: Long running Stored Procedure [message #483400 is a reply to message #483398]

Sun, 21 November 2010 13:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How can i know, what is happening the Oracle server right now with this procedure?

use DBMS_SYSTEM to enable SQL_TRACE so the trace file will contains details; but non-trivial to decipher

DBA_SOURCE shows content of procedure.

Report message to a moderator

Re: Long running Stored Procedure [message #483403 is a reply to message #483394]

Sun, 21 November 2010 14:25

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

Your first post made it sound as though you had identified the statement that is running for hours, but this
jesuisantony wrote on Sun, 21 November 2010 12:29

One example I can show is
Code: [Select all] [Show/ hide]
SQL> select sql_text from v$sqltext where sql_id='82hxvr8kxuzjq'; SQL_TEXT ---------------------------------------------------------------- BEGIN dbms_stats.gather_database_stats; END; SQL>
How can I drill down from here ?

implies that you do NOT know which statement it is. Did you choose to show this statement for any particular reason? Or is it just one of thousands in v$sqltext?

You do know which concurrent request it is that is running for hours, so can you post the code that it is running, with the request log file and output file?

Report message to a moderator

Re: Long running Stored Procedure [message #483415 is a reply to message #483403]

Mon, 22 November 2010 00:03

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

@BlackSwan: Let me know if I am wrong. The SQL_TRACE should be enabled for the period of time the procedure runs, but the procedure runs for a long time, is it that i can do anything on the run itself?

@John: The statement was retrieved after getting the spid and paddr information. I don't see any error message in either log file. The log file says like 'No Action Required'.Out file is not generated.

Report message to a moderator

Re: Long running Stored Procedure [message #483416 is a reply to message #483415]

Mon, 22 November 2010 00:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>@BlackSwan: Let me know if I am wrong. The SQL_TRACE should be enabled for the period of time the procedure runs, but the procedure runs for a long time, is it that i can do anything on the run itself?

Most folks do not tune on Production Server.

>is it that i can do anything on the run itself?
enable SQL_TRACE on test system where you can control the environment.
SQL statement does not know or care if it is running on Production server or not.

Report message to a moderator

Re: Long running Stored Procedure [message #483437 is a reply to message #483378]

Mon, 22 November 2010 02:34

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Thanks.... I wanted to make sure that I dont miss any shortcut methods...... I shall check this....

Report message to a moderator

Re: Long running Stored Procedure [message #483479 is a reply to message #483415]

Mon, 22 November 2010 07:12

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

OK, if the job that is running for hours is invoking DBMS_STATS, you need to look at how the job is set up. Remember that you aren't meant to invoke DBMS_STATS directly, you are meant to invoke it through FND_STATS. Has someone defined a concurrent task that does a full analysis more often than is necessary? A full analysis should be needed only once. FND_STATS is described in your 2System Adminstrators Guide - Configuration" manual.

Report message to a moderator

Re: Long running Stored Procedure [message #483711 is a reply to message #483378]

Wed, 24 November 2010 04:23

KARTHIC01
Messages: 3
Registered: November 2010
Location: CHENNAI

Junior Member

Will it do any DML in any of the tables? Or will transfer data from one table to another? Or is it running for a long time due to some invalid indexes? How do I identify that?

This might be a basic question. But I am beginner for performance tuning. Please advise........................

-----------------------------------------------------------

Report message to a moderator

Re: Long running Stored Procedure [message #483712 is a reply to message #483378]

Wed, 24 November 2010 04:24

KARTHIC01
Messages: 3
Registered: November 2010
Location: CHENNAI

Junior Member

Will it do any DML in any of the tables? Or will transfer data from one table to another? Or is it running for a long time due to some invalid indexes? How do I identify that?

This might be a basic question. But I am beginner for performance tuning. Please advise........................

-----------------------------------------------------------

Report message to a moderator

Re: Long running Stored Procedure [message #483718 is a reply to message #483712]

Wed, 24 November 2010 04:33

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

I am now totally confused (like you). I have asked several times if you know which concurrent manager request it is, and what the request does: you say you do, but you have NEVER given any detail. How is the request defined? What does it do? What makes you think that it is running DBMS_STATS procedure?

Aloso, you said earlier that you do know what dbms_Stats does, so you should be able to answer your question about whether it does any DML.

Report message to a moderator

Previous Topic:	help for finding Links for form ERP
Next Topic:	Help!!! Roles Invisible to USER after assigning.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 17 06:22:46 CDT 2025