wierd performance problem

2008/7/10 Dba DBA <oracledbaquestions_at_gmail.com>:

>
> I do the following:
>
> select count(*)
> from mytable
> where rownum < 2;
>
> No indexes. Takes 30 seconds. Same thing on other tables the same size take
> 2 seconds.
>
> I ran a 10046 trace and found that all my wait time is db file scattered
> read.
> dug deeper. Looked at the raw trace file. I noticed I am doing ALOT of db
> file scattered read calls. My individual wait time on each call does not
> appear to be considerable.
>
> why would I do so many scattered reads to just get the first record? I am
> having alot of performance problems with scanning this table and adding
> indexes.
>
>

You aren't just getting the first row; you are getting EVERY row from EVERY partition for COUNT(*). The ROWNUM predicate is useless (there's only one row coming back). Do you really need to do that, or do you just want to know whether there is at least 1 row?

If you look at the stats on the table and its partitions (number of blocks, empty blocks etc) maybe you'll see why this table takes so much longer than the others.

Regards Nigel

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