Re: Find our query that takes long time

mohitanchlia@gmail.com wrote: > Version: IDS 10 > > I just have a small question: Does informix stores the query execution > time, user who executed it etc. somewhere or something that gives some > Warnings about queries that are really bad. I was thinking if there is > any easy way to find out about query statistics without having to put > set explain on and some kind of debugging around the queries. It would> help a lot where there are thousands of queries in a complex > application. > > I am sure what I am asking for is not relevant for informix to store > because onus lies on the application developer, still I thought I'll > ask. > Your question is by no means irrelevant. In fact, a great effort was put on IDS v11 to allow what you need. It's called SQL history. You can in v11 keep an history of latest SQL statements executed. Then you can see the ones that were run more frequently, with higher cost, or that took more time. In pre-11 versions you can run some scripts to find: - The "currently" sessions consuming more CPUs - The "currently" sessions with more log space usage - The sessions with more sequential scans or buffer reads/writes - The tables with more sequential scans or buffer reads/writes - etc... It can be done and a lot of DBAs do it all the time but it takes more knowledge of IDS architecture and how it works. It's a process based mainly in onstat output. There are several scripts that may help on these tasks. In the IIUG repository you can find several of them and I'm sure other people here will have something to say about this. Knowledge of your tables and applications can be an important factor for this. Sometimes it's much easier for an inside person, even with less IDS knowledge to do these tasks than for an outside person although with bigger IDS know how. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

On Sep 16, 10:08 pm, Fernando Nunes <s...@domus.online.pt> wrote: > mohitanch...@gmail.com wrote: > > Version: IDS 10 > > > I just have a small question: Does informix stores the query execution > > time, user who executed it etc. somewhere or something that gives some > > Warnings about queries that are really bad. I was thinking if there is > > any easy way to find out about query statistics without having to put > > set explain on and some kind of debugging around the queries. It would> > help a lot where there are thousands of queries in a complex > > application. > > > I am sure what I am asking for is not relevant for informix to store > > because onus lies on the application developer, still I thought I'll > > ask. > > Your question is by no means irrelevant. In fact, a great effort was put on IDS > v11 to allow what you need. It's called SQL history. You can in v11 keep an > history of latest SQL statements executed. Then you can see the ones that were > run more frequently, with higher cost, or that took more time. > > In pre-11 versions you can run some scripts to find: > - The "currently" sessions consuming more CPUs > - The "currently" sessions with more log space usage > - The sessions with more sequential scans or buffer reads/writes > - The tables with more sequential scans or buffer reads/writes > - etc... > > It can be done and a lot of DBAs do it all the time but it takes more knowledge > of IDS architecture and how it works. It's a process based mainly in onstat output. > There are several scripts that may help on these tasks. In the IIUG repository > you can find several of them and I'm sure other people here will have something > to say about this. > > Knowledge of your tables and applications can be an important factor for this. > Sometimes it's much easier for an inside person, even with less IDS knowledge > to do these tasks than for an outside person although with bigger IDS know how. > IMHO getting an accurate description from the users is crucial, as is ensuring they are not seeing network delays rather than real database delays. My experience has been that all too often users complain 'everything is slow' but if you go and watch them work it turns out that just one or two things are genuinely slow. Then it's (usually) fairly easy to go back to the source code and find out what the problem is. Of course on occassion it's unreasonable expectations, and it's not unknown for the problem to be that the customer themselves has written the 'query from hell' which is actually the problem - persisting with ACE reports (with a huge pile of select statements and horrifying group by clauses that name 20+ columns) for things that should be written in 4GL is the usual culprit at my main site.