Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Wednesday, July 2, 2008

Slow running ontjup09.sql during 11.5.10.2 maintenance pack

ontjup09.sql is one of the slow running sqls during 11.5.10.2 upgrade. For this Oracle has given patch 4480056 in Metalink Note 331790.1. However this doesn't solve the issue completely. This is described in Bug 6673695. We faced the same issue during upgrade in an environment in which we merged 4480056 with 3480000 (maintenance pack patch). Even though the ontjup09.sql being called was the latest, the ontjup09.sql took more than 2 hours.

$ adident Header $ONT_TOP/patch/115/sql/ontjup09.sql
$ONT_TOP/patch/115/sql/ontjup09.sql:
$Header ontjup09.sql 115.0.11510.2 2005/07/28 19:45:08 jvicenti noship $

$ adident Header $ONT_TOP/patch/115/sql/ontjup07.sql
$ONT_TOP/patch/115/sql/ontjup07.sql:
$Header ontjup07.sql 115.4.11510.2 2005/07/21 00:54:30 jvicenti ship $

This could be due to the fact that oe_order_history table was 12 GB in size.

Makes me wonder why updates to history table can't be done in advance before the actual downtime. I am logging an SR with Oracle to find out if this can be done for future upgrades.

As indicated by one of the readers, this issue also occurs when you are upgrading to R12.  It has been apparently fixed in R12.0.6.  To people who are upgrading to a lower R12, you should download the SCM patch 7292303 and extract the ontjup07.sql from that patch.  Replace your ontjup07.sql with the one supplied with this patch.

2 comments:

Amar said...

We are ugarding our Apps from 11.5.9 to 12.0.4 and the script ontjup07.sql is taking 12 hrs to run. Any tips to reduce the time?

Thanks
Amar

Vikram Das said...

Hi Amar,

Oracle has given patch 4480056 for 11i this known issue. However you are upgrading to R12. For R12 there is an open bug I am pasting it for your reference here:

Bug No. 7137977
Filed 29-MAY-2008 Updated 01-DEC-2008
Product Oracle Order Management Product Version 12.0.4
Platform Sun Solaris SPARC (64-bit) Platform Version 10
Database Version 10.2.0.3.0 Affects Platforms Generic
Severity Severe Loss of Service Status Q/A to Dev/Patch or Workaround Avble
Base Bug N/A Fixed in Product Version 12.0.6

Problem statement:

R12: PERFORMANCE DEGRADATION ONTJUP07.SQL - SAME AS 4480056



*** 05/29/08 02:14 pm *** (ADD: Impact/Symptom->INSTALL/UPGRADE )

*** 05/29/08 02:14 pm ***

Tar Number: SR:6901338.993

BUG TYPE CHOSEN

---------------

Code

PROBLEM STATEMENT:

------------------

-- Problem Statement:

R12 Upgrade Script ontjup07.sql has a Performance Degradation (40hours).

.

Found note 331790.1 but the approach described is for 11.5.10 upgrade.

.

R12 version of ontjup07.sql:

REM $Header: ontjup07.sql 120.1 2006/01/25 14:19:09 spagadal noship $

.

This file does not include a parallism like it is described in note 331790.1.

.

.

So for a 11.5.10 upgrade there is a solution - but what to do on a R12

upgrade?

.

Verified the issue as noted below:

.

Version of ontjup07.sql:

REM $Header: ontjup07.sql 120.1 2006/01/25 14:19:09 spagadal noship $

.

This sql does not contain the ad_parallel_updates_pkg.initialize_rowid_range

as the ontjup09.sql in 11i.

*** 05/29/08 02:14 pm ***

*** 05/30/08 05:44 pm *** (CHG: Comp->CONTROL SubComp->VERSIONING)

*** 06/01/08 09:42 pm *** (CHG: Asg->NEW OWNER)

*** 06/01/08 11:22 pm *** (CHG: Sta->30)

*** 06/01/08 11:22 pm ***

*** 06/02/08 08:01 am ***

*** 06/03/08 06:09 am *** (CHG: Sta->11)

*** 06/03/08 06:09 am ***

*** 06/03/08 11:01 pm ***

*** 06/03/08 11:13 pm ***

*** 06/04/08 09:32 pm *** (CHG: FixBy->12.0.5)

*** 06/04/08 09:43 pm ***

++================ Bug closing comments by Development - Start ==============

++ 1) Bug Summary

++ A. Functional

++ 1. Description

-> Performance issue with upgrade script ontjup07.sql

++ 2. Resolution

-> The issue is fixed

++ 2) Bug Closing Status: 37

->

++ 3) Files modified or created (one file per line):

++

++ File (include directory) Version

++ ------------------------------------------- ------------------

-> $ont/patch/115/sql/ontjup07.sql 120.1.12000000.2

->

++ 4) ARUs released and/or bugs logged for other releases:

++

++ Release Bug Number Patchset/MiniPack Name Family Pack Name

++ -------- ----------- ------------------------ ----------------

->

++ 6) Does documentation need to be modified (Yes/No)? : No

++ If Yes, documentation bug number:

++ 7) Is this fix dependent on any other bugs, files, or data (Yes/No)? No

++ If Yes, list dependencies :

->

->

++================ Bug closing comments by Development - End ===============

*** 06/04/08 09:44 pm *** (CHG: Sta->37)

*** 06/05/08 05:49 am ***

*** 06/06/08 05:58 am ***

*** 06/16/08 11:07 pm *** (CHG: Sta->11 Asg->NEW OWNER)

*** 06/16/08 11:07 pm ***

*** 06/17/08 05:15 am ***

*** 06/17/08 05:15 am ***

*** 06/17/08 05:16 am *** (CHG: Fixed->12.0.5)

*** 06/17/08 05:16 am *** (CHG: Sta->81)

*** 06/17/08 05:16 am ***

*** 06/17/08 05:17 am ***

*** 07/20/08 09:26 pm *** (CHG: FixBy->12.0.6)

*** 07/20/08 09:26 pm ***

*** 09/02/08 06:18 am *** (CHG: Sta->11)

*** 09/02/08 06:18 am ***

*** 09/03/08 09:47 pm *** (CHG: Fixed->12.0.6)

*** 09/03/08 09:47 pm *** (CHG: Sta->81)

*** 09/03/08 09:47 pm ***

*** 09/04/08 02:44 am *** (CHG: Sta->11)

*** 09/04/08 02:44 am ***

*** 09/04/08 03:17 am *** (CHG: Asg->NEW OWNER)

*** 09/04/08 03:17 am ***

*** 09/11/08 03:56 am ***

Customer asks if the ontupj07.sql script can be sipped after the english

upgrade driver is finished and then the patch from this Bug applied. This

then allows the script to run normally.



Is this a valid workaround until a valid 11i patch is produced?

.



*** 09/12/08 02:21 am *** (CHG: Fixed->12.0.6)

*** 09/12/08 02:21 am *** (CHG: Sta->81)

*** 09/12/08 02:21 am ***

*** 10/15/08 03:37 am ***

*** 10/29/08 02:45 am ***

*** 11/01/08 03:05 am ***

*** 11/03/08 01:33 pm ***

*** 11/05/08 01:39 am ***

*** 12/01/08 10:48 pm ***

- Vikram