My experience is that heavy AJAX sites are working better now than a few months ago, the difference in some of them is amazing. For example, Windows Live Mail went from working half-assed in the basic view to working decently in the standard view, which is nice as that's another site I don't have to use Firefox (OK, IE7 would be the better for this) with. The biggest offender always was Yahoo! Mail, but surprise, surprise, after months without using it, I give it another try a couple of weeks ago, and now WORKS, not up to the standards of Firefox or IE, but is completely usable. I'm very happy now, because that means I can check up mail directly from Miranda, without any hoops like before.
Google sites are a mixed bag, some of them work nicely, others, well... Google Notebook is mostly unusable, and Google Docs was as well months ago. GMail or Google Reader run damn fine, and Opera is going to add support for Google Gears, so when Opera 9.5 hits final, I'm sure Google sites will work at least.
In the case of another pages, it's again a hit or miss thing, Newspond works beautifully, but has layout problems, Digg works fine now despite the cocky attitude of the company (we won't support our new comment system on a niche browser!), etc. I think that things are going for the better in the future, and soon we won't have as many problems as before, Opera's rising market share, more strict standards compliancy on part of the competing browsers, especially Safari, and the Opera guys fixing things, all combined will make it happen.
EDIT: Weird, Google Writer works just fine. Google Spreadsheets on the other hand...