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

serious performance downgrade on partitioned table.

From: Jessica Mao <hym0_at_hotmail.com>
Date: 17 Jan 2002 16:55:46 -0800
Message-ID: <8375780b.0201171655.77fe94e2@posting.google.com>


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

Original text of this message

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