Re: Add/Drop partition and CBO statistics

Hi,

    Local index is used here, but for performance reasons, global index is better in most case.But it is hard for maintenence, for DDL invalid global index.     Local index is better for performance only when in large DSS system, partition elimination is used. But in many case, partition elimination is just difficult to implement in real life database.     Here is why local index is bad for performance: --With Local index:

     11,115,678 344,355 32.3 11.9 3308940087 begin :sellcount := GetSellAmount ( :p_id ) ; END ; --With Global index:

     21,663,680 2,077,707 10.4 2.6 2165077703 begin :info := GetSellAmount ( :p_id ) ; END ; You can see, when using global index(non-partitoned table yet), this SQL only 2.6% of total buffer_gets, while using local index with much less data, now the consistent gets per executions tripled, and 12% of total system buffer_gets.

Regards
Zhu Chao.     

Original Message -----
BTW, local indexes are the only way to go -- I've never understood the point of global indexes on partitioned tables -- maybe someone else can?> -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, December 02, 2003 10:19 PM
>
>
> > If we are talking about two sql only, maybe you can use stored outline.
> >
> > Yechiel Adar
> > Mehish
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Tuesday, December 02, 2003 8:34 AM
> >
> >
> > > Hi, list friends:
> > > We are using partition to archive history data in our production
> OLTP
> > database. We get great performance gains(Far less disk io), but we also
> hit
> > performance trouble sometimes. So I am here ask for your experience.
> > >
> > > We used local index on all partitioned tables.We add/drop partition
> > monthly to archive the history data.
> > >
> > > But the trouble is, when add/drop partition is being done on the
> > partitioned table, CBO sometimes changed SQL execution path.We implemented
> > partition 2 monthes ago, and in the first time, add/drop partition went on
> > quite smoothly, but in the second time we add/drop partition, two SQL
> (just
> > TWO SQL) get bad execution path and server load rushed to 10 times(from 2
> to
> > 20 in uptime), all waiting for latch free event. It severely affected our
> > application. We are an online system and we do not have scheduled time
> every
> > month so we have to add/drop partition while db is still running.
> > >
> > > So, with system still up and running, how do you add/drop partition
> > without changing the SQL execution path? We do not have the time to
> > reanalyze/dbms_stats the tables ,analyze takes hours and if SQL execution
> > path changed, during these time, system is nearly unusable.
> > >
> > > I tried to import old statistics(dbms_stats.import_table_stats), but
> > did not fix the problem.
> > > So, can you share your experience on managing partitioned table?
> > >
> > > Regards
> > >
> > > Zhu Chao
> > > www.cnoug.org
> > >
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: zhu chao
> > > INET: chao_ping_at_vip.163.com
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > 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.net
> > --
> > Author: Yechiel Adar
> > INET: adar76_at_inter.net.il
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > 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.net
> --
> Author: Binley Lim
> INET: Binley.Lim_at_xtra.co.nz
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
-- 
Author: zhu chao
  INET: chao_ping_at_vip.163.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).