Finding the Bad Queries
User on IDS 11.7 FC5 wanted to identify queries causing excessive sequential scans on tables, similar to Oracle's top queries tool. Suggestions provided: use sysmaster:syssesprof to find sessions with high sequential scans, enable SQLTRACE (visible in OAT or sysmaster:syssqltrace) to track reads, check sysmaster:syssqexplain, and ensure UPDATE STATS is current for accurate query plans. OAT tool recommended for easier monitoring.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
AIX & IDS 11.7 FC5
In sysmaster:sysptprof I can see the tables with the most Sequential scans.
What I am trying to do is identify the queries that are causing these
SEQSCANS. I know some seqscans are actually good, not bad, so maybe my angle
of attack is wrong. I am for example able to identify tables with lots of
records, that are not supposed to have sequential scans on them.
This is probably not done by linking sysptprof to other tables. I do not have
the OAT tool installed, so excluding OAT, how do I identify these bad queries ?
My question is probably a bit vague (my apologies), but I am trying to
identify the worst queries so that they can be optimised. I know it is not as
simple as this. I've done some reading, and even SET EXPLAIN can give you
incorrect results - if UPDATE STATS is not up to date. And set explain can
only be used once you have identified a query and want to check the explain
output for said query.
In Oracle you have a web tool where you can select a database, and then let it
show you the top X queries in the database. I am basically trying to do
something similar by using onstat / sysmaster if possible.
Dirk
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
Hello.
In Informix, you also have an excellent resource, compared to the "dark side
of the moon".
Search Information Center website for sqltrace feature.
I recommend you to install OAT (even in your client machine), through CSDK
customized install. It´s much easier to monitor your queries, and it also has
some "slowest sql" reports, ready to go.
Just be sure that you are using the new statistics features (AUTO_STAT_MODE,
STATCHANGE), and you´ll get always a good query plan - at least an actualized
one for your queries.
Regards.
Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70
IBM Information Management Informix Technical Professional
IBM Infosphere DataStage Technical Professional
Informix Senior DBA - Orizon Brasil
BRIUG website administrator
Informix independent consultant
> To: ids@iiug.org
> From: moolma_dc@mtn.co.za
> Subject: Finding the Bad Queries [29687]
> Date: Tue, 5 Mar 2013 06:00:41 -0500
>
> AIX & IDS 11.7 FC5
>
> In sysmaster:sysptprof I can see the tables with the most Sequential scans.
> What I am trying to do is identify the queries that are causing these
> SEQSCANS. I know some seqscans are actually good, not bad, so maybe my angle
> of attack is wrong. I am for example able to identify tables with lots of
> records, that are not supposed to have sequential scans on them.
>
> This is probably not done by linking sysptprof to other tables. I do not have
> the OAT tool installed, so excluding OAT, how do I identify these bad queries
> ?
>
> My question is probably a bit vague (my apologies), but I am trying to
> identify the worst queries so that they can be optimised. I know it is not as
> simple as this. I've done some reading, and even SET EXPLAIN can give you
> incorrect results - if UPDATE STATS is not up to date. And set explain can
> only be used once you have identified a query and want to check the explain
> output for said query.
>
> In Oracle you have a web tool where you can select a database, and then let
it
> show you the top X queries in the database. I am basically trying to do
> something similar by using onstat / sysmaster if possible.
>
> Dirk
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
sysmaster:syssqexplain perhaps ?
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Dirk Cornel....
> Sent: Tuesday, 05 March 2013 01:01 PM
> To: ids@iiug.org
> Subject: Finding the Bad Queries [29687]
>
> AIX & IDS 11.7 FC5
>
> In sysmaster:sysptprof I can see the tables with the most Sequential
> scans.
> What I am trying to do is identify the queries that are causing these
> SEQSCANS. I know some seqscans are actually good, not bad, so maybe my
> angle
> of attack is wrong. I am for example able to identify tables with lots
> of
> records, that are not supposed to have sequential scans on them.
>
> This is probably not done by linking sysptprof to other tables. I do
> not have
> the OAT tool installed, so excluding OAT, how do I identify these bad
> queries
> ?
>
> My question is probably a bit vague (my apologies), but I am trying to
> identify the worst queries so that they can be optimised. I know it is
> not as
> simple as this. I've done some reading, and even SET EXPLAIN can give
> you
> incorrect results - if UPDATE STATS is not up to date. And set explain
> can
> only be used once you have identified a query and want to check the
> explain
> output for said query.
>
> In Oracle you have a web tool where you can select a database, and then
> let it
> show you the top X queries in the database. I am basically trying to do
> something similar by using onstat / sysmaster if possible.
>
> Dirk
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
There are a couple of things you can do. The syssesprof table in sysmaster
records the number of sequential scans for the session. You can search on
that to find longer running sessions that repeatedly scan tables and then
follow what queries that session is running.
Another is to enable the SQLTRACE which OAT can see. The
sysmaster:syssqltrace table contains details on each SQL statement in the
trace buffers. Number of sequential scans is not captured, unfortunately,
however, the number of reads from cache and from disk is tracked which is
certainly useful and high numbers of reads will likely identify queries
worth investigating further.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Mar 5, 2013 at 6:00 AM, Dirk Cornel.... <moolma_dc@mtn.co.za> wrote:
> AIX & IDS 11.7 FC5
>
> In sysmaster:sysptprof I can see the tables with the most Sequential scans.
> What I am trying to do is identify the queries that are causing these
> SEQSCANS. I know some seqscans are actually good, not bad, so maybe my
> angle
> of attack is wrong. I am for example able to identify tables with lots of
> records, that are not supposed to have sequential scans on them.
>
> This is probably not done by linking sysptprof to other tables. I do not
> have
> the OAT tool installed, so excluding OAT, how do I identify these bad
> queries
> ?
>
> My question is probably a bit vague (my apologies), but I am trying to
> identify the worst queries so that they can be optimised. I know it is not
> as
> simple as this. I've done some reading, and even SET EXPLAIN can give you
> incorrect results - if UPDATE STATS is not up to date. And set explain can
> only be used once you have identified a query and want to check the explain
> output for said query.
>
> In Oracle you have a web tool where you can select a database, and then
> let it
> show you the top X queries in the database. I am basically trying to do
> something similar by using onstat / sysmaster if possible.
>
> Dirk
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec554d2321bcfb404d72c181a
Thank you Alexandre.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Alexandre Marini
> Sent: Tuesday, 05 March 2013 01:41 PM
> To: ids@iiug.org
> Subject: RE: Finding the Bad Queries [29688]
>
> Hello.
> In Informix, you also have an excellent resource, compared to the "dark
> side
> of the moon".
> Search Information Center website for sqltrace feature.
>
> I recommend you to install OAT (even in your client machine), through
> CSDK
> customized install. It´s much easier to monitor your queries, and it
> also has
> some "slowest sql" reports, ready to go.
>
> Just be sure that you are using the new statistics features
> (AUTO_STAT_MODE,
> STATCHANGE), and you´ll get always a good query plan - at least an
> actualized
> one for your queries.
>
> Regards.
>
> Alexandre Marini
> IBM Informix Certified Professional v10 / v11.50 / v11.70
>
> IBM Information Management Informix Technical Professional
>
> IBM Infosphere DataStage Technical Professional
> Informix Senior DBA - Orizon Brasil
> BRIUG website administrator
> Informix independent consultant
>
> > To: ids@iiug.org
> > From: moolma_dc@mtn.co.za
> > Subject: Finding the Bad Queries [29687]
> > Date: Tue, 5 Mar 2013 06:00:41 -0500
> >
> > AIX & IDS 11.7 FC5
> >
> > In sysmaster:sysptprof I can see the tables with the most Sequential
> scans.
> > What I am trying to do is identify the queries that are causing these
> > SEQSCANS. I know some seqscans are actually good, not bad, so maybe
> my angle
> > of attack is wrong. I am for example able to identify tables with
> lots of
> > records, that are not supposed to have sequential scans on them.
> >
> > This is probably not done by linking sysptprof to other tables. I do
> not
> have
> > the OAT tool installed, so excluding OAT, how do I identify these bad
> queries
> > ?
> >
> > My question is probably a bit vague (my apologies), but I am trying
> to
> > identify the worst queries so that they can be optimised. I know it
> is not
> as
> > simple as this. I've done some reading, and even SET EXPLAIN can give
> you
> > incorrect results - if UPDATE STATS is not up to date. And set
> explain can
> > only be used once you have identified a query and want to check the
> explain
> > output for said query.
> >
> > In Oracle you have a web tool where you can select a database, and
> then let
> it
> > show you the top X queries in the database. I am basically trying to
> do
> > something similar by using onstat / sysmaster if possible.
> >
> > Dirk
> >
> > NOTE: This e-mail message is subject to the MTN Group disclaimer see
> > http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> >
> >
> >
> ***********************************************************************
> ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
Awesome, thanks !
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Tuesday, 05 March 2013 01:52 PM
> To: ids@iiug.org
> Subject: Re: Finding the Bad Queries [29690]
>
> There are a couple of things you can do. The syssesprof table in
> sysmaster
> records the number of sequential scans for the session. You can search
> on
> that to find longer running sessions that repeatedly scan tables and
> then
> follow what queries that session is running.
>
> Another is to enable the SQLTRACE which OAT can see. The
> sysmaster:syssqltrace table contains details on each SQL statement in
> the
> trace buffers. Number of sequential scans is not captured,
> unfortunately,
> however, the number of reads from cache and from disk is tracked which
> is
> certainly useful and high numbers of reads will likely identify queries
> worth investigating further.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor
> any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated
> nor
> those of the entities themselves.
>
> On Tue, Mar 5, 2013 at 6:00 AM, Dirk Cornel.... <moolma_dc@mtn.co.za>
> wrote:
>
> > AIX & IDS 11.7 FC5
> >
> > In sysmaster:sysptprof I can see the tables with the most Sequential
> scans.
> > What I am trying to do is identify the queries that are causing these
> > SEQSCANS. I know some seqscans are actually good, not bad, so maybe
> my
> > angle
> > of attack is wrong. I am for example able to identify tables with
> lots of
> > records, that are not supposed to have sequential scans on them.
> >
> > This is probably not done by linking sysptprof to other tables. I do
> not
> > have
> > the OAT tool installed, so excluding OAT, how do I identify these bad
> > queries
> > ?
> >
> > My question is probably a bit vague (my apologies), but I am trying
> to
> > identify the worst queries so that they can be optimised. I know it
> is not
> > as
> > simple as this. I've done some reading, and even SET EXPLAIN can give
> you
> > incorrect results - if UPDATE STATS is not up to date. And set
> explain can
> > only be used once you have identified a query and want to check the
> explain
> > output for said query.
> >
> > In Oracle you have a web tool where you can select a database, and
> then
> > let it
> > show you the top X queries in the database. I am basically trying to
> do
> > something similar by using onstat / sysmaster if possible.
> >
> > Dirk
> >
> > NOTE: This e-mail message is subject to the MTN Group disclaimer see
> > http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> >
> >
> >
> >
> ***********************************************************************
> ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --bcaec554d2321bcfb404d72c181a
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx