One very simple idea - zap all cached query results whenever you do an insert or update or call PL/SQL (new etc) unless specifically told not to. Before rejecting this simplistic notion out of hand consider that even the short-term caching that would result from this brain-dead approach is better than no caching at all.
don: while i agree that this approach is better than no caching at all, i have to say that it would take just a little more effort to maintain the cache in a smarter fashion and this would give us a great increase in performance in comparison the simplistic approach of blowing away the cache completely every time. one concrete example of this is the site node cache. until a few weeks ago the site node cache would be blown away completely on creation or update of a site node. on a site with a lot of site nodes (sloan has around 8500 site nodes) this was adding about 30 seconds of overhead for the creation of each site node. creation of a community will usually cause the creation of about 8 to 10 site nodes. you can see how this can become unreasonable. by being a little smarter about how the site node cache is maintained i was able to reduce the overhead to something reasonable (i believe the cache maintenance overhead is now in the order of XX milliseconds). check out the site node code in the development tree.
i know that maintaining this cache isn't as easy as the site node cache but we should be able to come up with a scheme that works. one possibility is to parse any FROM clauses and key our cache on that. when a table is updated we mark any cache entries that have that table in the key as dirty. i know there are plenty of holes that need to be worked out in this method, it is only an initial suggestion.
