Re: What space of a Oracle block is being used?

Hi Alberto,

Howard's answer is big and clear and mine is just big. Typical ;)

As Srivenu has mentioned and as I alluded to, you can always take a dump and see in lovely detail the contents of a block (this can be any block).

Therefore, I would question the worth of developing anything that details stuff at the block level. Note also with 9i, the fullness of blocks statistics can be easily derived with dbms_space.space_usage and automatic segment space management.

Then again, I'm not much into GUI pretty looking, colourful displaying tools and you might just be able to exploit a huge untapped market.

Then again, maybe not ...

Richard

Alberto Garzas wrote:
>
> Thank Howard for your big and clear answer!!!!
> Thank Richard for your big explanation!!!
>
> and then, It´s impossible know the amount of used space in the number 'x'
> block? Only I can know a approximate used of the Oracle block?
>
> I am thinking of make a tool which shows the block state of a table and I
> think that it will be very difficult or impossible mission. Do you think
> that I will be able to make that tool?
>
> New thank you! Bye!
> "Richard Foote" <Richard.Foote_at_oracle.com> escribió en el mensaje
> news:3D57330A.4B2A0313_at_oracle.com...
> > Hi Alberto,
> >
> > Just to add to what Howard has already mentioned.
> >
> > Note that the AVG_SPACE value in dba_tables gives you the average amount
> > of free space available in a block. This can be used to quickly
> > determine how much space you are potentially 'wasting' in your table.
> >
> > If you want to determine the amount of free/used space in specific
> > blocks or range of blocks, you could easily write you're own code (if
> > performing block dumps doesn't turn you on). Basically, you need to
> > group by the relevant components of the rowid for the tables of
> > interest, using say dbms.rowid, to give you a listing for each unique
> > combination of object number, relative file number and block number (if
> > your database has fewer than 1022 datafiles, you can simplify and ignore
> > the object number, the relative file number should be unique). By
> > summing the lengths of all the columns of all the rows in each of these
> > groupings (plus adding the block overhead as mentioned by Howard) you
> > can approximately calculate the amount of used space per block in the
> > table(s) in question. Any "missing" block numbers in a particular range
> > suggests there are no rows in that range, and this would need to be
> > catered for.
> >
> > You can then scan through the report and get a reasonable indication of
> > the row to block distribution and determine fragmentation or otherwise
> > of your targeted tables.
> >
> > Should be fun :)
> >
> > Richard
> > "Howard J. Rogers" wrote:
> > >
> > > You can't find out exactly. No view gives you that information directly.
> > >
> > > You can compute an average, though.
> > >
> > > Analyze table EMP compute statistics;
> > > Select * from dba_tables where table_name='EMP';
> > >
> > > Now, find the BLOCKS column. That tells you how many blocks do contain
> data,
> > > or might once have contained data.
> > >
> > > Find the NUM_ROWS value, and multiply it by AVG_ROW_LEN. That gives you
> the
> > > size (in bytes) the table might be expected to occupy, were each block
> > > packed 100% full, and there was no Oracle overhead in each block. You
> then
> > > need to take into account that overhead, and PCTFREE.
> > >
> > > Compare the 100% optimum with the actual BLOCKS, and that gives you an
> idea
> > > of how sparsely or densely populated your blocks really are.
> > >
> > > As an example. I use 8K blocks (and you should stop using 2K blocks as
> soon
> > > as possible, too). For table TEST, here's what DBA_TABLES reports:
> > >
> > > BLOCKS: 404
> > > NUM_ROWS : 29260
> > > AVG_ROW_LEN: 96
> > >
> > > So 29260 x 96 = 2808960 bytes optimal storage.
> > >
> > > 8192 bytes per block. Less 88 bytes for Oracle overhead = 8104 useable
> > > bytes. Less 10% PCTFREE = 7293 truly useable bytes per block.
> > >
> > > 2808960 optimal bytes / 7293 useable bytes = 386 blocks.
> > >
> > > Optimally, this table would use 386 blocks. It's actualy using 404.
> That's
> > > 18 'extra' blocks... which is about 5% 'extra'. So I would guess that my
> > > blocks were about 95% full to the PCTFREE level, and given that PCTFREE
> is
> > > 10% empty space already, it means my blocks are about 85% physically
> full.
> > >
> > > Regards
> > > HJR
> > >
> > > "Alberto Garzas" <NOSPAMalberto.garzas_at_wanadoo.es> wrote in message
> > > news:aj70v5$1su$1_at_news.wanadoo.es...
> > > > Hello group!
> > > >
> > > > I think that my question is easy but I havent solved yet.
> > > >
> > > > If I have a block which has 2Kb size, what percent of these 2Kb are
> used
> > > or
> > > > what percent of this 2Kb are free in a concrete moment?
> > > >
> > > > Thank you!
> > > >
> > > >

Received on Tue Aug 13 2002 - 01:47:26 CDT