It’s amazing how easy it is to miss a small text error when thinking about (what appears to be) a large logical error.

]]>By: jagdeepsangwanhttps://jonathanlewis.wordpress.com/2010/02/11/ignoring-hints-2/#comment-62729
Sat, 25 Jan 2014 10:57:34 +0000http://jonathanlewis.wordpress.com/?p=3071#comment-62729Jonathan,
I tried your suggestion of checking ‘outline’ using dbms_xplan to check optimizer_features_enable but was not going nowhere to get the results similar to you although OFE was set to current version only. And i thought to start from the scratch and look, and then i found that the tables i created with a prefix of “jt_” (to identify that these are the table that i have created and for maintaining my db).
In the query while hinting i was the using table name with the prefix while i was using the aliases below in the query. after providing the alias in hints i got the results which are same as that of yours.
Lesson Learned: A little discrepancy/mistake can make your query plan/execution a lot different
Note: I have checked this on 10.2.0.1.0 only as this is the only version on my personal laptop at home.
Thanks again and appreciate you for taking time out to look into this.
]]>By: jagdeepsangwanhttps://jonathanlewis.wordpress.com/2010/02/11/ignoring-hints-2/#comment-62726
Sat, 25 Jan 2014 07:33:44 +0000http://jonathanlewis.wordpress.com/?p=3071#comment-62726Jonathan,
i use the random page link to surprise myself what i have been missing about Oracle processing, untill i do not have some specific plan to read/learn a thing
I am randomizing now :)
]]>By: jagdeepsangwanhttps://jonathanlewis.wordpress.com/2010/02/11/ignoring-hints-2/#comment-62725
Sat, 25 Jan 2014 07:29:10 +0000http://jonathanlewis.wordpress.com/?p=3071#comment-62725Jonathan,
Sorry for the delay in reply, i was not having access to the internet for sometime at home due to some technical issues. I will check/try your suggestion so that my tests resembles yours. to be honest i have not till tried 10053 traces, i should now be looking into that now. and i will check your other suggestions also
Thanks again for quick reply
]]>By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2010/02/11/ignoring-hints-2/#comment-62704
Fri, 24 Jan 2014 12:18:01 +0000http://jonathanlewis.wordpress.com/?p=3071#comment-62704Jagdeep,

It’s nice to hear that someone’s using the random page link, by the way. I do it myself from time to time and surprise myself with what I rediscover.

I don’t have all your versions available to test, but I’ve just tried 10.2.0.5, 11.2.0.4, and 12.1.0.1 and they all behave as shown in the example.

I’d probably have to look at the 10053 traces from your tests to find out why your results are different (but please don’t post them), however, a starting guess would be that you have a parameter setting that affects the transitive closure effects. The simplest guess I can make is that your optimizer_features_enable set to some earlier version – if that’s the case then using the ‘outline’ option with dbms_xplan would show it – it’s possible some other parameters would show up in the same way.

]]>By: jagdeepsangwanhttps://jonathanlewis.wordpress.com/2010/02/11/ignoring-hints-2/#comment-62702
Fri, 24 Jan 2014 11:31:29 +0000http://jonathanlewis.wordpress.com/?p=3071#comment-62702Jonathan,
I was going through your blog by clicking the Random Page, then i landed on this article and thought of giving it a try on my Oracle versions and found the results not at all resembling to that of yours.
In 10.2.0.1.0 – NESTED LOOPs and TABLE ACCESS FULL for t1 not using the index at although i have created the primary key and collected the stats. and the join filter is also not there in the plan. and it is the same for both the runs.
In 11.2.0.1.0 and 12.1.0.1.0 – MERGE JOIN CARTESIAN and TABLE ACCESS FULL for t1 and INDEX RANGE SCAN for t2’s index followed by BUFFER short and the join filter is also not there in the plan. and it is the same for both the runs
I have checked the plans using the display_cursor with ALLSTATS option. I wonder why my results not matching with yours. first thought is that i am using completely different versions than of yours. But still i am i thinking to me somewhere near to yours result but i am far-off do not know why. Secondly why the index is not getting used for t1. I tried using the hint also by “index(t1 t1_pk)” but to no avail. I have collected the statistics also before running the query as you have mentioned.
Thanks and appreciate your contribution towards Oracle Community
]]>By: Ignoring hints « Timur Akhmadeev's bloghttps://jonathanlewis.wordpress.com/2010/02/11/ignoring-hints-2/#comment-54428
Tue, 02 Apr 2013 18:00:31 +0000http://jonathanlewis.wordpress.com/?p=3071#comment-54428[…] https://jonathanlewis.wordpress.com/2010/02/11/ignoring-hints-2/ […]
]]>By: Improving Performance by Using a Cartesian Join 2 « Charles Hooper's Oracle Noteshttps://jonathanlewis.wordpress.com/2010/02/11/ignoring-hints-2/#comment-36248
Tue, 18 May 2010 12:11:11 +0000http://jonathanlewis.wordpress.com/?p=3071#comment-36248[…] a 10053 trace might help solve part of the mystery. An interesting comment by Tanel Poder in this blog article suggests that a MERGE JOIN CARTESIAN is similar to a NESTED LOOP operation, just without filtering, […]
]]>By: Blogroll Report 05/02/2009 – 12/02/2010 « Coskan’s Approach to Oraclehttps://jonathanlewis.wordpress.com/2010/02/11/ignoring-hints-2/#comment-35735
Wed, 10 Mar 2010 02:28:49 +0000http://jonathanlewis.wordpress.com/?p=3071#comment-35735[…] Jonathan Lewis-Ignoring Hints-2 […]
]]>