Stop Guessing: Essential ProTop Features for OpenEdge Developers

You've been there: that heartless DBA is cruelly accusing you of doing vile things in your code. We designed ProTop's features specifically with developers in mind to help you prevent problems rather than just capture them after the fact.

If you're clever, you can even use ProTop to defend yourself and point the finger back: "Nope, that wasn't me. That was Paul who did that, for instance." It's all part of your quality process.

We're going to dive into the tools ProTop gives you to stop guessing and start proving.

Programmer Mode: On-Demand Diagnostics

By default, ProTop RT samples data automatically every 10 seconds. That's great for DBAs who want rate metrics. But as a programmer, you want to know precisely how much activity a specific action in your development or QA environment is having against the database.

Programmer mode (Ctrl+P) changes ProTop RT's entire mindset.

Proving the Impact of Your Code

1. Open your test window and get your test code ready.
2. Hit the spacebar in ProTop RT to initialize and zero out all counters.
3. Run your test code.
4. Hit the spacebar again.

Now you see exactly what raw impact that code had on the database. This is super helpful for confirming the tables and indexes you expected to use are actually what's happening. A lot of times, you'll be very surprised. You might see unexpected activity on the metaschema tables due to things like dynamic queries.

Zeroing In

While in programmer mode, you can use the capital U key to select a user number and see session-specific statistics. This is better than a global view, especially on a shared test server, because you aren't getting everyone else's mess mixed in.

Client Statement Cache (CSC) vs. ProGetStack

The Client Statement Cache (CSC) is a huge help because it doesn't just tell you there’s a problem, it tells you what program and, even better, what line number is driving the activity. This is your insight into the database.

We have two ways to get a stack trace for a running session:

What we love about the CSC is that a DBA can enable it and use it just from the database server, without having to reach out and touch client machines. Since about OpenEdge 11.4, it’s been very, very stable. We know customers, especially shared memory users, who enable it globally and just leave it running because it’s so useful.

Dealing with Table and Index Activity

Your database activity boils down mostly to tables and indexes. But before you start, you must set the stage.

Setting the Monitoring Stage

By default, Progress only enables the monitoring of the first 50 tables and indexes. Most real applications have way more than that.

• Use ProTop's Capital T screen. It evaluates your schema and suggests parameter settings for monitoring.
• We offer suggested settings for Application Tables only or Complete Settings (including metaschema tables). I usually run with the complete settings because it's fascinating to see that metaschema activity.
• Good News: Prior to OpenEdge 12, you had to restart the database to set these. Now you can use ProUtil and increase these parameters online - for example "proutil dbname -C increaseto -tablerangesize 500"
• LOBs: OpenEdge didn't track LOBs prior to version 12. For accurate data, especially where LOB data was misreported as table data, you need to be running at least 12.2.

User Activity on a Specific Table

Use the 8 or 9 command to ask for the top users of a particular table. You'll get users listed by activity, and if the CSC is enabled, you see the program and line number accessing that table.

The Churn Metric: Your Bottleneck Detector

The Churn column is critical for developers.

The problem is that almost every application has a few small control tables (maybe 10-100 records) that see an extraordinary amount of I/O—millions of reads per second sometimes. This drives latch contention in production.

• Churn Defined: It's the ratio of the number of reads to the size of the table. If a 100-record table is read 1,000 times in a sample, you get a Churn of 10.
• The Opportunity: A high Churn tells you that you have a chance to improve by caching that data. You can move it to a temp table or use object-oriented classes.

The "Ugly" Fix: Many applications happen to have an include file that is part of every program. If that is true for your application then you can take advantage of that fact and sneakily insert the ugly code into that include file and voila! no more churn. We've removed half of a customer's database activity doing that. Of course, YMMV, not applicable in the state of California, etc, etc..

Active Transactions and Blocked Sessions

Blocked Sessions (Record Locks)

If a session is blocked, it's usually a record lock. ProTop detects this and shows you:

• The table the user is waiting for.
• The Resrc ID (Resource ID). IF the "Wait" column says "REC" then Resrc ID is a RECID. Otherwise it might be something else (like a block number).
• Most importantly, the user who is blocking them.
• The code location of the blocking user (e.g., line 422 of $\text{getoparn.t}$).

Having both the blocked and the blocking session allows you to zero in on the faulty code.

The Long Transaction Problem

Progress is famous for the long transaction problem. A user starts a transaction and "goes to lunch, or they go on vacation." The BI file grows and grows.

• We track idle time (when the user last talked to the database) versus the transaction's duration.
• A user who is not talking to the database is probably the one who went for coffee.
• A user with zero idle time is actually working, even if the transaction is poorly scoped. This gives you better decision-making capabilities than just killing a session.

The CSC helps you figure out where in the code the transaction scope was too broad.

Warning on _Lock VST: You might want to use the _Lock VST in your own code to check locks. Be careful. While it's faster than it used to be, it's still not fast on large lock tables (1-2 million locks). Code that runs great in development often won't run well in production.

Progress Code Profiler

Forget crude ETIME and MESSAGE statements. The Code Profiler is better.

You can embed the profiler into your application using the session handle profiler:

• profiler:enabled = yes
• Give it a file name for output.
• When done, use disable to shut off profiling and write the data.

We ship sample code in the libzprof procedures. You can even try it out in ProTop itself using the capital Y key.

The Key Insight: The profiler report tells you where the total execution time was spent. This is actual execution of code or database requests, excluding time spent waiting for user prompts or external I/O. The key benefit is that this keeps you focused on where the time is actually spent - instead of wasting effort shaving microseconds off code that runs in a millisecond you can cut seconds, minutes , or hours off of code that makes a difference to users.

Cautions & Caveats

• Size: Profiler files can become really big in a hurry. Years ago, a customer tried profiling every session, and it consumed an enormous amount of disk space, forcing them to randomly sample 1 in 10 users. Be careful.
• Compilation: You must compile with the debug list option to get the line numbers.
• Multiple Statements: If you have code with multiple statements on one line (e.g., do i = 1 to 100: i = i + 1.) the profiler can only see the first statement. That code will hide.

Temp Tables and User Table Stats

Temp Tables 

You can't reach into another session for temp table info, but you can instrument your own session.

• This requires OpenEdge 11+.
• It uses the progress.database.timptableinfo class.
• You get data on size, record counts, and the reads/writes to individual temp tables.

User Table Stats

This allows you to embed code to report what your session is doing regarding table and index usage. If you're running the Smart Component Library, you already get an example of this in your app servers (thanks to our friends at Consultingwerk).

A Few More Handy Tools

• Server Info: See your client-server traffic—crucial now that more people are moving away from shared memory.
• Appmon: A developer hook that lets you add and trend application-specific KPIs.

Take Back Control

You've just reviewed the tools that turn runtime mysteries into actionable facts. From isolating your code's exact database impact using Programmer Mode to pinpointing problematic transactions and preventing future bottlenecks with the Churn metric, ProTop gives you the evidence you need to confidently address issues before they become big problems!

It's time to stop relying on assumptions and letting others dictate the conversation about performance. With ProTop in hand, you've got the proof and the power. Start proving your code is clean today!