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

"Richard Foote" <Richard.Foote_at_oracle.com> wrote in message news:3D58AB7E.19C7F531_at_oracle.com...
> 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.

That's if you want to use ASSM, of course. Which you would be mad to do, unless freelist contention is a big issue in your life. Which practically dictates its a RAC thing only.

It's either the nasal hairs growing in my nostrils, or I can smell a new Oracle myth in the making: "Use ASSM! All the time! It's brilliant!".

It's not. It's bloody awful for full table scans unless you have freelist contention growing as prolifically as my nasal hairs. So there!

Regards
HJR
> 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, Its 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 - 03:29:39 CDT