Analyze database activity using v$log_history

The v$log_history view contains important information on how application’s users use the database , this view can help you define periods with the most activity in the database.

v$log_history queries

You can adapt the query to your needs, you just have to change the way you format the date to be able to drilldown to the precision you want.

This one gives the following output :

You can find out which day of the week is the most active. You can also have a day to day analysis for the last month :

here is the output :


You can also drilldown to hours in the day :

Here the output :

Be creative :)

SQLcl, a revolution for SQL*Plus users

What is SQLcl ?

SQLcl is a new command line interface like SQL*PLUS coming along with SQL Developper 4.1 Early Adopter. It is a lightweight tool (only 11MB) developed by the SQL Developer team fully compatible with Windows and Unix/Linux. You don’t need to install it so it is totally portable.

The tool does not need any Oracle client to be installed, however you need a JRE 7.0 or newer.

Continue reading SQLcl, a revolution for SQL*Plus users

Top 5 wait events from v$active_session_history

This query returns the top 5 wait events for the last hour from the v$active_session_history view.

Be careful, this view is part of the diagnostic pack, you should not query this view if you not licensed for it.

Top 5 wait events from v$active_session_history

This is obviously an approximation, because v$active_session_history contains only 1 second samples, and who knows what happens during each second sample. If you compare the time given by this query with information from v$system_event it will not exactly match but you should be close if you choose a sufficiently long period.