Most Oracle professionals who focus on performance optimization are familiar with Method-R, which Cary Millsap tirelessly promotes.  Briefly stated, this method focuses on a specific process which is not performing satisfactorily.  We trace the process and see precisely what the process is doing.

We recently had a problem in which client connections were failing about half the time.  While this wasn’t a performance optimization problem, we used the same basic concepts that we use for performance problems.

1. We decided to do Oracle Net tracing to get more information. This is similar to doing a session trace when we’re investigating a performance problem. Unless it’s quickly obvious from a system level what queries are causing problems, a trace is one of the primary methods of further investigation. In the connection case, pings and tnspings are good starts, but if it’s not obvious from them what’s happening, Net tracing should be used.
2. We determined the problem was ours to figure out. The people at the client site had done what investigation they could, and asked us for help. Database Specialists has always been strong at trying to help solve issues, even if they appear to “not be our fault”, rather than pointing fingers or just saying “don’t know”. In this case it turned out that the issue was related to networking settings, so being willing to dig in wound up giving us all a learning experience that will help us in other installations.
3. We opened a Service Request with Oracle Support, and also bounced things off our Database Specialists team. I’ll admit that opening an SR tends to be more of a last resort. Bringing in the team is important because each of us has a slightly different skill set, and we also think about things in different ways. In most cases, we’ll resolve the problem faster based on DSI team input than with Oracle Support’s help.
4. We slogged through the trace files. I won’t claim to be an expert at interpreting Oracle Net trace files. But looking through quickly for address-like items, when the errors indicate addressing as an issue, isn’t too hard. I found an address that didn’t resolve, Iggy found that connections were being redirected (to an incorrect alias), and we were able to resolve the issue.  Oracle Support came to the same conclusion not too long after this.

This was just a case of using the standard practices of Database Specialists to break a problem down in manageable pieces and then the solution(s) became obvious.  By tracing the activity, and utilizing a team approach, we were able to make a customer happy.