All good advice.
AND, you can probably identify the submitting user from the concurrent manager
tables. That user can probably tell you whether this was a “normal” sized batch.
A bigger batch can do a couple things that slow correct processing down:
1) Just being bigger (more rows, linear increase)
2) Spilling from memory sorts (including groupbys and union non-alls) to
temp on disk
Program pathways: something about your data triggers data validation on lots of
rows. An old example was journal import with dynamic account creation on; until
the first new account was encountered accounts were just check for existence in
the code combinations table but once a no-find occurred the rules checking took
place before an attempt to insert the new account. For large batches the
difference could be substantial. (That’s just an example.)
So asking the submitter of the job whether anything is different about this
batch often useful, especially if you can do it being clear this is an
exploration of possibilities and not an accusation they have done something
wrong. Next time around, solved or not, knowing the job name and program might
be useful to the thread.
Unpurged accumulated workflow history and errors. I’ve seen this go for YEARS
as a slowly growing problem. A new slug of errors from some other process can
also affect your truly unmodified programs with no new data conditions and
average size.
Good luck.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Tim Gorman
Sent: Thursday, April 02, 2015 8:40 AM
To: ORACLE-L
Subject: Re: Oracle Apps concurrent program issue
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
