EBS 12.2 -- ADOP PREPARE -hangs- while generating report to display the status of important tablespaces / ADZDSHOWTS.sql

Recently dealt with an issue in my forum. It was about patching an EBS 12.2 instance with A;DOP.

The issue was super clear -> Before starting the online patching cycle, ADOP prepare was executed, but the execution was never completed.

Just for the record, the environment was an EBS 12.2.5 consisting of 2 apps nodes (shared appl top) and 2 db nodes (active-active Oracle RAC)

The log/out was showing that ADOP was executing ADZDSHOWTS.sql and it was just waiting there.

[EVENT] Generating report for key tablespaces.
[PROCEDURE] [START 2023/05/11 15:27:38] Generating tablespaces report
[PROCEDURE] [START 2023/05/11 15:27:39] Generating report to display the status of important tablespaces. (ie. free space)
[STATEMENT] Report: /u01/UATERM/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWTS.sql

The SQL was a standard one (a standard EBS AD SQL), and it was not expected to run slow actually..

SELECT df.tablespace_name , round((df.total_space/(1024*10
24*1024)),2) total_space , round(((df.total_space - fs.free_sp
ace)/(1024*1024*1024)),2) used_space , round((fs.free_space/(1
024*1024*1024)),2) free_space , round(((df.total_space-fs.free
_space) / df.total_space)*100, 2) PCT_USED FROM ( SELECT tab
lespace_name, SUM(bytes) TOTAL_SPACE FROM dba_data_files
GROUP BY tablespace_name) df , ( SELECT tablespace_name,
SUM(bytes) FREE_SPACE FROM dba_free_space GROUP BY t
ablespace_name) fs WHERE df.tablespace_name = fs.tablespac
e_name(+) and df.tablespace_name in ('SYSTEM', 'APPS_T
S_SEED', 'APPS_TS_TX_DATA', 'APPS_TS_TX_IDX') ORDER BY fs.tables
pace_name

Moreover; ADZDSHOWTS.sql was not generating an output (adzdshowts.out), clearly it was waiting in the database, and the wait event of its session was "db file sequential read". It was visiting the internal tables (X$) through indexes and nothing seemed weird, but! checking the execution plan gave us some clues.

The following 2 MOS noted visited for this;

Queries on DBA_FREE_SPACE are Slow (Doc ID 271169.1)
Space Related Dictionary View Performance Slow in Multitenant Database with Large Number of Pluggable Databases (Doc ID 2053791.1)

But! the actual solution was *collecting the fixed table stats and flushing the shared pool.

Remember -> Fixed objects are the "X$" tables and their indexes. Those v$ views are defined in top of X$ tables. So if you don't have any stats for them (not configured any auto stats jobs -- considering you are above 12C, cause prior to 12C fixed object stats are not included in auto stats gathering job), there are some defaults and optimizer uses those defaults to build its plans. 

However; sometimes as in this case, this may create you some troubles, so collecting the fixed object stats ( when there's a representative workload on the database) is a crucial thing especially after a major database or application upgrade or enabling a new module, or making changes to the database configuration. 

That's the tip of the day. I hope you 'll find it useful.