Re: Slow query on table after adding 6 million rows....

db_block_buffers was increased from 7,000 to 30,000.

That was a good shot, but the change was up! - Brendan

In article <39e5dce3.2278436_at_news.news-ituk.to>,   See Message body for real address wrote:
>
> if you reduced the db_block_buffers it would slow things down...
>
> sbrendan_at_my-deja.com wrote:
>
> >The table has a single local index that has been dropped and rebuilt
> >after the 6 million rows added.
> >The statistics have been deleted and then estimated 25%
> >The show plan looks good for both partitions.
> >Ran an SQL trace during query of each partition and the trace came
 out
> >with the same plan, however, the October partition shows the same
 query
> >resulted in five times the disk activity.
> >
> >We changed the following server parameters in the past three weeks -
 I'm
> >wondering if any could be the culprit (I left off the existing and
> >previous values - will provide if necessary):
> >bitmap_merge_area_size
> >db_block_buffers
> >db_block_lru_latches
> >db_block_max_dirty_target
> >distributed_transactions
> >fast_start_io_target
> >hash_area_size
> >large_pool_size
> >log_archive_dest
> >log_archive_format
> >log_archive_start
> >log_buffer
> >log_checkpoint_interval
> >log_checkpoint_timeout
> >log_checkpoints_to_alert
> >max_dump_file_size
> >max_rollback_segments
> >parallel_adaptive_multi_user
> >parallel_automatic_tuning
> >parallel_execution_message_size
> >parallel_max_servers
> >processes
> >rollback_segments
> >sessions
> >shared_pool_reserved_size
> >shared_pool_size
> >sort_area_retained_size
> >timed_statistics
> >transactions
> >
> >- Brendan
> >
> >
> >
> >
> >In article <8s4gqc$ebc$1_at_nnrp1.deja.com>,
> > Mark D Powell <markp7832_at_my-deja.com> wrote:
> >> In article <Bl9F5.34641$XV.1873175_at_nntp3.onemain.com>,
> >> "Barbara Kennedy" <barbken_at_teleport.com> wrote:
> >> > What is the explain plan? Is the index marked unusable?
> >> > Jim
> >> > <sbrendan_at_my-deja.com> wrote in message news:8s2b01
 $m4i$1_at_nnrp1.deja.com...
> >> > > We have a datawarehouse database based on a 'star' schema.
> >> > >
> >> > > Each month we 'roll' forward the prior months data into a new
 months
> >> > > partition.
> >> > >
> >> > > When we rolled 'Septembers' data into 'Octobers' partition
 query
 time on
> >> > > just the 'October' partition slowed down and is now four times
 slower
> >> > > than the 'September' partition for the same amount of data.
> >> > >
> >> > > There are five keys on the table, are are number(38).
> >> > >
> >> > > We have been working with Oracle support for five days and do
 not
 have
> >> > > any relief yet.
> >> > >
> >> > > We have moved the data accross table spaces, changed index
 scheme,
> >> > > changed degree of parallel, unpartition the table, all produced
 the
 same
> >> > > slow results.
> >> > >
> >> > > Could there be a problem when copying data from one partition
 to
> >> > > another?
> >> > >
> >> > > Looking for any ideas on what might be the problem.
> >> > >
> >> > > Thanks.
> >> > > - Brendan
> >> > >
> >> I think Jim asked a couple of very good questions; now I will ask a
> >> coupld of dumb ones?
> >>
> >> Did you update the statistics after the partition split?
> >>
> >> Do you have local indexes on each partion? If so did you add a
 new
> >> local index for the new partion?
> >>
> >> --
> >> Mark D. Powell -- The only advice that counts is the advice that
> >> you follow so follow your own advice --
> >>
> >> Sent via Deja.com http://www.deja.com/
> >> Before you buy.
> >>
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 80,000 Newsgroups - 16 Different Servers! =-----
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 12 2000 - 13:36:03 CDT