Phil... I'm on the shared plan and, like you, I had a lot of questions about the dedicated plan. You can see my post and Danielle's responses to my questions
here.
Like you, I wish there was a more obvious and automated way of knowing if and when a site is getting close to the "excessive resource limit"... just so there would be some time to make plans for a transition. However, my guess is, sites rarely reach the limit. And probably in most cases, it's run-away scripts. You can send in a help ticket and ask for your "top processes" and how close you are to the limit, and they will get back to you. That's what I did, and learned I wasn't even close. Now, I don't get the 90K+ visitors that stephan gets, but I do get 1000 visitors per day, and about 20,000 page views per day, and each page is dynamically generated using PHP/MySQL. So, I'd say if your scripts are efficient, you can have quite a busy site before really needing a dedicated plan. Of course, the dedicated plan does give you more control over your setup too.