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

The only clustering factor in Oracle that I can think of is an analyze statistic that is based on the data itself but can be affected by storage parameters pctfree, initrans, and maxtrans. That is a large pctfree or preallocation of a large number of initrans crowds out data reducing the clustering factor. If rows for the same key and sequential key value rows are stored in the same block you get a higher clustering factor and less physical IO is required to retrieve the data in index order since one physical IO finds several consecutive keys.

I would ask support to define what the clustering factor is and what affects it. I do not think any of the init.ora parameters will result in what you are seeing.

Since the problem appears to be a relatively excessive amount of physical IO against the new file have you also verified that there is no disk related problem such as the other files being OS striped and this one isn't etc...

In article <hnEF5.35321$XV.2050767_at_nntp3.onemain.com>,   "Barbara Kennedy" <barbken_at_teleport.com> wrote:
> 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 --
> > >> >>

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. Received on Fri Oct 13 2000 - 10:21:50 CDT