Oracle Database Discussions

Hi,

1)
We have a proc where we are passing a table name and it gives next value of a sequence corresponding to the table name. We have 100 such tables.

previously, the sequences were used with NOCACHE
which impacted the perf obviously.

We have tested again with (50,000 rows load) with cache 50000 and cache 1000 anc cache 500

--The performance was decreased when set 50000
-- Got max perf when set to 1000
-- Again perf decread when set to 500

How to know what is the optimal cache value for a sequence.
Does it depends on data load? or any DB parameter? or cache size (if so, what is that)

2)
I read some where that sequnce cache values are cached in SGA which is for all sessions. Suppose there are 200 sequences with 20000 cache. Does all these cached values of 200*200000 are stored in SGA?
Will there be any max value for the memory for seq cache?

Awaiting you inputs.

Thanks,
Kishore