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

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