We want to migrate our database from 9i to 10g. Our application has been built using Designer and Headstart 184.108.40.206. A test script which inserts, updates and deletes a number of rows in a simple table having a TAPI and CAPI is three times (!) slower in 10g than in 9i. A test with a table having only a TAPI has about the same performance in 10g as compared to 9i. So it seems that there is functionality in the CAPI's which is considerably slower in 10g than in 9i. Does anyone have the same experience?
We have found the cause. The capi performs a query on qms_rowstack very often and in 10g this query used the wrong index. By changing the order of the columns in qms_rws_uk1 index (package_name as first column) the query uses this column, resulting in the same performance as in 9i.