Tracing/Monitoring the database operations
Sharing my experience...
I just would like to share with you my experience using SQL Monitor in one of my Delphi applications.
The scenario ...
We had a huge application written in Delphi 5 with little or no documentation at all. It contained so many screens with many database operations. The company wanted us to prepare a detailed documentation related to the database operations happening while running the application. For each screen, there could be some database operations like inserting,updating, deleting etc., We can find out all those database operations by looking at the code. But I dont need to say how horrible to look at the code line by line to find out whatz happening in the backend?
SQL Monitor comes to our rescue...
So we decided to use the SQL Monitor. What we had is the following:
1. The full-fledged running version of the Delphi Application(application.exe)
2. The Oracle database with whole bunch of valid data.
In SQL Monitor, there is an option called Trace Options and in that you can find two tabs: One is to set the Trace Categories and the other one is for Buffer.
In the Trace Categories, you can set the options as you needed and that depends on what are all the backend operations you want to monitor. In the Buffer tab, there are two options: One is to set the Buffer size and the other one is managing the buffer. Let me explain this further.
In buffer management, you can either choose Circular or Page to Disk. If you choose Circular, once the buffer is full, it will be overwritten with the next trace information. If you choose Page to Disk, once the buffer is full, the entire content will be written to a text file you specifiy in the File Name box thereby you can view the trace information later on. This will help you a lot in analyzing the database operations.
We segregated the entire application into several modules based on the user functionality. For example, we had an hospital application and in that there were several modules like appointment scheduling, registration, patient admission, discharging etc., There were a set of screens available to the users for each functionality. We took each module at a time and monitored the dabase operations happening behind the screen. By that we can find the affected tables, triggers, stored procedures etc., This helped us understanding the system clearly as an analyst point of view.
Please make sure that SQL Monitor runs before the application starts. You cannot monitor the backend operations with SQL monitor if you start in the middle of an application running stage. It should find the Application's initialization first and only then it can start monitoring.
It's really a great tool provided by Borland and it was really helpful for us.
Enjoy!!!