Re: Optimizing a Simple 'select count(*)'

From: Juan Carlos Reyes Pacheco <jcdrpllist_at_gmail.com>
Date: Thu, 20 Mar 2014 19:12:27 -0400
Message-ID: <CAGYrQyvXW62s2Y0ryZ_qT8bZ-cdKZ91N1rtiUvm-ga=Lk6buzw_at_mail.gmail.com>

I think you have to do more information, in oracle 9i I solved setting this

optimizer_max_permutations=2500
OPTIMIZER_INDEX_COST_ADJ  =  10
OPTIMIZER_INDEX_CACHING   =  90

A few times I had seen (after gathering full statitics) There is a range on the size of some tables tables that causes problems to the optimizer, in that cases I had to add more statistics information and it got solved magically. This just happened today, suddenly a report become slow in a new customer, i added this and it got solved.   EXECUTE IMMEDIATE 'alter session set TIMED_STATISTICS=TRUE';   EXECUTE IMMEDIATE 'alter session set STATISTICS_LEVEL=ALL';

you too have to set a big PGA_AGGREGATE_TARGET at least to try, because optimizer takes tough decision based on the size of the pga aggregate target.

And of course you have to create an index on that columns, maybe you can try with asc, desc, and changing the order of the columns, and see which chooses de optimizer.

Etc.

I think you have to give more information because the list of possible causes is long, at least for my experience.

2014-03-05 13:56 GMT-04:00 Fred Habash <fmhabash_at_gmail.com>:

> select count (*) from my_table where completion_time between
> to_date(:"SYS_B_0",:"SYS_B_1") and to_date(:"SYS_B_2",:"SYS_B_3")
>
> so does these variations of it ...
>
> select (my_col) where my_col is PK
> select (rowid) ...
> select count(1) ...
> select /*+ index(my_index) */
>
> This takes 1630769 PIOs, 1630774 LIOs, 165 secs and does an FTS on
> my_table.
>
> My research led me to the following ...
> 1) As of 8i, count(*) is the same as count(rowid), count(1), &
> count(my_col). This is true conceptually & practically.
> 2) count(*) should be served via a fast full index scans. Why is it not in
> this example?
> 3) There are options to rely on CBO stats to get row count, but this
> happens at the expense of accuracy.
>
> So, why should we live with such costly resource consumptions for such a
> simple query?
>
>
>
> Thank you ...
> ----------------------------------------
> Fred Habash, Database Solutions Architect (Oracle OCP 8i,9i,10g,11g)
>
>

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

Received on Fri Mar 21 2014 - 00:12:27 CET