Posts

Showing posts from October, 2020

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

Oracle-DB: Retrospective analysis of blocking locks with Panorama

Image
The recorded session activity in Oracle databases (Active Session History / ASH) enables the retrospective analysis of locking scenarios between database sessions. This post shows how the free performance analysis tool  Panorama  supports this investigation. Precondition for using ASH is the Enterprise Edition of Oracle DB and the licensing of the Diagnostics Pack. If you don't have licensed Diagnostics Pack or you are running Standard or Express Edition, then you can use the similar function of Panorama-Sampler to record the session activity. Panorama evaluates both sources (AWR/ASH or Panorama-Sampler) transparently in the same way. In general there are several ways in Panorama for retrospective evaluation of previous blocking lock situations: Top down analysis of lock dependency tree to identify a root blocking session Top down analysis of wait event dependencies to get an overview based on blocking and blocked events (new in Panorama) Bottom up analysis to find the root cause