Re: Count(*) slower than *
Date: Tue, 16 Mar 2010 01:50:22 -0700 (PDT)
Message-ID: <ef9505a3-2887-40f8-b252-8597b2aaa53e_at_d27g2000yqf.googlegroups.com>
On Mar 15, 9:54 pm, "bob123" <bob..._at_gmail.com> wrote:
> I have the following query (9.2.0.6)
>
> with * => <1s
>
> with count(*) => 7:52 min
>
> do you know why ?
If you want to understand why the usual advice applies: Trace the execution with 10046 extended trace to find out where the excess work happens. The COUNT(*) plan includes a INDEX FULL SCAN as inner row source of a NESTED LOOP operation that looks suspicious at first glance - it could be a bug caused by the FIRST_ROWS(n) mode in conjunction with the insane OICA and OIC parameters used by Siebel in your early 9.2.0.6 version.
It also advisable to use DBMS_XPLAN.DISPLAY to format the EXPLAIN PLAN output. Unfortunately the AUTOTRACE feature in Oracle 9i still uses the old formatting (In 10g it uses the DBMS_XPLAN.DISPLAY output). In particular the "Predicate Information" section of the DBMS_XPLAN output is very helpful to understand which operation applies which filter.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Tue Mar 16 2010 - 03:50:22 CDT