Re: serious performance downgrade on partitioned table.

"Kendall" <kendallwillets_at_yahoooo.com> wrote in message news:<u4ev3sjek3q2de_at_news.supernews.com>...
> In article <8375780b.0201171655.77fe94e2_at_posting.google.com>, "Jessica
> Mao" <hym0_at_hotmail.com> 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:
> >
>
> It's possible that the size of the index range scan has simply doubled.
> Are you scanning more rows matching the predicates on end_t and
> account_obj_id0?

Thank you for your help.

Yes. the number of rows per account_obj_id0 doubled in average, from 1 row per account_obj_id0 to 2. While rows per end_t remained about 250~500. But! after I deleted all the added rows, even re-created event_t table, re-created all its local indexes, analyzed with compute statistics, dropped 12 out of the 14 partitions (the 1 has data was not dropped :oP)... I still couldn't get my original throughput number -- not until I totally convert event_t into non-partitioned table.

-Jessica Received on Fri Jan 18 2002 - 14:12:25 CST