PhysX is a closed source middleware physics engine developed by Ageia that Nvidia bought and is now trying to turn it into a leading brand name.
It's direct competitor is the Havok engine. Havok is the engine you see in Half-Life 2, Red Faction: Guerrilla and lots of other games.
Lots of game use PhysX, but not every game using PhysX supports it's hardware acceleration extras. An example of two of games that use the engine but not the hardware acceleration: Mass Effect(PC) & Rainbow Six Vegas 2(PC). An example of two of games that use the engine and the hardware acceleration: Mirror's Edge (PC) & Sacred II (PC).
Since no current console has a PhysX hardware card you won't see any console games with the added effects. Most likely if the game is being made for any console & a PC your only going to see limited added effects on the PC version anyway (the steam in Batman and the glass breaking in Mirror's Edge).
ATI is working with Intel (they own Havok) to bring hardware acceleration to their own cards. Pretty soon PhysX hardware acceleration maybe a moot point as ATI will be doing the same thing through Havok.
Edit: Personally, if the Nvidia card is comparable to the ATI card AND cheaper I'd buy the Nvidia for the PhysX. However, it's not that big a deal to me that I'd pay even $10 extra just to have PhysX in my games.
Last comment, MS has been looking into adding physics support into DirectX since 2006, supposedly it'll be in DX 11.
List of games using Havok Engine post 2006.
List of games using Havok Engine pre 2006.
List of games using PhysX
List of PhysX games with/without hardware acceleration