Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Slow query on table after adding 6 million rows....

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

From: <sbrendan_at_my-deja.com>
Date: Thu, 12 Oct 2000 15:17:20 GMT
Message-ID: <8s4klm$hsd$1@nnrp1.deja.com>

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

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. Received on Thu Oct 12 2000 - 10:17:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US