Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> serious performance downgrade on partitioned table.
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 - 18:55:46 CST