Re: Cost-Based Optimizer

Barry Hannigan; EdTel (hannigan_at_cs.ualberta.ca) wrote:
: jdennis_at_netcom.com (John Dennis) writes:

: >
: >I have a nasty query that worked fine on Oracle 6 (just made the switch)
: >but is choking on Oracle 7. I "EXPLAINED" the query and it shows
 

: on the hpux port, we uses alter session set sqltrace true to dump the execution
: path of a large stored procedure. it was consistantly picking the wrong index
: for the query ( a simple one by the way ). the default optimizer_mode value
: was in effect. for v7, that is COST. alter your init<FOO>.ora and add the line
 

: optimizer_mode=RULE
 

: after this i think you'll find the hints work. did for us.
 

: praise elvis.
: barry
: --

We are able to give hints AND use cost-based optimization. Cost-Based work best for unoptimized queries, which we have quite a few. I have specified to do SORT_MERGE on all the tables and the optimizer obliged. The performance was so bad that I found out that the EXECUTE PLAN will put "####" in place of a cost that exceeds 9999.

The heart of the problem is that, for this query, I can't get the FUTURE_BKGS table to the "inner" (or non-driving) table using the USE_NL(inner_table_name) hint. It *does* work in other simple queries.

So it appears that, for some reason, the FUTURE_BKGS table simple can't be used as an inner table. Or conversly the DEMOGROUP table can be used as a driving table. Why is the $10,000 question.

BTW, N * thanks for your input!

John Dennis

Atlanta, GA Received on Thu Mar 17 1994 - 14:40:07 CET