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 5 more comments

2 Answers 2

The query is sometimes fast because it is in the database's cache.

Make a test:

  1. alter system flush shared_pool; (as sysdba - NOT IN PRODUCTION !!)
  2. execute the query and display execution plan;
  3. execute the query and display execution plan (should be faster because the query is not parsed but only executed );
  4. alter system flush shared_pool; (as sysdba)
  5. execute the query and display execution plan (should be slower the query is first parsed then executed)

In a ideal world, a query is parsed once and bound/executed over and over.

share|improve this answer
add comment

Are the plans always the same? The Oracle optimizer is a pretty complex beast - just ask Jonathan Lewis who wrote a book about it - 536 pages and that's only the "fundamentals"!!! From here

Have you ever been in a situation in which some database queries that used to behave well suddenly started performing poorly? More likely than not, you traced the cause back to a change in the execution plan.

Could have been written for you! :-). This might also be of interest - it seems to be predicated on the philosophy of "better the devil you than the devil you don't" - i.e. just go with a query that performs reasonably well under most circumstances rather than taking the risk of the optimizer going mental and thrashing your disks once in while - the downside being that superior plans will also be missed - Ah... engineering - it's all about compromises...

Another thought - you mention that "This is on a test database hosted on my PC" - I know that on a good many dev machines that I have used, one occasionally gets spikes in I/O for reasons that can only be divined by the Oracle (in the Classical rather than the RDBMS sense). If you haven't segregated your OS, server, temp and data onto different disks and/or arrays, then all dev machine testing will be subject to quasi-quantum variation.

I've noticed (this is anecdotal and I don't claim omniscience) that it seems to happen more on machines with RDBMSs (esp. Oracle) - caching/flushing/checkpointing?? If you could test this on a RAIDed system, you might obtain a more consistent query response time?

I know that this is "hand-wavy" and "waffly", but c'est la vie...

share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.