_optimizer_ignore_hints is now optimizer_ignore_hints

I’m refreshing our Hands-On Lab for Oracle Database 18c. And as part of a performance evaluation I wanted to set an underscore parameter to ignore all hints my load test tool sets. But then I learned: This does not work anymore as _optimizer_ignore_hints is now optimizer_ignore_hints. We made the underscore obsolete in Oracle Database 18c.

_optimizer_ignore_hints is now optimizer_ignore_hints

Actually in Oracle Database 12.2.0.1 you can find a long list of obsolete parameters here:

• Obsolete Parameters in Oracle Database 12.2.0.1 (Nov 10, 2016)

To be precise: We obsoleted159 parameters. In addition, in Oracle Database 18c we added an additional 8 parameters to the list. And just by coincidence I learned that _optimizer_ignore_hints is one of them. But luckily it didn’t get removed but turned into a regular parameter. Which means: It is now documented as well.

This is how I learned about the change:

SQL> alter system set "_optimizer_ignore_hints"=true;
alter system set "_optimizer_ignore_hints"=true
*
ERROR at line 1:
ORA-25138: _OPTIMIZER_IGNORE_HINTS initialization parameter has been made
obsolete

And this works now in Oracle Database 18c:

SQL> alter system set optimizer_ignore_hints=true;
System altered.

In addition, we introduce optimizer_ignore_parallel_hints which gives you even more control.

Obsolete Parameters in Oracle Database 18c

In addition to the 159 parameters we made obsolete with Oracle Database 12.2.0.1, we obsoleted the following ones in Oracle Database 18c:

_bloom_use_crchash
_key_vector_caching
_log_event_queues
_optimizer_ignore_hints
_post_wait_queues_dynamic_queues
_px_reuse_server_group
standby_archive_dest
utl_file_dir

–Mike