Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What space of a Oracle block is being used?

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

From: Alberto Garzas <NOSPAMalberto.garzas_at_wanadoo.es>
Date: Tue, 13 Aug 2002 02:32:10 +0200
Message-ID: <aj9k11$43h$1@reader2.wnet>


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 Mon Aug 12 2002 - 19:32:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US