Re: Single record insert intermittently slow

From: Dennis Williams <oracledba.williams_at_gmail.com>
Date: Wed, 26 Oct 2011 15:24:43 -0500
Message-ID: <CAGRGHjwCVH4c6sJn+EkfyqmF0mBRmY0-LpJpYuj9JW4GSfKjSg_at_mail.gmail.com>

Rich,
Just let me understand your problem definition. So you are saying that sometimes it takes far less than one second to insert a single record and some times it takes more than 30 minutes? Wow! And earlier it was consistently less than one second, and you only recently saw it taking more? Here are my random questions:

     Is it completely random? No pattern to when it takes a short time or long time? Is the long time consistently long - always 30-minutes? Does it just randomly take 30-minutes then is quick for many times, then another long time?

     What else is going on the server during this time? Does the server CPU max out or go idle? What is the disk I/O during this time?

     What is the SQL like? In my experience, often erratic times results from changing execution plans. You mention that it is insert record load table conventional. Do you mean it is something like:

     insert into mytable values ( . . . . - no subqueries, etc.?
     Your table includes a blob. Does the blob size vary a lot? Is the blob

stored in the same tablespace as the other columns? Is the blob stored over many blocks? I'm just thinking whether there could be any scrambling for available blocks. Low likelihood, but just thinking.

Dennis Williams

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 26 2011 - 15:24:43 CDT