Oracle performance issue when fetching BLOBs
Guys, I just came back from a customer who has a problem, they are keeping a large BLOB which contains serialized java classes per each subscriber in their system.
Usually Oracle fetches the BLOB fast enough for them to deliver within their performance goals, but once in a while (about 0.5% of the cases) the fetch takes longer times and causing them delays that are unacceptable.
I have two questions for you:
(1) is that right that Oracle is not a "real-time" system, therefore it will not guarantee response time? why does Oracle sometime takes more time than average to process a query, and can this be optimized by configuration??
(2) does it make sense to store the data not in the database (e.g. file-system, LDAP, SAN, gFS, ...) given that all we need is to be able to lock, read and write the data.
What do you think??
Comments
Leaving aside the strong urge I get when I hear of abominations like this to throw the uber-smart-pants' laptop into a fish tank (class compatibility issues? who said that?!), a database does seem like a particularly bad place to keep binary class files. BLOBs can be tricky, and might cause the hick-ups you've mentioned. If they *must* access it via tabular entities(for non-trivial select/where/order purposes, for example), they should keep a soft link - i.e. a path to the file system in the database and make sure that when updating, both actions occur simultaneously - i.e. updating path column and physical file - something which can be done using declarative transactions. A whole better approach altogether would be to store only the state of the classes, but I guess that's not possible due to some decision taken somewhere in the beginning of this Via Dolorosa. You know what? shag this. Simply tell them they got what they deserve, no more no less :)
and regarding realtime database - let me know if they find one. Database is a multithreaded, multiprocessed, behavioral, statistical being, and as such non-deterministic unless viewed statistically. In the meantime, if they want to get production-ish, they should probably start getting rid of fancy shmancy constraints: foreign keys etc., which take down performance on critical tables.
(Zvika)
you should probably play with the lob storage, the default in oracle is in-row if the length is equals to or less then 4000b, else it will be stored in the lob segment. you can change this behavior.
an extreme ides is to serialize the objects to text and store them as text columns...
see this:
http://www.anysql.net/en/oracle/lob_inout_row.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1421...
http://www.dba-oracle.com/t_table_blob_lob_storage.htm
Back in the year 2000, I dealt with a system that stored MPEG video files and images (which were generated from each video) inside oracle as BLOBS and since then I had the chance to work with Oracle BLOB's at least twice more, once with direct JDBC and once with the Hibernate API. As for your questions:
1-As long as you are not running a real time OS (such as VxWork) there is no guarantee for the response times. The time required to retrieve a BLOB depends on many factors such as the table and tablespace definitions (usualy it should have its own tablaspace),the oracle chunk size used, and so many other factors that it would be impossible to point to the exact problem without a complete statistical analysis.
2-No, it does not make any sense. You will not get any performance boost from that and will have to write code for accessing and locking files in a multi-threaded environment. And what will happen with transaction management? how will you "roll back" an OS and DB write in the same transaction?
I suggest you gather statistic using the oracle stat packs, but take note that it is an iterative process that will take you some time to complete and I am not sure if at the end you will get the pseudo real-time feel they are looking for.
A good place to start with any oracle problem is: http://asktom.oracle.com
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3084...
Hope this helps,
Shlomo.
Thank you all !
This is my answer to the customer:
(1) you need to make sure that the LOB are stored in the LOB segment and not in-row. By default Oracle stores LOBs that are under 4Kb in-row, but the default can be changed.
(2) it might be helpful to store the LOB segment in a separate table-space.
(3) Generally the fetch LOB performance is most effected by the chunk size, which is configurable.
This is an outstanding paper by ESRI (GIS stuff):
http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/blo...
They disagree with you on point number one above:
"Tests by ESRI have shown that allowing storage in row provides the best performance, so you are advised not to disable in-row storage."
The rest of the paper discusses the other issues.
Shlomo.