Re: Identifying deleted rows for a table
Date: Tue, 19 Apr 2011 02:59:59 -0700 (PDT)
Message-ID: <88070ed3-9e85-463e-a1b6-7073614364f6_at_a26g2000vbo.googlegroups.com>
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/schema003.htm#insertedID0
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/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Tue Apr 19 2011 - 04:59:59 CDT