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

What did you decrease? Anything?

<sbrendan_at_my-deja.com> wrote in message news:8s50aj$sva$1_at_nnrp1.deja.com...
> 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 - 18:04:30 CDT