IMO, It is good to have the connection open only when the DB access is required and close that once you are done with DB operations. If there is a sequence of opetations to be done with the database within a procedure/function, then it might be good that open the connection once the control enters the procedure and close that before the control exits from there. So far I have been sticking to this way of approach. But should be careful in closing connection then and there after use.
When Application/Session level db connection are used, then the connection per user is open till the time user quits the site, even when the user is roaming around the static pages.
May be you would get good inputs from others in this forum too.
Hope that helps.
- Vijay G
Strive for Perfection