Home » RDBMS Server » Performance Tuning » Hint is not working (oracle10g)
Hint is not working [message #399707] Thu, 23 April 2009 10:58 Go to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
Hello, I am using oracle10g. I am using hint in one of my query and it is not taking...

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

Here is my anlyze statement.

scott@orcl> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',TABNAME => 'EMP',ESTIMATE_PERCE
NT => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', CASCADE => TRUE);

PL/SQL procedure successfully completed.

scott@orcl> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',TABNAME => 'DEPT',ESTIMATE_PERC
ENT => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', CASCADE => TRUE);

PL/SQL procedure successfully completed.


Here is my query. You might ask question, why do you need hint here? I do understand that, just to return four records, i do not worry at all.. But i am doing this just for testing purpose.
Please let me know your thoughts...

Here is the query and explain plan.

scott@orcl> SELECT  /*+ USE_HASH(emp dept) */  emp.empno, dept.dname
  2  FROM EMP , DEPT
  3  WHERE  emp.deptno = dept.deptno
  4  and dept.deptno = 10
  5  /

     EMPNO DNAME
---------- --------------
      7782 ACCOUNTING
      7839 ACCOUNTING
      7934 ACCOUNTING


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=5 Bytes=100
          )

   1    0   NESTED LOOPS (Cost=4 Card=5 Bytes=100)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1
          Card=1 Bytes=13)

   3    2       INDEX (UNIQUE SCAN) OF 'SYS_C005770' (INDEX (UNIQUE))
          (Cost=0 Card=1)

   4    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=5 Byte
          s=35)



Re: Hint is not working [message #399711 is a reply to message #399707] Thu, 23 April 2009 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Please let me know your thoughts...
The CBO knows better than you.
Re: Hint is not working [message #399740 is a reply to message #399711] Thu, 23 April 2009 18:13 Go to previous messageGo to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
The CBO knows better than you. 


Blackswan, Thanks for your reply. When we use Hint,
it means, we are forcing optimizer to use the different
scanning... Howcome, optimizer is ignoring me when
i use hint.. Please correct me if i am wrong...

Regards
Shrinika
Re: Hint is not working [message #399741 is a reply to message #399707] Thu, 23 April 2009 18:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Hint is not working
HINT is "working" (ignored) as documented!

By the way, you used USE_HASH hint.
Why was USE_HASH hint used and not any of the other dozens & dozens of hints available?

>When we use Hint, it means, we are forcing optimizer to use the different scanning...
Obviously the statement above is not correct because the hint was ignored & not "forced".

>Howcome, optimizer is ignoring me when i use hint..
The CBO knows better than you.

>Please correct me if i am wrong...
Needless to say, you are wrong; as you saw for yourself.
The resultant behavior is clearly documented in the Performance Tuning Guide.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/toc.htm

Are you unwilling or incapable to Read The Fine Manual?

[Updated on: Thu, 23 April 2009 18:32]

Report message to a moderator

Re: Hint is not working [message #399764 is a reply to message #399741] Thu, 23 April 2009 22:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle will frequently ignore JOIN METHOD hints that are not accompanied by a JOIN ORDER hint. I don't understand why.

Try adding an ORDERED or LEADING hint to the query in addition to your USE_HASH.

Ross Leishman
Re: Hint is not working [message #399895 is a reply to message #399740] Fri, 24 April 2009 07:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
When we use Hint, it means, we are forcing optimizer to use the different scanning...


When you add a hint to a query, it add a substantial weighting which lowers the cost of using that access path when the CBO evaluated different ways of resolving query.
If another way is still better, despite that weighting then the CBO figures that the other approach is better, and uses it.

Re: Hint is not working [message #399991 is a reply to message #399895] Fri, 24 April 2009 17:30 Go to previous messageGo to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
Thank you for your comments....
Re: Hint is not working [message #400047 is a reply to message #399707] Sat, 25 April 2009 08:26 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
If you add a FULL hint on the DEPT and EMP table, your USE_HASH hint will probably work.

You probably only need the FULL on the DEPT table, but if you are going to use hints to force a plan, you might as well put belt and braces into it to make sure you keep getting the same plan in every instance, so I would add a FULL hint for both tables.
Re: Hint is not working [message #401292 is a reply to message #399707] Sun, 03 May 2009 20:49 Go to previous message
iamadba
Messages: 1
Registered: May 2009
Junior Member
get a 10053 trace, you will know why it not use the hint.
Previous Topic: value BETWEEN x AND y
Next Topic: dynamic view to get 'SQL ordered by...' section from AWR
Goto Forum:
  


Current Time: Sun Dec 11 04:03:06 CST 2016

Total time taken to generate the page: 0.08611 seconds