Informix

Sergio,

 

Some things about sequential scans:

 

Detection:

 

1) Any small table will get a sequential scan.

 

2) Generally, I look at those tables with more than 60,000 rows as these are the ones that can do the most damage.

 

3) Defined, a sequential scan is the search of table without the aid of an index. So a table being searched with 7 million rows and no index available is always a problem.

 

4) Typically it's user queries in user tables you want to look at. So you could exclude the Informix provided databases from your data on tables doing sequential scans (so seqscans > 0 and nrows > 60,000) and dbsname not in ("sysmaster","sysadmin",etc.). Sort in reverse order on sequential scans. This gives you a place to start looking.

 

5) What would be even better is to look at the queries actually doing sequential scans.

 

6) Live running queries can be found in sysmaster:syssqexplain.sqx_sqlstatement (this captures the first 32,000 bytes of the query in memory) where sysmaster:syssqexplainsqx_seqscan > 0. Here these is a small problem, informix will run parts of a query with a multi-part where clause as a sequential scan, such as a join to a table with less than 2000 rows. So capturing all of the running sequential scan queries into a file, multiple times at different times of the week, chopping them out into separate files, and adding set explain on avoid_execute over the top of a query and sending the query plan to sqexplain.out will capture how informix will run the query without executing it. Knowing the originating source code files pays off big time here.  Informix has something called the estimated numeric cost of running a query. Any query running a sequential scan will have an abnormal estimated cost (normally you see single digits or up to 3 digits for properly executing queries) and having an estimated cost of > 10,000 is worth looking at, especially if it is running a lot in a lot of sessions.

Any query with a sequential scan with an estimated cost > 100,000 is definitely a problem and those more than 1,000,000 are likely to take longer than anyone's lifetime to return an answer and therefore worth fixing.

 

So it is a bit involved, but this is how you do it and gradually, you prove your case to the programming staff this way by having the proof. Otherwise, they will say it is the responsibility of the dba to do it. Save all of your set explain outputs as evidence. Of course, if you are the dba and programmer ........ you have to fix it.

 

7) Make sure you are running update statistics as indicted in the scheduler.

 

Solutions:

 

one or more of these:

 

1) Query rewrite

2) Update statistics

3) New Index creation

 

There are several other possibilities for slow systems as well, but for sequential scans, this is a good start.

 

 

 

6) Live running queries can be found in sysmaster:syssqexplain.sqx_sqlstatement (this captures the first 32,000 bytes of the query in memory) where sysmaster:syssqexplain.sqx_seqscan > 0. Here these is a small problem, informix will run parts of a query with a multi-part where clause as a sequential scan, such as a join to a table with less than 2000 rows. So capturing all of the running sequential scan queries into a file, multiple times at different times of the week, chopping them out into separate files, and adding set explain on avoid_execute over the top of a query and sending the query plan to sqexplain.out will capture how informix will run the query without executing it. Knowing the originating source code files pays off big time here.  Informix has something called the estimated numeric cost of running a query. Any query running a sequential scan will have an abnormal estimated cost (normally you see single digits or up to 3 digits for properly executing queries) and having an estimated cost of > 10,000 is worth looking at, especially if it is running a lot in a lot of sessions.

Any query with a sequential scan with an estimated cost > 100,000 is definitely a problem and those more than 1,000,000 are likely to take longer than anyone's lifetime to return an answer and therefore worth fixing.