Re: Load slowing down?

Thanks for help. I do truncate right before I load the data by SQLLoader. And from each file I have no more than 5000 rows in load table. I also noticed that db process uses 45% of CPU time while processing data in load tables.

Thing is, that two days ago everything was fine. Now it's too slow. Not only that, time to process one ASCII file is increasing.

"news" <kennedy-family_at_home.com> wrote in message news:k6uN7.1235$726.346442_at_news1.sttln1.wa.home.com...
> Ahh. I was thinking of it the other way, but what you describe is the
> symptom I was thinking of.
> If you can truncate or drop and create the load table just prior to a load
> (or after the completion of a good load) then that will probably help.
> What is going on is that in a full table scan is that Oracle scans ALL the
> database blocks that are below the high water mark whether or not they
 have
> data in them or not. For example, if you built a table and loaded a
> 1,000,000 rows and then deleted them the high water mark would be up to
 the
> last database block that the 1,000,000 rows occupied. If you then insert
 1
> row and issue a query that does a full table scan then Oracle will scan
 ALL
> the database blocks that the 1,000,000 occupied. OUCH!
> By issuing a truncate table xxx; you reset the high water mark (and lose
 all
> the data in the table) or you can drop and rebuild the table for the same.
> If you look at the query against the load table you might be able to build
> an index that makes accessing the load table faster (also analyze the
 table
> and index) after the data is loaded into the load table from sqlloader.
> Jim
> "andrija" <ar35644_at_fer.hr> wrote in message
> news:9u5je8$6njq$1_at_as201.hinet.hr...
> > Well, as I can see in DBA studio, when inserting into main table it
 makes
> > the full scan of load table and then performs insert. Is that what
 you're
> > asking?
> > By the way, the main table is partitioned.
> >
> >
>
>
Received on Fri Nov 30 2001 - 10:06:42 CET