Usage of Read Cache Size and Write Cache Size

sumesh.abraham · Post by **sumesh.abraham** » Tue May 24, 2011 3:24 pm

Hello,

I am analyzing a set of Server jobs for performance analysis.
One of the jobs has a design as
Sequential file -> Transformer(Hashed file lookup) -> Transformer (hashed file lookup) -> Load Oracle table (DRS stage).

916990 records are passed into 2nd transformer and the lookup is against hashed file with 178678 records. The lookup and subsequent load to Oracle table (Array Size and Transaction size are 15000) takes close to 14 minutes. I am considering to raise the project default setting for Read cache size and Write cache size of 128 MB to higher like 256 MB.
Is this a good approach to improve performance?

Appreciate your inputs.

ray.wurlod · Post by **ray.wurlod** » Tue May 24, 2011 4:54 pm

Possibly, but we can't really say because we don't know how large your rows are. If the smaller cache size is working (all your hashed file data fit into memory) then allocating more memory will have no effect.

chulett · Post by **chulett** » Tue May 24, 2011 6:45 pm

Keep in mind that a change at that level will affect all cached hashed files and effectively double the memory they will allocate when running (assuming you go 128 -> 256).

sumesh.abraham · Post by **sumesh.abraham** » Tue May 31, 2011 5:18 pm

Thanks. The lookups that we perform vary from few hundred records to 200 or 3000 thousand records. My understanding is that the Read cache and Write cache are set at Project level. In this case the other jobs that do lookup for fewer records may not require the lookups to happen in memory. I just want to make sure that we are not impacting other jobs while trying to tune a handful.

Please let me know your thoughts.