Oracle _optimizer_ignore_hints tips

Oracle Tips by Burleson Consulting
February 10, 2008

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 MetaLink (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.

Need an Oracle Health Check?

Do you have bad performance after an upgrade?
Need to certify that your database follows best practices?

BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page.

Burleson Consulting

The Oracle of database support

Oracle� is the registered trademark of Oracle Corporation.