Re: Count(*) slower than *
Date: Tue, 16 Mar 2010 01:50:22 -0700 (PDT)
On Mar 15, 9:54 pm, "bob123" <bob..._at_gmail.com> wrote:
> I have the following query (220.127.116.11)
> 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 18.104.22.168 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.
Oracle related stuff blog:
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