Jessica, It looks like your query has to deal with all 14 partitions, because the column 'poid_id0', which your table partitioned on, is not in 'where' clause. That's why Oracle can not eliminate other (not populated) 13 partitions.
Igor Neyman, OCP DBA [EMAIL PROTECTED] ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 23, 2002 6:15 PM > Oracle 8.1.7.0.0 > > table event_t range partitioned by column poid_id0. only 1 partition called p_1 out of the 14 contains data. A query on event_t became significantly slow after rows increase: > > 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 > > 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 > > Index I_EVENT__ACCTOBJ_END_T was created on event_t ( account_obj_id0, end_t ) using LOCAL. > Other 2 columns involved in the where clause have either only one distinct value or a few. So are not indexed. > column account_obj_id0 has 1 million unique values in event_t and remain unchanged during the tests. when rows insert, average rows per account_obj_id0 value increase as well. > > Trace shows always the same execution plan but elapsed time increased enormously! > I did 2 rounds of tests, every round I dropped and recreated event_t empty: > > In test round 1: > 1.) inserted 1 million rows into event_t with same end_t value. Query returned: > call count cpu elapsed disk query current rows > ------- ------ -------- ---------- ---------- ---------- ---------- ---- ------ > Parse 23 0.02 0.09 0 0 0 0 > Execute 156 0.02 0.29 0 0 0 0 > Fetch 156 0.14 1.09 8 2698 0 195 > ------- ------ -------- ---------- ---------- ---------- ---------- ---- ------ > total 335 0.18 1.47 8 2698 0 195 > > 2.) inserted ANOTHER 1.5 million rows into event_t with 10,000+ different end_t values. Query returned: > 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 > > In test round 2: > 1.) inserted 1 million rows into event_t with same end_t value. Query returned as round1 step 1.) > > 2.) inserted ANOTHER 5 million rows into event_t with ANOTHER end_t value. Query returned: > Parse 40 0.00 0.11 0 0 0 0 > Execute 139 0.02 0.12 0 0 0 0 > Fetch 139 0.25 4.66 303 2868 0 761 > ------- ------ -------- ---------- ---------- ---------- ---------- ---- ------ > total 318 0.27 4.89 303 2868 0 761 > > 3.) inserted ANOTHER 2 million rows into event_t with 12,000+ different end_t values. Query returned: > Parse 34 0.01 0.01 0 0 0 0 > Execute 97 0.00 0.06 0 0 0 0 > Fetch 97 0.58 89.93 1257 4260 0 614 > ------- ------ -------- ---------- ---------- ---------- ---------- ---- ------ > total 228 0.59 90.00 1257 4260 0 614 > > > In test round 1 elapsed time increased 60 times from 1 million to 2.5 million rows. In round 2 it increased 3 times from 1 to 6 million rows, and 18 times from 6 to 8 million rows. So #-of-rows in event_t is not the #1 convict for large physical reads. It's more likely the #-of-different-end_t-values. Before I always thought that to an indexed column the more different values the better. But what's going on in this case? Am I missing anything? > > Top wait event in statspack report is 'db file sequential read'. oradebug event 10046 shows 'db file sequential read' is waiting on object event_t. > > Thank you! > > Jessica Mao > Portal Software, Inc. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jessica Mao > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
