Result_cache blocking

Had a result_cache problem today.

If you didn’t know it already, DBA_HIST_ACTIVE_SESS_HISTORY is a real boon for getting a very good idea of a) when the problem started and b) how wide the impact was.

300 session were backing up on a latch free wait. Turns out the latch was the “Result Cache: Latch”.

The blocking session was doing a select from a view that used some result_cache function lookups.

The blocking session having been killed and given a couple of minutes to clean up, the blockage cleared in a instant.

Still trying to get to the bottom of the result cache issue – is there a serious result_cache flaw or have I just implemented it poorly?

Related info:


2 Responses to Result_cache blocking

  1. Pingback: Result_cache blocking II « OraStory

  2. dombrooks says:

    Interesting comment from Jonathan Lewis on this post

    “A point that someone (possibly Connor McDonald) mentioned at the UKOUG conference is the threat when two processes both want a result that has just been invalidated.

    One process will start to rebuild it, the other will wait for up to 10 seconds (in 11.2) for the build to complete and then decide to query the base table if it’s not ready.

    So if it takes (say) 15 seconds to rebuild a result cache entry, the first process will take 15 seconds to complete, the second will take 25 because it waits 10 seconds then spends 25 building.

    The upshot is that you should be cautious about using the feature for results that take more than about 9 seconds to rebuild if they are liable to popular use combined with occasional invalidation.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: