Re: CBO Increase the Cost of Test tables.

Skoupal wrote:
> I'm using Oracle 9.2.0.7 in an Unix environment.
>
> I was testing this in our old development environment on some queries
> that had mutiple joins. I tried increadsing the index num of rows with
> the set_index_stats. Reading explain plans are still new to me, but
> from my understanding the furthest line in is the starting point based
> on the smallest set of data then works its way back out?
>
> Example:
>
> In this case I increased the RTI_IDS_PK row_num to 10,000,000 and block
> count to 500,000....
> I tried doing similiar things on multiple tables, but nothing changed.
>
> SELECT STATEMENT Cost = 8
> SORT ORDER BY
> FILTER
> NESTED LOOPS OUTER
> NESTED LOOPS
> NESTED LOOPS
> VIEW VW_NSO_1
> SORT UNIQUE
> TABLE ACCESS BY INDEX ROWID RTI_IDS
> NESTED LOOPS
> TABLE ACCESS BY INDEX ROWID RTI_IDS
> INDEX RANGE SCAN RTI_IDS_PK
> INDEX RANGE SCAN RTI_IDS_2_IX
> TABLE ACCESS BY INDEX ROWID RTI_BUS_ACCOUNTS
> INDEX RANGE SCAN RTI_BACCT_3_IX
> INDEX UNIQUE SCAN RTI_BENT_PK
> TABLE ACCESS BY INDEX ROWID RTI_IDS
> INDEX RANGE SCAN RTI_IDS_PK
>
>
> What I really need is to find some documentation on how to properly do
> this. The only thing I found while searching was someone briefly
> mentioning set_table_stats. My experience in this area is lacking
> mainly b/c I'm brand a brand new dba, so if you could just point me in
> the right direction I'm sure I can figure out the rest.
>
> Thanks
> Shawn

I have two demos in Morgan's Library at www.psoug.org doing this that might be of value. You'll find one under DBMS_STATS.SET_TABLE_STATS and another on under TUNING with the title Optimizing Joins. Likely one or both were inspired by something Tom Kyte did but notice that what is altered is not just rows but also blocks. In both cases they force a change to the plan.

HTH

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org