30 percent... Alex 2010/9/20 Pavel Ermakov <ocp.pauler@xxxxxxxxx> > Hi > > Ok. Are you try to use estimate_percent null or 10 or 100 or > auto_sample_size? > > Best regards, Pavel. > > 2010/9/17 amonte <ax.mount@xxxxxxxxx> > > why not...? >> >> It works well in my experience >> >> 2010/9/15 Pavel Ermakov <ocp.pauler@xxxxxxxxx> >> >> Hi >>> >>> Why estimate_percent => 30? >>> >>> >>> Best regards, Pavel. >>> >>> >>> 2010/9/14 amonte <ax.mount@xxxxxxxxx> >>> >>>> Hi >>>> >>>> dbms_stats.gather_table_stats(owner, table_name, estimate_percent => 30, >>>> cascade => TRUE, granularity = >ALL) >>>> >>>> TIA >>>> >>>> alex >>>> >>>> >>>> 2010/9/14 Pavel Ermakov <ocp.pauler@xxxxxxxxx> >>>> >>>>> Hi >>>>> >>>>> Could you please post how are you gather statistics? >>>>> >>>>> Best regards, Pavel. >>>>> >>>>> 2010/9/14 amonte <ax.mount@xxxxxxxxx> >>>>> >>>>> yes the statistics are up to date >>>>>> >>>>>> the execution is very simple: parallel index scan (with artition >>>>>> pruning) and then table access >>>>>> >>>>>> Thanks >>>>>> 2010/9/13 Pavel Ermakov <ocp.pauler@xxxxxxxxx> >>>>>> >>>>>> Hi! >>>>>>> >>>>>>> Are you statistics up-to-date? How are you gather statistics?Could >>>>>>> you post explain plan? >>>>>>> >>>>>>> Best regards, Pavel. >>>>>>> >>>>>>> 2010/9/10 amonte <ax.mount@xxxxxxxxx> >>>>>>> >>>>>>> Hi all >>>>>>>> >>>>>>>> I have a data warehouse running in 11.1.0.7. >>>>>>>> >>>>>>>> There is a fact tables with 3000 million of rows range partitioned >>>>>>>> at entered_date per hour, entered_date is also indexed (btree index). >>>>>>>> This >>>>>>>> is giving some performance headaches because many queries are so >>>>>>>> simple as >>>>>>>> >>>>>>>> select ..... >>>>>>>> from fact_table1 >>>>>>>> where entered_date >= to_date(......) >>>>>>>> and entered_date > to_date(......) >>>>>>>> >>>>>>>> The range can go from 1 hour to 7 days. The problem is that instead >>>>>>>> of scanning partitions (full scans) it uses index range scan and to >>>>>>>> check 9 >>>>>>>> hour data instead of taking around 20 seconds (using no_index hint) it >>>>>>>> takes >>>>>>>> 2 hours. >>>>>>>> >>>>>>>> This is not the first time I have seen this issue, I am almost >>>>>>>> certian that index on the partition key does not help in this case. >>>>>>>> Shall I >>>>>>>> drop the index? >>>>>>>> >>>>>>>> >>>>>>>> TIA >>>>>>>> >>>>>>>> Alex >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >