RE: Identifying free space in a block

From: Paul Houghton <Paul.Houghton_at_uis.cam.ac.uk>
Date: Tue, 24 Jan 2017 16:32:22 +0000
Message-ID: <HE1PR0701MB26346B9DE8A570CE37F7264BE6750_at_HE1PR0701MB2634.eurprd07.prod.outlook.com>


Thanks all

That is what I was after.

And yes, this is largely academic interest, but piqued looking at another problem, which was rather dull!

I realise the format of these dump files is largely undocumented. Is there anywhere I can go to read up on them?

Cheers

PaulH

> -----Original Message-----
> From: Stefan Koehler [mailto:contact_at_soocs.de]
> Sent: 24 January 2017 16:06
> To: Paul Houghton; oracle-l_at_freelists.org
> Subject: Re: Identifying free space in a block
>
> Hey Paul,
> not quite sure if we are talking about the same, but the total amount of
> available space is also included in the block dump. Here is just a short
> demo from 12.1.0.2
>
> -------------------8<---------------------
> SQL> alter system dump datafile 12 block 483;
>
> Start dump data blocks tsn: 5 file#:12 minblk 483 maxblk 483 ...
> Object id on Block? Y
> seg/obj: 0x13d8e csc: 0x00000000004204c8 itc: 3 flg: E typ: 1 - DATA
> brn: 0 bdba: 0x30001e0 ver: 0x01 opc: 0
> inc: 0 exflg: 0
>
> Itl Xid Uba Flag Lck Scn/Fsc
> 0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn
> 0x00000000004204c8
> 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
> 0x0000.00000000
> 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
> 0x0000.00000000
> bdba: 0x030001e3
> data_block_dump,data header at 0x7fb6e23bbe74 ===============
> tsiz: 0x1f80
> hsiz: 0x9a
> pbl: 0x7fb6e23bbe74
> 76543210
> flag=--------
> ntab=1
> nrow=68
> frre=-1
> fsbo=0x9a
> fseo=0x3ca
> avsp=0x330
> tosp=0x330
> ...
> -------------------8<---------------------
>
> avsp = The amount of space available in this block for new entries. Does
> not include any space another transaction using this block has just freed
> due to a delete or update. This will be reflected the next time a total
> cleanout occurs. This value is compared with PCTFREE and PCTUSED to
> determine if the block should belong to a freelist.
>
> tosp = The total amount of space available in this block. If this value is
> different from the ‘avsp’ amount, it should be equal to the ‘avsp’ plus
> any free space credits within the ITL list. An Interested Transaction List
> (ITL) is present in every block header and is used to associate changes to
> rows with a particular transaction. The ITL is composed of an index
> number, transaction identifier (undo segment number, transaction table
> slot number, and wrap or version number of the slot), commit SCN, and free
> space credit. The free space credit tracks the amount of space in the
> block released by a transaction (updates or deletes)
>
> Best Regards
> Stefan Koehler
>
> Independent Oracle performance consultant and researcher
> Homepage: http://www.soocs.de
> Twitter: _at_OracleSK
>
> > Paul Houghton <Paul.Houghton_at_uis.cam.ac.uk> hat am 24. Januar 2017 um
> 12:13 geschrieben:
> >
> > Hi
> >
> > How do I identify how much free space is in a specific block? I know
> > that the ITL can grow if there is space in the block. I have dumped the
> block and can see there are 5 entries, so how can I tell if a 6th entry
> could be created?
> >
> > I don't believe it needs to grow, I am just curious.
> >
> > Oracle 12.1.0.2 Linux x86_64
> >
> > Thanks
> >
> > Paul Houghton
†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Tue Jan 24 2017 - 17:32:22 CET

Original text of this message