Category Archives: Troubleshooting

You’ll find here scripts that will help you troubleshoot your performance problems and identify bottlenecks.

Find missing or stale statistics

Purpose

Statistics are primordial for the optimizer to choose the best execution plan possible. Sometimes you encounter a sub-optimal plan and need to find out if the tables involved in the statement are up to date. This query will help you find out which tables have been modified significantly since the last statistics gathering. For this query to work you will need the MONITORING to be activated on your tables, this is automatically the case since 11g.

Continue reading Find missing or stale statistics

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 🙂

SQL Tuning Health Check (SQLHC)

What is SQL Tuning Health Check?

Sql Tuning Health Check
The SQL Tuning Health Check is provided by Oracle (Doc ID 1366133.1) in order to check the environment where the problematic SQL query runs.
It checks the statistics, the metadata, initialization parameters and other elements that may influence the performance of the SQL being analyzed.

The script generates an HTML report with information on data collected by the script. The script has no footprint and can be run on any system.

You must connect as SYS or with a user with the DBA role.
The script takes 2 parameters, the first one is about your licensing (Tuning or Diagnostics or None T|D|N), the second is the SQL_ID that needs to be analyzed. Not that if you want to use both the Diagnostic and Tuninbg pack you have to use T as the first parameter.
Continue reading SQL Tuning Health Check (SQLHC)