TomBascom
Curmudgeon
If it is related to "table size" then it is really "number of records being evaluated" and you should be able to confirm that by monitoring the number of logical reads that the sessions experiencing the problem are executing.
The simplest way to do that is with ProTop. (Since you are running Windows and it seems unlikely that you will obtain a copy of OE10.2 even just to explore you will need to down the character version. You will also probably need to repair Progress' bogus Window sizing -- click on the icon in the upper left corner, select properties change the both screen sizes to 80x36 and then save the change.) The "User IO" display is what you want. (Documentation for ChUI ProTop is at http://www.greenfieldtech.com/
If ProTop is too daunting you can also try to use PROMON. R&D, 3 "Other Displays", 2 "I/O Operations by Process". You will need to page to the user in question, note their number of "db accesses" and then repeat the process and compare the change in values over time.
IF your problem is that a large query is causing your session to "hang" then you should see very large numbers of "logical reads" or "db accesses" occurring (tens of thousands per second) for the session which is hung. If you don't see that then the problem is something else.
If you do see that then you still don't know what program or even what table is involved.
With version 9 the best that you are going to be able to do is to narrow down the table. Again, ProTop is your friend. Just look at "Table Statistics". The chances are very good that one table is going to be head and shoulders more busy than any other. (You must start the server with -tablerangesize and -indexrangesize large enough to cover all tables and indexes.) There is no PROMON equivalent. You will have to assume that the busy table corresponds to the busy user -- table stats by user are an OpenEdge 10.1B feature. If you are in a test environment it should be easy enough to verify. Use "Index Statistics" to determine which index is involved.
To figure out what program is involved you need to compile your code with the XREF option. Then search the output for references to the table and index that are busy paying special attention to WHOLE-INDEX. This is not an exact process. You will probably have to evaluate a lot of false positives. (OpenEdge 10.1C has a stack trace feature which would tell you exactly what line is being executed and what procedure as well as the whole stack trace that got you there.)
You might also try using Oracle's diagnostics in a similar manner but I cannot tell you much about those.
I would also set the -t and -T parameters for client sessions to an isolated folder so that I could isolate the temp files and see if any of those are unusually large and active. That might provide interesting clues too.
Lastly, if you still cannot pin it down you could try using the profiler. This will tell you where your code is spending its time but you will have to let the session finish normally -- if you kill it you will lose the profiling trace.
The simplest way to do that is with ProTop. (Since you are running Windows and it seems unlikely that you will obtain a copy of OE10.2 even just to explore you will need to down the character version. You will also probably need to repair Progress' bogus Window sizing -- click on the icon in the upper left corner, select properties change the both screen sizes to 80x36 and then save the change.) The "User IO" display is what you want. (Documentation for ChUI ProTop is at http://www.greenfieldtech.com/
If ProTop is too daunting you can also try to use PROMON. R&D, 3 "Other Displays", 2 "I/O Operations by Process". You will need to page to the user in question, note their number of "db accesses" and then repeat the process and compare the change in values over time.
IF your problem is that a large query is causing your session to "hang" then you should see very large numbers of "logical reads" or "db accesses" occurring (tens of thousands per second) for the session which is hung. If you don't see that then the problem is something else.
If you do see that then you still don't know what program or even what table is involved.
With version 9 the best that you are going to be able to do is to narrow down the table. Again, ProTop is your friend. Just look at "Table Statistics". The chances are very good that one table is going to be head and shoulders more busy than any other. (You must start the server with -tablerangesize and -indexrangesize large enough to cover all tables and indexes.) There is no PROMON equivalent. You will have to assume that the busy table corresponds to the busy user -- table stats by user are an OpenEdge 10.1B feature. If you are in a test environment it should be easy enough to verify. Use "Index Statistics" to determine which index is involved.
To figure out what program is involved you need to compile your code with the XREF option. Then search the output for references to the table and index that are busy paying special attention to WHOLE-INDEX. This is not an exact process. You will probably have to evaluate a lot of false positives. (OpenEdge 10.1C has a stack trace feature which would tell you exactly what line is being executed and what procedure as well as the whole stack trace that got you there.)
You might also try using Oracle's diagnostics in a similar manner but I cannot tell you much about those.
I would also set the -t and -T parameters for client sessions to an isolated folder so that I could isolate the temp files and see if any of those are unusually large and active. That might provide interesting clues too.
Lastly, if you still cannot pin it down you could try using the profiler. This will tell you where your code is spending its time but you will have to let the session finish normally -- if you kill it you will lose the profiling trace.