1 2 3 4 Previous Next 46 Replies Latest reply: May 23, 2011 9:26 AM by 834371 Go to original post RSS
      • 45. Re: Long running update
        Randolf Geist
        Randolf Geist wrote:
        As mentioned above, it looks like 11.1 introduced a variation of the bug that only surfaces with OLTP compression or basic compression in combination with partitioning - the latter corresponds to your configuration used in your first four runs.

        The underlying reason for the anomaly is probably different from the basic bug since there are still ITL slots available but the block is full and therefore doesn't allow any further migrated rows to go into due to the row size, but the symptoms ought to be the same: You'll see the "ktspscan_bmb" and "ktspfsrch" functions on top in the output if you should really hit this.
        Just to wrap this up: You are probably hitting a variation of bug 9341448 - see also note "Performance Issue with Update Statement in Compressed Tablespace [ID 1101900.1]" on My Oracle Support. I've also published a blog post about those variations of the ASSM "free space search" bug, it covers among others your "basic compression + partitioning" scenario: http://oracle-randolf.blogspot.com/2011/05/assm-bug-reprise-part-2.html

        The underlying problem in your case seems to be the following: There is a bug with OLTP compression where blocks are considered as candidates for inserts if they were re-compressed, but this re-compression never happens. Therefore the more blocks get into this state, the harder Oracle has to work to find a suitable block for inserts and migrated rows.

        When using basic compression with partitioning, Oracle sometimes seems to get confused and gets into this "re-compression" state even with basic compression - although basic compression should never do such re-compression attempts.

        There is an odd workaround for this variation of the bug: Flushing the Shared Pool or some other form of Shared Pool invalidation (e.g. by commenting on the table or altering it)...

        It is probably recommended to apply at least the one-off patch 9667930 if you want to make further use of compression (in different scenarios, this scenario here shouldn't be using compression) - it seems also to be fixed in some patch sets / PSU on top of 11.1.0.7.

        Hope this helps,
        Randolf
        • 46. Re: Long running update
          834371
          aaa
          1 2 3 4 Previous Next