Protheus slow? Where to look for the cause: database, DBAccess and infrastructure

A screen that freezes in the middle of an order, a closing routine that runs into the small hours, a report nobody expects to finish anymore: slow Protheus is one of the most common tickets we get on call. And the investigation almost always starts in the wrong place. This guide shows how to isolate the cause across the ERP’s three tiers and what to look at in each one, in the right order.

The three tiers where slowness hides

Protheus is a three-tier system, and each tier has its own slowness signature:

1. AppServer (the application): widespread slowness, across all routines, usually with CPU or memory maxed out on the application server.
2. DBAccess (the translator): freezes in bursts, connections piling up, routines stuck waiting while the database is not overloaded.
3. Database (SQL Server or Oracle): specific routines running slow, a dragging month-end close, blocking between users. This is where the cause lives in most of the cases we handle.

Start with the database: it is suspect number 1

Outdated indexes and statistics

Protheus tables (SB1, SC5, SE1 and friends) grow every day, and the database optimizer picks execution plans based on statistics. Stale statistics mean a bad plan, and a bad plan means the same routine that used to run in seconds now taking minutes. Index maintenance and statistics updates are the first check, before blaming hardware.

Find the heaviest queries

In SQL Server, the database itself hands you the ranking of what consumes the most time:

SELECT TOP 10
       qs.execution_count,
       qs.total_elapsed_time / qs.execution_count AS tempo_medio_us,
       SUBSTRING(st.text, 1, 200) AS trecho_sql
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_elapsed_time DESC;

In Oracle, the equivalent path is V$SQL:

SELECT sql_id,
       executions,
       ROUND(elapsed_time / 1000000, 1) AS segundos_total
FROM   v$sql
ORDER  BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

With the SQL_ID in hand, the next step is reading the execution plan. We have a complete guide on how to interpret the execution plan in Oracle and a ready-made script (showplan.sql) that cross-references the plan with wait events. For SQL Server, see how to capture slow queries with Extended Events and Query Store.

Blocking: when one user freezes the whole company

Long ERP routines hold locks on core tables, and everyone who needs them gets in line. The classic symptom: the system suddenly slows down for everybody and goes back to normal on its own when someone finishes (or gives up). Monitoring blocking sessions and fixing the routine that holds the lock solves more slow Protheus cases than a server upgrade.

DBAccess: the link almost nobody checks

• A DBAccess version far behind the Protheus release causes incompatibilities and silent slowness
• An exhausted connection pool makes routines wait for a slot while the database sits idle
• The DBAccess monitor shows running queries in real time: it is the fastest way to see what the ERP is asking of the database at that moment
• The network between AppServer, DBAccess and the database needs low latency: every millisecond multiplies across millions of round trips

AppServer and infrastructure

If the database and DBAccess are healthy, look at the application and the infrastructure: load balancing across AppServer slaves, threads per service, available memory, high-latency storage and antivirus scanning ERP directories are the most frequent culprits. In virtualized environments, it is worth checking whether the host is running with overcommitted CPU.

Quick slow Protheus checklist

1. Is the slowness general or limited to specific routines?
2. Did it start suddenly (a recent change?) or worsen gradually (growth)?
3. Are database statistics and indexes up to date?
4. What are the 10 heaviest queries right now?
5. Are there blocked or blocking sessions?
6. Is DBAccess up to date, with room to spare in the connection pool?
7. Are server CPU, memory and disk latency healthy?
8. Does the problem coincide with a backup, an integration or a heavy job?

When to call a specialist

If the checklist above did not close the diagnosis, or it did and nobody on the team has the bandwidth to attack the cause, it is time to bring in people who do this every day. Furushima’s Protheus consulting and managed support practice covers exactly this cycle: an assessment of the environment, database performance tuning driven by wait events with measured results, and continuous monitoring so the slowness does not creep back in.