Oracle _optimizer_ignore_hints tips

The _optimizer_ignore_hints enables all embedded SQL hints to be ignored, very handy when testing optimizer behavior.  Oracle provides the _optimizer_ignore_hints hidden initialization parameter to provide workarounds for system-wide issues.

For example, some ancient Oracle applications are plagued with /*+ rule */ hints embedded into the SQL, and setting _optimizer_ignore_hints allows these statements to execute under the cost-based optimizer.

Nominet notes that they used the _optimizer_ignore_hints parameter to correct an issues with SQL optimization and ODBC when they migrated to Oracle 10.2:

"The rule based optimizer, which the Oracle ODBC driver is using, was deprecated in Oracle 10.1. For some reason the rule based optimizer performs the statements OK on 10.1 but terribly on 10.2.

This is documented on Oracle MOSC (Note 373129.1). The Oracle bug is 4150034 and is fixed in patch 5211342. Unfortunately this fix involves updating and patching the Oracle ODBC driver, which would require an update to every PC in the organization.

A better solution we discovered was to create a logon trigger to set the _optimizer_ignore_hints parameter to true for all sessions connecting to the database via the ODBC application."

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.