Re: serious performance downgrade on partitioned table.

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 Received on Fri Jan 18 2002 - 12:29:27 CST