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

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

Oracle-DB: Link between audit trail and active session history

Unfortunately the audit trail of the Oracle-DB uses a different session identifier (AudSid) than the Active Session History (SID + Serial#). Both identifiers are available in v$Session (AudSid, SID, Serial#). So during lifetime of a session it is possible to link between the session info from v$Session an audit trail. But neither the AudSID is stored in ASH (v$Active_Session_History) nor the SID + Serial# is stored in audit trail. This prevents from combining session info of both sources after the session is closed. There is a possible way to link audit trail with ASH by establishing a logon trigger for that. The LOGOFF records in audit trail (if AUDIT SESSION is active) record also the Client_Identifier from v$Session. So supplying v$Session.Client_Identifier with the needed info allows to retrieve it from DBA_Audit_Trail.Client_ID.  This logon trigger does it: CREATE OR REPLACE TRIGGER Client_ID AFTER LOGON ON DATABASE -- Put unique session identifier into client-id to have it a
Read more