The plan cache on Oracle in combination with prepared statements where the optimizer can't peek into the parameters has been really problematic for me in the past. I usually had to go in and either add hints or force a plan.
Even simple queries like SELECT * FROM t WHERE x = TRUE; could turn into a nightmare depending on the distribution of the x values in the table.
With Postgres I rarely encountered such problems but I must admit that I haven't used Postgres with prepared statements.
I have seen some queries with slow planning time (>100ms) where a cache could have been useful but I don't remember ever really needing to optimize one.
Is x a parameter because it doesn't look like it? MSSQL has parameter sniffing and will make multiple plans based on incoming parameters I would be surprised if Oracle does not do the same. It can actually be problematic to sniff parameters sometimes and it can be disabled with a hint!
Even simple queries like SELECT * FROM t WHERE x = TRUE; could turn into a nightmare depending on the distribution of the x values in the table.
With Postgres I rarely encountered such problems but I must admit that I haven't used Postgres with prepared statements.
I have seen some queries with slow planning time (>100ms) where a cache could have been useful but I don't remember ever really needing to optimize one.