1) BLOBs and CLOBs are identical in creation and use, their major difference is in that BLOBs are used for binary data (much like LONG RAW) while CLOBs are used for single byte character storage (like VARCHAR2).
Recommended to use CLOB if data is VARCHAR, and BLOB if data is going to be graphics, image, etc.
2) Always specify the LOB storage clause, if you force the system to do a default storage each time a BLOB or CLOB exceeds 4000 bytes you could cause datafile fragmentation and performance problems. The LOB storage clause gives you control instead of the system.
3) If most LOBs in your database tables are small in size—8K bytes or less—and only a few rows have LOBs larger than 8K bytes, then use the following guidelines to maximize database performance:
• Use ENABLE STORAGE IN ROW
• Set the DB_BLOCK_SIZE initialization parameter to 8K bytes and use a chunk size of 8K bytes
4) When storing a large LOB inside an Oracle data file, you should consider a 32k blocksize tablespace (to avoid chaining) and carefully set pctfree and pctused to minimize wasted space within the Oracle data blocks. Different blocksizes can improve performance in a variety of ways:
Contention reduction –
small rows in a large block perform worse under heavy DML than large rows in a small blocksize.
Faster updates –
Heavy insert/update tables can see faster performance when segregated into another blocksize which is mapped to a small data buffer cache. Smaller data buffer caches often see faster throughput performance.
Reduced Pinging –
RAC can perform far faster with smaller blocksizes, reducing cache fusion overhead.
Less RAM waste –
Moving random access small row tables to a smaller blocksize (with a corresponding small blocksize buffer) will reduce buffer waste and improve the chance that other data blocks will remain in the cache.
Faster scans –
Tables and indexes that require full scans can see faster performance when placed in a large blocksize.
Recommended to use CLOB if data is VARCHAR, and BLOB if data is going to be graphics, image, etc.
2) Always specify the LOB storage clause, if you force the system to do a default storage each time a BLOB or CLOB exceeds 4000 bytes you could cause datafile fragmentation and performance problems. The LOB storage clause gives you control instead of the system.
3) If most LOBs in your database tables are small in size—8K bytes or less—and only a few rows have LOBs larger than 8K bytes, then use the following guidelines to maximize database performance:
• Use ENABLE STORAGE IN ROW
• Set the DB_BLOCK_SIZE initialization parameter to 8K bytes and use a chunk size of 8K bytes
4) When storing a large LOB inside an Oracle data file, you should consider a 32k blocksize tablespace (to avoid chaining) and carefully set pctfree and pctused to minimize wasted space within the Oracle data blocks. Different blocksizes can improve performance in a variety of ways:
Contention reduction –
small rows in a large block perform worse under heavy DML than large rows in a small blocksize.
Faster updates –
Heavy insert/update tables can see faster performance when segregated into another blocksize which is mapped to a small data buffer cache. Smaller data buffer caches often see faster throughput performance.
Reduced Pinging –
RAC can perform far faster with smaller blocksizes, reducing cache fusion overhead.
Less RAM waste –
Moving random access small row tables to a smaller blocksize (with a corresponding small blocksize buffer) will reduce buffer waste and improve the chance that other data blocks will remain in the cache.
Faster scans –
Tables and indexes that require full scans can see faster performance when placed in a large blocksize.
No comments:
Post a Comment