Re: 11g query taking 24 seconds, same query was instant in 9i.

From: ddf <oratune_at_msn.com>
Date: Wed, 26 May 2010 11:12:35 -0700 (PDT)
Message-ID: <4c605382-0b4e-4292-a4c7-c23ea14f1e23_at_p5g2000pri.googlegroups.com>



Comments embedded.

On May 26, 1:35 pm, gs <g..._at_gs.com> wrote:
> <snip>
>
> thanks...
>
> stat's are fresh on 11g (I'll have to check the 9i, since its an old
> test instance) - just reran the query again, both return 30 rows, and
> data is the same. 9i this time took .98 and 11g took 10.73. 9i sorts by
> date field by default

No, it doesn't, that's a happy accident of the SORT GROUP BY used in 9iR2 and earlier releases. You should not rely on 'default' behaviour as it can change with no apparent notice. Since 10gR1 the group by operation migrated to a HASH GROUP BY mechanism, and the results do have a default order, by hash key, which doesn't match up to any column in your output. Had you coded ORDER BY .... in the original application query you wouldn't be hearing user complaints of 'the data isn't sorted any more'.

> whereas 11g does not for some reason. I'll try
> altering the date as per your suggestion after I put some other fires out..
>
>
>
>
>
> > What I notice is a different way of handling the implicit date
> > conversions when dates are compared to strings like
>
> > WHERE (sample_date >= '01-apr-09') and (TRUNC(sample_date) <='30-apr-09')
>
> > which in 9i are treated as
>
> > TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
>
> >  and in 11g as
>
> > TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09').
>
> > You might check if this causes an index to be ignored.
>
> > Try
> > WHERE (sample_date >= to_date('01-apr-09', 'DD-MON-YY')) and
> > (TRUNC(sample_date) <=to_date('30-apr-09','DD-MON-YY'))
>
> > And:  a different estimate of rows returned by
>
> >  > | 5 | VIEW | VW_CEM_CORR | *10* | 370 | 87 |  (*9i*)
>
> >  > | 5 | VIEW | VW_CEM_CORR | *2305* | 29965 | 2126 (4)| 00:00:26 |  
> > (*11g*)
>
> >  I wonder if your statistics are correct, or if you're using the same data.
>
> > Shakespeare- Hide quoted text -
>
> - Show quoted text -

David Fitzjarrell Received on Wed May 26 2010 - 13:12:35 CDT

Original text of this message