Extensive writing in .srt-files

Do you know why there's so much srt activity (eg. sorting on non-indexed fields)? Perhaps the code is the best place to fix this.

Thanks for your reply.

Generally we use quite a bit of temp-tables, some of them un-indexed. Would applying indeces to these tables help with the .srt file issue? And in that case, should that be indexes matching the "BY" statements?

Consider this

FOR EACH tt WHERE tt.a = param1 AND tt.b = param2 BY tt.c BY tt.d:

END.

Should I have

1) One index matching a, b, c and d or 2) One index matching a and b and one index matching c and d. 3) Another option?

Best regards

Jens

Sorting temp-table records could be a cause. Havin the appropriate indexes will improve performance and reduce srt file usage. To figure out exactly which indexes you should have, take a look at the index selection rules (http://www.psdn.com/library/entry.jspa?externalID=2118 - don't be too concerned if you're not on 10.2A, the rules haven't changed for a long time).

For this particular example, multiple indexes could be used (concurrently) to resolve the query. In this case, it'd probably be best to have two indexes - one on tt.a and the other on tt.b. In many other instances though, multi-index query resolution will not be possible. The doc mentioned above has all the details.

Indexes on (large) temp-tables do help a lot. In general there is not much difference between the way temp-tables are used and the way database tables are used.

Ar other temp-tables getting big as well? How about the dbi files? Then a large -Bt parameter might help (but watch the memory consumption is you are using a lot of agents).

Using a -T parameter to direct the temp-files (srt, dbi, ...) ot a different drive (or a RAM disk) may help as well.

But that's just tuning around an application problem (no indexes, or wrong index usage as Jamie already mentioned).

One problem is that we have a hard time using the installed memory, I've played around with -Bt and -mmax parameters.

Current values are -Bt 5000 -mmax 16000

We've got 8 gigabytes of memory installed but according to top rarely more than 2 gigabytes are used. Currently: Memory: 8192M real, 6630M free, 1375M swap in use, 7952M swap free.

Right now there isn't much load on the server.

It seems to be more srt files than dbi. Its a bit tricky to keep track of since they are unlinked so I have to use iosnoop to check writes and reads. Basically all our variables and temp-tables are no-undo's.

You can also use the -t parameter to link the lbi & srt files in to the file system.

I'm guessing that the spare memory will be being used for OS buffers, but you're right in thinking it'd be better used for application things. More information about database buffers & blocksize and number of users might help us to give further suggestions on memory usage.

Top isn't telling you what you think that it is telling you. The amount of swap space used has very little to do with the amount of memory that you are using.

I had a feeling about that. How can I check actualy memory use?

That depends on your OS although most are pretty weak at telling you what you really want to know. IMHO AIX has the best tools for this purpose. NMON and SVMON can tell you a lot.

In general one major source of misdirection is that every modern OS uses any left over RAM to buffer disk IO. Thus almost all RAM is usually being used in some form or another. Breaking it out to just RAM being used by Progress is usually difficult or outright impossible.

Regardless your first step should be to put together a spreadsheet detailing what your memory usage should be. This will be very useful for planning any changes as well as for helping to understand and validate whatever the OS tells you.

Back to the original question... SRT file usage has a couple of potential sources (at least prior to 10.2 -- there have been some changes post 10.2...) Obviously sorting results sets is a big driver. So you might want to check your query code.

Another possibility is dynamic queries. If you are using dynamic queries with the FORWARD-ONLY property (or without the -noautoresultlist startup parameter) then you may be building large result sets that you do not want.

Lastly there is r-code swapping. This happens when the -mmax buffer is not large enough to hold all of your active r-code. GUI code tends to be large and a -mmax of 65535 might be a good starting place.

Thanks Tom,

We're running 10.1C and it's a webspeed (cgi-wrapper) application without any dynamic queries. I've gone through the most accessed parts of the application and added a few indeces to temp-tables. This seems to decrease the number & size of srt files.

Now I will increase the -mmax (currently it was set to 16k).

Don't know if this is changed in 10.2 but in 10.1c the maximum value of mmax seems to be 65534.

Another possibility is dynamic queries. If you are

using dynamic queries with the FORWARD-ONLY property

(or without the -noautoresultlist startup parameter)

then you may be building large result sets that you

do not want.

Correction:

both dynamic and static queries will generate a result list which goes in the .srt file if neither FORWARD-ONLY or -noautoreslist is set.

Yes, that's what I meant to say

I knew better but I seem to have formed the bad habit of pre-pending "dynamic" onto "query" in much the same way that all too many people automatically add FIRST whenever they type FIND I'll try to break that habit.

As for the improved phrasing regarding FORWARD-ONLY and -noautoresultlist I can only plead lack of caffeine and hope for mercy. Tim put it much more clearly than I did.