Re: Oracle Apps concurrent program issue

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Thu, 2 Apr 2015 17:54:06 -0700
Message-ID: <CAHDOOG5gQENRwfNG-2wJjPcoE0-xQ7xo+yM2KhKJK88f4H0f+A_at_mail.gmail.com>

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 - 02:54:06 CEST