Re: Why doesn't my hint work?

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Tue, 3 Dec 2013 15:44:53 +0100
Message-ID: <CAJu8R6h3i_cKu3RKZjm15PysPENJCi2LgZBQWeft+gFZaLRnDQ_at_mail.gmail.com>

And if you collect adequate statistics , particularly the join column statistics (career) you might not need to hint your query in order to obtain your desired execution plan

http://hourim.wordpress.com/2013/11/22/on-how-important-is-collecting-statistics-adequately/

BEGIN

   dbms_stats.gather_table_stats  (ownname          => user,
                                   tabname          => 'EMP_APP_SEC',
                                   estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE,
                                   cascade          => true,
                                   method_opt       => 'FOR ALL COLUMNS
SIZE 1'
                                  );

END;
/

Best regards
Mohamed Houri
www.hourim.wordpress.com

2013/12/3 Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>

> Hi Paul,
>
> At first glance, the issue seems to be that you are using an alias for the
> table and the INDEX hint has reference to the actual table name. When you
> alias something in the query, the hints should also use the same alias. I
> would modify the hint to the following and see whether it fixes the issue:
>
> select /*+ LEADING (b a)
> INDEX (b EMP_APP_SEC_I) */ *
>
> from oprid_sec a, emp_app_sec b where a.oprid = :1
> AND a.career = b.career
> and a.org = b.org
> and b.latest_row = 'Y';
>
> Cheers,
>
> Gaja
>
> Gaja Krishna Vaidyanatha,
> CEO & Founder, DBPerfMan LLC
> http://www.dbperfman.com
> http://www.dbcloudman.com <http://www.dbperfman.com/>
> Phone - +1 (650) 743-6060
> LinkedIn - http://www.linkedin.com/in/gajakrishnavaidyanatha
> Co-author: Oracle Insights:Tales of the Oak Table -
> http://www.apress.com/9781590593875
> Primary Author: Oracle Performance Tuning 101 -
> http://www.amzn.com/0072131454
> Enabling Exadata, Big Data and Cloud Deployment & Management for Oracle
>
> ------------------------------
> *From:* Paul Houghton <Paul.Houghton_at_admin.cam.ac.uk>
> *To:* "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
> *Sent:* Monday, December 2, 2013 10:25 AM
> *Subject:* Why doesn't my hint work?
>
> Hi
>
> I am trying to influence the execution plan of a query using hints. Oracle
> enterprise edition 11.2.0.3. Tables are heap and indexes are all b-tree.
>
> select * from oprid_sec a, emp_app_sec b where a.oprid = :1
> AND a.career = b.career
> and a.org = b.org
> and b.latest_row = 'Y';
>
> ---------------------------------------------------
> | Id | Operation | Name | Starts |
> ---------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 |
> |* 1 | HASH JOIN | | 1 |
> |* 2 | INDEX RANGE SCAN | OPRID_SEC | 1 |
> |* 3 | TABLE ACCESS FULL| EMP_APP_SEC | 1 |
> ---------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - access("A"."CAREER"="B"."CAREER" AND "A"."ORG"="B"."ORG")
> 2 - access("OPRID"=:1)
> 3 - filter("B"."LATEST_ROW"='Y')
>
> The developer is asking me to make the access on EMP_APP_SEC use an index.
> I assume they want a plan something like:
>
> ----------------------------------------------------------------
> | Id | Operation | Name | Starts |
> ----------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 |
> |* 1 | NESTED LOOPS | | 1 |
> |* 2 | TABLE ACCESS BY INDEX ROWID | EMP_APP_SEC | n |
> | 3 | INDEX access of some sort | EMP_APP_SEC_I | n |
> |* 4 | INDEX RANGE SCAN | OPRID_SEC | 1 |
> ----------------------------------------------------------------
>
> i.e. use the OPRID_SEC table as the main loop and look up the index on
> EMP_APP_SEC for each row to get the row(s) to join. I thought that I could
>
> create index emp_app_sec_i on emp_app_sec (career,org,latest_row);
>
> select /*+ LEADING (b a)
> INDEX (EMP_APP_SEC EMP_APP_SEC_I) */ *
> from oprid_sec a, emp_app_sec b where a.oprid = :1
> AND a.career = b.career
> and a.org = b.org
> and b.latest_row = 'Y';
>
> But the hints are not having the effect I intended. The leading hint works
> to change the join order, but the index is not used.
>
> ---------------------------------------------------
> | Id | Operation | Name | Starts |
> ---------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 |
> | 1 | NESTED LOOPS | | 1 |
> |* 2 | TABLE ACCESS FULL| EMP_APP_SEC | 1 |
> |* 3 | INDEX UNIQUE SCAN| OPRID_SEC | 583K|
> ---------------------------------------------------
>
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("B"."LATEST_ROW"='Y')
> 3 - access("A"."OPRID"=:1 AND "A"." CAREER"="B"." CAREER" AND
> "A"." ORG"="B"." ORG")
>
>
> What am I doing wrong? (I hope it is more interesting than a syntax error!)
>
> I understand this is likely to be slower than the plan the optimiser
> chose, but I would like to be able to measure the difference.
>
> Thanks
>
> PaulH
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>

-- 
Bien Respectueusement
Mohamed Houri

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 03 2013 - 15:44:53 CET