ODC Appreciation Day: Two Useful Parameters in 18c #ThanksODC

Happy to be part of the #ThanksODC campaign in 2018. Often I learn something new related to Oracle from a forum or blog or a social media post or an article written by someone who loves to help the Oracle community. The Oracle community has helped me tremendously to do my job better. Kudos to the Oracle Development Community! Keep growing!!

I like two parameters introduced in Oracle Database 18c -optimizer_ignore_hints and  optimizer_ignore_parallel_hints. Maybe I have seen too many SQL statements with hints when trying to fix performance issues after upgrading to a newer release of the database!

In earlier releases the optimizer_ignore_hints parameter was an underscore parameter (_optimizer_ignore_hints). Now we can set the parameter at the database level (ALTER SYSTEM) or at the session level (ALTER SESSION) to disable hints. This parameter makes the Oracle optimizer ignore all embedded hints in the SQL statement. By default, the value for this parameter is FALSE, meaning, all embedded hints will be recognized in 18c. 

The related parameter is optimizer_ignore_parallel_hints. Here the optimizer only ignores the PARALLEL hints. 


My preference would be to set optimizer_ignore_parallel_hints in the init file (at the database level) for OLTP databases if the application uses many SQLs with hints embedded. If there is a need to set optimizer_ignore_hints, I would set it for the applications (or users) using a logon trigger at the session level (ALTER SESSION).