Why? Oracle should be smart enough to cache a frequently used table in RAM.And if they're all frequently used? :-)
Furthermore your problem was that the query was spinning lots of CPU.If I gave that impression, it was erroneously. Performance was decreased, but that doesn't mean more CPU necessarily. Basically the question you ask the profiling tool is "how much time is being spent doing foo?" Whether that time is spent doing IO or spinning the CPU doesn't matter. It's still time spent. And if the time spent is 15% of the overall time spent across the system then it's a good candidate for optimization.
I'll have to talk to the DBA on Tuesday to get the particulars. The developer who rewrote it was a little fuzzy on why it was so slow in the first place (this was two years ago). He just remembered that it had something to do with file IO, and pinning the table made no difference.