Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a simple update query on a table with about 100 rows in it:

UPDATE RESULTS_DATA 
     SET IMAGE_BLOB=:myBlob 
WHERE MEASUREMENT_INDEX=:mIndex 
  AND BATCH_ID=:batchId 
  AND SYSTEM_ID=:sysId

There are indexes on all 3 columns I am searching on. The BLOB is 1.2MB in size. Sometimes the query takes 0.5 seconds to complete, but other times it takes around 3-4 seconds. Looking in the V$SQL table shows that almost everything is the same between multiple runs of the same query, but the following fields are very different:

IO_INTERCONNECT_BYTES (1277952 on fast query, 2818048 on slow query) PHYSICAL_READ_REQUESTS (1 on fast query, 27 on slow query)
PHYSICAL_READ_BYTES (8192 on fast query, 393216 on slow query)
PHYSICAL_WRITE_REQUESTS (155 on fast query, 164 on slow query)
PHYSICAL_WRITE_BYTES (1269760 on fast query, 2424832 on slow query)

This is from running exactly the same query sending exactly the same BLOB. Could anyone tell me why this would happen?

Thanks!

share|improve this question
    
Are you sure that the traffic that the database receives from other users is the same both the times? –  Rachcha Mar 11 '13 at 11:58
    
@Rachcha: This is on a test database hosted on my PC, so I am the only person connecting to it. –  user1578653 Mar 11 '13 at 12:00
    
Then have you checked with the other processes running? Depending upon the availability of your processor time your queries will perform. They will perform faster when there are fewer other applications running. Also, when you run the query for the first time, a blob will be inserted. The next time you run this query, the old blob will have to be implicitly deleted and new one has to be added to the column. This can make the query run slow. Have you checked all these conditions? –  Rachcha Mar 11 '13 at 12:05
    
Thanks for your comment. There doesn't seem to be any peak in CPU activity and when I've been testing it there have been the same processes running. Also, each time the query is run it is on a different row, so it is always updating a blank BLOB column rather than deleting an existing one and overwriting it. Also, if it was other processes causing this, would the PHYSICAL_WRITE_BYTES increase like that? –  user1578653 Mar 11 '13 at 12:25
1  
Any chance you're writing to a tablespace in autoextend mode, with a very small next? –  Mat Mar 11 '13 at 13:13
show 4 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.