tag:blogger.com,1999:blog-4924761592080147339.post2820171727541627786..comments2023-09-12T07:08:02.095-07:00Comments on Ric Ramblings: NO_PUSH_PRED – the non-hintRic Van Dykehttp://www.blogger.com/profile/13372723714711916938noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-4924761592080147339.post-17327745287854490852018-10-22T04:34:52.908-07:002018-10-22T04:34:52.908-07:00Hi Tod!
That is excellent! It is one of the mor...Hi Tod! <br />That is excellent! It is one of the more "tricky" hints in that it doesn't work they way you would expect. <br /><br />I'd caution you on using the cost as an absolute about performance. A cost changing in that dramatic way is likely a very good indicator that that plan did get better. However whats really important is that it ran much fast and even more important is that it (I assume) did far less LIOs which is really the best indicator of performance. <br /><br />Great to hear you found a good use of this hint. Ric Van Dykehttps://www.blogger.com/profile/13372723714711916938noreply@blogger.comtag:blogger.com,1999:blog-4924761592080147339.post-74645459290689801082018-10-20T04:09:00.592-07:002018-10-20T04:09:00.592-07:00Hi there,
I found this post from long ago when I ...Hi there,<br /><br />I found this post from long ago when I was searching for info about this hint. I can confirm that the /*+ NO_PUSH_PRED(v)*/ DOES work!! <br /><br />I just changed the execution plan estimated cost for a complex query from 4,377,497,362 to 49,312 with this one hint, placed in the proper view! The actual execution time went from ... well I don't know because the first version did't finish after a *long* time ... down to about 10 seconds after adding the hint.<br /><br />It took me some time looking at the explain plan to understand where I needed to put the hint. At first I was trying it in my top-level query statement... that didn't work. I had to put in in the view (nested several levels down in the chain of views) where the optimizer had decided to do the "UNION ALL PUSHED PREDICATE". <br /><br />I am using Oracle 12.2.0.1.0<br /><br />Tod Flakhttps://www.blogger.com/profile/11124794625326803196noreply@blogger.comtag:blogger.com,1999:blog-4924761592080147339.post-39636185364618473862014-11-02T07:53:55.919-08:002014-11-02T07:53:55.919-08:00Hi Martin! Well it's a mystery that's fo...Hi Martin! Well it's a mystery that's for sure. What really is a pushed predicate? So it is only thru some sort of join? That seems odd but maybe that is the case, so what is it called in my example? I'd think that would be a pushed predicate as well. And on top of that the example from the docs doesn't work as well. You'd think they'd have an example that really did demonstrate the functionality. <br /><br />As is somewhat typical of hints, we really don't know how they work. <br /><br />It's interesting that both the no_merge and the index hint are needed to get this example to show the pushing and non-pushing of the predicate. It will not push the predicate into the view when using a full table scan view created on T2. <br /><br />I've rerun all the examples here (my two simple ones and you two more complex ones) and done a 10053 trace on them. Looks that is it very much about the join. For my two example there is a reference like this:<br /><br />Predicate Move-Around (PM)<br />**************************<br />PM: Passed validity checks.<br />PM: Pushed down predicate "T2"."USERNAME"='OP'<br /> from query block SEL$1 (#0) to query block SEL$2 (#0)<br /><br /><br />For your examples there is this:<br /><br /><br />JPPD: Considering Cost-based predicate pushdown from query block SEL$1 (#1)<br />************************************<br />Cost-based predicate pushdown (JPPD)<br />************************************<br />kkqctdrvTD-start on query block SEL$1 (#1)<br /><br /><br />For the first example it actually does the push down and that appears to be happening in/around these lines in the trace (which follow the above a few lines down):<br /><br />Cost-Based Join Predicate Push-down<br />***********************************<br />JPPD: Checking validity of push-down in query block SEL$1 (#1)<br />JPPD: Checking validity of push-down from query block SEL$1 (#1) to query block SEL$2 (#2)<br />Check Basic Validity for Non-Union View for query block SEL$2 (#2)<br />JPPD: Passed validity checks<br />JPPD: JPPD: Pushdown from query block SEL$1 (#1) passed validity checks.<br />Join-Predicate push-down on query block SEL$1 (#1)<br /><br />The second one where it doesn't do the push down has a line like this:<br /><br />Check Basic Validity for Non-Union View for query block SEL$2 (#2)<br />JPPD: JPPD bypassed: hinted.<br /><br />So we can see that the hint stopped if from doing the push down.<br /><br />Cool stuff. <br /><br />Thanks much for the example!! Ric Van Dykehttps://www.blogger.com/profile/13372723714711916938noreply@blogger.comtag:blogger.com,1999:blog-4924761592080147339.post-75240144284348328122014-10-28T02:27:05.179-07:002014-10-28T02:27:05.179-07:00Hello,
Just an add-on to my previous comment (whi...Hello,<br /><br />Just an add-on to my previous comment (which I was not able to post within the same comment due to space limitation).<br /><br />Looking at the execution plans in your example, I think the optimizer believes it is not doing predicate pushdown in any of the cases. This would explain why the NO_PUSH_PRED hint has no effect. I see the following indications supporting my claim:<br />- The "VIEW PUSHED PREDICATE" does appear in none of the plans. <br />- In my example the NO_QUERY_TRANSFORMATION hint disables predicate pushdown (even without a NO_PUSH_PRED hint), whereas in your example the restriction "T2"."USERNAME"='OP' is evaluated within the view T_VIEW1 despite the NO_QUERY_TRANSFORMATION hint.<br /><br />The Oracle documentation on the PUSH_PRED / NO_PUSH_PRED hints states they control join predicate push down (http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements006.htm). As the predicate "T2"."USERNAME"='OP' in your example is not used for a join, it is maybe not considered a join predicate and thus not affected by NO_PUSH_PRED hint?<br />Maybe the 10053 trace might clarify if the optimizer does or does not believe it is doing a predicate push-down.<br /><br />I do however not understand, what the operation/transformation executed by the optimizer in your example would be (if not a predicate push-down) and why it seems not to be affected by the NO_QUERY_TRANSFORMATION hint.<br /><br />regards<br />Martin<br />Anonymoushttps://www.blogger.com/profile/15790567233648594198noreply@blogger.comtag:blogger.com,1999:blog-4924761592080147339.post-7790184849751188312014-10-28T01:57:54.327-07:002014-10-28T01:57:54.327-07:00Hello,
I get the same behaviour you describe on m...Hello,<br /><br />I get the same behaviour you describe on my Oracle 11.2.0.3 DB. However I found the example below where the NO_PUSH_PRED hint works on that same DB.<br /><br />Preparation<br />-----------<br /><br />create table t1(c1, c2)<br /> as select level, mod(level, 100)<br /> from dual<br /> connect by level <= 4000;<br /> <br />create table t2(c1, c2, c3)<br /> as select mod(level, 4000), level, level<br /> from dual<br /> connect by level <= 100000;<br /><br />alter table t1 add constraint pk_t1 primary key (c1);<br /><br />alter table t2 add constraint pk_t2 primary key (c1, c2);<br /><br />exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false);<br /><br />exec dbms_stats.gather_table_stats(user, 't2', no_invalidate=>false);<br /><br />Test Execution<br />--------------<br />select t1.c1, t1.c2, v.*<br /> from t1,<br /> (select /*+ no_merge index(t2) */ c1, sum(c2), max(c3)<br /> from t2<br /> group by c1) v<br /> where<br /> t1.c1 = v.c1(+)<br /> and t1.c2 in (1, 2);<br /><br />SQL_ID f04x5crt5afz9, child number 0<br />-------------------------------------<br /> <br />Plan hash value: 2725946496<br /> <br />----------------------------------------------------------------------------------------<br />| Id | Operation | Name | E-Rows | Cost (%CPU)| A-Rows |Buffers |<br />----------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | 1092 (100)| 80 | 2094 |<br />| 1 | NESTED LOOPS OUTER | | 80 | 1092 (1)| 80 | 2094 |<br />|* 2 | TABLE ACCESS FULL | T1 | 80 | 7 (15)| 80 | 9 |<br />| 3 | VIEW PUSHED PREDICATE | | 1 | 14 (0)| 80 | 2085 |<br />| 4 | SORT GROUP BY | | 1 | 14 (0)| 80 | 2085 |<br />| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 25 | 14 (0)| 2000 | 2085 |<br />|* 6 | INDEX RANGE SCAN | PK_T2 | 25 | 1 (0)| 2000 | 85 |<br />----------------------------------------------------------------------------------------<br /> <br /> <br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /> <br /> 2 - filter(("T1"."C2"=1 OR "T1"."C2"=2))<br /> 6 - access("C1"="T1"."C1")<br /> <br /><br />select /*+ no_push_pred(v) */ <br /> t1.c1, t1.c2, v.*<br /> from t1,<br /> (select /*+ no_merge index(t2) */ c1, sum(c2), max(c3)<br /> from t2<br /> group by c1) v<br /> where<br /> t1.c1 = v.c1(+)<br /> and t1.c2 in (1, 2);<br /><br />SQL_ID 2cbu0t9tvgfmy, child number 0<br />-------------------------------------<br /> <br />Plan hash value: 1823425757<br /> <br />----------------------------------------------------------------------------------------<br />| Id | Operation | Name | E-Rows | Cost (%CPU)| A-Rows |Buffers |<br />----------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | 50386 (100)| 80 | 100K|<br />|* 1 | HASH JOIN OUTER | | 80 | 50386 (1)| 80 | 100K|<br />|* 2 | TABLE ACCESS FULL | T1 | 80 | 7 (15)| 80 | 9 |<br />| 3 | VIEW | | 4000 | 50379 (1)| 4000 | 100K|<br />| 4 | HASH GROUP BY | | 4000 | 50379 (1)| 4000 | 100K|<br />| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 100K| 50379 (1)| 100K| 100K|<br />| 6 | INDEX FULL SCAN | PK_T2 | 100K| 139 (6)| 100K| 264 |<br />----------------------------------------------------------------------------------------<br /> <br /> <br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /> <br /> 1 - access("T1"."C1"="V"."C1")<br /> 2 - filter(("T1"."C2"=1 OR "T1"."C2"=2))Anonymoushttps://www.blogger.com/profile/15790567233648594198noreply@blogger.com