Re: Oracle picks the wrong driving table?

Apart from Richard's comments about the
two optimizer_tweak parameters (which can have some counter-intuitive effects due to the point in the algorithm where Oracle does the scaling, and the effects of rounding) have a look at the cardinalities quoted - are they reasonable, or completely whacky ?

Does your query really return about 75,000 rows ? Are there really about 1,000 rows in the player_research table ? Are there really 7,500 rows in the roster_cache_1 where user_id = 26 ?

If Oracle's estimates are a long way from the actual values then perhaps you need to generate histograms for the critical columns.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message
news:3fa01d77.452936998_at_nyc.news.speakeasy.net...


> Can somebody help me understand why oracle picks the wrong driving

> table here? I've been inserting too many ORDERED and RULE hints over

> the past few days.

>

> SELECT pr.player_id

>             FROM roster_cache_1 rc, player_research pr

>             WHERE

>                pr.player_id = rc.player_id

>         AND rc.user_id = 26

>

>

> Execution Plan

> ----------------------------------------------------------

>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=75479

> Bytes=1132185)

>    1    0   NESTED LOOPS (Cost=2 Card=75479 Bytes=1132185)

>    2    1     INDEX (FULL SCAN) OF 'PLAYER_RESEARCH_PLAYER_ID'

> (UNIQUE) (Cost=6 Card=1008 Bytes=3024)

>    3    1     INDEX (RANGE SCAN) OF 'ROSTER_CACHE_I_UP_1'

(NON-UNIQUE)


>        2153  consistent gets

>

> SELECT /*+ ORDERED */ pr.player_id

>             FROM roster_cache_1 rc, player_research pr

>             WHERE

>                pr.player_id = rc.player_id

>         AND rc.user_id = 26

>

>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=75479

> Bytes=1132185)

>    1    0   NESTED LOOPS (Cost=2 Card=75479 Bytes=1132185)

>    2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ROSTER_CACHE_1'

> (Cost=1 Card=7488 Bytes=89856)

>    3    2       INDEX (RANGE SCAN) OF 'ROSTER_CACHE_I_1'

(NON-UNIQUE)


> (Cost=3 Card=7488)

>    4    1     INDEX (UNIQUE SCAN) OF 'PLAYER_RESEARCH_PLAYER_ID'

> (UNIQUE)

>          20  consistent gets

>

> init.ora parameters u might be interested in:

> hash_area_size                       integer 65536

> hash_join_enabled                    boolean TRUE

> hash_multiblock_io_count             integer 0

> object_cache_optimal_size            integer 102400

> optimizer_features_enable            string  8.1.7

> optimizer_index_caching              integer 90

> optimizer_index_cost_adj             integer 10

> optimizer_max_permutations           integer 80000

> optimizer_mode                       string  CHOOSE

> optimizer_percent_parallel           integer 0

> db_file_multiblock_read_count        integer 32

> .......

> We use Oracle 8.1.7.4 on Solaris 2.7 boxes

> remove NSPAM to email