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

I just got off the phone with Oracle and wanted to give you an update:

Oracle development has determined that the 'Clustering Factor' between September's(FAST) and October's(SLOW) data is different. This difference in the 'Clustering Factor' means much more (five times) disk I/O when accessing October's data.

So, the question now is, which Configuration Parameter that was changed (in the list below) would cause the 'Clustering Factor' difference?

To answer the two previous threads:
1) I will try a 30% estimate
2) No configuration parameter was reduced.

My next steps are set the configuration parameters back to the initial setting and begin adding them back until I find the point at which it slows down. As well as check back here to see if anyone has an idea which setting might affect the 'clustering factor'.

Thanks for everyone's great suggestions - we'll get this thinkg licked!

• Brendan

In article <2srF5.6114$mC.428182_at_monger.newsread.com>,   "Van Messner" <vmessner_at_bestweb.net> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 13 2000 - 08:58:07 CDT