Bad performance using OCI-9 instead of OCI-7 on Oracle 9i

We have rewritten our OCI platform to use the new OCI api functions in order to make full use of Unicode on Oracle 9i. (OCI-9). We have noticed that the new platform runs a lot slower than our old (OCI-7) api version that uses the deprecated functions such as 'olog' and friends.
Whilst comparing execution times of SQL across the two APIs (to 9i server), we noticed that the UGA level was running at around 300K on the OCI-9 api wheras it was around 4MB when running the OCI-7 api. We were wondering if this low level may be impacting performance but we have found no way of increasing this level. The PGA was also smaller for OCI-9 being 1.6 MB as opposed to 5MB.

A more interesting observation was running some simple SQL using a bound variable.
"Select col from table where col = :PRM0" table contained only the one column. The column was varchar2 for one set of tests and nvarchar2 for another. In both cases the column was populated with around 200K unique values.

We got the following results
OCI-7 OCI-9

120mS   122mS      nvarchar2 column no index
3mS     6mS        nvarchar2 column with index
100ms   105ms      varchar2 column with no index
3mS     385mS      varchar2 column with index

Pretty much what you'd expect, apart from the OCI-9 varchar2 with index.
Now we can understand that a bound unicode variable against a non-unicode index would result in the index not being used, but the time taken is nearly four times that for the non-indexed sql. Again we were wondering if the small UGA/PGA values may be compromising the optimiser or something along those lines.

Any ideas much appreciated. Also if anyone knows how to increase the UGA/PGA levels.

I should add that we are completely unicode on the OCI-9 layer, so we use unicode data and meta data, ie we put the OCI environment into UTF16 mode.
The tests were carried out against the same table on the same database using a 'mickey mouse' test program that ran the same sql against both OCI-7 and OCI-9 APIs. Received on Thu Jan 16 2003 - 05:37:56 CST