Indetifying badly running sql

Hi All, I have a customer who is complaining of poor performance in IDS 9.40.FC7, my cache hit rates are very good, both at about 99% however. I see a lot of sequential scans, which leads me to want to trap what queries are causing this and may be running poorly, but how? 1 - Wait for user complaint, and investigate 2 - I was told that and I have script that, cycles looking at "onstat -g ntt", it compares the difference between the open and read times and reports the "onstat -g ses" details. The problem I'm experiencing, is that the application has some batch processes which open a connection to the db, perform some task, and then wait for a long time before performing another task, without closing the connection, so they appear to be a problem, but may or may not be. Also there's a lot of stored procedures, which just show up as the "execute procedure xxxx". So are there any other ways of finding poorly running queries? Thanks Andy

There could be many reasons like badly written SQLs, missing indexes, Update stats, Seqscans, I/O bottleneck etc. To start with, I would look at sessions doing seqscans. Get SQL used by such sessions. Run explain plan and see which table is having seq scans. Sysmaster can give you session info doing seq scans. How often you run update stats? Do you run update stats on SP? If not, this could also be an issue. If you know sessionid, you can capture all SQLs by using onmode -Y <sid> 1|0 Cheers ANDREW DIBBINS <andy_d@rapier.demon.co.uk> wrote: Hi All, I have a customer who is complaining of poor performance in IDS 9.40.FC7, my cache hit rates are very good, both at about 99% however. I see a lot of sequential scans, which leads me to want to trap what queries are causing this and may be running poorly, but how? 1 - Wait for user complaint, and investigate 2 - I was told that and I have script that, cycles looking at "onstat -g ntt", it compares the difference between the open and read times and reports the "onstat -g ses" details. The problem I'm experiencing, is that the application has some batch processes which open a connection to the db, perform some task, and then wait for a long time before performing another task, without closing the connection, so they appear to be a problem, but may or may not be. Also there's a lot of stored procedures, which just show up as the "execute procedure xxxx". So are there any other ways of finding poorly running queries? Thanks Andy ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. --------------------------------- Never miss a thing. Make Yahoo your homepage.

If you think you have an issue with a procedure... Set explain on Update statistics for procedure {name here} Will create sqexplain.out with the explain of all SQLS in the procedure. MW -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of H.G Sent: Thursday, 29 November 2007 9:56 p.m. To: ids@iiug.org Subject: Re: Indetifying badly running sql [10524] There could be many reasons like badly written SQLs, missing indexes, Update stats, Seqscans, I/O bottleneck etc. To start with, I would look at sessions doing seqscans. Get SQL used by such sessions. Run explain plan and see which table is having seq scans. Sysmaster can give you session info doing seq scans. How often you run update stats? Do you run update stats on SP? If not, this could also be an issue. If you know sessionid, you can capture all SQLs by using onmode -Y <sid> 1|0 Cheers ANDREW DIBBINS <andy_d@rapier.demon.co.uk> wrote: Hi All, I have a customer who is complaining of poor performance in IDS 9.40.FC7, my cache hit rates are very good, both at about 99% however. I see a lot of sequential scans, which leads me to want to trap what queries are causing this and may be running poorly, but how? 1 - Wait for user complaint, and investigate 2 - I was told that and I have script that, cycles looking at "onstat -g ntt", it compares the difference between the open and read times and reports the "onstat -g ses" details. The problem I'm experiencing, is that the application has some batch processes which open a connection to the db, perform some task, and then wait for a long time before performing another task, without closing the connection, so they appear to be a problem, but may or may not be. Also there's a lot of stored procedures, which just show up as the "execute procedure xxxx". So are there any other ways of finding poorly running queries? Thanks Andy ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. --------------------------------- Never miss a thing. Make Yahoo your homepage. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

ANDREW DIBBINS wrote: > Hi All, > > I have a customer who is complaining of poor performance in IDS 9.40.FC7, my > cache hit rates are very good, both at about 99% however. I see a lot of > sequential scans, which leads me to want to trap what queries are causing this > and may be running poorly, but how? > > 1 - Wait for user complaint, and investigate > 2 - I was told that and I have script that, cycles looking at "onstat -g ntt", > it compares the difference between the open and read times and reports the > "onstat -g ses" details. > > The problem I'm experiencing, is that the application has some batch processes > which open a connection to the db, perform some task, and then wait for a long > time before performing another task, without closing the connection, so they > appear to be a problem, but may or may not be. Also there's a lot of stored > procedures, which just show up as the "execute procedure xxxx". > > So are there any other ways of finding poorly running queries? > > Well, you could try using onstat -g ppf to identify which tables are being scanned, then go through the source code looking for queries that read those tables and run explain plans on each ... could be a lot of work, though. Having just seen a remarkably unusual case where everything was done correctly, but the statistics got out of date very quickly, I'd ask whether your statistics are good, too. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.