Re: OCI (8.1.6) documentation? (for a C++ programmer)

Should be in there. Always use prepared statements. Bind , parse , execute, rebind, execute many. Look at the asktom.oracle.com site and do a search.

You will not see performance increase of 1 prepared statement vs 1 non-prepared statement running with one client. You will see a significant increase when you execute the same query (with different values) over and over again. I had a developer who did not believe me that bind variables made a difference. So he decided that he "knew better". The application was shipped (management didn't think load testing needed to be done) and customers screamed! The application used bind variables everywhere but in this particular developer's code. His code was a type of import. When the import ran with a large set of records to import most other users could no longer use the system. Why? It was NOT a record locking issue; it was a CPU usage issue. We took a 1,000 record import benchmark and without bind variables it took 26 minutes to run and chewed up CPU - 100 % usage most of the time. We took half a day to add bind variables. We ran the same benchmark. Now the whole thing ran in under 6 minutes and used an average of 5% CPU with short bursts to 35% CPU usage. Customers were now very happy. They could use the system and not notice when a large import was occurring. (and the imports were much faster also)

The other thing we did was use the array interface in OCI. We also built in some instrumentation so from the application you could hit a special key combination and all the sql statements would emit timing information to a file. Each step in the OCI calls would write out to local disk with a millisecond timer. (also the bind variables and their values) So if you wanted to look at any timing information just subtract two millisecond timers and voila you have the time it took from point A to point B. Very valuable once it got in the field. You could quickly find what statement was causing the problem. (Yes, you leave the instrumentation in, you just have a variable that says whether to write it out or not type of thing)

Jim

-- 
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Peter Vos" <peter.vos_at_dns.be> wrote in message
news:pan.2003.06.20.13.27.15.102288_at_dns.be...


> Hello,

>

> At the moment, I have the Programmer's guide from Oracle,

> which describes all that's inside OCI, but fails (at least

> for me) to explain how to use it efficiently.

> I have an application that stores a lot of queries, and

> executes them on demand. My questions are about when to

> prepare what statements, how to best use contexts and

> connections (and how not to use them - because there seem

> to be some bugs around),... and I would also like to know

> how to check what's really going on? (Maybe I prepare all

> statements, but don't get any extra performance - how do

> I check what Oracle does behind the scenes,...)

>

> Can anybody point me to a good book about OCI, or online

> documentation? Maybe even hints about using some of the

> existing C++ wrappers instead?

>

> Thanks,

> Peter.

>