Oracle-DB: Retrospective analysis of blocking locks with Panorama

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:

  1. Top down analysis of lock dependency tree to identify a root blocking session
  2. Top down analysis of wait event dependencies to get an overview based on blocking and blocked events (new in Panorama)
  3. Bottom up analysis to find the root cause of a particular blocked session

Top down analysis of lock dependency tree to identify the root blocking session

Both root session analysis and wait event analysis are available in menu "DBA general" / "DB-Locks" / "Blocking locks historic from ASH".
Choose the time period to consider at first and hit the button "Blocking locks session dependency tree".






This gives a list of all detected root blocking sessions in time period ordered by the amount of wait time for all directly or indirectly blocked sessions.




This function for identification of root blocking sessions has already been described in detail in a previous block post.

Top down analysis of wait event dependencies to get an overview based on blocking and blocked events

This list shows blocking and blocked sessions grouped by their wait events.




This gives an overview over how sessions blocked on a particular wait event are dependent from blocking sessions which are currently in a particular wait event.
This way it allows to evaluate the reason of locking problems even if there are large amounts of database sessions included.

For RAC systems it is also possible to show the wait events per instance to identify concurrency between RAC nodes. 

Clicking one of the chart icons in the upper left corner shows a time chart with the amount of sessions that are affected by blocking/blocked combination.



For each combination of blocking/blocked wait event you can drill down into the blocking or blocked sessions as well as drill down into the fine grained ASH samples.
Blocking sessions:


Blocked/waiting sessions:



Single ASH samples the previous aggregations are based on:


Other links allows further investigation of the whole ASH history for particular sessions, SQL-IDs etc. .

Bottom up analysis to find the root cause of a particular blocked session

From each single ASH record of a blocked session you can pass through the blocking sessions dependency tree up to the root blocking session that is not blocked by any other session.

Here are single ASH records of a blocking/waiting event combination:




Hit button "Thread" in column "Block." to view the whole dependency tree up to the root blocking session:



This way you see all sessions included in the lock dependency tree and identify the session causing this locking cascade.

Some times you may also see a cross dependency that is cleared later on by the database by "deadlock detected". The last session before cycling in dependency is marked "DEADLOCK" in this case.



Please feel free to try out this functions and share a comment if you have been successful (or not).



Location: Oberhermsdorf, Wilsdruff, Deutschland

Comments

Post a Comment

Popular posts from this blog

Oracle-DB: How to check for appropriate sequence caching

Image
A common pitfall while using sequences in Oracle-DB is forgetting to define sequence caching. The default for sequences is cache size = 0. That means, every sequence.nextval operation triggers a single write operation on dictionary table sys.SEQ$. Especially if frequently calling sequence.nextval uncached sequences may cause significant performance degradation. Frequently calling sequence.nextval in parallel sessions may lead to ramdom lock scenarios in library cache. Setting a cache size for a sequence may heavy increase performance of nextval-operations. There's a minimal memory overhead for caching a sequence because all that's needed is a counter to increase in SGA memory and a upper limit. If this limit is reached with the counter, the upper limit is increased by cache size and the new upper limit is written down once to SEQ$. The possible drawback for sequence caching is that you loose all your values between the current value and the highest cached value if yo...
Read more

Common pitfalls using SQL*Net via Firewalls

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet...
Read more

Oracle-DB: Identify excessive logon/logoff operations with short-running sessions

Image
Creating and destroying database sessions in loop may lead to significant database load. This blog post contains more about quantifying this load. Applications should use connection pooling at client or database side to prevent excessive session creation. But how can I identify the client processes causing this sessions? Scan DBA_Audit_Trail to identify context info If auditing logon/logoff-operations is activated for your database, scan DBA_Audit_Trail. You can do this by 2 was: Alternative 1: Use my tool "Panorama" to evaluate audit trail Panorama is available here. Go to menu "DBA general" / "Audit Trail": Select start and end time for selection Filter column "Action" for "LOGON" Set "Grouping" to "Minute" Execute selection via button "Show audit trail" Add columns of your choice to diagram by context menu (right mouse button) This way you can identify the machine, DB-user ...
Read more