Re: Identifying deleted rows for a table
From: dba cjb <chris.brown_at_providentinsurance.co.uk>
Date: Tue, 19 Apr 2011 03:26:32 -0700 (PDT)
Message-ID: <45cebe75-4575-4741-ac8a-5fee493a316e_at_q12g2000prb.googlegroups.com>
On Apr 19, 10:59 am, Randolf Geist <mah..._at_web.de> wrote:
> On Apr 18, 4:48 pm, dba cjb <chris.br..._at_providentinsurance.co.uk>
> wrote:
>
> > Oracle 10.2.0.4 enterprise windows 2003
>
> > I am interested in finding out how much of a table is made up of
> > deleted rows
>
> > SELECT blocks as BLOCKS_USED, empty_blocks
> > FROM dba_tables
> > WHERE table_name='TASK'
>
> > It shows me I have no empty blocks
>
> EMPTY_BLOCKS is not maintained nowadays. It is populated by a
> ANALYZE ... ESTIMATE/COMPUTE STATISTICS call. However, even if you
> used ANALYZE ... ESTIMATE/COMPUTE STATISTICS which is usually not
> recommended for various reasons the meaning of the column would be
> misleading: It will only show you the number of blocks that *never*
> have been used, viz. are "unformatted" and therefore won't tell you
> what you are looking for.
>
> Running ANALYZE ... ESTIMATE/COMPUTE STATISTICS populates another
> column that is called "AVG_SPACE" that tells you the average space per
> block and might give you a rough estimate of how much space could be
> reclaimed by re-organizing the object.
>
> If you attempt to use ANALYZE you should not mix it with DBMS_STATS
> calls - there are some subtle side effects regarding the cost
> calculation where ANALYZE produces slightly different results than
> DBMS_STATS and furthermore the CBO uses some information only
> populated by ANALYZE (I think it is the CHAIN_CNT info in particular),
> so you should probably save your current statistics before running
> ANALYZE or delete and re-gather statistics using DBMS_STATS
> afterwards. Note that deleting the statistics explicitly is an
> important step in that procedure otherwise you end up with a mixture
> of statistics updated by DBMS_STATS and some left unchanged from the
> ANALYZE call.
>
> Due to these potential issues with ANALYZE it is probably much easier
> to follow Jonathan's post and use the query provided instead...
>
> If your objects reside in an ASSM tablespace you could also use the
> DBMS_SPACE.SPACE_USAGE procedure to get an overview of the "fill
> grade" maintained in the ASSM bitmap information which also might give
> a rough idea how much space could be reclaimed.
>
> Finally if you want to you could try the "Space/Segment Advisor"
> provided by Oracle, however I'm unsure about the license implications
> since it says that it will use AWR historic information for growth
> trend analysis, but the Segment Advisor itself is not mentioned in any
> license document:http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/schema...
>
> Regards,
> Randolf
>
> Oracle related stuff blog:http://oracle-randolf.blogspot.com/
>
> Co-author of the "OakTable Expert Oracle Practices" book:http://www.apress.com/book/view/1430226684http://www.amazon.com/Expert-Oracle-Practices-Database-Administration...
Date: Tue, 19 Apr 2011 03:26:32 -0700 (PDT)
Message-ID: <45cebe75-4575-4741-ac8a-5fee493a316e_at_q12g2000prb.googlegroups.com>
On Apr 19, 10:59 am, Randolf Geist <mah..._at_web.de> wrote:
> On Apr 18, 4:48 pm, dba cjb <chris.br..._at_providentinsurance.co.uk>
> wrote:
>
> > Oracle 10.2.0.4 enterprise windows 2003
>
> > I am interested in finding out how much of a table is made up of
> > deleted rows
>
> > SELECT blocks as BLOCKS_USED, empty_blocks
> > FROM dba_tables
> > WHERE table_name='TASK'
>
> > It shows me I have no empty blocks
>
> EMPTY_BLOCKS is not maintained nowadays. It is populated by a
> ANALYZE ... ESTIMATE/COMPUTE STATISTICS call. However, even if you
> used ANALYZE ... ESTIMATE/COMPUTE STATISTICS which is usually not
> recommended for various reasons the meaning of the column would be
> misleading: It will only show you the number of blocks that *never*
> have been used, viz. are "unformatted" and therefore won't tell you
> what you are looking for.
>
> Running ANALYZE ... ESTIMATE/COMPUTE STATISTICS populates another
> column that is called "AVG_SPACE" that tells you the average space per
> block and might give you a rough estimate of how much space could be
> reclaimed by re-organizing the object.
>
> If you attempt to use ANALYZE you should not mix it with DBMS_STATS
> calls - there are some subtle side effects regarding the cost
> calculation where ANALYZE produces slightly different results than
> DBMS_STATS and furthermore the CBO uses some information only
> populated by ANALYZE (I think it is the CHAIN_CNT info in particular),
> so you should probably save your current statistics before running
> ANALYZE or delete and re-gather statistics using DBMS_STATS
> afterwards. Note that deleting the statistics explicitly is an
> important step in that procedure otherwise you end up with a mixture
> of statistics updated by DBMS_STATS and some left unchanged from the
> ANALYZE call.
>
> Due to these potential issues with ANALYZE it is probably much easier
> to follow Jonathan's post and use the query provided instead...
>
> If your objects reside in an ASSM tablespace you could also use the
> DBMS_SPACE.SPACE_USAGE procedure to get an overview of the "fill
> grade" maintained in the ASSM bitmap information which also might give
> a rough idea how much space could be reclaimed.
>
> Finally if you want to you could try the "Space/Segment Advisor"
> provided by Oracle, however I'm unsure about the license implications
> since it says that it will use AWR historic information for growth
> trend analysis, but the Segment Advisor itself is not mentioned in any
> license document:http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/schema...
>
> Regards,
> Randolf
>
> Oracle related stuff blog:http://oracle-randolf.blogspot.com/
>
> Co-author of the "OakTable Expert Oracle Practices" book:http://www.apress.com/book/view/1430226684http://www.amazon.com/Expert-Oracle-Practices-Database-Administration...
Thanks to everyone for their input on this
Just to clarify my interest was to compile my own cost/benefit analysis of completing an import/export procedure
The benefits that I thought may accrue were
- Reducing the space that a table / + indexes takes up
- Reducing the disk reads/buffer gets that a query using the object in question would require
My wider goal is to get performance benefit from studying those queries which awr report suggest are using the most db time
Hopefully there is a method that would lend a mathematical approach to
calculating benefit
/ the feedback provided suggests that this may more complex than I'd
anticipated
regards
Chris B
Received on Tue Apr 19 2011 - 05:26:32 CDT