| 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
|
|
|
|
|
|
| Re: Long running Stored Procedure [message #483386 is a reply to message #483378] |
Sun, 21 November 2010 09:59  |
John Watson
Messages: 8958
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.
|
|
|
|
| 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
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 ?
|
|
|
|
| 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?
|
|
|
|
|
|
| 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.
|
|
|
|
|
|
| Re: Long running Stored Procedure [message #483403 is a reply to message #483394] |
Sun, 21 November 2010 14:25 |
John Watson
Messages: 8958
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:29One example I can show is
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?
|
|
|
|
| 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.
|
|
|
|
|
|
|
|
| Re: Long running Stored Procedure [message #483479 is a reply to message #483415] |
Mon, 22 November 2010 07:12 |
John Watson
Messages: 8958
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.
|
|
|
|
|
|
|
|
| Re: Long running Stored Procedure [message #483718 is a reply to message #483712] |
Wed, 24 November 2010 04:33 |
John Watson
Messages: 8958
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
