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

From: <sbrendan_at_my-deja.com>
Date: Thu, 19 Oct 2000 14:50:16 GMT
Message-ID: <8sn1n5$2l1$1@nnrp1.deja.com>

(Sorry for the long post)

We have learned a lot in the last two weeks!

OK, so the new partition with 6.5 million rows produced a higher clustering factor which resulted in some existing code not to perform well ( went from 2 hours to never finishing after 22 hours).

Our first line of defense was to address the clustering factor. This proved futile. Why? First, we were unable to effect a change with clustering factor and secondly, we do not necessarily want our data clustered on a particular column to aide a nightly batch process and potentially hurt user queries, this is a data warehouse.

So, we called upon some Oracle experts to have a round table discussion and came up with a few other altnatives, all of which seem common sense now that we have a solution.

We initially had our indexes defined as 'prefixed local' indexes, meaning the partition column was the first column in the index. Since we were only accessing a single partition the first column did not provide much (any) selectivity. So we re-ordered our indexes, putting the partition key at the end of the index. The optimizer will still 'prune' out unneeded partitions, thus yeilding the same benefit as having a prefixed local index. So now our local indexes first column provide more selectivity within a given partition.
Rework the update statement in question to force a partition scan and a hash join. This makes use of new syntax in 8.1.6. The new version even reduces run time from 2.5 hours down to 1 hour to update 6.5 million rows. I expect this to go down to 1/2 hour when we fix the redo wait time. Here is the SQL:

update
(

select --+ full (a)

            a.TOTAL_POSITION_VALUE_AMT,
            a.TOTAL_SHARE_QTY,
            b.FIN_INSTRUMENT_PRC
    from    A_FPBAL_AUG_SEP_OCT_NOV    a,
            MSC_FIN_INSTRUMENT_PRICES   b
    where   a.MONTH_TIME_ID     = b.MONTH_TIME_ID
    and     a.FIN_INSTRUMENT_ID = b.FIN_INSTRUMENT_ID
    and     b.MONTH_TIME_ID     = 36799

)
set a.TOTAL_POSITION_VALUE_AMT = a.TOTAL_SHARE_QTY * b.FIN_INSTRUMENT_PRC
;

This syntax allows you to do a join with many tables while updating a single table - good stuff. I think I'll post this syntax as a seperate thread.

Also, at the round table we came up with 10 configuration settings to help alleviate some redo log wait time, better sorting, etc.

Thanks to all who helped out with this issue - it was great getting good suggestions / advice - helped maintain there was hope!

Cheers.
- Brendan

In article <8s7nsh$4rk$1_at_nnrp1.deja.com>, sbrendan_at_my-deja.com wrote:
> Otober's (SLOW) partition has been defined in the same table space as
> all the other months partition which are fast. This tablespace is 20
GB
> and is made up of 10 2GB files that are evenly striped accross 12
> drives. I will do some more testing with the pctfree, inittrans and
> maxtrans.
>
> - Brendan
>
> In article <8s79a5$noj$1_at_nnrp1.deja.com>,
> Mark D Powell <markp7832_at_my-deja.com> wrote:
> > 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.
> >
>
> 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 19 2000 - 09:50:16 CDT