Re: performance problem with partitioned table query.

Strange, I'd expect, that dropping 12 partitions should speed up the query.

Still partitioning helps only if column, used for partitioning, is specified as one your search criteria, or if you do full table scan in parallel, or in maintenance when you can quickly drop a partition instead of deleting rows. Otherwise, it can only slow down your retrievals. Why did you partition your table at all? And, why did you partition by this particular column "poid_id0"?

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, January 24, 2002 3:20 PM

> Thank you Igor. But only 1 of the 14 partitions contains data during all
the tests. Why should the extra 13 empty partitions slows down the query? I also tried to drop 12 of the empty partitions. Results didn't change. -Jessica
>
> -----Original Message-----
> Sent: Thursday, January 24, 2002 5:37 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
> ineyman_at_perceptron.com
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> 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: jmao_at_portal.com
  > >
  > > 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: ListGuru_at_fatcity.com (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: ineyman_at_perceptron.com
  >
  > 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: ListGuru_at_fatcity.com (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: Jessica Mao
  > INET: jmao_at_portal.com
  >
  > 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: ListGuru_at_fatcity.com (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: ineyman_at_perceptron.com

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: ListGuru_at_fatcity.com (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).