Re: Explain plan cardinality way off / bad plan problem

I know there are several things I can do to optimize the query, I wrote it as an outer inner select since in the application it is using a view with that definition. When I modify the query I still get the odd cardinality values and even if I get a good plan, the optimizer doesnt realize it and I have to over hint the plan to get it do something reasonable. So I think the underlying problem has to do with statistics.

The statistics are up to date for the table stats, dbms_stats runs for the table and indexes nightly, and the process_control_strategy table has this:

TABLE_NAME PROCESS_CONTROL_STRATEGY
TSAMPLE_SIZE 5299
NUM_ROWS 5299
BLOCKS 65
LAST ANALYZED 07-20-2005 09:20:21 And every column has NDV, selectivity, density and stuff as well.

The parameters for optimizer_index_cost_adj is set at 100, and optimizer_index_caching is set at 0. What do these parameters do...? Can they have an impact on cardinality estimates?

Also if I run a simple query like...
select * from process_control_strategy

I get a normal looking plan with correct cardinality like (it realizes 5k rows):

SELECT STATEMENT Optimizer Mode=CHOOSE 5 K 16

  TABLE ACCESS FULL ASPECT.PROCESS_CONTROL_STRATEGY 5 K 419 K 16 Toad says there are histograms for every column on the table definition... if I look in user_histograms they look funny to me... when you have a histogram it normally has numbers for each of the end values so you can figure out how many values are in each bucket... If I delete them and do a dbms_stats again with no histograms it puts the funny ones back. Is this normal?

select * from user_histograms where
table_name='PROCESS_CONTROL_STRATEGY'
order by column_name, endpoint_number

PROCESS_CONTROL_STRATEGY|CATEGORY|0|3.44097361510664E35|
PROCESS_CONTROL_STRATEGY|CATEGORY|1|4.38534725154962E35|
PROCESS_CONTROL_STRATEGY|CONTROL_LEVEL|0|-1|
PROCESS_CONTROL_STRATEGY|CONTROL_LEVEL|1|3|
PROCESS_CONTROL_STRATEGY|CRITICALITY|0|3.42691592663299E35|
PROCESS_CONTROL_STRATEGY|CRITICALITY|1|3.89422264390112E35|
PROCESS_CONTROL_STRATEGY|DAYS_BACK|0|0|
PROCESS_CONTROL_STRATEGY|DAYS_BACK|1|2|
PROCESS_CONTROL_STRATEGY|DEFAULT_BATCH_RUN_NO|0|0|
PROCESS_CONTROL_STRATEGY|DEFAULT_BATCH_RUN_NO|1|2000|
PROCESS_CONTROL_STRATEGY|DEFAULT_RUN_NO|0|0|
PROCESS_CONTROL_STRATEGY|DEFAULT_RUN_NO|1|2000|
PROCESS_CONTROL_STRATEGY|DOC_SK|0|101324|
PROCESS_CONTROL_STRATEGY|DOC_SK|1|542992|
PROCESS_CONTROL_STRATEGY|EVENT_TYPE_SPEC|0|245509|
PROCESS_CONTROL_STRATEGY|EVENT_TYPE_SPEC|1|519332|
PROCESS_CONTROL_STRATEGY|GP_RETENTION_DAYS|0|-1|
PROCESS_CONTROL_STRATEGY|GP_RETENTION_DAYS|1|181|
PROCESS_CONTROL_STRATEGY|HIDDEN_GP_CAT_MASK|0|0|
PROCESS_CONTROL_STRATEGY|HIDDEN_GP_CAT_MASK|1|42|
PROCESS_CONTROL_STRATEGY|IS_INIT|0|2.49881831560547E35|
PROCESS_CONTROL_STRATEGY|IS_INIT|1|4.36804518467801E35|
PROCESS_CONTROL_STRATEGY|IS_UNDER_INIT|0|3.64112362448313E35|
PROCESS_CONTROL_STRATEGY|IS_UNDER_INIT|1|4.36804518467801E35|
PROCESS_CONTROL_STRATEGY|MES_DISPLAY_OPTION|0|0|
PROCESS_CONTROL_STRATEGY|MES_DISPLAY_OPTION|1|2|
PROCESS_CONTROL_STRATEGY|MODULE|0|2.3524166983145E35|
PROCESS_CONTROL_STRATEGY|MODULE|1|4.53338657072659E35|
PROCESS_CONTROL_STRATEGY|OWNER|0|3.38908161144163E35|
PROCESS_CONTROL_STRATEGY|OWNER|1|6.14829189888108E35|
PROCESS_CONTROL_STRATEGY|OWNER_INFO|0|2.55440723342227E35|
PROCESS_CONTROL_STRATEGY|OWNER_INFO|1|6.24155123508901E35|
PROCESS_CONTROL_STRATEGY|RUNS_BACK|0|0|
PROCESS_CONTROL_STRATEGY|RUNS_BACK|1|4801|
PROCESS_CONTROL_STRATEGY|SCF_COLLECTION|0|239944|
PROCESS_CONTROL_STRATEGY|SCF_COLLECTION|1|451683|
PROCESS_CONTROL_STRATEGY|SCF_HISTORY_DAYS|0|0|
PROCESS_CONTROL_STRATEGY|SCF_HISTORY_DAYS|1|3000|
PROCESS_CONTROL_STRATEGY|SPCCHK_FOR_STATS_WITH_OOC_ONLY|0|3.64112362448313E35|
PROCESS_CONTROL_STRATEGY|SPCCHK_FOR_STATS_WITH_OOC_ONLY|1|4.36804518467801E35|
PROCESS_CONTROL_STRATEGY|TITLE|0|1.66153499473115E35|
PROCESS_CONTROL_STRATEGY|TITLE|1|6.19858832565732E35|
PROCESS_CONTROL_STRATEGY|VERSION_SK|0|101325|
PROCESS_CONTROL_STRATEGY|VERSION_SK|1|543060|

Thanks for all your help it is very much appreciated.

Sincerely,
Katrea A. Received on Fri Jul 22 2005 - 08:58:14 CDT