Everything will depend on the implementation of the DBD. For some databases the prepare doesn't do anything (other than saving the info about the prepare) and delays the actual database-interaction to the execute. In those cases the number of "active" statement handles is only limited by the memory of your computer.

That being said, I have never experienced any problems with multiple statement handles for the same database handle. As a matter of fact, modules such as Class::DBI only use one connection for all their database work and I haven't heard anyone complaining that this is broken on some databases.