19c DB Bug in ADZDDBCC.sql – Online Patching Readiness Checker – Poor Performance

As of EBS ICM Application DBA Online Patching ADOP Analyzer version 200.39

ADOP Analyzer was not updated for 19c databases:

Per 19c Database with Oracle E-Business Suite R12 Known Issues & Solutions ( Doc ID 2662860.1 )

Running ADZDDBCC.sql in 19c database is taking time for Section 26.

Before running report set parameter _px_cdb_view_enabled to false .
alter session set “_px_cdb_view_enabled”=false ;

This is run both as part of 12.2 upgrade steps, and whenever you log an SR for ATG/ICM.

Work-around – add the “ALTER SESSION” command towards the top of the ADZDDBCC.sql – otherwise, on a 19c database, you’ll discover that when the dependency check portion runs, which looks like:
SELECT col.owner,
col.table_name,
col.column_name,
col.data_type_owner,
col.data_type
FROM dba_tables tab, dba_tab_columns col
WHERE tab.owner IN (SELECT oracle_username
FROM fnd_oracle_userid
WHERE read_only_flag IN (‘A’,
‘B’,
‘E’,
‘U’))
AND col.owner = tab.owner
AND col.table_name = tab.table_name
AND col.data_type_owner NOT IN (‘APPS_NE’,
‘SYS’,
‘MDSYS’,
‘SYSTEM’)
AND NOT EXISTS
(SELECT 1
FROM SYSTEM.fnd_oracle_userid fou,
fnd_product_installations fpi,
ad_obsolete_objects aoo
WHERE fpi.application_id = aoo.application_id
AND fou.oracle_id = fpi.oracle_id
AND fou.oracle_username = tab.owner
AND aoo.object_name = tab.table_name
AND aoo.object_type = ‘TABLE’)
AND EXISTS
(SELECT NULL
FROM fnd_oracle_userid au, dba_users du
WHERE au.oracle_username = du.username
AND au.read_only_flag = ‘U’
AND du.editions_enabled = ‘Y’)
<snip>

on 19c databases, you start seeing PX queue waits for every row fetch.

Adding the alter session avoids the queue/dequeue process occurring.

James Lui
2022 OATUG President
OATUG Board of Directors

Advertisement

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.