Re: select count(*)

Cut off the right-hand columns - we just need the operations/object bit - and then paste it into the email. Don't forget the predicates section.

Basic view - anything which is a descendent of the FILTER (null is not null) should not happen. It is possible (though very unlikely) that the extra time is simply some side-effect of extra parse time in 10gR2 compared to 10gR1. But a parse time of 31 minutes is rather unlikely.

Check your session statistics:

    connect

    run query

    select name, value from v$mystat ms, v$statname sn     where ms.statistic# = sn.statistic#
    and value != 0

Where are the biggest changes in the stats

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: "Sarma Aryasomayajula" <avnsarma_at_hotmail.com> To: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> Sent: Friday, October 06, 2006 5:05 PM Subject: Re: select count(*)

> Hi Jonathan,
>
> Thank you very much for answer and you are right, in predicate section it
> says null is not null.
>
> I did the explain plan but I am not sure wether I can attach here or not?
>
> one other thing I observed is, in 10gR1 I see sort group by in the plan
> whereas in 10gR2 it is hash group by.
>
> When I checked metalink for hash group by I got a link which says it is a bug.
>
> Subject: Wrong Results Possible on 10.2 When New "HASH GROUP BY" Feature
> is Used
> Doc ID: Note:387958.1
>

--
http://www.freelists.org/webpage/oracle-l