I'm currently working with a customer who is transferring data from SQL Server to Oracle using SSIS and a 3rd party plugin (Attunity Connector for Oracle). Some of the data that is being transferred has a target type in Oracle of CLOB and there are certain parameters in the plugin that can be changed if required.
One of the parameters is "LobChunkSize" and this is currently set to 32,768. It all works as it is, but there's a lot of data that needs transferring and what I'm wondering is whether performance could be increased if this parameter was altered. I'm by no means an expert on LOBs and chunk sizing, but the reason I'm wondering is because I had a look at the Oracle end of things:
- the database block size is 8,192 (using: SELECT VALUE FROM v$parameter WHERE NAME = 'db_block_size')
- the chunk size of the table containing the CLOB is 8,132 (using: SELECT DISTINCT dbms_lob.getchunksize(clob_col) FROM table_with_clob)
Is chunk size related to block size? Should they be the same? Should the "LobChunkSize" be set to 8132 or 8192? Or would it not really make a noticeable difference?
Platform is Windows Server 2008 (R2) Enterprise and Oracle version is 11.2.0.4.
Thanks