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: serious performance downgrade on partitioned table.

Re: serious performance downgrade on partitioned table.

From: Jessica Mao <hym0_at_hotmail.com>
Date: 18 Jan 2002 15:25:38 -0800
Message-ID: <8375780b.0201181525.75ccec6a@posting.google.com>


Svend Jensen <Master_at_OracleCare.Com> wrote in message news:<3C486987.6020305_at_OracleCare.Com>...
> Jessica Mao wrote:
>
> > Table event_t has 14 range partitions, only 1 out of 14 contains data
> > -- about 1 million rows. A workload called CSR has a query on event_t.
> > When event_t increased from 1 million rows to more than 2 million --
> > still inside 1 partition, CSR throughput dropped from 390 ops/sec to
> > 200 ops/sec. Here's the query statement:
> >
> > ********************************************************************************
> >
> > select poid_DB, poid_ID0, poid_TYPE, poid_REV, start_t, end_t,
> > sys_descr
> > from
> > event_t where event_t.end_t >= :1 and event_t.end_t < :2 and
> > event_t.poid_TYPE like :3 and (event_t.account_obj_ID0 = :4 and
> > event_t.account_obj_DB = 1 ) order by event_t.end_t desc
> >
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 36 0.00 0.04 0 0 0
> > 0
> > Execute 118 0.01 0.01 0 0 0
> > 0
> > Fetch 118 0.61 86.71 1385 5045 0
> > 587
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 272 0.62 86.76 1385 5045 0
> > 587
> >
> > Misses in library cache during parse: 1
> > Optimizer goal: CHOOSE
> > Parsing user id: 21 (PIN)
> >
> > Rows Execution Plan
> > ------- ---------------------------------------------------
> > 0 SELECT STATEMENT GOAL: CHOOSE
> > 0 SORT (ORDER BY)
> > 0 PARTITION RANGE (ALL) PARTITION: START=1 STOP=14
> > 0 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF
> > 'EVENT_T' PARTITION: START=1 STOP=14
> > 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> > 'I_EVENT__ACCTOBJ_END_T' (NON-UNIQUE) PARTITION:
> > START=1
> > STOP=14
> >
> > ********************************************************************************
> >
> > The index 'I_EVENT__ACCTOBJ_END_T' was created as:
> >
> > create index i_event__acctobj_end_t on event_t ( account_obj_id0,
> > end_t )
> > tablespace pinx04
> > initrans 16 storage (initial 1m next 10m maxextents unlimited
> > pctincrease 0 freelists 16)
> > local nologging parallel 15 ;
> >
> > event_t range partitioned by column poid_id0.
> > event_t frequently analyzed with estimate statistics.
> > Oracle 8.1.7.0.0
> >
> > Why did this query become so slow when rows increased? Is there
> > anything I can do to improve it? Is there any related documents I can
> > study on?
> >
> > Thank you very much.
> >
> > -Jessica
> >
>
> Your table is partitioned on poid_id0, but the column is neither in the
> where clause of the select nor is it indexed. Hence the optimizer has no
> oppotunity of partition elimination nor can it use an index for lookup.
> A full table scan on all partitions (explain plan) is the result. More
> rows => longer exe time.
>
> Partition your table on event_t.end seems to be a good solution, or at
> least build local indexes with columns from your where clause.
>
> /Svend

Thank you for your help.

poid_id0 is locally indexed as unique key. We have other querys perfectly taking advantage on it (that's why we cannot use end_t as range partition key just to benifit this single query). But in this statement I don't think this index should involve.

from the explain plan it's using index 'I_EVENT__ACCTOBJ_END_T', which is created on 2 columns: account_obj_id0 and end_t. Both the columns are used in the where clause. The other 2 columns in where clause are not indexed because they are way from unique.

currently only 1 out of 14 partitions contains data. I tried steps below:
1.)delete the added rows in event_t, rebuild all its indexes; 2.)drop and recreate event_t with original rows, recreate all its indexes;
3.)drop 12 out of the 14 partitions, leave one contains data and 1 empty;
4.)convert event_t into non-partitioned table.

I did "analyze table compute statistics" after each step and ran the same workload. I didn't get same throughput as 390 ops/sec until I tried the last step. Why didn't step 1,2,and 3 help enough? Received on Fri Jan 18 2002 - 17:25:38 CST

Original text of this message

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