Not licensed for AWR nor ASH ? Try Lighty with L-ASH and statspack instead !
If you don’t have the diagnostic and tuning packs licensed or if you use a standard edition, features like AWR and ASH are not available. These features are essential to analyze the activity of your databases.
ASH (Active Session History) is certainly the most improvement in recent years in terms of monitoring activity.
It has radically changed our approach to performance problems. This feature has now become essential and many articles have been written about its usage.
An alternative solution exists that allows you to have all the necessary elements to make your analysis :
Install Statspack , L-ASH and use them with Lighty
Statspack
Statspack is the ancestor of AWR. It has been deprecated since the 10G version. Less complete than its successor, it is still a good alternative to the AWR.
L-ASH
L-ASH is a small utility delivered with Lighty. It is less complete than ASH but simulates this feature by taking a snapshot of all active sessions every 10 seconds. This snapshot is stored in the table L_ASH (ASHSTAT schema).
It comes as sql scripts in the directory L-ASH or downloadable from L-ASH.zip
It is very simple to install Statspack and L-ASH (See Documentation).
Once the installation is complete, you have to configure Lighty. This can be done by changing the properties of your database.
Lighty
The software is able to extract information from statspack.
Franck Pachot (Twitter @FranckPachot), an oracle expert working at the company dbi services , wrote an excellent article about using Lighty with a focus on statspack :
The power of the activity viewer tab can be used with L-ASH in the same way that you would use ASH datas.The main graph gives you a clear understanding of the activity. You can then drill down into these statistics by customizing the dashboard gadgets.
You can set filters, select a time range,
display the activity (SQLs, Sessions, Wait Classes, Events, Segments, Blocking Sessions, …)
L-ASH is used when selecting a sql id to display the timeline statistics.
You know immediately who, when, and how !!
L-ASH is also used when selecting a session id to display the timeline statistics.
You know immediately what, when and how !!
Conclusion
Even if you don’t have AWR nor ASH licensed, this configuration allows you to have all the necessary tools !!
It’s always cool to know that we have the right tools for future analysis.
Just in case…
