My main experience with Hibernate but I could give you a nice long list of examples. The big problem is that there is a pretense of transparency and simplicity when it doesn't actually exist.
For example, Hibernate tries to optimise how much it loads and saves. This means it doesn't load foreign keys automatically and it has its own logic for exactly when and how it flushes data.
If you are loading an object it will by default not load the collections of objects that are related to it - that will happen lazily when those properties are accessed. Sounds fine, right? Exactly what a good developer would do by default anyway. Until you pass one of those objects around to a naive code module that knows nothing of the database and it tries to access the property - which looks like a nice in-memory collection to them - and it now executes an expensive query on the database that you weren't expecting. In fact, the naive code module might get a whole list of these things to work with and it might sequentially query each one. The best case here is that it's thousands of times more expensive than it should be, but the worst case is far worse - the database transaction might have ended or have been rolled back (which generates a giant exception+stack trace), or the rows might have been locked (which might just make things slow but could result in deadlocks since this naive code doesn't know anything about the database).
The way I see it in the end is that ORMs try to hide some of the most important parts of your software design from plain sight. You feel like they are hiding trivial stuff that is boring and you don't want to waste time on. But in the end that stuff is absolutely critical to your design and some of the most core stuff you should be thinking about. Having it all hidden away makes you not think about it at all until its too late and you've built a whole giant application on shaky ground.
After being through this enough times I am now expert enough to confidently build applications using Hibernate and save time over plain SQL. But the body of knowledge I had to accumulate just about Hibernate is equal to all the knowledge I ever had to learn about plain old relational databases. These days, if I start a new project using an ORM I try to minimize its use down to just being a convenient utility for loading rows and map them into an object's properties.
The one thing I will say in their defense is that when time is your most critical factor - eg: you're a startup with 3 months of runway and your end product is going to be mainly demo ware to get your next round of funding - an ORM like ActiveRecord, etc. is absolutely the right thing.