Re: Optimizer wonk sought

From: William Robertson <william_at_williamrobertson.net>
Date: Fri, 25 Apr 2008 07:21:52 +0100
Message-ID: <48117880.3030304@williamrobertson.net>

AFAIK the MATERIALIZE hint (like its counterpart, INLINE) is only for WITH clauses. For example (11g but should work the same in 9i/10g):

SQL> set autotrace traceonly explain

SQL> SELECT sub.ename, sub.dname FROM
  2 ( SELECT /*+ materialize */ ename, dname   3 FROM emp e, dept d
  4 WHERE d.deptno = e.deptno ) sub
  5 WHERE sub.dname > sub.ename;

Execution Plan

---

Plan hash value: 4009592485

---

| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 1 | 22 | 6
(17)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 22 | 6
(17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2
(0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1
(0)| 00:00:01 |

|*  4 |   FILTER                     |         |       |       


|            |          |

|*  5 |    SORT JOIN                 |         |    14 |   126 |     4  
(25)| 00:00:01 |


|   6 |     TABLE ACCESS FULL        | EMP     |    14 |   126 |     3   

(0)| 00:00:01 |

---

Predicate Information (identified by operation id):

---

   4 - filter("DNAME">"ENAME")
   5 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")

SQL> ed
Wrote file afiedt.buf

  1 WITH sub AS
  2 ( SELECT /*+ materialize */ ename, dname   3 FROM emp e, dept d
  4 WHERE d.deptno = e.deptno )
  5 SELECT sub.ename, sub.dname
  6 FROM sub
  7* WHERE sub.dname > sub.ename
SQL> / Execution Plan

---

Plan hash value: 3090087022

---

| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | |

14 |   224 |     8  (13)| 00:00:01 |


|   1 |  TEMP TABLE TRANSFORMATION     |                           

|       |       |            |          |

|   2 |   LOAD AS SELECT               |                           

|       |       |            |          |

|   3 |    MERGE JOIN                  |                           |    

14 |   308 |     6  (17)| 00:00:01 |


|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT                      |     

4 |    52 |     2   (0)| 00:00:01 |


|   5 |      INDEX FULL SCAN           | PK_DEPT                   |     

4 |       |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN                  |                           |    
14 |   126 |     4  (25)| 00:00:01 |


|   7 |      TABLE ACCESS FULL         | EMP                       |    

14 |   126 |     3   (0)| 00:00:01 |
|*  8 |   VIEW                         |                           |    
14 |   224 |     2   (0)| 00:00:01 |


|   9 |    TABLE ACCESS FULL           | SYS_TEMP_0FD9D6669_5B4069 |    

14 |   224 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   6 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   8 - filter("SUB"."DNAME">"SUB"."ENAME")

Maybe a simplified test case would clarify what is going on.

-----Original message-----
From: Bobak, Mark
Date: 24/4/08 14:47

> Oracle version number?
>
> How about this:
> select query_alias2.result_column_alias, query_alias2.other_columns from
> (
> select /*+ materialize */ some_tables.some_columns,
>           stored_function.function_result(columns_returned_from_query_alias1) result_column_alias,
>           query_alias2.other_columns
> from
>     (
>    select some_tables.some_columns
>    from  some_tables,
>            a_view_without_stored_function
>    where some_criteria_about_the_view_are_met and
>           some_joins_on_the_tables_are_true
>     ) query_alias1
> ) query_alias2
> where
>           query_alias2.result_column_alias > sysdate;
>
> Since you say you're getting the performance you want until you wrap the query and add the outermost where clause, I'm thinking the materialize hint will force the optimizer to completely evaluate and materialize the inner query, and then simply apply the outermost where clause as a filter predicate...?
>
> -Mark
>
> --
> Mark J. Bobak
> Senior Database Administrator, System & Product Technologies
> ProQuest
> 789 E. Eisenhower, Parkway, P.O. Box 1346
> Ann Arbor MI 48106-1346
> +1.734.997.4059  or +1.800.521.0600 x 4059
> mark.bobak_at_proquest.com
> www.proquest.com
> www.csa.com
>
> ProQuest...Start here.
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mary Elizabeth McNeely
> Sent: Thursday, April 24, 2008 1:37 AM
> To: oracle-l_at_freelists.org
> Subject: Optimizer wonk sought
>
> Can some brave optimizer wonk interested in a challenge please help me see what I'm missing here?
>
> Terms of reading further: Only read further with the kindest of intent.  No laughing at the blonde DBA.  No taunting the blonde DBA about all the silly things she tried to do to fix this.
>
> This is a long drawn out explanation; excuse typos in my psuedocode if there are any (due to the nature of my situation, I have to stay with psuedocode):
>
> I have a query that currently works like this, which calls a view, and the view has a stored function populating one of the columns returned:
>
> select some_tables.some_columns,
>           a_view_with_stored_function.column_with_function_result,
>           a_view_with_stored_function.other_columns
> from  some_tables,
>         a_view_with_stored_function
> where some_criteria_about_the_view_are_met and
>           some_joins_on_the_tables_are_true and
>           a_view_with_stored_function.function_result > sysdate;
>
> The stored function is executing many more times (144,000) than number of rows that would be returned by the view alone (1266), and also more than the number of rows returned by the view when joined with the rest of the query (1245) (for brevity, I'll call that "too darned often").  What I want it to do instead is execute the stored function only against the candidate rows that are returned from the already-completed view-table join.  (I have done my homework and am certain this is the more efficient way to do it.)
>
> My problem is, I can't convince Oracle to follow the execution path I want.
>
> Here's what I've tried:
>
>
> TRY 2 - take the stored function out of the view and put it directly into the main query
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> select some_tables.some_columns,
>           stored_function.function_result(columns_returned_from_the_view) result_column_alias,
>           a_view_with_stored_function.other_columns
> from  some_tables,
>         a_view_without_stored_function
> where some_criteria_about_the_view_are_met and
>           some_joins_on_the_tables_are_true and
>           result_column_alias > sysdate;
>
> Result: Won't parse - Oracle's mad that I have tried to use the result_column_alias in the where statement.
>
>
> TRY 3 - take the stored function out of the view and put it directly into the main query and where clause of main query
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> select some_tables.some_columns,
>           stored_function.function_result(columns_returned_from_the_view) result_column_alias,
>           a_view_with_stored_function.other_columns
> from  some_tables,
>         a_view_without_stored_function
> where some_criteria_about_the_view_are_met and
>           some_joins_on_the_tables_are_true and
>           stored_function.function_result(columns_returned_from_the_view) > sysdate;
>
> Result: Function still executes "too darned often".
>
>
> TRY 4 - take the stored function out of the view and put it directly into the callling query and where clause of calling query, but in a subquery
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> select query_alias.result_column_alias, query_alias.other_columns from
> (
> select some_tables.some_columns,
>           a_view_with_stored_function.column_with_function_result result_column_alias,
>           a_view_with_stored_function.other_columns
> from  some_tables,
>         a_view_without_stored_function
> where some_criteria_about_the_view_are_met and
>           some_joins_on_the_tables_are_true
> ) query_alias
> where
>           query_alias.result_column_alias> sysdate;
>
> Result: ERROR at line 67:
> ORA-01843: not a valid month
>
> I'm feeding it the same columns it was getting from the view, not sure what's up with that, but abandoning this case for now, especially based on results I saw in later cases ... this approach probably wouldn't have worked anyway.
>
>
> TRY 5 - take the stored function out of the view and also (I had planned/hoped) externalize it from the table-view join activity +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> select query_alias.result_column_alias, query_alias.other_columns from
> (
> select some_tables.some_columns,
>           stored_function.function_result(columns_returned_from_query_alias1) result_column_alias,
>           query_alias2.other_columns
> from
>     (
>    select some_tables.some_columns
>    from  some_tables,
>            a_view_without_stored_function
>    where some_criteria_about_the_view_are_met and
>           some_joins_on_the_tables_are_true
>     ) query_alias1
> ) query_alias2
> where
>           query_alias2.result_column_alias > sysdate;
>
> Result: Function still executes "too darned often".
>
>
> TRY 6 - bang head against the desk, then decompose the TRY 5 query to learn more about it, tracing during each step
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> TRY 6A:
>
>    select some_tables.some_columns
>    from  some_tables,
>            a_view_without_stored_function
>    where some_criteria_about_the_view_are_met and
>           some_joins_on_the_tables_are_true
> -- innermost query alone returns 1245 rows - the function isn't called in this query - just trying to prove the function doesn't execute during a_view_without_stored_function without my realizing it, causing part of the problem - trace files prove the function isn't executing here
>
> TRY 6B:
> select some_tables.some_columns,
>           stored_function.function_result(columns_returned_from_query_alias1) result_column_alias,
>           query_alias2.other_columns
> from
>     (
>    select some_tables.some_columns
>    from  some_tables,
>            a_view_without_stored_function
>    where some_criteria_about_the_view_are_met and
>           some_joins_on_the_tables_are_true
>     ) query_alias1
> )
> -- add the stored function in: innermost query and next outer query alone returns 1245 rows, and executes the stored function about 1266 times - this is about the number of times I want the function to execute, so far, so good
>
> TRY 6C:
> Add the outermost query back in, but not the outermost where clause from TRY 5:
> (
> select some_tables.some_columns,
>           stored_function.function_result(columns_returned_from_query_alias1) result_column_alias,
>           query_alias2.other_columns
> from
>     (
>    select some_tables.some_columns
>    from  some_tables,
>            a_view_without_stored_function
>    where some_criteria_about_the_view_are_met and
>           some_joins_on_the_tables_are_true
>     ) query_alias1
> ) query_alias2
> -- still returns 1245 rows, and executes the stored function about 1266 times - so far, still so good, and I'm getting the CPU, I/O count, and run-time gains I'd hoped for.  The only thing left to do is add the where clause back in.
>
> -- put the outermost where clause back
> select query_alias2.result_column_alias, query_alias2.other_columns from
> (
> select some_tables.some_columns,
>           stored_function.function_result(columns_returned_from_query_alias1) result_column_alias,
>           query_alias2.other_columns
> from
>     (
>    select some_tables.some_columns
>    from  some_tables,
>            a_view_without_stored_function
>    where some_criteria_about_the_view_are_met and
>           some_joins_on_the_tables_are_true
>     ) query_alias1
> ) query_alias2
> where
>           query_alias2.result_column_alias > sysdate;
> -- still returns 1245 rows, but the wheels fall off at this point - we're back to function still executing "too darned often" (144000 times)
>
> SO, THE OPTIMIZER IS BEING "HELPFUL", TRYING TO APPLY THE OUTERMOST WHERE PREDICATE DURING THE EXECUTION OF THE INNER QUERIES, SOMEHOW HAVING REWRITTEN THEM IN A "SMARTER" FASHION.  Not really helpful - uses twice the I/O, twice the CPU, and twice the run time of the query without the outermost where clause.
>
>
> Other things I tried:
>
> TRY 7 - externalizing the query into another view (so now there are two layers of views)
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> create view whywhywhy_dammit as
> select query_alias2.result_column_alias, query_alias2.other_columns from
> (
> select some_tables.some_columns,
>           stored_function.function_result(columns_returned_from_query_alias1) result_column_alias,
>           query_alias1.other_columns
> from
>     (
>    select some_tables.some_columns,
>              query_alias2.result result_column_alias,
>             query_alias2.other_columns
>    from  some_tables,
>            a_view_without_stored_function
>    where some_criteria_about_the_view_are_met and
>           some_joins_on_the_tables_are_true
>     ) query_alias1
> ) query_alias2;
>
> select some_columns, result_column_alias
> from whywhywhy_dammit
> where result_column_alias > sysdate;
>
> Result: Function still executes "too darned often" - the optimizer continues to be HELPFUL
>
>
> TRY 8 - externalizing the two "internal" queries from TRY 5 into a WITH clause
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Result: Function still executes "too darned often" - the optimizer continues to be HELPFUL
>
>
> TRY 9 - getting desperate, add NO_MERGE hint to TRY 5
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Result: Function executes 2525 times - not the 1266 I wanted, but much better, and I/O is lower, but bizarre execution plan takes a long time to execute and lots of CPU time, both worse than the original query
>
>
> TRY 9 - getting more desperate, add PUSH_SUBQ hint to TRY 5, even though I'm not sure what it does, but it has the word subquery in its help text, so it must be all good, right?????
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Result: Function executes 2525 times - not the 1266 I wanted, but much better, and I/O is lower, but same bizarre execution plan as TRY 9 takes a long time to execute and lots of CPU time, more than the original query
>
>
> TRIES 10 and 11 - now completely thrashing and having nothing to lose (pride hours ago having evaporated), tried adding UNNEST and then NO_UNNEST hints added to TRY 5
> +++++++++++++++++++++++++++++++++++++++++++++++++
> Result: back to function still executing "too darned often", in both cases
>
>
>
>
> TYPING IN ALL CAPS BECAUSE I'M NOW YELLING: ALL I WANT IS THE OPTIMIZER TO EXECUTE THIS USING THE PLAN IT USED IN TRY 6C:
>
> select query_alias.result_column_alias, query_alias.other_columns from
> (
> select some_tables.some_columns,
>           stored_function.function_result(columns_returned_from_query_alias1) result_column_alias,
>           query_alias2.other_columns
> from
>     (
>    select some_tables.some_columns
>    from  some_tables,
>            a_view_without_stored_function
>    where some_criteria_about_the_view_are_met and
>           some_joins_on_the_tables_are_true
>     ) query_alias1
> ) query_alias2
>
>
> THEN DO THIS STEP, only seeing result_column_alias as an atomic value being returned from its child query, not an opportunity to perform slick optimizer techniques:
> where
>           query_alias2.result_column_alias > sysdate;
>
>
>
> HOW CAN I GET THERE FROM HERE?  What am I missing?  alter system set smart_alec_optimizer=off; ?
>
>
>
> As always, thanks for whatever hints you can offer.  Let me know if you need more information - hope this is enough for you to get the concept even with just pseudocode.
>
>
> Mary Elizabeth
> (not feeling very smart, with head sore from being banged on desk, client team members think she's crazy because she's taken to walking around the office muttering this afternoon)
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>   

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

Received on Fri Apr 25 2008 - 01:21:52 CDT