| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Excessive fetching
Hi,
I'm a software engineer working on a product which requires oracle. I know this is a mainly DBA list but my company's DBA turned me onto it when he could no longer answer my questions. I'm hoping someone here can help.
Since we're using C++ when the product was first designed OCCI didn't exist (in Oracle 8) we used ocicpp. It has now been determined that this C++ library is incomplete and in general not a great performer (of course our wrappers didn't help either).
Recently a decision was made to tie us more directly to oracle to increase our performance and to take advantage of some other oracle features. We're now going with OCCI on Oracle 9i.
As a proof-of-concept I wrote a little test in OCCI where we could time some of our DB operations and compare them with our current numbers.
One of the queries we're testing in an INSERT of a row:
INSERT INTO my_table (id) VALUES (:1)
I then lock the row to update a CLOB column:
SELECT my_clob FROM my_table WHERE id = :1 FOR UPDATE
Now when a run a 10046 level 12 trace on my test program (which does 1000 such inserts) I see that it executes the SELECT 1000 times but fetches 2000.
I originally thought this is because how CLOBs are stored but in the code we're currently running (which we want to replace) 1000 of these inserts result in 1000 fetches.
Even with the extra fetch per row our performance numbers are much better than they were but I would like to make then as good as they can be by removing this unnescessary fetch.
Any help you can give me will be greatly appriciated!
-Mike
 |
 |