!--a11y-->
Solving Performance Problems with a SELECT Statement 
Use
This procedure tells you how to solve problems with a single
SELECT statement.This procedure reflects SAP note 29873.
Prerequisites
Procedure
For more information on SQL Trace, see
Performance Trace: Overview.
$ su - informix
$ dbschema -d <sid> -t <table> -ss <table>.sql
$ more <table>.sql
For example:
$ dbschema -d c11 -t rfblg -ss rfblg.sql
If no appropriate index for the
SELECT statement ( WHERE -statement fields, starting on the left, specified without a 'like', up to the first gap) exists in the file, this also does not exist in the database. As the creation of an index can have extensive side effects on other applications, we now recommended you to contact the EarlyWatch hotline.In the online log file there is a reference to a corrupt index, such as:
Index <abc> is now unusable. Run 'oncheck -c <xyz>',
Or a transaction terminates with the SQL error -243, -244, -245 or
-246:
-243 Could not position within a table table-name.
-244 Could not do a physical-order read to fetch next row.
-245 Could not position within a file via an index.
-246 Could not do an indexed read to get the next row.
Or, during the explicit check with
oncheck , an error is reported:$ su - informix
$ oncheck -ci <sid>:<tab>
For example:
$ oncheck -ci c11:rfblg
You must then set up the index in question again. For this, use the current version of the SAP database administration tool, SAPDBA, for this. You can find the current version on the
sapserv host. For more information, see SAP note 38401.select distinct t.tabname, c.colname, d.constructed
from systables t, sysdistrib d, syscolumns c
where d.tabid=t.tabid
and d.tabid=c.tabid
and d.colno=c.colno
and t.tabname='<table>';
If the date is not current or key fields are missing, you must renew the statistics, as described in SAP note 12184. Use the SAP database administration tool, SAPDBA, for this. You can find the current version on the
sapserv host. For more information, see SAP note 38401. For more information about new features in SAPDBA for update statistics, see SAP note 79094.The statistical distribution of all key fields should now be up-to-date. The new distribution data is nonetheless only activated if the relevant
SELECT statement is reprepared (see below).If the previous reasons are not relevant, you can then check whether the index is used to execute a

The table is
tab , the index consists of col1 and col2 .The
SELECT statement to be analyzed is:SELECT * FROM tab
WHERE col1 = 'a' and col2 = 'b' and col3 = 'c'
A suitable test statement could then be:
SELECT * FROM tab
WHERE col1 = 'x' and col2 = 'y'
It is best if no hit is returned from the statement.
Enter the following commands:
$ su - informix
$ dbaccess <sid> -
> set explain on;
> select * from tab where col1 = 'x' and col2 = 'y';
> set explain off;
> <CTRL C>
$ more sqexplain.out
From the last entry of the file
sqexplain.out , it can be seen whether the index is used. An example of the output is:QUERY:
------
select * from tab where col1 = 'x' and col2 = 'y';
Estimated Cost: 327
Estimated # of Rows Returned: 1
1) sapr3.tab: INDEX PATH
(1) Index Keys: col1 col2
Lower Index Filter: (sapr3.tab.col1 = 'x'
AND sapr3.tab.col2 = 'y' )
To now make sure that a new query plan is also created in the SAP System for a
SELECT statement, it must be reprepared. To do this, the cursor cache must be initialized. This occurs in the following situations: