Re: Tested 11g OLTP compression and found rather serious issue
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 31 Jul 2009 13:03:36 -0700 (PDT)
Message-ID: <461dfcc2-bbbb-4f5b-bebe-ce8f691df63f_at_j21g2000yqe.googlegroups.com>
On Jul 31, 12:59 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jul 31, 6:46 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Jul 31, 5:02 am, "Tamkat..._at_gmail.com" <tamkat..._at_gmail.com> wrote:
>
> > > Oh, I forgot this important info, regret :
>
> > > SQL> select * from v$version;
>
> > > BANNER
> > > --------------------------------------------------------------------------------
> > > Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
> > > PL/SQL Release 11.1.0.7.0 - Production
> > > CORE 11.1.0.7.0 Production
> > > TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
> > > NLSRTL Version 11.1.0.7.0 - Production
>
> > > - Kenneth Koenraadt
>
> > Ken, I do not know how table compression works on 11g but when first
> > introduced if you updated a row in a compressed table Oracle stored
> > the updated row back in uncompressed format. Compression was
> > suggested for tables what had little to no update activity. Your
> > results could be partially due to migrated rows if no enhancement to
> > recompress the row was not added in 11g.
>
> > Interested in any updates you come up with.
>
> > HTH -- Mark D Powell --
>
> Of course, I have no idea how this really works, but see metalink
> Note: 466362.1
>
> "As an example on what would happen when inserting into block that is
> part of a compressed table, the cycle would be like :
>
> - The block is empty and available for inserts.
> - When you start inserting into this block, data is stored in an
> uncompressedformat (like for uncompressed tables).
> - However, as soon as you reach the PCTFREE of that block, the data is
> automatically compressed, potentially reducing the
> space it originally occupied.
> - This allows for new uncompressed inserts to take place in the same
> block, until PCTFREE is reached again. At that point
> compression is triggered again to reduce space occupation in the
> block."
>
> So I'm wondering if either there is a bug, or Kenneth has some
> combination of PCTFREE and compressible data that doesn't trigger the
> compression with the particular pattern of updating - since each row
> is adding only one byte, the additional length is only the number of
> rows in the block, and the wasted space below PCTFREE on the original
> load is less than that (assuming a row inserted originally that would
> go over PCTFREE winds up in the next block). I'm also wondering what
> the first update of object_id=object_id actually does.
>
> Again, this is all speculation, but suggests experiments that could be
> verified with tracing and block dumps, especially my assumptions,
> which may be saying the last line of the quote from metalink is
> misleading. If that line is not misleading, this would imply the
> update works completely differently than the insert which jams as much
> as possible in the block under PCTFREE, and any large proportion of
> updates would necessarily explode in size with massive chaining -
> which would seem to say the docs lie about the applicability of this
> feature to OLTP:http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/schema...
> Even though it does say this should only be used on data that isn't
> changing much, what a weasel line.
>
> That's probably unfair, I think what the docs should be making clear
> is the feature is to be used for the parts of OLTP systems that have
> settled down and aren't being updated any more, which in the past
> would be archived out. Now you'd probably do something like move the
> data into compressed partitions or tablespaces, depending on options.
> This could make a simple app upgrade that simply adds a column or
> something into a huge undertaking.
>
> jg
> --
> _at_home.com is bogus.
> So, how long do you need data?http://www3.signonsandiego.com/stories/2009/jul/31/1m31plane001745-ww...- Hide quoted text -
>
> - Show quoted text -
Date: Fri, 31 Jul 2009 13:03:36 -0700 (PDT)
Message-ID: <461dfcc2-bbbb-4f5b-bebe-ce8f691df63f_at_j21g2000yqe.googlegroups.com>
On Jul 31, 12:59 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jul 31, 6:46 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Jul 31, 5:02 am, "Tamkat..._at_gmail.com" <tamkat..._at_gmail.com> wrote:
>
> > > Oh, I forgot this important info, regret :
>
> > > SQL> select * from v$version;
>
> > > BANNER
> > > --------------------------------------------------------------------------------
> > > Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
> > > PL/SQL Release 11.1.0.7.0 - Production
> > > CORE 11.1.0.7.0 Production
> > > TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
> > > NLSRTL Version 11.1.0.7.0 - Production
>
> > > - Kenneth Koenraadt
>
> > Ken, I do not know how table compression works on 11g but when first
> > introduced if you updated a row in a compressed table Oracle stored
> > the updated row back in uncompressed format. Compression was
> > suggested for tables what had little to no update activity. Your
> > results could be partially due to migrated rows if no enhancement to
> > recompress the row was not added in 11g.
>
> > Interested in any updates you come up with.
>
> > HTH -- Mark D Powell --
>
> Of course, I have no idea how this really works, but see metalink
> Note: 466362.1
>
> "As an example on what would happen when inserting into block that is
> part of a compressed table, the cycle would be like :
>
> - The block is empty and available for inserts.
> - When you start inserting into this block, data is stored in an
> uncompressedformat (like for uncompressed tables).
> - However, as soon as you reach the PCTFREE of that block, the data is
> automatically compressed, potentially reducing the
> space it originally occupied.
> - This allows for new uncompressed inserts to take place in the same
> block, until PCTFREE is reached again. At that point
> compression is triggered again to reduce space occupation in the
> block."
>
> So I'm wondering if either there is a bug, or Kenneth has some
> combination of PCTFREE and compressible data that doesn't trigger the
> compression with the particular pattern of updating - since each row
> is adding only one byte, the additional length is only the number of
> rows in the block, and the wasted space below PCTFREE on the original
> load is less than that (assuming a row inserted originally that would
> go over PCTFREE winds up in the next block). I'm also wondering what
> the first update of object_id=object_id actually does.
>
> Again, this is all speculation, but suggests experiments that could be
> verified with tracing and block dumps, especially my assumptions,
> which may be saying the last line of the quote from metalink is
> misleading. If that line is not misleading, this would imply the
> update works completely differently than the insert which jams as much
> as possible in the block under PCTFREE, and any large proportion of
> updates would necessarily explode in size with massive chaining -
> which would seem to say the docs lie about the applicability of this
> feature to OLTP:http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/schema...
> Even though it does say this should only be used on data that isn't
> changing much, what a weasel line.
>
> That's probably unfair, I think what the docs should be making clear
> is the feature is to be used for the parts of OLTP systems that have
> settled down and aren't being updated any more, which in the past
> would be archived out. Now you'd probably do something like move the
> data into compressed partitions or tablespaces, depending on options.
> This could make a simple app upgrade that simply adds a column or
> something into a huge undertaking.
>
> jg
> --
> _at_home.com is bogus.
> So, how long do you need data?http://www3.signonsandiego.com/stories/2009/jul/31/1m31plane001745-ww...- Hide quoted text -
>
> - Show quoted text -
Interesting comments JG. It would seem from Ken's work so far that OLTP compression might be suitable only for static tables and perhaps audit history tables where the DML activity consists only of inserts of new audit/history data.
- Mark D Powell --