Re: serious performance downgrade on partitioned table.

Are you indexes local or global?

Daniel Morgan

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
Received on Thu Jan 17 2002 - 12:15:30 CST