RE: Optimizer wonk sought
Date: Thu, 24 Apr 2008 14:48:29 -0500
Message-ID: <7070047601C21A4CB387D50AD3661F6E0994D7E8@050EXCHANGE.research.na.admworld.com>
I haven't been through the whole thread but have you tried the rowid
trick?
Select some_tables.some_columns,
stored_function.function_result(columns_returned_from_the_view)
result_column_alias
from (
select rowid, some_tables.some_columns,
columns_returned_from_the_view,
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))
);
-----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 8:59 AM
To: oracle-l_at_freelists.org
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_at_yahoo.com> To: oracle-l_at_freelists.org 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)
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l CONFIDENTIALITY NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply. -- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 24 2008 - 14:48:29 CDT