Very interesting article written by Adam Leventhal.
I actually like the idea of L2ARC especially on low-end systems. Imagine a 1U or 2U x86 box with one internal disk being a 143GB SSD used for L2ARC - basically you getting 144GB fast read cache for your MySQL database (or anything else). You probably can't even put that much memory in 1U or 2U system in a first place (not to mention cost).
Now imagine much larger database. You buy additional entry-level array like Sun's 2540 and you put 12x143GB SSD drives for L2ARC (and at least one for SLOG if required). This gives you about 1,5TB of cache! You can cache relatively large database here.
Now I like the way L2ARC works - if you unmount ZFS pool and mount it again old L2ARC content will be re-used (thanks to ZFS checksums possible stale data will be detected, skipped and read from disks). What it means is that if you connect your 2540 (or whatever) full of SSD drives to a cluster and you failover your database along with your ZFS pool to another node your 1,5TB of cache will be still warm. So the impact of failover on your database performance can be greatly reduced.
Of course there are other scenarios and I'm keen to do some testing... :)