RE: Why doesn't my hint work?

From: Paul Houghton <Paul.Houghton_at_admin.cam.ac.uk>
Date: Fri, 6 Dec 2013 10:26:58 +0000
Message-ID: <FF5D5F2AD07EE2429C8AFD9BC0EE57FB4E882FE216_at_LUCIFERTWO.internal.admin.cam.ac.uk>



Thanks for all you help.

In the end we decided to hint to get the desired plan. This particular statement was expected to be used mostly by oprids with few rows in the oprid_sec table. I tried using the cardinality hint and found that the plan changed to my desired plan at a cardinality of 4 or less, where the actual cardinality was 10 (The optimizer calculated 232). I was confused as to why histograms did not correct the cardinality, but even if they had the plan would still not be what we wanted. The optimiser seems to be more concerned about the extra CPU on the nested loops than the I/O of the table scan.

It looks like this is just an edge case. The difference in run time is only a few hundredths of a second.

Cheers

PaulH

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Dec 06 2013 - 11:26:58 CET

Original text of this message