RDBMS statistics update

[roro_coeur]

Hello,

In the database cleanup workflow, when XtkCleanup_NoStats is set to 0, it does a "RDBMS statistics update".

What does it mean exactly? Is it some kind of vaccum? I am running ACC on a Oracle DB. So no vaccum functionnality.

Any idea?

Thank you,

[Milan_Vucetic · Accepted solution]

Hi @roro_coeur,

Statistics simply are a form of dynamic metadata that assists the query optimizer in making better decisions. For example, if there are only a dozen rows in a table, then there's no point going to an index to do a lookup; you will always be better off doing a full table scan. But if that same table grows to a million rows, then you will probably be better off using the index.
It is important to remember that statistics are only helpful if they are kept up to date. This can be done with automatic updating statistics or a regularly scheduled (nightly/weekly) update statistics command. Also, index rebuilds will automatically perform a statistics update.

Regards,
Milan

[roro_coeur]

Thank you Milan!

[CedricRey]

Hello @Milan_Vucetic 

Thank you for the explanations.

Also, I have a question about this specific part : in one of my cleanup processes, I have a very long process for this update.

It logs "Mise à jour des statistiques RDBMS effectuée en 10h 1mn 52s", which means "RDBMS statistics update done in 10h". The cleanup takes 11h in total to run, and 10h for the RDBMS stats updates alone.

If I understood correctly, RDBMS deals with FDA, I am wrong ? We used to connect with FDA, but haven't for a while, and all "external database" accounts are disabled. I wonder why the RDBMS takes so long. It seems that we have a configuration problem, but I can be totaly wrong in my understanding of what "RDBMS statistics" is.

Can you confirm that RDBMS is related to FDA, or not at all ?

Thank you very much for your help.

Cedric

[Milan_Vucetic]

Hi @CedricRey,

Honestly, I am not sure but think it does not deal with FDA databases when connectors are disabled. You may check if inside of connectors you pushed all necessary functions towards FDA databases and check if there are improvements.

It depends how often your clean up workflow is scheduled. Mine DB is huge and workflow works every night and never longer than ~2h but you must be consistent here.

Of course it depends how long you keep the data like broadlogs/tracking and other setup in deployment wizards.  You can always approach DB team to extract queries received from the job and try to see which one take long to possible identify the issue.

The XtkCleanup_NoStats option allows you to control the behavior of the storage optimization step of the cleanup workflow.

If the XtkCleanup_NoStats option does not exist or if its value is 0, this will execute the storage optimization in verbose mode (VACUUM VERBOSE ANALYZE) on PostgreSQL and update statistics on all other databases. To make sure that this command is executed, check the PostgreSQL logs. VACUUM will output lines in the format: INFO: vacuuming "public.nmsactivecontact" and ANALYZE will output lines in the format: INFO: analyzing "public.nmsactivecontact".

If the value of the option is 1, statistics updating isn’t executed on any database. The following log line will appear in the workflow logs: Option 'XtkCleanup_NoStats' is set to '1'.

If the value of the option is 2, this will execute the storage analysis in verbose mode (ANALYZE VERBOSE) on PostgreSQL and update statistics on all other databases. To make sure that this command is executed, check the PostgreSQL logs. ANALYZE will output lines in the format: INFO: analyzing "public.nmsactivecontact".

Cheers,

Milan

[CedricRey]

Hi @Milan_Vucetic 

Thanks a lot for your explanations, it will be very useful for the next time.

I had a talk with Adobe consultants and they advised us to disable the RDBMS process with the XtkCleanup_NoStats set to 1, so we did.

Also, I confirm that not related to FDA at all, I don't know why I believed this ?

This appends on an Oracle DB, but with a SQL Server it's not the same.

Anyway, our problem is resolved, thank you a lot for your time and the explanations.

Cheers,

Cédric