SQL*Forms performance problem

We have been having some performance problems with a particular SQL*Forms 3.0 application. The form runs on an RS6000 against a local database. The database runs at revision 6.0.34.

We have looked at various statistics (bstat/estat, monitor, etc) and haven't noticed any contention problems. Right now all we know is that one particular query accounts for 95% of the total execution and elapsed time the application uses. The query requires a large number of consistent reads.

From what I have read, consistent reads should not bee that expensive. However, in this application there seems to be a strong tie between high CPU/elapsed times and number of consistent reads. The nature of the application is such that multiple users are reading and writing to the table at the same time, however, the set of rows accessed by each user is disjoint.

Any pointers as to where else we can look to increase performance would be greatly appreciated.

• Scott

This is an extract from tkrpof showing the offending query:

SELECT SR_ID,SR_SRVYQUE_ID,SR_SRVYFRM_ID,SR_SRVYTYP_ID,SR_RESPONSE,SR_RESPO NSE_LONG,SR_VSRVYR_ID,ROWID FROM SURVEY_RESPONSES WHERE sr_srvyque_id = :1 and sr_srvyfrm_id = :2 and sr_srvytyp_id = :3 order by sr_vsrvyr_id, sr_id

            count     cpu    elap    phys      cr     cur    rows
Parse:         71      36     141       0       0       0
Execute:       71    4679   45097       0   10823       0       0
Fetch:         71       1       9       0       0       0       0

Execution plan:
SORT (ORDER BY)
  TABLE ACCESS (BY ROWID) OF 'SURVEY_RESPONSES'     AND-EQUAL

      INDEX (RANGE SCAN) OF 'SRVYRES_SRVYSX_FK' (NON-UNIQUE)
      INDEX (RANGE SCAN) OF 'SRVYRES_SRVYFRM_FK' (NON-UNIQUE)
      INDEX (RANGE SCAN) OF 'SRVYSR_SRVYSX2_FK' (NON-UNIQUE)

-- 
This is my signature. There are many like it, but this one is mine.
Scott Holt                 		Internet: scott_at_prism.gatech.edu
Georgia Tech 				UUCP: ..!gatech!prism!scott
Office of Information Technology, Technical Services