Unlike a great deal of Oracle professionals I started my career building Software using OO methodologies and Java. I approach the Oracle world with an application developer paradigm, and have had to unlearn certain OO precepts. Mostly I’ve had to concede that in practical development of technology solutions (greatest benefit, least cost, highest ROI) that the persistence layer can not be a big dumb chalkboard exclusively. Perhaps some of my Java colleagues may disagree but there are significant cost, implementation, and maintenance advantages to leveraging the power of a full featured RDBMS.

As I was reading Mark Rittman’s article on his Performance Tuning Excursion I was reminded of a SWAT task I was given working with the DBA group at an online retailer. The data access patterns of OO, their OO-Relational mapping components, etc can sometimes be downright counterproductive towards building a sound technology solution. I was working with an original implementation of this and witnessed a seldom used feature end up nearly bringing the entire site to it’s knees. The page received a brand name, and was meant to display the products and their respective primary categories. The result sets would range from 1 to about 100 products. I witnessed a similar behavior to what Mark describes in that the persistence layer was querying thousands of times for smaller atomic units to build the composite objects on the application server. I’ll leave it to the reader to delve into Mark’s thorough explanation…

Consider using the extended SQL trace instructions from Mark or further consider p6spy if you don’t have access to Oracle (and it’s a Java application of course).