Panorama: Long-term trend analysis of Oracle database workload

Why

Oracle DB allows retrospective evaluation of workload of active sessions by Active Session History (ASH) if you have Enterprise Edition and Diagnostics Pack.
The default retention time for ASH is 7 days back from now, usual settings for production are around 30 days.

But sometimes you need to analyze the evolution of database load over longer periods like over many years (e.g. as base for hardware planning and capital budgeting).
There are less builtin alternatives yet:
  • Increase the ASH retention time up to years leads to a huge amount of data, cumbersome or impossible to handle
  • Using the AWR-warehouse function in an EM Cloud Control installation may fit your needs, but especially for single or less database instances this causes an inappropriate effort.

What

Panorama-Sampler allows you to extract some information from ASH and store it compressed as summary other periods between one hour and one day.
The Panorama-GUI contains table-views and diagrams for evaluation of stored workload history.

You can let Panorama-Sampler feed this long-term trend data from original ASH as well as from Panorama's own ASH-like sampling.
This way you don't really need EE and Diagnostics Pack. Sampling this long-term trend data also works for Standard Edition.

Stored data contains the summary time of all active sessions over the snapshot period, distributed by some topics. This topics are:
  • Instance-Number
  • Wait-Class
  • Wait-Event
  • User-Name
  • TNS-Service-Name
  • Machine
  • Module
  • Action
The size of the stored long-term data is much smaller than the ASH source data so it is possible to store several years of workload with acceptable storage requirements. 
For one snapshot per day storage footprint is about 1/1000 compared to ASH data. 

How

Configure sampling engine

You configure your target database like shown for Panorama-Sampler and activate long-term trend recording independently for each target database:


You may control for each topic if it records the active time/session load for each occurrence of this topic or not. This allows you to influence the amount recorded data.

A value for "Subsume limit" sets a limit for less relevant values of a topic to be subsumed under "[OTHERS]". This feature also reduces the amount of recorded data. 

Evaluate stored long-term trends with Panorama

Evaluation of long-term trend data is similar to evaluation of ASH data in Panorama.
Start with menu entry "Long-term trend":



Choose time and first grouping criteria to start digging into the recorded data:





Show evolution over time in diagrams:




Dig deeper in top-down way by clicking the number of distinct occurrences of a topic, like "User-Name":







Show evolution over time for your selection:



Conclusion

There is an easy way for long-term analysis based on Active Session History.
For the frequently used 10 TB DB system shown here as example, the stored long-term ASH for the last four years consumes about 55 MB of storage capacity with a data accuracy of one day.





Location: Dresden, 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