In the first article we considered the issue of managing small data volumes when working with Oracle LOBS and thinking about I/O tuning, and in the second, we considered buffer cache alternatives.
If your database is running in ARCHIVELOG mode (as are the majority of databases), the problem of generating too many logs becomes a real headache for DBAs. Since LOBs have their own storage segments, it is possible to set up the logging option, which may be different from the table owning the LOB column. Unfortunately, having NOLOGGING for a column in case of a catastrophic crash means that whole rows would not be accessible until the LOB columns are reset to a stable state. To solve this problem, the SecureFile mechanism introduced the FILESYSTEM_LIKE_LOGGING option, which preserves all metadata while not logging any changes to the LOB itself. This makes the whole table accessible even in the case of a major failure or switchover to a standby. This option may be viable if the data in the CLOB can be easily retrieved from other sources or is of a temporal nature. The option also provides a significant performance boost in terms of both time and resource utilization:
TIP & TECHNIQUE
You cannot use CACHE/CACHE READS and NOLOGGING at the same time (for all implementations—both BasicFile and SecureFile).
Leave a Reply