I very much agree with you that OO->Relational mapping tend to work much better for small applications where performance is not critical. But that is not to say that it cannot be used within larger more performance critical applications. We tend to use our own Object Persistence/OO->Relational tools for things like the user-administration portion of our reporting applications where performance is not critical, while using optimized SQL/DBI code for the reports themselves. It greatly simplifies the code in the user-admin portion of the site and makes it much easier to maintain, which works out well cost-wise too since that portion of the site it not really what our customers care about.
A lot of smaller applications can decompose their data into a class-to-table relational structure.
One misconception about OO->Relational mapping tools is that they are bound to class-to-table relationships. This may be true of Class::DBI (I don't know I have never used it), but the more generalized OO->Relational mapping patterns are not confined to that basic a relationship. Simple tools will map table-column to class-field, and this is only so useful. A more complex tool (or possibly custom written code) could use a longer more complex query with multiple table joins to return a result set which can then be mapped into an object. So now your class-fields map to columns in multiple tables. Of course this gets much more complicated with INSERTs, UPDATEs and DELETEs. This may require a lot of up-front work, but IMO that is the right kind of laziness.
I recently picked up a book called Patterns of Enterprise Application Architecture by Martin Fowler. It's examples are mostly in Java or C#, but the patterns themselves are language agnostic. It has a number of patterns dealing with OO->Relational mapping, you might want to check it out, very interesting stuff.