What are the implications of holding a connection to the database open
forever (and running millions and millions of tiny transactions flat out
through that connection)?

We have a system whose performance gradually gets worse and worse, and
we have a feeling that one of the things that gets it back to normal is
restarting one particular process which does all its business through a
single permanent connection. We're wondering whether dropping and
re-making the connection from time to time would help, but doing a
decent set of before-and-after experiments would take months and would
cause significant disruption to a live system.

I think we know about stored procedures not being re-optimised if the
connection is held open, but we're not worried about that in this
instance as the system is mature and the nature of the data (and hence
the statistics) isn't changing much.

So:

What other information, resources, etc, does the database hold
internally linked to an open connection?
How might these affect performance?
What difference would it make if we dropped the connection periodically?
This is with 2.1 - are there any changes in 2.5 in this area?

(I have asked a similar question some time ago but didn't end up with
any actual diagnosis of our problem.)

--
Tim Ward