Last time we installed a new callback for a queue. The next day this callback failed. The side effect was that the RDBMS did not make automatic calls of callbacks for messages enqueued after the message, for which the callback failed.
We have no entry in the alert log nor traces, so this was quite weird.
The solution is somewhat trivial. One needs to improve failing callback - which is obvious, because if not, we will hit the same situation again. The second thing is to dequeue all the not consumed messages and possibly enqueue them again (or rerun the processes, which register such messages).
More details one may find at MOS 1265138.1
Wednesday, 30 November 2011
Tuesday, 29 November 2011
More than 255 columns and chained rows
We have a table, which has around 276 columns or so. Due to this it hits the known Oracle limitation (or feature) that a row is internally divided into rows of 255 columns.
The table data is stored unoptimally mainly due to chained rows. Theoretically the rows in that table will be divided no matter what, so one could say there is no sense in rebuilding that table (by alter table move command).
However it is possible to locate more of the chain in the same block.
Below there is a dump of a table with one row in it (for simplicity).
As we see internally this one row is divided into 2 rows - 255 columns in the first one and 45 columns in the second one, and both pieces are stored in the same block. So possibly rows chained throughout a table after move may be stored with all the pieces in the same block, which certainly is a bonus.
After all the important factor here is a row length and not a chaining bound with more than 255 columns.
The table data is stored unoptimally mainly due to chained rows. Theoretically the rows in that table will be divided no matter what, so one could say there is no sense in rebuilding that table (by alter table move command).
However it is possible to locate more of the chain in the same block.
Below there is a dump of a table with one row in it (for simplicity).
Block dump from disk:
buffer tsn: 4 rdba: 0x0100163e (4/5694)
scn: 0x0009.b6833260 seq: 0x01 flg: 0x06 tail: 0x32600601
frmt: 0x02 chkval: 0xdfc2 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xFFFFFFFF7A506A00 to 0xFFFFFFFF7A508A00
[.. dump info here ..]
Block header dump: 0x0100163e
Object id on Block? Y
seg/obj: 0x18d76 csc: 0x09.b683325f itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1001638 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.010.00000fc8 0x00c0008f.031b.0c --U- 2 fsc 0x0000.b6833260
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
bdba: 0x0100163e
data_block_dump,data header at 0xffffffff7a506a7c
===============
tsiz: 0x1f80
hsiz: 0x16
pbl: 0xffffffff7a506a7c
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1b2b
avsp=0x1b15
tosp=0x1b15
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1bba
0x14:pri[1] offs=0x1b2b
block_row_dump:
tab 0, row 0, @0x1bba
tl: 966 fb: -----L-- lb: 0x1 cc: 255
col 0: [ 2] c1 2e
col 1: [ 2] c1 2f
[.. dump info here ..]
col 253: [ 3] c2 03 63
col 254: [ 3] c2 03 64
tab 0, row 1, @0x1b2b
tl: 143 fb: --H-F--- lb: 0x1 cc: 45
nrid: 0x0100163e.0
col 0: [ 1] 80
col 1: [ 2] c1 02
[.. dump info here ..]
col 43: [ 2] c1 2c
col 44: [ 2] c1 2d
end_of_block_dump
As we see internally this one row is divided into 2 rows - 255 columns in the first one and 45 columns in the second one, and both pieces are stored in the same block. So possibly rows chained throughout a table after move may be stored with all the pieces in the same block, which certainly is a bonus.
After all the important factor here is a row length and not a chaining bound with more than 255 columns.
Subscribe to:
Posts (Atom)