You're correct in stating that IPSec stateful failover is not supported. As for how long the tunnel takes to failover, that depends really.
Worst case scenario, the other end doesn't know anything's gone wrong and just keeps sending packets to the backup PIX, which the backup PIX drops cause it doesn't have a tunnel built. It will try and build a tunnel, but the other end will deny this cause it already has one. This is a common occurrence with IPSEc, especially with different vendors, since there's nothing within the IPSec spec that defines a keepalive of any sort.
If you're building this tunnel to another PIX, an IOS router or a VPN3000 then enable keepalives on the tunnel (isakmp keepalive x y) and at least then the two ends will determine that the tunnel has gone down and rebuild it. Generally you'll still have a 30-60 second outage depending on what you set the timers to, but then everything should come up automatically.
Also keep in mind that if these are client VPN tunnels, then the client will be disconnected but will be able to reconnect straight away without making any changes to the client config.
If you're building this to another vendor, then you're out of luck with the keepalives. You can set the Phase 2 tunnel expiry time to fairly low, but I wouldn't suggest anything less than every 10 minutes or so, even higher if you have a lot of tunnels. Then worst case scenario is you'll have to wait for the tunnel to be rebuilt for everything to come back.
Command reference:
http://www.cisco.com/univercd/cc/td/doc/product/iaabu/pix/pix_62/cmdref/gl.htm#1027312
