First Insert Into Compressed Table on ASSM is Slow

(Doc ID 1948087.1)

Last updated on APRIL 04, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

First INSERT statement into a compressed table on ASSM is slow. Subsequent INSERTS are fine.

For each block it seems to be performing multiple checks (chkdba), and the block is then failing some test and the block is added to space cache (presumably this is why subsequent inserts are fast).  If the blocks are genuinely full, why isn't the database able to mark them as such so we won't have to check them next time.

[ktspisc] client-hint l1dba:0x00147080 l2dba:0x00002501 minfree:2 maxfree:5
Scan begin: option: HINT
[ktspsrch] setl2 sc:0x110a54870 l2: 0x00002501 curl2: 0x00002501 scanopt:1(HINT) hsdba:0x00000000 skip:0/5
Ready to search 0. L1: 0x147080
[ktspsrch] setl1 sc:0x110a54870 l1: 0x00147080 curl2: 0x00002501 curl1: 0x00147080
[ktspsrch] setl1 sc:0x110a54870 l1: 0x00147080 curl2: 0x00002501 curl1: 0x00147080
[ktspsrch] chkdba sc:0x110a54870 dba:0x001472e9 (0/40) exist
[ktspsrch] chkdba sc:0x110a54870 dba:0x00147329 (1/40) exist
[ktspsrch] chkdba sc:0x110a54870 dba:0x00147369 (2/40) exist
[ktspsrch] chkdba sc:0x110a54870 dba:0x001473a9 (3/40) exist
[ktspsrch] chkdba sc:0x110a54870 dba:0x001473e9 (4/40) exist
...
..Omitting similar rows
...
[ktspsrch] chkdba sc:0x110a54870 dba:0x0014742b (37/40) exist
[ktspsrch] chkdba sc:0x110a54870 dba:0x0014746b (38/40) exist
ktspfsrch: Returns: BlockDBA:0x001470ec
kdt_bseg_srch_cbk: examine dba=18.0x001470ec
kdt_bseg_srch_cbk:Compressable Block dba=18.0x001470ec avs=1090 afs=0 tosp=1090 full=0
kdt_bseg_srch_cbk: found dba=18.0x001470ec avs=1090 afs=0 tosp=1090 full=0
ktspfsrch returned :Success
Exit ktspgsp_main SH:18/0x00002502 objd:119571 ----
ndba:0x001470ec
[ktspsrch] adddba sc:0x110a54870 dba:0x001470ec curl1:0x00147080 curl2:0x00002501
kdtgsp:Adding Compressable block to SpaceCache dba=18.0x001470ec tosp=1090 full=0

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms