| Subject: | prepare_cached needs a cache limiter by default |
prepare_cached needs more information to implement a "smart" cache.
prepare_cached caches until the cows come home by default. But, it needs a limiter in DBI by default an to not relied on every programmer to find and implement their own buggy caching limiter. The issue is that if a VERY complex system shares a database handle then it will eventually hit the databases limit (not Perl's limit). Yes, I'm using bind variables.
DBD::Oracle::db prepare_cached failed: ORA-01000: maximum open cursors exceeded
For example DBD::Oracle in my environment only allows about 1000 open cursors. What I'd like to see is smart cache implemented in DBI where the least valuable statements fall off the stack.
I had implemented a prepare cache in DBIx::Array but was planning to use DBIs cache. My limiter was hard coded at 16 statements when it hit 17 it would just clear the cache and restart. Now that DBI has a cache of it's own, I was hopping to not have to maintain that logic in DBIx::Array but as of now I still need to.
I'd like to see a smart limiter that could make these types of decisions automatically.
1) Keep a maximum of X statements cached.
2) Keep statements based on a three factor requirement.
a) The prepare database time. DB
b) The prepare age (start time). T0
b) The number of times the cursor has been used. N
c) Last time the cursor has been used. TN
The math behind what to keep and what not to keep is the golden apple.
A formula would be something like this where the weights (w0-3) would have to be derived experimentally.
w0 * DB + w1 * (time-T0) + w2 * N + w3 *(time-TN) = Statements likeliness to have value
Then the DBI prepare cache would keep the most valuable statements. To save processing power it might only keep the top X - 10% and then rerun the math when we hit X again.
Setting X to 0 would effectively turn off prepared_cache.
Thanks,
Mike
CPAN: MRDVT
mrdvt92