The problem is not enough abstraction. If your application code sees things like cursors or SQL statements, your design is broken.
I'm talking about inside Drupal itself. Locking strategy is another one: it's impossible to optimize for one database without sucking on another. Lots of developers with experience only on one don't even know they're doing it, they think this is just how databases work.
Being database agnostic makes as much sense as trying to write code that compiles in both C and Java.
Being database agnostic makes as much sense as trying to write code that compiles in both C and Java.
Maybe if you are writing the realtime transaction processing system for Visa... but this is a CMS. Flexibility is more important than absolute speed. 99% of page views are going to be served from the cache.
The percentage of page views served from cache depends on the type of site you're building. Newspaper site? Sure, cache it all. Social network? It's going to be tough to manage a cache when the content is constantly changing and is viewed differently by each user.
In practice if you try to be database agnostic you end up with something like SAP which runs like molasses in Winter equally on all databases...