Hi Team,

    We are facing slowness in our marketing instance and somehow we are suspecting that slowness is due to gather statistics is taking longer time to complete on broadlog table and because of this mid workflow is taking time to complete and also leads to slow downs the delivery analysis and message preparation processes.

As part of this slowness analysis we are looking to understand on what scenarios below procedures will get called especially the table gather stats procedure (up_UpdateTableStats). Is there any particular scenarios like when ever sql records crossed some amount of then this procedure needs to call? This info is very helpful for us to find the root cause.

----------------------------------
-- Other Functions --
----------------------------------

-- stored procedure called to update database statistics
-- can be changed accoring to your preferences as a DBA
create or replace PROCEDURE up_UpdateStats(sSchema varchar2) is
begin

DBMS_STATS.GATHER_SCHEMA_STATS(ownname => sSchema);

END;
/

-- stored procedure called to update table statistics
-- can be changed accoring to your preferences as a DBA
CREATE OR REPLACE PROCEDURE up_UpdateTableStats(sSchema varchar2, sTable varchar2, iPercent INTEGER) IS
BEGIN

IF UPPER(sTable) IN ('TMPBROADCAST', 'TMPBROADCASTPAPER') THEN
DBMS_STATS.SET_TABLE_STATS(USER, 'TMPBROADCAST', numrows=> 2000000, numblks=>40000);
ELSE
DBMS_STATS.GATHER_TABLE_STATS(ownname => sSchema,
tabname => sTable,
estimate_percent => iPercent,
cascade => true);
END IF;
END;
/

Note: We are also looking some answers for below questions that is helpful to learn the things.

1) On what scenarios, mid sourcing delivery logs wf will become slow and will this workflow get timeout due to slowness in internal query/gather stats executions

2) We are looking some precise points on scenarios when a slowness will raise in adobe campaign application.

3) We are also suspecting that DB pooling settings will cause slowness, do we have option to optimize the DB pooling parameters and how/where to do the changes if it helps to increase the performance.

Please correct me if my understanding is wrong in adobe campaign classic application slowness..

Thanks