Recently I discovered that with the Kwin window manager on KDE 4.1 (and 4.0) you can have many of the cool special effects people love without any special driver or hardware support, using XRENDER instead of OpenGL for compositing (see last picture).
These screenshots are from my old laptop, with a 500Mhz cpu and a Trident Cyber 9525 with an amazing 2.5mb video ram, and of course no 3d support.
Also, it works with Virtual Machines too! I can get cool effects using just the vesa driver on VirtualBox.
Screenshots taken with KDE 4.1 Beta 1 on Kubuntu.
You can also have shadows, that were fixed to work with XRENDER after beta 1 (so I don't have them on my laptop yet).
Most effects are completely usable, consuming of course some cpu, but as they are one-time (animations, alt+tab, etc) don't have that much impact. Semi-transparent windows of course take their toll (especially when dragging them), but if you have a decent cpu, it can probably handle it fine.
Thanks to the kwin folks, your rock!