wierd performance problem

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Thu, 10 Jul 2008 12:10:37 -0400
Message-ID: <f30139790807100910n566b7e05r48d7f42100b3ed03@mail.gmail.com>

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
Redhat 4.5

As I said there is not any other activity.

Received on Thu Jul 10 2008 - 11:10:37 CDT

Original text of this message