Re: wierd performance problem

Perhaps your table is badly fragmented?

On Thu, Jul 10, 2008 at 11:26 AM, Dba DBA <oracledbaquestions_at_gmail.com> wrote:

>
>
>
>> 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.
>>
>>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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