SQL Performance (MOSC)

MOSC Banner

Meaning of IGNORE_OPTIM_EMBEDDED_HINTS in execution plan

edited Jan 13, 2023 8:30AM in SQL Performance (MOSC) 11 commentsAnswered

Hi,

My version is 19.12 EE.

I added a dynamic sampling hint to a query causing performance issue and when I retrieved the execution plan using the following statement:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('g5cr4q9kg8uva'));

I noticed this comment at the bottom of the result:

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
  0 - SEL$4
         U - dynamic_sampling(4) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS

 So if I get it right the dynamic_sampling(4) hint I added was ignored?

I didn't find much information about IGNORE_OPTIM_EMBEDDED_HINTS. The following note is kind of tautological :

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center