During an R12.2 upgrade, the worker icxr12pd.sql takes very long time. This is document in My Oracle Support Article ICXR12PD.Sql Runs Slowly During Upgrade to 12.1.1 (Doc ID 864898.1)
Cause: The pre-upgrade steps for iProcurement were not implemented as documented in the upgrade guide
Though this article talks about R12.1.1, it also applies to R12.2. If you read the 11i to R12.2 upgrade guide ( http://docs.oracle.com/cd/V39571_01/current/acrobat/122upg11i.pdf ). it has specifically mentioned on page 285:
iProcurement
Completing these tasks could substantially reduce the downtime required for your
upgrade.
The Catalog Data Pre-upgrade Process
This pre-upgrade process is strongly recommended if you are upgrading iProcurement
from 11.5.9 or 11.5.10 to Release 12. It pre-processes bulk-loaded content to reduce the
actual time required for the upgrade and to ensure the upgrade process runs smoothly.
You can run it multiple times. If exceptions are found, then make corrections and re-run
the program until no exceptions are noted. Running this program does not require users
to log off the system. Note: Exceptions noted and not fixed before the upgrade will not
be available in the iProcurement Catalog. Specifically, this program shortens the time it
takes to run these upgrade scripts:
• -icxiftug.sql: updates POR_TEMPLATE_INFO
• -icxr12in.sql: general setup upgrade script needed for iProcurement
• -poxujpoh.sql:updates PO_HEADERS_ALL (for example, create_language)
• -poxukpol.sql: updates PO_LINES_ALL (for example, ip_category_id)
• -poxukrt.sql: updates PO_REQEXPRESS_LINES_ALL (for example, ip_catetory_id)
• -icxr12ug.sql: migrates iProcurement data model to PO
• -icxr12rt.sql: populates the requisition templates in iProcurement intermedia index
tables
• -icxr12pd.sql: populates the Purchasing documents in iProcurement intermedia
index tables: blanket purchase agreements (BPAs), global blanket agreements
(GBPAs), and quotes
• -icxr12mi.sql: populates the master items in iProcurement intermedia index tables
• -poxukfi.sql: purchasing script to approve the GBPAs created during iProcurement
migration
• -poxukat.sql: purchasing script to update the attachments
• -icxr12fi.sql: final upgrade script, which upgrades favorite lists, purges the BPAs
and GBPAs that are not approved, and creates the intermedia index
Make sure that you complete the tasks described in the upgrade guide, during the pre-upgrade phase, much before actual cutover downtime starts.
If you haven't done this and proceed to upgrade, it is going to take a long time for all the workers which are going to run icxr12pd.sql. Based on my analysis of v$sql_longops, this is the insert query most fired during this worker's execution:
INSERT INTO AP_INV_DISTS_TARGET T1 (BATCH_ID, INVOICE_ID, INVOICE_LINE_NUMBER,
INVOICE_DISTRIBUTION_ID, DISTRIBUTION_LINE_NUMBER, LINE_TYPE_LOOKUP_CODE,
DESCRIPTION, DIST_MATCH_TYPE, ORG_ID, DIST_CODE_COMBINATION_ID, ACCOUNTING_DATE,
PERIOD_NAME, ACCRUAL_POSTED_FLAG, CASH_POSTED_FLAG, AMOUNT_TO_POST,
BASE_AMOUNT_TO_POST, POSTED_FLAG, ACCOUNTING_EVENT_ID, SET_OF_BOOKS_ID, AMOUNT,
BASE_AMOUNT, EXCHANGE_DATE, ROUNDING_AMT, QUANTITY_VARIANCE, BASE_QUANTITY_VARIANCE,
MATCH_STATUS_FLAG, ENCUMBERED_FLAG, PACKET_ID, USSGL_TRANSACTION_CODE,
USSGL_TRX_CODE_CONTEXT, REVERSAL_FLAG, PARENT_REVERSAL_ID, CANCELLED_FLAG, INCOME_TAX_REGION,
TYPE_1099, STAT_AMOUNT, CHARGE_APPLICABLE_TO_DIST_ID, PREPAY_AMOUNT_REMAINING,
PREPAY_DISTRIBUTION_ID, PREPAY_TAX_PARENT_ID, PARENT_INVOICE_ID, CORRECTED_QUANTITY,
PO_DISTRIBUTION_ID, RCV_TRANSACTION_ID, UNIT_PRICE, MATCHED_UOM_LOOKUP_CODE, QUANTITY_INVOICED,
FINAL_MATCH_FLAG, RELATED_ID, ASSETS_ADDITION_FLAG, ASSETS_TRACKING_FLAG, PROJECT_ID, TASK_ID, EXPENDITURE_T
In our test environment, we have 25 million + rows in ap_inv_dists_target. We have already waited for 8 hours for all the workers to complete. Here's the status of the queries:
set pages 300 lines 300
col "Username" for a12
col "QC/Slave" for A8
col "Slaveset" for A8
col "Slave INST" for A9
col "QC SID" for A6
col "QC INST" for A6
col "operation_name" for A30
col "target" for A30
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(px.inst_id) "Slave INST",
substr(opname,1,30) operation_name,
substr(target,1,30) target,
sofar,
totalwork,
units,
start_time,
timestamp,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST"
from gv$px_session px,
gv$px_process pp,
gv$session_longops s
where px.sid=s.sid
and px.serial#=s.serial#
and px.inst_id = s.inst_id
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
order by
decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),
px.QCSID,
decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
px.SERVER_SET,
px.INST_ID
/
Username
QC/Slave SlaveSet Slave INS OPERATION_NAME TARGET
SOFAR TOTALWORK UNITS
START_TIME TIMESTAMP QC SID QC INS
------------
-------- -------- --------- ------------------------------
------------------------------ ---------- ----------
-------------------------------- --------------- --------------- ------
------
APPS QC 1 Table Scan
AP.AP_INVOICE_DISTRIBUTIONS_AL 1570329 1570329 Blocks
08-FEB-14 2335
APPS
QC 1 Table Scan
AP.AP_INVOICE_DISTRIBUTIONS_AL 1570329 1570329 Blocks
08-FEB-14 2335
APPS QC
1 Table Scan
AP.AP_INVOICE_DISTRIBUTIONS_AL 1570329 1570329 Blocks
08-FEB-14 2335
APPS QC
1 Table Scan
AP.AP_INVOICE_DISTRIBUTIONS_AL 1570329 1570329 Blocks
08-FEB-14 2335
APPS QC
1 Gather Table's Index Statistic
0 0 Indexes
08-FEB-14 2335
APPS QC 1
Table Scan AP.AP_INVOICE_DISTRIBUTIONS_AL
1570329 1570329 Blocks 08-FEB-14
2335
APPS QC 1 Table Scan
AP.AP_INVOICE_DISTRIBUTIONS_AL 1570329 1570329
Blocks 08-FEB-14 2335
7 rows selected.
Posts
