Re: 7.3 schedule

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