RE: Optimizer wonk sought

You're absolutely right.  I stand corrected.

--
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@xxxxxxxxxxxx
www.proquest.com
www.csa.com

ProQuest...Start here.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of William Robertson
Sent: Friday, April 25, 2008 2:22 AM
To: Oracle L
Subject: Re: Optimizer wonk sought

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@xxxxxxxxxxxx
> www.proquest.com
> www.csa.com
>
> ProQuest...Start here.
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of Mary Elizabeth McNeely
> Sent: Thursday, April 24, 2008 1:37 AM
> To: oracle-l@xxxxxxxxxxxxx
> 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


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

Other related posts: