Oracle-DB: Cost of dedicated DB session connect/disconnect
A generally assessed poor technique is to create an own dedicated database session for each atomic DB activity. You may sometimes encounter such strategies like: processing a large amount of items in a loop, each processing step in the loop creates a DB session, executes a small set of SQL statements and terminates the session a polling process checks a SQL result one time a second, each in a new DB session But what costs are generated by frequently connecting and disconnecting DB session? The internal recording of database activity (AWR/ASH) has no answer because establishing the DB connection is not a SQL activity. A simple comparison of system load gives a fuzzy hint to the price of connection creation. Example: An idle database instance on a single host with 4 older CPU cores (Intel Xeon E312xx, 2,6 GHz) an external (not on DB host) SQLPlus client which executes a single "SELECT SYSTIMESTMP FROM DUAL" per DB session Delay between the SQLPlus calls is calculated so that o...
