Re: Cached Query Plans (was: global prepared statements)

Another issue with plan caches, besides contention, in Oracle at least, is
shared memory fragmentation (as plans aren't all the same size in memory ...)

But this cache is very helpful for developments where every query is done via
prepare/execute/deallocate. I've seen it a lot on java apps, the purpose
being to benefit from the advantages of prepared statements, but without
having to deal with storing those prepared statements somewhere.

And of course, as said before, the statistics associated with those plans can
be very helpful, mostly for all those very small queries that are run very
frequently (a badly developped app most of the time, but that happens).

Le Sunday 13 April 2008 06:21:41 Jonah H. Harris, vous avez écrit :
> On Sat, Apr 12, 2008 at 10:17 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > Yes, this is worthless on large active databases. The logging
> > > overhead alone starts to affect performance.
> >
> > But somehow, all that stuff with cached plans is free?
>
> Of course not. The first time you execute a query, it is cached... so
> you pay the same penalty you do in PG, but in many cases, only once.
> In regards to plan re-use, sure there's going to be some contention on
> the hash buckets... but that can be mitigated in a lot of ways.
>
> In addition to that, Oracle collects over two thousand other
> statistics in real-time... yet somehow Oracle is quite fast. So, I
> would say that the usual complaint about collecting stats should be
> more an issue of proper implementation than a complaint about the act
> of collection itself.
>
> --
> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
> EnterpriseDB Corporation | fax: 732.331.1301
> 499 Thornall Street, 2nd Floor | jonah(dot)harris(at)enterprisedb(dot)com
> Edison, NJ 08837 | http://www.enterprisedb.com/