Peter Ramm's Oracle performance analysis stuff

 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...

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 c...

Even if you think to have enough free space in tablespace your operation may end up in: ORA-01653 unable to extend table <xy> by <z> in tablespace <TS> The reason for this often unexpected behavior is: The table needs a contiguous free space in tablespace in next extent size to allocate a new extent. If free space in total is enough available but only in smaller slices than the required size of the extent to allocate than this will happen. Several years / Oracle versions ago fragmentation issues have been addressed by uniform extent size for tablespaces. Today this issue ist mostly addressed by locally managed tablespaces with an automatically defined limited number of used extent sizes. Locally managed tablespaces reduce the risk of fragmentation issues, but not completely. So especially if less space remains available in tablespace fragmentation sometimes becomes important. One way to check a database for the risk of running into ORA-01653 is to look fo...