Slow cost processing in Protheus, now what? What to do about it?

Sloth crawling slowly along a gravel road, like slow cost processing in Protheus

The ticket always arrives the same way. Cost processing started at seven in the evening, everyone went to bed expecting it to be done by morning, and at six a.m. it is still running. Month-end close stalled, costing chasing, executives asking questions.

And the reaction is always one of these three: restart the service, upsize the machine, or blame the database.

I have seen all three fail at the same client. An environment that doubled vCPU and memory, and the recalculation kept taking the same nine hours. An AppServer restart that “fixed it” for twenty minutes. A DBA taking the heat for a slowdown that was not even in the database.

In the post about slow Protheus we showed the overall map of the layers. Here the zoom is on a single routine, the one that hurts most at month-end close. Because the cost bottleneck is almost never where people look for it.

What the recalculation really does

MATA330 reprocesses inventory movements in the correct chronological order and rewrites the cost into the balances, the SB2, and into the movement files themselves. It reads SB9 as the opening balance for the period, sweeps SD3, builds a work file along the way and keeps writing.

Notice what that means for the database: it is not one monstrous query. It is a huge volume of small, ordered operations, each depending on the previous one. Average cost is chronological by nature. Today’s cost depends on yesterday’s movement.

That changes the kind of problem you have. A heavy report is fixed with an index and fresh statistics. Processing like this is not.

The ping-pong: one record at a time

This is where the cause lives in a huge share of the cases we handle.

Protheus talks to the database through DBAccess. And a lot of code, especially old ADVPL customizations, walks the table in emulated ISAM mode: seek, read one record, decide, write, move to the next. Júlio Wittwer, a TOTVS engineer, has an entire series on his blog explaining this mechanism, and TOTVS’s own query documentation is blunt: access through emulated ISAM is slow and taxes the database, the network and DBAccess itself. The official recommendation is SQL whenever possible.

But the legacy code is there, running every night.

Do the math with me. An SD3 with 8 million movements in the period. A round-trip latency of 1 millisecond between AppServer, DBAccess and the database, which is a good network. One round trip per movement, being generous.

8,000,000 × 1 ms = 2 hours and 13 minutes. Stalled. Waiting on the network.

The database idle, server CPU at 8%, monitoring without a single alert. Nothing is wrong with the parts. What is wrong is the total.

And that is why a bigger machine does not help: there is no work queue waiting for a processor. The time is traveling down the cable.

The stored procedures exist because of this

TOTVS has known about this problem for a long time. So much so that it ships an official package of stored procedures for Inventory and Costs, the P12_xx.SPS file, which installs the MATA330 processing inside the database. The loop stops traveling: instead of millions of round trips, the calculation runs on the other side.

In the performance tips documentation for average cost, installing the procedures is the first recommendation on the list. It is not an exotic optimization, it is the official path.

Now, three traps I run into in the field all the time:

First: outdated SP. Applied an update package and did not reinstall the procedures? The processing may not even complete, and when it does, it runs in slow mode. There are environments that have run for years with a procedure from two releases back.

Second: changed MV_A330GRV? TOTVS’s recommendation is to reinstall the procedures afterwards. Almost nobody does.

Third: an installed procedure is not an efficient procedure forever. It runs inside the database, so it inherits everything that is bad in there: stale statistics, fragmented indexes, dead weight in the tables. The SP takes the network out of the way, but it works no miracles on a poorly maintained database.

How far the database can optimize

This conversation comes up in every crisis meeting: “but the query is fast”.

It is. It is fast eight million times in a row.

The optimizer optimizes one query at a time. Hand it a five-table join over two hundred million rows and it finds a decent plan. Now send the same SELECT by key eight million times and there is nothing left to optimize, each execution is already optimal. It does not see the whole, it does not know that this will repeat all night long, it has no way to turn a loop into a batch. That decision lives in the code.

On the DBA side, two honest paths remain. Reduce the number of round trips, which means a conversation with development or installing the procedures. Or reduce the cost of each round trip: latency between the layers, DBAccess on the same network segment as the database, one firewall hop less along the way.

I have seen removing one network hop between DBAccess and the database pay off more than doubling the machine.

CHAR everywhere, and what it costs

There is something every DBA discovers on day one with Protheus and spends a career managing: almost everything is character.

A date field in ADVPL becomes CHAR(8) in the database, in YYYYMMDD format. An empty date is eight blank spaces. A logical field becomes CHAR(1) with “T” or “F”. And every character field is padded with spaces up to its declared length. This is not forum folklore, it is documented by TOTVS’s own engineering staff.

The database does not know that is a date. To it, it is text.

In practice, the toll shows up like this: the cost period filter becomes a string comparison (it works, because YYYYMMDD sorts correctly, but none of the optimizer’s date intelligence applies). The padding becomes dead IO, a CHAR(15) product code using six positions carries nine spaces in every row, every index, every page read, every backup, and on an SD3 with hundreds of millions of rows that is gigabytes of useless reads per night.

And then the worst of all: implicit conversion. All it takes is one customization comparing a character field with a number and the index stops being used. That full scan that appears out of nowhere in the middle of the night and nobody can trace? Look for this.

You are not going to change the dictionary, and TOTVS will not change it for you. But knowing this changes the hunt: instead of looking for the magic index, you look for the query that broke the index that already existed.

Dead records nobody ever deleted

In Protheus, a deleted record does not leave the table. It stays there, flagged with an asterisk in the D_E_L_E_T_ field.

A ten-year-old database with no maintenance accumulates a frightening amount of this. And the problem is not disk, disk is cheap. The problem is that the index carries this garbage along: more levels, more pages, and every access pays the bill. Statistics count these records, scans read these records, the recalculation steps over them all night long.

Run this on the tables cost processing uses most:

-- Oracle: ajuste o owner e o sufixo da empresa (010, 990...)
SELECT 'SD3' tabela, COUNT(*) total,
       SUM(CASE WHEN D_E_L_E_T_ = '*' THEN 1 ELSE 0 END) mortos,
       ROUND(100 * SUM(CASE WHEN D_E_L_E_T_ = '*' THEN 1 ELSE 0 END)
             / NULLIF(COUNT(*), 0), 2) pct
  FROM SD3010
UNION ALL
SELECT 'SB2', COUNT(*),
       SUM(CASE WHEN D_E_L_E_T_ = '*' THEN 1 ELSE 0 END),
       ROUND(100 * SUM(CASE WHEN D_E_L_E_T_ = '*' THEN 1 ELSE 0 END)
             / NULLIF(COUNT(*), 0), 2)
  FROM SB2010
UNION ALL
SELECT 'SD1', COUNT(*),
       SUM(CASE WHEN D_E_L_E_T_ = '*' THEN 1 ELSE 0 END),
       ROUND(100 * SUM(CASE WHEN D_E_L_E_T_ = '*' THEN 1 ELSE 0 END)
             / NULLIF(COUNT(*), 0), 2)
  FROM SD1010
 ORDER BY 4 DESC;

Above 30% on any of them? Part of your hours may be right there.

Just do not start deleting. Purging in Protheus involves ordering, the dictionary and integrity. A DELETE straight in the database on top of D_E_L_E_T_ is a recipe for disaster, do it the right way or do not do it at all.

The balance tables suffer in silence

SB2 gets hammered with updates all night long during the recalculation. And during accounting, the CQ family, the accounting balance tables such as CQ1 (balance per account per day), takes a flood of inserts, updates and deletes every month-end close.

Result: bloated indexes, half-empty leaves from so many deletes, growing height, and the routine that needs to read all of it in sequence hopping from block to block across the entire disk.

-- Oracle: tamanho e altura dos índices das tabelas do custo
SELECT i.index_name, i.table_name, i.blevel, i.leaf_blocks,
       ROUND(s.bytes/1024/1024) mb, i.last_analyzed
  FROM dba_indexes i
  JOIN dba_segments s
    ON s.owner = i.owner
   AND s.segment_name = i.index_name
 WHERE i.table_name IN ('SB2010', 'SB9010', 'SD3010', 'SD1010', 'CQ1010')
 ORDER BY s.bytes DESC;

BLEVEL climbing and LAST_ANALYZED from months ago on the tables cost processing hammers: that is your signal.

The detail almost everyone gets wrong is not the rebuild, it is its timing and criteria. A maintenance job on Sunday is useless if the close is on the fifth business day and the mess was made on Tuesday. Statistics and indexes have to be current on the eve of the cost window, not on a calendar that was finalized in 2019 and nobody revisited.

And rebuilds cannot be copied. A recipe that worked in someone else’s environment becomes forum folklore, and everyone goes around applying it without measuring. Your mother already warned you: if everyone else jumps off a bridge, will you jump too? You are not everyone else. The volume is yours, the dictionary is yours, the customization is yours, the storage is yours. A smart rebuild is chosen by criteria, on the index that needs it, at the time it needs it, and monitored before and after to prove it was worth it.

The same goes for statistics: it is not gathering for the sake of gathering. It is calibrating sample size and histograms for your data, and watching the plans after the gather. New statistics that turn into a worse plan do exist, and only those who monitor catch the regression before the user feels it.

Inactive sessions in Oracle

A classic, and poorly understood.

DBAccess keeps a connection pool. The processing ends, the sessions stay there with INACTIVE status. And here is the trap: INACTIVE does not mean harmless. It only means the session is not executing a call at this exact moment. It may be holding PGA, holding a temporary segment and, worst case, holding a row lock on SB2 since yesterday because someone left a transaction open and went to lunch.

-- Oracle: quem está ocioso, de onde, há quanto tempo
SELECT s.status, s.machine, s.program,
       COUNT(*) AS sessoes,
       ROUND(MAX(s.last_call_et)/60, 1) AS ocioso_min
FROM v$session s
WHERE s.type = 'USER'
GROUP BY s.status, s.machine, s.program
ORDER BY sessoes DESC;

-- Oracle: quem está travando quem, agora
SELECT s.sid, s.serial#, s.username, s.status,
       s.event, s.seconds_in_wait,
       s.blocking_session, s.sql_id, s.machine
FROM v$session s
WHERE s.blocking_session IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

Now combine that with the fact that restarting the AppServer kills sessions, releases locks and clears the pool. See why the restart “works”? For twenty minutes everything flies. Then it comes back, because the cause is still there.

I use the restart as information, not as a solution: if a restart fixes it, your problem is sessions and locks, not capacity. No new server saves you from that.

Threads: the parameter everyone raises first

There is MV_M330THR, which defines how many threads the cost recalculation and accounting use. When the window overruns, the automatic reaction is to raise that number. 4 became 8, 8 became 16, and the processing… got worse.

Nobody can explain it. But the explanation is in TOTVS’s own documentation, spread across three places:

One: slowness in the recalculation frequently comes down to write contention on SB2. So much so that MV_A330SB2 exists, which makes the routine work on an auxiliary balance table (the TR2xxSP) instead of fighting everyone else for SB2.

Two: the official guidance on threads says to evaluate gradually, in increments of 5, and advises against multi-threading when disk or processor are already at 80 to 90% utilization.

Three: parallelism assumes divisible work. Average cost is chronological and has per-product dependencies. Part of it is serial by nature.

Put the three together: if the bottleneck is lock contention on SB2, more threads means more people fighting over the same row. You did not speed up the processing, you made the queue longer. That is why raising the parameter sometimes makes it slower, and that is why the right order is measure first, change later.

While you are here, the other parameters TOTVS points to for this routine:

MV_A330GRV set to .F.: only products with a balance or movement in the period get their opening balance recalculated. Official recommendation for databases with more than 10 thousand records in SB2, because it skips the obsolete ones. And reinstall the procedures after changing it.
MV_CUSTEXC set to N: takes the routine out of exclusive mode and allows parallel processing. A prerequisite for threads to make any sense at all.
MV_THRSEQ set to .F.: work file generation in parallel.
And in the routine’s prompts: turn off what you do not use. Accounting nobody consumes and labor cost calculation with no time entries are processing hours thrown away every month.

How to prove it, instead of guessing

On Oracle with the Diagnostics Pack you do not even need to set up a collection: ASH already snapshots the active sessions every second. In the morning, just ask what the window waited on:

-- Oracle: retrato da janela pelo ASH (exige Diagnostics Pack)
SELECT NVL(event, 'ON CPU') evento,
       COUNT(*) amostras,
       ROUND(100 * RATIO_TO_REPORT(COUNT(*)) OVER (), 1) pct
  FROM v$active_session_history
 WHERE sample_time BETWEEN TIMESTAMP '2026-09-21 19:00:00'
                       AND TIMESTAMP '2026-09-22 06:00:00'
 GROUP BY event
 ORDER BY amostras DESC;

Without the pack, you can build the same picture by hand: the blocking query from the previous section, scheduled every 30 seconds, writing to a table.

Either way, in the morning you have a portrait of the night. And it falls into one of three scenarios:

Not everything is the database

I need to be honest in this part, because it is where many DBAs get lost defending turf.

The database is where the problem shows up. It is not always where it lives. An undersized AppServer, a DBAccess pool too small for the number of threads configured, storage with latency spikes that the monitoring average hides, antivirus scanning the data folder, a new firewall in the path between the layers. All of it ends up in the same symptom: “the database is slow”.

Look at the whole ecosystem before accepting the blame. And before passing the blame along too.

On band-aids

Sometimes the root cause does not fit the deadline. The customization is eight years old, whoever wrote it has left, and the close is on Friday.

That is when you buy time: an exclusive window with no competing inventory movements, threads down instead of up, MV_A330SB2 turned on, procedures reinstalled, rebuild and statistics on the eve, DBAccess right next to the database.

A band-aid is legitimate. It saves the close, and saving the close is the job.

Just do not call it a definitive solution, and do not let it take root. A band-aid that stays up for two years becomes architecture. And then the problem is no longer slowness, it is debt.

When to call a specialist

If your cost processing is overrunning its window and you have already tried a bigger machine and a restart with no result, the path is to measure an entire window and find out which of the three scenarios your environment is in. That is exactly what Furushima’s Protheus consulting and managed support practice does in an environment assessment: collection during the real processing, a reading of the blocking chain and the waits, and an action plan separating what is parameter, what is database and what is code.

Cover photo: Ian D. Keating, CC BY 2.0, via Wikimedia Commons.

References

Tagged Average Cost, DBAccess, MATA330, Performance, Protheus