pajama

INTRODUCTION

In some circumstances small mistake in managing an IBM® Informix® Dynamic Server™ (IDS) can generate significant performance problems. A common mistake is to not monitor the number of sequential scans in an instance.

Often the Database Administrator (DBA) analysis the tables when they are created but does not continue to monitor their usage, so new users or new applications can query the tables using a different WHERE condition that is not optimized. This can generate sequential scans and in some circumstances can create significant performance problems. The sequential scan is the slowest way to query a table. 

An example is an update or a delete with WHERE conditions. If the table involved in this operation uses an index scan, the rows will be read directly and a lock will be put only on the rows or pages that satisfies the conditions. 

If the optimizer, instead of using an index scan, chooses to perform a sequential scan for the rows that satisfy the conditions, the following problems can arise:

• Unnecessary pages are read from disk. This mean a lot of I/O operations, loss of hardware resources that can impacts the instance performance.

• Increase in the number of locks used. If the table is very large and this operation is executed during the peak time, the lock requests can rise until the maximum limit allowed is reached. Other users that are executing operations in different tables will not be able to execute operations that request locks and they will receive an error message.

• Lack of appropriate indexes on large tables.

• Statistics are missing or are not updated correctly using UPDATE STATISTICS.

• Query the partition table to identify which tables have a large amount of sequential scans.

• Understand why the optimizer is not choosing an index scan when accessing those tables.

BEFORE YOU BEGIN

• If TBLSPACE_STATS configuration parameter in your ONCONFIG is set to 0, the partition profiles is disabled then all columns in the sysptntab will contain the value 0, therefore the methods shown above will not return any useful info.

• The command onstat -z will reset all the statistical information. If this command was executed recently this can generate false results.

• When the database instance is started or restarted all the statistical information is reset.

To find the name of the tables with a high number of sequential scans and to improve the performance you follow the following steps:

1. Query the partition table to find the tables with a high number of sequential scans

From dbaccess connect to sysmaster then execute the following select:

      SELECT  dbsname,tabname,b.partnum,pf_dskreads,pf_dskwrites,pf_seqscans
      FROM    systabnames as a, sysptntab as b
      WHERE   pf_seqscans > 200 AND a.partnum=b.partnum


This select will display only the tables that had more than 200 sequential scans performed since the database instance was started or since the last onstat -zwas executed. 

Example


dbsname       databasename
tabname       tablename
partnum       2097154
pf_dskreads   265432456
pf_dskwrites  543678954
pf_seqscans   34000



2. Check pf_dskreads, pf_dskwrites, this number represents the number of pages read and write from the disk per table. If those numbers are low then it could mean that it is of no great importance to optimize the access method for this table. 

3. If pf_dskreads, pf_dskwrites,are quite high then the DBA need to analyse the activity on this table. Try to discover which users are using this table and analyse the SQL statements that they have submitted. 
The command onstat -g sql 0 > file.out can help the DBA to obtain all the sql queries that are being executed in a particular moment. In version 7.x the command onstat -g sql 0 doesn't exist. However you can have the same result executing an awk script, for more information regarding awk look at the Related Information section at the bottom of this article.

4. once you have found the query that you think may generate a sequential scan you can use the sql command SET EXPLAIN on to obtain the path that the optimizer has chosen to execute the query. If the explain output shows a sequential scan try to improve the performance with one of the following actions:

• Create the appropriate indexes for these queries.

• Execute UPDATE STATISTICS (medium or high) for these tables. To generate the proper update statistics for your database, have a look at the Article Update statistics script. There is a link to the article at the end of this page.

The DBA can use a different approach that will avoid part of the activity in the sysmaster. The following command will read directly from the shared memory reducing the impact of the previous select on sysmaster:

1. Execute the following command from prompt:

onstat -g ppf|awk '{ if ($12 > 200) print }' -

where $12 is the twelfth columns that in 7.31, 9.40 and 10.00 represent the column "seqsc" that contain the number of sequential scans executed by the partition. Note that in other informix versions the position of this column "seqsc" can change.

2. Check “isrd” “iswrt” that represent the number of pages read and write related to this table. If those numbers are low it could mean that it is not important to optimize the sequential scan for this table.

3. Transform the partnum into decimal form. 

4. To find the name of the table, from dbaccess connect to sysmaster then execute the following select:


SELECT dbsname, tabname 
FROM systabnames 
WHERE partnum= xxxx    
</code>
where xxxx is the partnum transformed in decimal. 


5. once you have found the query that you think may generate a sequential scan you can use the sql command  SET EXPLAIN on to obtain the path that the optimizer has chosen to execute the query. If the explain output shows a sequential scan try to improve the performance with one of the following actions:

• Create the appropriate indexes for these queries.

• Execute UPDATE STATISTICS (medium or high) for these tables.