First Insert Into Compressed Table on ASSM is Slow
(Doc ID 1948087.1)
Last updated on FEBRUARY 21, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A 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
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |