Great books that helped me boost my knowledge and skills

Since i started my career about ten years ago I’ve read thousands of articles,white papers,blog posts,presentations and official documentation.Beside those there were very good book i’ve read that helped me boost my knowledge and skills in different area.So here here is the complete list in no particular order :

KISS principle : SQL patches to the rescue

On my last blog post i described a particular problem we faced recently following a database upgrade. To resume the issue : one of the DML have seen a severe performance degradation (More than 3 times slower) caused by high version count on the recursive SQL triggered by the delete on cascade after setting the optimizer_features_enable to 11.2.0.4. The problem seems to be related to a BUG causing a switch of the optimizer_mode from ALL_ROWS to CHOOSE on recursive statement. So what’s next !

The story of optimizer_features_enable downgrade and high cursor version count

Following the upgrade of one the databases to 19.6 from 11.2.0.4 , part of the application was hit by severe performance regression (No previous testing where done but this is not the subject) .So until figuring out what’s going on a quick workaround was applied by changing the parameter optimizer_features_enable to 11.2.0.4. Every thing seems to be ok after this change but one apparently simple DML that’s was running ok has seen a severe performance impact and it’s now 2 to 3 times slower.

How setting cursor_sharing= force can break you application !

Here we are talking of course of “very” badly designed application ! When setting “cursor_sharing= force” you may spot that some part of the application start crashing or generating wrong result. This due to one of the common but not well known side effect of setting cursor sharing to FORCE.

Find all direct and indirect FK reference to a table

When working on particular project one of the requirement was to list all tables referencing directly or indirectly a target table using foreign key constraint. So nothing fancy here i just wanted to share the used code maybe it will be useful to someone else (Cycle are not handled on the code so watch-out if you got for example a self referencing foreign key) . This can be useful for example on “delete cascade situation” or when excluding a specific table using impdp to evaluate the impact !

Load_sql_patch.sql a script to deal with SQL patches and force matching

Using  SQL Plan Baselines, SQL Profiles and SQL Patches is great way to mitigate SQL performance issues by insuring Plan Stability. Every alternative has it’s own pro and cons, but let’s take a particular situation . You are on an oracle 19C standard edition database (no Tuning/Diagnostic Pack) on which the application is heavily using literals. You want to enhance the plan stability of particular query what method should you use ? (Changing CURSOR_SHARING to FORCE is not an acceptable option)

Configuring the dm-multipath scheduler : request-based device-mapper

Although “request-based device-mapper” has been introduced since 2009, i sill think that it’s necessary to emphasis this point as i still see some configuration mistake. Here is the dilemma, when tuning your I/O scheduler for your specific workload using multipathing where are you going to configure it ? At the underlying device’s scheduler (i.e. “/sys/block/sdhi/queue/scheduler”) or on the DM-multipath device’s scheduler (i.e. “/sys/block/dm-40/queue/scheduler”) ? Which one take precedence ?