After you decide that the system may benefit from having a table loaded in memory, you must decide whether the table should be included in SDO cache. The following guidelines can be followed in this analysis:
An SDO cache table can be read-only during an application server session. This means that only pure read-only tables are good candidates for SDO as the updating of SDO tables is more involved, requiring that the application server be recycled twice.
Only data object getObjectByKey() calls can take advantage of the SDO caching mechanism.
Query objects cannot be set up to be SDO cached.
Tables with a large number of rows may benefit by following the SDO cache mechanism along with expiration logic as a limited number of rows can be stored in memory forcing database calls for the less frequently referred rows. Incorporating expiration logic is not normally recommended.
Only a finite number of tables can be marked to be memory resident or be stored on the SDO cache because of memory limitations. Careful estimation of the memory requirements is required before marking a table for SDO. Factors to consider are the number of rows and record size and typical growth in record access rates for SDO.