Re: Program delays (Pro*C)

On Thu, 15 May 2003 06:31:05 +0000, Dilan wrote:

> We have been experiencing some delays in a transaction intensive OLTP
> program written in COBOL running under Win2K. After narrowing it down to an
> embedded sql statement EXEC SQL COMMIT WORK END-EXEC., to get more clarity I
> re-wrote the program in C. I was able to reproduce the delay in a block of
> code that the Pro*C pre-compiler generated for the embedded SQL directive
> "EXEC SQL COMMIT WORK." The line reads
>
> "sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);".
>
> Looks harmless, but has anyone seen anything like this before. Are there any
> name resolution operations, potential timeouts around this function call.
> The longest delay I have seen is about 62 seconds.
>
> TIA
> Dilan

Well, SQLLIB calls are not documented, so I cannot help you there. Have you monitored the database side? Any problems there? What does v$session_wait show during that wait? Do you have a gigantic log_buffer that gets filled and then you have to wait for LGWR to write all of it down to something slightly slower then a 5.25 floppy? If not, database, there is also the network component. Any problems there? Have you tried pinging the box?
If the database is 9i, set event 10046, level 10 in your session (dbms_system.set_ev will do the trick) and analyze the resulting trace file with tkprow, wait=yes. If you have good reflexes, monitor gv$session_wait as well.

-- 
Mladen Gogala
Software is like sex, it is better when it is free.
Linus Torvalds