I would say where most DBAs mess up shared pool latch contention is not clearly differentiating the latch situation, and therefore essentially making guesses at solutions. The library cache latch is related to finding objects in the shared pool. The shared pool latch is about memory management. Just keeping these two concepts clear will have a dramatic effect on your shared pool optimization success.

While nearly all shared pool problems are highly connected, try your best to isolate the problems and apply solutions to each of them. Our solutions become muddy and watered-down if we get sloppy and don't separate the problems. A solid understanding of the core shared pool architecture and Oracle's wait interface will allow you to do a terrific job at resolving problems.

I tell my students that if presented with a multiple-choice exam question about where a memory object resides in Oracle, and you're not sure, always guess it's in the shared pool. Oracle's shared pool is packed full of all types of memory structures-everything from shared SQL to undo related structures.1 If that's not scary enough, consider this simple query: