Tom Lane wrote:
> Yes, that is the part that was my sticking point last time around.
> (1) Because shared memory cannot be extended on-the-fly, I think it is
> a very bad idea to put data structures in there without some well
> thought out way of predicting/limiting their size. (2) How the heck do
> you get rid of obsoleted cached plans, if the things stick around in
> shared memory even after you start a new backend? (3) A shared cache
> requires locking; contention among multiple backends to access that
> shared resource could negate whatever performance benefit you might hope
> to realize from it.
>
> A per-backend cache kept in local memory avoids all of these problems,
> and I have seen no numbers to make me think that a shared plan cache
> would achieve significantly more performance benefit than a local one.
>
Oracle's implementation is a shared cache for all plans. This was
introduced in Oracle 6 or 7 (I don't remember which anymore). The net
effect was that in general there was a significant performance
improvement with the shared cache. However poorly written apps can now
bring the Oracle database to its knees because of the locking issues
associated with the shared cache. For example if the most frequently
run sql statements are coded poorly (i.e. they don't use bind variables,
eg. 'select bar from foo where foobar = $1' vs. 'select bar from foo
where foobar = || somevalue' (where somevalue is likely to be
different on every call)) the shared cache doesn't help and its overhead
becomes significant.
thanks,
--Barry