One thing I really hate about CGI is when filmmakers use it to make explosions, because they can easily be done for real (even really huge scale ones like the nuclear explosion in Terminator 2 - they made it work really well without making the whole thing out of CGI). The main example of this on my mind is when Senator Amidala's ship explodes at the beginning Attack of the Clones. It's a CGI explosion and it looks cheap and stupid. It could've - and should've - been done by blowing up a model of the ship on blue or green screen. Afterwards they could've just inserted it into the shot and digitally enhance the whole thing (adding in extra debris, if necessary, adding in the troops that were sent flying from the ship in the explosion, etc.) instead of doing the entire thing out of CGI. That way, it would've looked pretty realistic and would've had a much better effect.
CGI nowadays is really being used as the lazy way out for filmmakers. It's still new and expensive and everything, but a lot of the time it's faster than having to build sets, models, etc. so they just make it digitally. When it comes to the prequels, I really think George Lucas is using this much CGI because he wants to show off what it can do. A lot of the really big sequences (especially in Episode II) feel like they were made that big simply because George Lucas had the magic of CGI and felt he could show off what it could do. A good example of this is the digital Yoda. In one part of the movie, when Obi-Wan is talking with Mace Windu, he's flying around on a levitating chair. I have a feeling that Lucas did something like that entirely because Yoda was being done digitally, so he thought he should just do it.
CGI is absolutely not needed to make good special effects (in fact, most of the time it actually looks worse than models and miniatures). Just look at 2001: A Space Odyssey. Despite being almost forty years old, the special effects are still ten times better than ANY of today's computer generated garbage. And back then they had absolutely no computers or anything. 2001 is one of the very few movies where you really can't see the effects, and that's because they were brilliantly done. I'd pick the effects from 2001 over the effects from the prequels any day.