oracle insert delay

[porkslap]

We are in the process of investigating an Oracle insert delay.  The page in question does two inserts into two separate tables.  The first insert completes immediately but the second query can take up to 3 minutes to get written to the db.

Does CF receive any acknowledgement from oracle that an insert command was received or completed from Oracle?  It seems obvious that CF will continue to process code after the second insert which means that CF doesn't wait for the insert to be completed.

Is it possible that CF is holding  the second insert command in a buffer due to server load or some other circumstances?

[WolfShade]

There are so many variables that can come into play that might lead to the issue you are experiencing.  Unless I am missing something, CF shouldn't continue processing anything until after both queries have completed (unless you are placing them in CFTHREAD, in which case they would process independently and then CF would continue regardless of their status.)

How much data is being inserted in the second query?  Are you including any binary files as part of that insert?  Have you tested for network bottlenecks?  How many webservers are working with that database?  How many users?  Is this in production, or staging/development?  What is the architecture?

I'm sure others here can come up with many more questions.

V/r,

^ _ ^

[porkslap]

Hi WolfShade,

no cfthread involved.  I assumed that CF handled any query the same way regadless of whether it was a select, insert, etc.  There is not alot of data in either query (purchase info, name address, items ordered etc, no files, binary data).

We are using separate CF (windows)  and Oracle servers (unix).  We have many CF apps going against the same oracle server without issue.  It is only this one app and seems to be only with inserts and updates to the order_detail table - which by the way has over 2 million records.  The db folks have "indexed" the table.

The issue is in production.  The database folks cloned the prod db to dev and we cannot replicate it in dev.  The issue began right after the clone.

[WolfShade]

My first reaction is to think that the index in production isn't exactly the same as the index in dev.  Have you tried re-indexing the production db?

V/r,

^ _ ^

[WolfShade]

Hi, porkslap,

Just following up on this.  Have you manually re-built the index on the db?  Have you checked to see if there is any network latency/bottleneck that could be causing this?

V/r,

^ _ ^

[porkslap]

the DB group reindexed, problem showed up again yesterday afternoon.  We're looking into the idea of the concurrency process on the db side.

Thanks for your time so far, I'll post any news.

[Dave Watts]

CF doesn't continue after a CFQUERY until it gets a response from the database. However, it's possible that the database is not actually completing the insert before it responds to CF - or at least, that other clients aren't able to view the inserted data yet. This kind of thing is pretty unlikely, but it is possible. Remember that your database is designed to be accessed concurrently by multiple users, and has mechanisms to make this possible without showing concurrency issues to users. I suspect that's what's happening here, simple as that. You can always try running the same query from SQL*Plus and see what happens there.

Dave Watts, Fig Leaf Software