3 ms·
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 pa
by aeyes 3y ago
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.
- SigmundA 3y agoIs 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!
- aeyes 3y agoI just made up something simple but yes, I had these problems with bind variables in prepared statements. > SELECT * FROM t WHERE x = :var; But I haven't used Oracle in years and back in the day, there was no bind variable peeking.