Would it be possible to store plans with an indication of the
search path that was used to find tables, and for temp tables
some snapshot of the statistics for the table if any?
My suspicions are that:
* where you have a lot of short-lived connections then actually they will often use the default search path - or a
similarone
* if a temp table is in use then normally these will be small or contain 'similar' data
There is a danger that these heuristics will be poor if long-running
connections are in play - but they have no excuse not to do their
own preparation.
Perhaps you could have named cache segments and connections
could 'opt in' to a cache segment if they want such sharing?
James