Increasing Sequence CACHE -vs- intermittent commits, any SGA ramifications?
45878Aug 23 2007 — edited Aug 28 2007We are getting a change management ready to increase our CACHE value for our sequences from CACHE=0 to CACHE=500000. The data warehouse tables have tens of millions of rows inserted each month, thereby the large CACHE value of 500000.
However, our DBA warned me that we do bulk commits in batches of 5,000 and he made mention of affecting the SGA. I'm not immediately realizing how the two can affect each other.
My question is, if we increase the size of a Sequence CACHE to 500,000, we are going to insert 55,000,000 rows, but we COMMIT to the database in bulk for 5,000 rows, will this have any negative effect on performance? I don't see it, and I can't find anything specific out there on this subject. I have found that increasing the CACHE will increase performance, but no research in conjunction with commits.
Thanks, Kate.