Archives Annuelles 2014

One more reason why it is recommended to clone a database instead of importing datas for testing

Some days ago, a customer encountered an application hang in its production database.

A little version upgrade was in progress. The upgrade was done online during the day because of the small ddl changes.

There were 4 ddls to execute. The first 3 were ok but the problem occurred when the last one started.

It’s just a column update to allow null values.

Normally, the sql runs in a few milliseconds as it modifies only the dictionary.

alter table TABLE_NAME modify COLUMN null;

But this time, the sql took nearly 25 minutes to run with a lock in the library cache, preventing access to the table and causing the application hang.

The entire table was read and all blocks have been modified. The table contains more than 100 million rows.

So, why ?


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 :