Change all the parameters back and see what happens and then change 1 at a
time and retest.
Jim
"TurkBear" <johng_at_mm.com> wrote in message
news:39e70f7a.71938411_at_news.news-ituk.to...
> OK, but the slowdown appears to be a result of the changes made ( duh);
there
> were so many at once, however, that a precise cause might be very
difficult to
> retroactively spot...Perhaps some memory-using settings are now too high,
> causing excessive swapping of memory segments...
>
>
>
> sbrendan_at_my-deja.com wrote:
>
> >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.
>
>
>
> -----= 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! =-----
Received on Fri Oct 13 2000 - 08:48:33 CDT
