Entering content frame

Procedure documentation Solving Performance Problems with a SELECT Statement Locate the document in its SAP Library structure

Use

This procedure tells you how to solve problems with a single SELECT statement.

This procedure reflects SAP note 29873.

Prerequisites

Procedure

  1. Determine the SQL statement as follows:
    1. Activate the SQL Trace immediately before execution of the problematic transaction.
    2. Start the transaction to be traced in the old window. Once finished, switch off SQL Trace by choosing TRACE OFF .
    3. Choose LIST TRACE .
    4. In the displayed listing you can analyze all SQL statements with their respective database duration in the DURATION column. The values are shown in microseconds.

For more information on SQL Trace, see Structure link Performance Trace: Overview.

  1. Produce a query plan as follows:
    1. Place the cursor on the relevant SELECT statement.
    2. Choose EXPLAIN SQL .
  1. Evaluate the query plan as follows. If it is clear from the plan that no index is used but that a sequential scan is performed or an unsuitable index is used, this can have several reasons:

$ 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 SELECT statement. To do this, you should construct a SELECT statement that structurally resembles the one to be analyzed.

Example

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:

Leaving content frame