process blocked on cache

[Tim Nelson]

I have seen a number of posts about this but have not yet been able to find a solution, if ther is any.

I have a process that uses a temporary sort space on disk. Almost all the sort is running out of cache as I see 99% cache hit rate and almost all io for the process is logical io. The process spends most of it's time 60% or so blocked on Cache. I have been fiddling with filesystem mounts for this space using the tmplog and nodatainlog but cannot seem to free up or lower this % waiting on Cache.

Any suggestions ? I am sure the process is running very well, just looking for options to try to make it run better. ( maybe making something good = better is not worth the time)

[A. Clay Stephenson · Accepted solution]

Well, if it is doing almost all logical i/o as opposed to physical i/o, there's not much that can be done from an OS/filesystem tuning perspective. The real fix is the application itself. It looks as though it should be doing much more in-memory activity than it currently is. You don't bother to mention your OS relase but 11.11 is more efficient at searching the buffer cache than is 11.0. In a few case, I've actually seen improvements by decreasing the size of the buffer cache -- which is counter-intuitive.
It would also help to know if your box is under any memory pressure. I would also suggest that you go with a static buffer cache because that makes analyzing results much, much easier.

[Tim Nelson]

Thanks for the response. Sorry for the delay in responding and giving points.

Appreciate the feedback.

Tim