Re: Cheap Hardware and tons of data to load

No indexes either. But found a GREAT tool

SQL analyzer with the Tuning pack. I had tried Oracle expert and followed all it's recommendations but there was no performace improvement.

A quick look at the SQL analyzer showed that the developer had a select statement which was scanning thru the entire table and inserting rows ( Oh well !). There were no indexes which made the select crawl....

So ...Moral of the story: ALWAYS check the application before looking at the Oracle configuration.

Thanks to all who responded.

SR

"Van Messner" <vmessner_at_bestweb.net> wrote in message news:YdZW4.10516$LM4.739565_at_monger.newsread.com...
> Hi Sharad
>
> The question you didn't answer is whether you have indexes that are
> constantly being rebuilt as you load new data. If so, your best bet may
 be
> to drop whatever indexes you can and recreate them after all the rows are
> loaded. The same applies to triggers. If you have any are they
 absolutely
> necessary for the initial data load?
>
> Van
>
> Yahoo!! <estancio AT cyberspace.org> wrote in message
> news:sinvjm59o191_at_corp.supernews.com...
> > No archive log mode
> > No constraints while loading.
> > Data is loaded using a PL/SQL loop script.
> >
> > How do I find if we are having any system wait events?
> >
> > More info:
> >
> > The database has redo-log files close to 100MB.( Is it too big ?)
> >
> >
> >
> > "Mike Hately" <mike.hately_at_virgin.net> wrote in message
> > news:8gfg66$4at$1_at_lure.pipex.net...
> > > Are you getting any system wait events ?
> > > What is the table like? Do you have any indexes/integrity
> > > constraints/triggers on it?
> > > What are you using to load the records?
> > > Are you in archive log mode?
> > >
> > > Cheers
> > >
> > > Mike Hately
> > >
> > > Sharad <estancio_at_cyberspace.org> wrote in message
> > > news:simamq23o130_at_corp.supernews.com...
> > > > We need to load close upwards of 100 million rows in our Oracle
 8.0.4
> > > > database. The hardware is a PIII 500MHz, 256 MB RAM, Single IDE
 disk,
 1GB
> > > > swap.
> > > >
> > > > I know the hardware is not sufficient for this. However, we still
 need
 to
 do
> > > > it ( Come'on you understand...)
> > > >
> > > > The issue we need to load close to a 100,000 user information into
 the
> > > > database. When we load 500 users it loads up in under 2 minutes. The
> > > > subsequent 500 users take 4 minutes and then 10 minutes and so
 on......
> > > >
> > > > ==============================================================
> > > > db_name = PER1
> > > > db_files = 1020
> > > > control_files = ("D:\apps\orant\database\PER1\\ctl1PER1.ora",
> > > > "D:\apps\orant\database\PER1\\ctl2PER1.ora")
> > > > db_file_multiblock_read_count = 16
> > > > db_block_buffers = 20000
> > > > shared_pool_size = 90000000
> > > > log_checkpoint_interval = 10000
> > > > processes = 100
> > > > dml_locks = 250
> > > > log_buffer = 131072
> > > > sort_area_size = 3000000
> > > > sort_area_retained_size = 3000000
> > > > open_cursors = 128
> > > > sequence_cache_entries = 30
> > > > sequence_cache_hash_buckets = 23
> > > > #audit_trail = true
> > > > #timed_statistics = true
> > > > background_dump_dest =
> > > > user_dump_dest = db_block_size =2048
> > > > compatible = 8.0.4.0.0
> > > > log_checkpoint_timeout = 0
> > > > remote_login_passwordfile = shared
> > > > max_dump_file_size = 10240
> > > >
> > > > =================================================
> > > >
> > > > I tried Oracle expert. Performance pack blah blah . Does not help.
> > > >
> > > > Please help.
> > > >
> > > > Much appret
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Wed May 24 2000 - 00:00:00 CDT