Re: wierd performance problem

> I did some more tests.
> set autotrace on shows 122,000 physical reads. To get just one row? huh?
>
> I then tried to restrict to a date that I know is not in the table (I am
> partitioned by date range) so I do
>
> mypartColumn < to_date(20000001) (so less than the year 2000 and I do not
> have any values less than april of this year. It has been running for 15
> minutes.
>
> Nothing else is running. Only happens with 1 table.
>
> why would I get so much IO? There are no indexes either.
>
> ----
>
>
>
> This only occurs with one table. The table is partitioned by day. It has
> about 160 gb.
>
> 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.
>
> Nothing else is going on. I am on a SAN.
> Oracle 10.2.0.3
> Redhat 4.5
>
> As I said there is not any other activity.
>
>

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