Re: Oracle Apps concurrent program issue

From: Carlos Sierra <carlos.sierra.usa_at_gmail.com>
Date: Fri, 3 Apr 2015 11:26:42 -0400
Message-Id: <93984D6C-738C-4C5A-87C2-47DF27423345_at_gmail.com>

Again. Since you have SQL_ID you may want to use SQLT or SQLd360 to facilitate diagnostics. There are many possible reason for a Plan to change.

Sent from my iPhone

> On Apr 2, 2015, at 20:54, Kumar Madduri <ksmadduri_at_gmail.com> wrote:
> 
> Hello:
> Thank you for all the inputs so far.
> I did actually get the concurrent program name and other details associated  (like program name, module name and even user name).  Running this
> select sql_id, sql_plan_hash_value, count(*) cnt
> from dba_hist_active_sess_history
> where sql_id = 'cd18pgvsvmg8c'
> group by sql_id, SQL_PLAN_HASH_VALUE;
>  
> I found sql_plan_hash_value  2624643529 was executed 4100 times and this was what was running yesterday when the problem happened(can confirm that from the time period).
>  Now the question for me is what caused this bad plan to be produced ? The user says nothing changed on their side. I thinking flushing the sql above helped in making the program complete. But why that bad plan gets generated (I mean how can I track it back to the process that caused this to happen).
>  
> Thank you
> Kumar
> 

>> On Thu, Apr 2, 2015 at 5:39 AM, Tim Gorman <tim_at_evdbt.com> wrote:
>> Kumar,
>>
>> You had found the related rows in v$session and v$active_session_history, so yes you certainly have tied it back to a session. Perhaps not to a person's name and a workstation, but you could check MOS for notes like " When Users Login In Apps Where Is Registered Login Information Stored? [ID 436512.1]" to determine that.
>>
>> Since this is EBiz, that means you have other information such as the PL/SQL package and procedure, as well as the EBiz PROGRAM, MODULE and ACTION, so you should have some idea of what is executing (i.e. interactive user, ConcMgr, report, etc).
>>
>> You can run a query like...
>>
>> select trunc(timestamp) day, sql_plan_hash_value, count(*) cnt
>> from dba_hist_active_sess_history
>> where sql_id = 'value'
>> group by trunc(timestamp)
>> order by day;
>>
>> ...to see if the SQL execution plan (i.e. SQL_PLAN_HASH_VALUE) has changed over time. If it has, then you have the answer to the variability in elapsed time of the SQL. Now you just need to track down the cause.
>>
>> Let us know what you find?
>>
>> Hope this helps...
>>
>> -Tim
>>
>>
>>

>>> On 4/2/15 2:06, Kumar Madduri wrote:
>>> Hello:
>>> A program that normally runs for 30 minutes was running for hours and this what I observed.
>>> 1. From v$session and v$active_session_history noticed             that it was waiting on the same sql (waited short time).
>>> 2. v$session.row_wait_obj#, v$session.row_Wait_file#, v$session.row_wait_block# was pointing to the same object (which was an index). But the rowid that I got from this was also changing periodically (every 5 min)
>>> 3. The wait event was direct path temp read
>>> 4. The program definition was not changed recently
>>> Question is how to proceed further once we find the offending object and rows (index and rows which were changing). I was not able to tie this back to a session.
>>> 
>>> Thank you
>>> Kumar
> 

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 03 2015 - 17:26:42 CEST