Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Slowness at marketing instance operations

Avatar

Level 4

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

1 Reply

Avatar

Employee Advisor

Which instance is being affected? your on-premise marketing instance? for these kind of issues only Adobe TechOps can help.

 

You could liaise with your db administrator to correlate database performance during execution of this particular procedure or setup a monitoring job to check for long running SQL query executions to find out if there is a pattern that can be tracked down to workflows execution which could be running heavy queries. Furthermore you may perform a database health check while at it to check for index fragmentation in the big tables.

 

Also, what are the settings on the deployment wizard for data purging? are they on default or have these settings been increase which means you may have more data in the metadata tables. what are your database server hardware specs? is it a VM on the cloud or a physical server ?

 

David__Garcia_0-1638513426226.png

 

 

 

Avatar

Level 4

Hi David,

 

    Thank you so much for your support and help. I totally missed your reply and i will get back to you on your questions shortly

 

Thanks