Re: Optimizer wonk sought
- From: Mary Elizabeth McNeely <mary_mcneely@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 8 May 2008 08:57:44 -0700 (PDT)
All, thanks for putting such good thought into the problem - you are what makes
this list such an excellent resource for all of us.
The materialize hint with a "with" made the function execute 1366 times instead
of the previous 144,000.
----- Original Message ----
From: Mary Elizabeth McNeely <mary_mcneely@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Thursday, April 24, 2008 8:58:32 AM
Subject: Re: Optimizer wonk sought
All,
Database version is 10.2.0.2. Thanks for the ideas so far. I'll try them out
and get back with you with results.
----- Original Message ----
From: Mary Elizabeth McNeely <mary_mcneely@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Thursday, April 24, 2008 12:36:55 AM
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)
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
