Hint not working properly

[indrajit2002]

Yes but what is causing the hint behave so whimsically? Is it because of CBO?

Regards,
Indrajit

[PAVB]

Could you please define "causing the hint behave so whimsically" - I'm pretty sure hint is doing what is supposed to do. If you don't trust explain plan then trace the query.

[gopu_g]

Indrajit ,

A hint will be ignored if it is:

o Syntactically incorrect. If the hint syntax has not been followed
then the hint will be treated like a comment and ignored.

o Semantically incorrect. If the hint is semantically incorrect
(i.e. it references invalid or unusable constructs or if it conflicts
with other hints) then it may be ignored.

o Rule hints may be overridden by the presence of Cost Based only features.

o The hint must reference the hinted objects correctly. Spelling mistakes in object
names will cause hints to be ignored. Also if the object name in question
contains 'special characters' you will need to double quote the name to
allow the name to be picked up correctly.

[indrajit2002]

I found a new thing. In qua database when I am running this query with hint it is fetching the data in 1 hour. But in prod database when I executed the query without hint it is taking hours and hours

[PAVB]

I found a new thing. In qua database when I am running this query with hint it is fetching the data in 1 hour. But in prod database when I executed the query without hint it is taking hours and hours

Did you trace the query in both environments?

[indrajit2002]

Yes traced both the session. Will upload both the trace files.

[stecal]

Why all the brain damage on this? Just look at the execution plans and see if they are the same in terms of access and join methods. You can do that for yourself. You can also run 10053 trace event and see what the optimizer is doing.