Mandalika's scratchpad [ Work blog @Oracle | Stock Market Notes | My Music Compositions ]

Old Posts: 09.04  10.04  11.04  12.04  01.05  02.05  03.05  04.05  05.05  06.05  07.05  08.05  09.05  10.05  11.05  12.05  01.06  02.06  03.06  04.06  05.06  06.06  07.06  08.06  09.06  10.06  11.06  12.06  01.07  02.07  03.07  04.07  05.07  06.07  08.07  09.07  10.07  11.07  12.07  01.08  02.08  03.08  04.08  05.08  06.08  07.08  08.08  09.08  10.08  11.08  12.08  01.09  02.09  03.09  04.09  05.09  06.09  07.09  08.09  09.09  10.09  11.09  12.09  01.10  02.10  03.10  04.10  05.10  06.10  07.10  08.10  09.10  10.10  11.10  12.10  01.11  02.11  03.11  04.11  05.11  07.11  08.11  09.11  10.11  11.11  12.11  01.12  02.12  03.12  04.12  05.12  06.12  07.12  08.12  09.12  10.12  11.12  12.12  01.13  02.13  03.13  04.13  05.13  06.13  07.13  08.13  09.13  10.13  11.13  12.13  01.14  02.14  03.14  04.14  05.14  06.14  07.14  09.14  10.14  11.14  12.14  01.15  02.15  03.15  04.15  06.15  09.15  12.15  01.16  03.16  04.16  05.16  06.16  07.16  08.16  09.16  12.16  01.17  02.17  03.17  04.17  06.17  07.17 


Wednesday, March 21, 2007
 
Oracle Apps on T2000: ORA-04020 during Autoinvoice

The goal of this brief blog post is to provide a quick solution to all Sun-Oracle customers who may run into a deadlock when a handful of threads are running Autoinvoice Master Program (RAXMTR) on SunFire CoolThreads server, T2000.

If you are not happy with Autoinvoice performance, or if you feel like the AI program is crawling* when multiple threads are handling the submitted jobs, check the trace files generated in udump directory for a pattern like 'ORA-04020: deadlock detected while trying to lock object ...'. If there is a deadlock, you may see something like:

*** ACTION NAME:(Concurrent Request) 2007-03-20 17:55:25.238
*** MODULE NAME:(RAXMTR) 2007-03-20 17:55:25.238
*** SERVICE NAME:(SYS$USERS) 2007-03-20 17:55:25.238
*** SESSION ID:(2469.90) 2007-03-20 17:55:25.238

A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:

ORA-04020: deadlock detected while trying to lock object AR.RA_INTERFACE_LINES_ALL

--------------------------------------------------------
object waiting waiting blocking blocking
handle session lock mode session lock mode
-------- -------- -------- ---- -------- -------- ----
457bf9a78 479d813f0 46dddbd10 X 47fd9ce08 468973ee0 S
46561f560 47fd9ce08 46894f520 X 479d813f0 46dc4edf8 X
--------------------------------------------------------
...
...

Setting profile option AR: Autoinvoice Gather Statistics to Yes or leaving it to the default value of NULL is one of the reasons for this deadlock. When the profile option is set to either Yes or NULL, by default it runs Autoinvoice Gather Statistics every time Autoinvoice program is run.

Running Autoinvoice Gather Statistics process is too expensive; and it is not necessary to run this process as part of the Autoinvoice process. Hence Autoinvoice Gather Statistics can be safely turned off as part of a normal Autoinvoice processing exercise.

To implement this, simply set the profile option AR: Autoinvoice Gather Statistics to No. Navigation: System Administrator responsibility -> Profile -> System.

Perhaps this solution is applicable to all other platforms where this deadlock issue shows up with quite a few threads running Autoinvoice (RAXMTR). I'm not sure - I never tried it.
________

*Run the following SQL to get the list of Autoinvoice request IDs, request date along with the actual start and completion times.
SELECT REQUEST_ID,
TO_CHAR (REQUEST_DATE, 'yyyymmddhh24mi') "REQUEST DATE",
TO_CHAR (ACTUAL_START_DATE, 'yyyymmddhh24mi') "START DATE",
TO_CHAR (ACTUAL_COMPLETION_DATE, 'yyyymmddhh24mi') "COMPLETION DATE"
FROM FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID IN
(SELECT REQUEST_ID
FROM APPLSYS.FND_CONCURRENT_REQUESTS FCR, APPLSYS.FND_CONCURRENT_PROGRAMS FCP
WHERE FCP.CONCURRENT_PROGRAM_ID = FCR.CONCURRENT_PROGRAM_ID
AND FCP.CONCURRENT_PROGRAM_NAME = 'RAXTRX'
AND ACTUAL_COMPLETION_DATE > <desired_date>);
________
| | | |


Comments: Post a Comment

Links to this post:

Create a Link



<< Home


2004-2017 

This page is powered by Blogger. Isn't yours?