!! Please do not post Off Topic to this List !!
The meaning of the word hint doesn't imply anything
forceful. If you allow me, I may say that is a piece
of advice. You say that CBO declines when hint is not
possible, however as we saw at the end of his mail the
path is feasible.
Stole from the Oracle forum (Doc ID: 143684.999) and
from someone's email:
.............
I am glad that the ordered clause worked for you. Here
is another excerpt from Developement...
"Optimizer hints are used to restrict the optimizer
search space. For
example the ORDERED hint tells the optimizer only to
consider one join
order (that specified in the FROM clause). Consider
the query
select a.x from a, b where a.y = b.y;
in this case the optimizer considers all of the
following alternatives:
a NL b
a HA b
a SM b
b NL a
b HA a
b SM a
But for the query
select /*+ ordered */ a.x from a, b where a.y = b.y;
it will only consider the following alternatives:
a NL b
a HA b
a SM b
it will not consider any of the alternatives which
involve the join
order "b JOIN a", because the hint says look at the
order of the
tables in the FROM clause and only consider
alternatives which use
this join-order (i.e. "a JOIN b" in this case).
A join hint like USE_NL or USE_HASH also restricts the
optimizer
search space. The USE_NL(yyy) hint will cause the
optimizer to only
consider a nested-loops join when table yyy is the
inner table of a
join. So if our query contained a USE_NL(a) hint
select /*+ use_nl(a) */ a.x from a, b where a.y = b.y;
the optimizer would only consider the following
alternatives:
a NL b
a HA b
a SM b
b NL a
The optimizer will not consider "b HA a" or "b SM a"
because the hint
says that when "a" is the inner table, the optimizer
should only
consider doing a nested-loops join."
If you would like your specific issue investigated
further you can file an iTAR to provide all the
necessary information to an RDBMS analyst. If
development analysis is needed the analyst will be
able to file a bug on your issue.
Gina
Oracle Support Services
.............
In part I agree with you about statistics. However, as
the note and I said the hint only restricts the paths.
Therefore, it allows Oracle to change the path. So if
you have accurated statistics, maybe it choose one
path of the other.
Maybe it deals with the size of the hash area size or
the setting of hash_multiblock_io_count.
Regards.
- "Shevtsov, Eduard" <EShevtsov_at_flagship.ru> wrote:
> !! Please do not post Off Topic to this List !!
>
> Hi Christian,
>
> Sorry, I'm not sure I share your opinion. CBO can
> decline hint only if the path is not possible at
> all.
> It doesn't matter whether statistics exist or not.
> AFAIK that's the idea of hints.
> As you can see earlier that path (N-L) is possible
> for the join.
>
> Thanks,
> Ed
>
>
> > !! Please do not post Off Topic to this List !!
> >
> > Using hints don't force Oracle to use the path
> choosen
> > when you write the query. Much more true in a join
> > operation where Oracle has many choices. What you
> do
> > is to restrict options. However Oracle have to
> look at
> > the statistics for a final decision.
> >
> > Appart from what I said, are your statistics
> accurated
> > ?. Try without aliases not only in the hint, also
> for
> > the whole query. Are both tables indexed ?. Maybe
> is
> > because of the cost of the unique scan of the
> index.
> > The emp_id it is a sequence. If this is the case,
> > maybe the clustering of the index is too high.
> >
> > Regards.
> >
> >
> > --- "Shevtsov, Eduard" <EShevtsov_at_flagship.ru>
> wrote:
> > > !! Please do not post Off Topic to this List
> !!Hi
> > > List,
> > >
> > > did anybody notice that sometimes CBO ignores
> USE_NL
> > > hint without ORDERED
> > >
> > > The following example was taken from oracle
> docs:
> > >
> > > SQL>
> > > SQL>
> > > SQL> select name, value
> > > 2 from v$parameter
> > > 3 where name = 'optimizer_mode';
> > >
> > > NAME
> > > -----------------------------------
> > > VALUE
> > > -----------------------------------
> > > optimizer_mode
> > > CHOOSE
> > >
> > > SQL> desc employees
> > > Name
> > > --------------------
> > > EMP_ID
> > > MGR_ID
> > > LAST_NAME
> > > FIRST_NAME
> > > HIREDATE
> > > JOB
> > > SALARY
> > >
> > > SQL> desc courses
> > > Name
> > > --------------------
> > > CRS_ID
> > > SHORT_NAME
> > > DESCRIPTION
> > > DAYS
> > > DEV_ID
> > > CAT_ID
> > > LAST_UPDATE
> > >
> > > SQL> explain plan set statement_id = '37'
> > > 2 for
> > > 3 select /*+ ordered use_nl(e) */
> > > 4 e.first_name
> > > 5 , e.last_name
> > > 6 , c.short_name
> > > 7 from courses c, employees e
> > > 8 where e.emp_id = c.dev_id
> > > 9 ;
> > >
> > > Explained.
> > >
> > > SQL> @opt\explain_n
> > > statement id: 37
> > >
> > > Query Plan
> > > ------------------------------------------
> > > SELECT STATEMENT Cost = 1022
> > > NESTED LOOPS
> > > TABLE ACCESS FULL COURSES
> > > TABLE ACCESS BY INDEX ROWID EMPLOYEES
> > > INDEX UNIQUE SCAN EMP_PK
> > >
> > > SQL> ed
> > > Wrote file afiedt.buf
> > >
> > > 1 explain plan set statement_id = '38'
> > > 2 for
> > > 3 select /*+ use_nl(e) */
> > > 4 e.first_name
> > > 5 , e.last_name
> > > 6 , c.short_name
> > > 7 from courses c, employees e
> > > 8* where e.emp_id = c.dev_id
> > > SQL> /
> > >
> > > Explained.
> > >
> > > SQL> @opt\explain_n
> > > statement id: 38
> > >
> > > Query Plan
> > > -----------------------------------------
> > > SELECT STATEMENT Cost = 84
> > > HASH JOIN
> > > TABLE ACCESS FULL COURSES
> > > TABLE ACCESS FULL EMPLOYEES
> > >
> > >
> > > QUESTION: why does the CBO ignore USE_NL without
> > > ORDERED ?
> > >
> > >
> > > Regards,
> > > Ed
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Shevtsov, Eduard
> > > INET: EShevtsov_at_flagship.ru
> > >
> > > Fat City Network Services -- (858) 538-5051
> FAX:
> > > (858) 538-5051
> > > San Diego, California -- Public Internet
> > > access / Mailing Lists
> > >
> >
>
> > > To REMOVE yourself from this mailing list, send
> an
> > > E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing:
> UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be
> removed
> > > from). You may
> > > also send the HELP command for other information
> > > (like subscribing).
> >
> >
> > =====
> > Eng. Christian Trassens
> > Senior DBA
> > Systems Engineer
> > ctrassens_at_yahoo.com
> > ctrassens_at_hotmail.com
> > Phone : 541149816062
> >
> > __________________________________________________
> > Terrorist Attacks on U.S. - How can you help?
> > Donate cash, emergency relief information
> >
>
http://dailynews.yahoo.com/fc/US/Emergency_Information/
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Christian Trassens
> > INET: ctrassens_at_yahoo.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX: (858) 538-5051
> > San Diego, California -- Public Internet
> access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> ORACLE-L
> > (or the name of mailing list you want to be
> removed from). You may
> > also send the HELP command for other information
> (like subscribing).
> >
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Shevtsov, Eduard
> INET: EShevtsov_at_flagship.ru
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Eng. Christian Trassens
Senior DBA
Systems Engineer
ctrassens_at_yahoo.com
ctrassens_at_hotmail.com
Phone : 541149816062
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christian Trassens
INET: ctrassens_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Sep 13 2001 - 14:53:47 CDT
