wierd performance problem
- From: "Nigel Thomas" <nigel.cl.thomas@xxxxxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Jul 2008 19:37:53 +0100
2008/7/10 Dba DBA <oracledbaquestions@xxxxxxxxx>:
>
> 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
Other related posts:
