Publish Pending Stats

Env – RAC 5 nodes, AIX 7.2

As part of Database upgrade from 12c(12.1.0.2) to 19c (19.14), we are gathering table stats, split the tables of schemas & running parallel across 5 nodes, and publish pending stats post gather stats. Publish pending stats is taking time ~ 6Hrs in our Performance Testing env (refresh from prod and almost same data). We are trying to reduce the time taken for Publish pending stats. Splitting the schema and running parallel on multiple instances will result in any locking issues?. Please let suggest on this.

*Excerpt from publish pending stats script:-*

############################### List of Schemas #########################

set -A SCHEMA_LIST FDB31_1 FDB31_2 INFORMATICA9_RX NDDF_1 NDDF_2 RXADMIN RXOWNER RXOWNERMIROR_1 RXOWNERMIROR_2 RXSTAGE RX_INTAKE

#########################################################################

for Name in `echo ${SCHEMA_LIST[_at_]}`; do

echo "LIST of Tables to be PUBLISHED stats for schema : ${Name}\n"

sqlplus -s '/ as sysdba' <<-EOF

SET LINES 200 PAGES 1000 TRIMSPOOL ON COL OWNER FOR A15 COL TABLE_NAME FOR A30 COL STATTYPE_LOCKED FOR A5 SELECT A.OWNER,A.TABLE_NAME,A.STATTYPE_LOCKED FROM DBA_TAB_STATISTICS A WHERE A.OWNER='${Name}'

AND A.OBJECT_TYPE='TABLE' AND A.STATTYPE_LOCKED IS NULL AND A.TABLE_NAME IN (SELECT DISTINCT TABLE_NAME FROM DBA_TAB_PENDING_STATS WHERE OWNER='${Name}');

EOF sqlplus -s '/ as sysdba' <<-EOF

WHENEVER SQLERROR EXIT 4 set trimspool on pages 0 head off feedback off lines 300

spool /tmp/publish_stat.sql

SELECT 'EXEC
DBMS_STATS.PUBLISH_PENDING_STATS('||CHR(39)||A.OWNER||CHR(39)||','||CHR(39)||A.TABLE_NAME||CHR(39)||');' FROM DBA_TAB_STATISTICS A WHERE A.OWNER='${Name}'

AND A.OBJECT_TYPE='TABLE' AND A.STATTYPE_LOCKED IS NULL AND A.TABLE_NAME IN (SELECT DISTINCT TABLE_NAME FROM DBA_TAB_PENDING_STATS WHERE OWNER='${Name}');

Thanks,

Mani

--
http://www.freelists.org/webpage/oracle-l