Issue with statistics
A report query on a secondary (reporting) server slowed dramatically after a developer added another table to the join, and the poster asked how to check statistics/indexes. Advice: get the query plan with "set explain on avoid_execute;" (remains on until "set explain off" or session end) and read sqexplain.out; check stats freshness via systables.ustlowts/nrows, dbschema -hd, or sysdistrib.constructed. Practical snags were also covered: put long SQL in a .sql file run via dbaccess (801 buffer/802 file errors), replace "?" placeholders with real values (error 254), and use onstat -u/-g ses/-g sql with the slow session's ID. The thread ends before the plan was captured, so no resolution of the actual slowdown is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We have a primary and a secondary. The secondary is a reporting server, and when one of the report creators adds a table to the sql query, the performance of the report grinds to a halt. It ran fast before adding this table to the query. Is there some way I can check to see what is going on, like if it needs statistics update or check the indexes?
The first step is to get a query plan and see what is going on.
Run the query in dbaccess and precede the SQL with the statement:
set explain on avoid_execute;
This will create a file named "sqexplain.out" in the current directory.
Review the query plan to see how the new table is being accessed. It will
give you some clues as to the next step.
If you have any questions on the output, please google informix explain
plans and access methods before posting the query plan here. There's plenty
of info out there.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI
LONG
Sent: Wednesday, March 14, 2018 1:03 PM
To: ids@iiug.org
Subject: Issue with statistics [40842]
We have a primary and a secondary. The secondary is a reporting server, and
when one of the report creators adds a table to the sql query, the
performance of the report grinds to a halt. It ran fast before adding this
table to the query.
Is there some way I can check to see what is going on, like if it needs
statistics update or check the indexes?
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Mike! That gives me an idea where to start.
How can I find out if the statistics on a table are up to date?
You can see when LOW stats was last run by checking systables.ustlowts. You
can also compare systables.nrows with the actual number of rows in the table
to see how closely they match.
You can see when HIGH or MEDIUM stats were run by looking at the output of
"dbschema -hd <table> -d <database>", or you could try the following to see
for all tables:
dbschema -hd all -d <database>| egrep "^Distribution|^Constructed | Mode,"
This won't tell you if they are stale or not, but will tell you at least if
stats have been updated recently.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI
LONG
Sent: Thursday, March 15, 2018 6:34 AM
To: ids@iiug.org
Subject: Re: RE: Issue with statistics [40846]
How can I find out if the statistics on a table are up to date?
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Run the following:
dbschema -d <database -hd <table>
This will show you the date they were constructed.
Alternatively, you can query the "constructed" column of the sysdistrib table
to find the same information.
Pam
________________________________________
From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of BENJI LONG
[ruggedmouse@hotmail.com]
Sent: Thursday, March 15, 2018 8:34 AM
To: ids@iiug.org
Subject: Re: RE: Issue with statistics [40846]
How can I find out if the statistics on a table are up to date?
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
set isolation to dirty read;
select unique t.tabid, t.tabname,
case t.nrows when 0 then '*' else ' ' end nr,c.colname, c.colno, d.mode, d.constructed
from sysdistrib d, outer systables t, syscolumns c
where d.tabid = t.tabid
and d.tabid = c.tabid
and d.colno = c.colno
and t.tabid > 99
order by t.tabid
into temp stats with no log;
select unique s.*, "1" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part1
union
select unique s.*, "2" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part2
union
select unique s.*, "3" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part3
union
select unique s.*, "4" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part4
union
select unique s.*, "5" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part5
union
select unique s.*, "6" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part6
union
select unique s.*, "7" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part7
union
select unique s.*, "8" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part8
union
select unique s.*, "9" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part9
union
select unique s.*, "10" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part10
union
select unique s.*, "11" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part11
union
select unique s.*, "12" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part12
union
select unique s.*, "13" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part13
union
select unique s.*, "14" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part14
union
select unique s.*, "15" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part15
union
select unique s.*, "16" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part16
into temp stats2 with no log;
select tabid, tabname[1,18], nr, colname[1,18], head, mode, constructed
from stats2
order by 7,1,4,5;-- Tables with no stats at all
select 1 tabid,"No Stats" tabname,0 nrows,0 nindexes
from systables where tabid = 1
union all
select t.tabid, t.tabname[1,18], t.nrows, t.nindexes
from systables t
where t.tabid not in (select unique tabid from stats2)
and t.tabid > 99 and tabtype = "T"
order by 1;
select unique created, count(*) from sysprocplan
group by 1
order by 1;
Mike, is this correct? I run set explain on avoid_execute; Then run the select query Then do I need to shut that off so that nothing else is written to the file?
The set explain remain active, and will continue writing to the file until you end the session or run: set explain off; -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI LONG Sent: Thursday, March 15, 2018 8:00 AM To: ids@iiug.org Subject: Re: RE: Issue with statistics [40850] Mike, is this correct? I run set explain on avoid_execute; Then run the select query Then do I need to shut that off so that nothing else is written to the file? **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
ok, thanks! Trying it on my dev box to see how this all works. Appreciate your quick and helpful replies!
Hi Mike, when I put the query in, it gives me an error of 801 that the SQL buffer is full
That's a text editor thing. Are you using "vi" from within dbaccess, or
using the built-in editor? I would recommend vi.
You could just put the SQL in a file, with the set explain, and execute it
with:
dbaccess <database> <filename>
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI
LONG
Sent: Thursday, March 15, 2018 9:05 AM
To: ids@iiug.org
Subject: Re: RE: Issue with statistics [40853]
Hi Mike, when I put the query in, it gives me an error of 801 that the SQL
buffer is full
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
I was just trying to put the script into the dbaccess query. I will try that
suggestion. Use vi to save the file, and run that way. Thanks!
Lots to learn! I have found that both tables have indexes on them. One table
has 200,000 rows and the new table they are joining has 500,000 so now trying
to get the explain plan for it.
arrrghhh. Another issue. I saved a file called benji.txt using the informix account. When I ran debates storesdemo benji.txt I got 802: Cannot open file for run. I looked this up and it says Review the filename that you specified. If it is spelled as you intended, check that it exists in the current directory or in a directory that is named in the DBPATH environment variable and that your account has read permission for it. it is spelled correctly. The file is informix:informix -rw-rw-r-- Not sure about the DBPATH variable thing.
dbaccess will look for a file ending in .sql. Rename your file.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI
LONG
Sent: Thursday, March 15, 2018 9:27 AM
To: ids@iiug.org
Subject: Re: RE: RE: Issue with statistics [40856]
arrrghhh. Another issue. I saved a file called benji.txt using the informix
account. When I ran debates storesdemo benji.txt I got 802: Cannot open file
for run.
I looked this up and it says Review the filename that you specified. If it
is spelled as you intended, check that it exists in the current directory or
in a directory that is named in the DBPATH environment variable and that
your account has read permission for it.
it is spelled correctly. The file is informix:informix -rw-rw-r-- Not sure
about the DBPATH variable thing.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
That worked! Had a bad example I was following online or I misunderstood it. Ok, now running in PROD. Lots of hurdles knocked down to get to this point. :)
It is giving 254: Too many or too few host variables given. If the query takes parameters for times, (I think this is what it is doing), what would happen if I gave the developer the code set explain on avoid_execute; If she added that to the first line of the select code of the report and ran it, would it still put the explain plan in that file, so that I could review it?
The query you have submitted has question marks in it as placeholders,
indicating that the SQL is prepared and the values are supplied at runtime.
You need to plug in some realistic values in place of the "?" (be sure to
put quotes around where relevant - for dates and character fields).
You can get some values by watching the SQL execute (onstat -g sql or onstat
-g ses) - they'll be displayed below the SQL. This is probably the easiest
way.
Alternatively the user or developer may be able to give you some realistic
values. Ideally you want values that were being used when the query was
running slowly.
You could have the developer add the "set explain" line, but I don't know
the way they are executing it and whether it can support multiple statements
in one go. Also finding the explain plan after can be tricky - there are a
number of factors which determine where the file is placed.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI
LONG
Sent: Thursday, March 15, 2018 10:24 AM
To: ids@iiug.org
Subject: Re: RE: RE: RE: Issue with statistics [40859]
It is giving 254: Too many or too few host variables given.
If the query takes parameters for times, (I think this is what it is doing),
what would happen if I gave the developer the code set explain on
avoid_execute;
If she added that to the first line of the select code of the report and ran
it, would it still put the explain plan in that file, so that I could review
it?
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Ok, that is making sense. I will see if I can track those dates down. Thanks
My dbschema -hd customer -d storesdemo didn't seem to work. When I ran that on
my test box, it just seems to hang. I hit control-C to stop it. I just ran it
from the command prompt. Is that correct?
I also read online that there is a warning with the dbschema command. Do I
need to worry about this or is that if I use the dbschema utility to check
something else other than the statistics distribution?
******Use of the dbschema utility can increment sequence objects in the
database, creating gaps in the generated numbers that might not be expected in
applications that require serialized integers.*****
I don't know why dbschema might hang. Perhaps your environment is not set
up correctly, or maybe the stats against the system tables are way out of
whack. I can't help you there.
I hadn't heard of the error, but it makes sense. It's not a problem if you
don't use sequences.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI
LONG
Sent: Thursday, March 15, 2018 11:07 AM
To: ids@iiug.org
Subject: Re: RE: RE: RE: RE: Issue with statistics [40861]
Ok, that is making sense. I will see if I can track those dates down. Thanks
My dbschema -hd customer -d storesdemo didn't seem to work. When I ran that
on my test box, it just seems to hang. I hit control-C to stop it. I just
ran it from the command prompt. Is that correct?
I also read online that there is a warning with the dbschema command. Do I
need to worry about this or is that if I use the dbschema utility to check
something else other than the statistics distribution?
******Use of the dbschema utility can increment sequence objects in the
database, creating gaps in the generated numbers that might not be expected
in applications that require serialized integers.*****
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
What does this onstat -g ses show me?
I am seeing session id's, total memory, used memory and the 20 or so lines
that shows all have dynamic explain = off.
Also the onstat -g sql says unable to open input file sql.
Not sure what these were for...
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.welcome.doc
/welcome.htm
On Thu, Mar 15, 2018 at 1:41 PM BENJI LONG <ruggedmouse@hotmail.com> wrote:
> What does this onstat -g ses show me?
> I am seeing session id's, total memory, used memory and the 20 or so lines
> that shows all have dynamic explain = off.
>
> Also the onstat -g sql says unable to open input file sql.
>
> Not sure what these were for...
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
From what I could get online, I did a onstat -g ses and it show about 20
session id's.
All of them had an empty hostname except two, so I picked the one that had the
secondary which I am on and ran
onstat -g sql 12
I was expecting that to show me the sql it was running but it came up empty.
- - - - Lock Mode of Not wait, then 0 0 FE Vers 9.24 and then Off
For both onstats (-g sql, and -g ses) - supply the session ID of the query
that is taking a long time to run.
If you don't know the session ID, and if there's not much else running, use
"onstat -u", and looks for sessions where the flags do NOT begin with a "Y"
and the username is appropriate. Run "onstat -g ses <sid>" to confirm that
this is the session you're looking for.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI
LONG
Sent: Thursday, March 15, 2018 12:41 PM
To: ids@iiug.org
Subject: Re: RE: RE: RE: RE: Issue with statistics [40863]
What does this onstat -g ses show me?
I am seeing session id's, total memory, used memory and the 20 or so lines
that shows all have dynamic explain = off.
Also the onstat -g sql says unable to open input file sql.
Not sure what these were for...
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
That's likely a system thread.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI
LONG
Sent: Thursday, March 15, 2018 12:54 PM
To: ids@iiug.org
Subject: Re: RE: RE: RE: RE: Issue with statistics [40865]
>From what I could get online, I did a onstat -g ses and it show about
>20
session id's.
All of them had an empty hostname except two, so I picked the one that had
the secondary which I am on and ran
onstat -g sql 12
I was expecting that to show me the sql it was running but it came up empty.
- - - - Lock Mode of Not wait, then 0 0 FE Vers 9.24 and then Off
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Ok, I am going to try this tomorrow. I'll see if I can get them to tee up the report and then I'll grab this information and get back to trying to find the execution plan. They aren't getting back to me with those dates they are running in the query, so they must be busy on something else.
Benji, I would like to suggest you look at coming to The IIUG Conference in October. You will leave learning much about the Informix product as Well as meeting the nicest DBAs you will ever meet. http://www.iiug.org/iiugworld/ Bruce Simms St. Louis, MO IBM Champion -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI LONG Sent: Thursday, March 15, 2018 2:11 PM To: ids@iiug.org Subject: [IE] Re: RE: RE: RE: RE: RE: Issue with statistics [40868] Ok, I am going to try this tomorrow. I'll see if I can get them to tee up the report and then I'll grab this information and get back to trying to find the execution plan. They aren't getting back to me with those dates they are running in the query, so they must be busy on something else. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. This message contains proprietary information from Equifax which may be confidential. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e-mail postmaster@equifax.com. Equifax® is a registered trademark of Equifax Inc. All rights reserved.