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

/ just to see if there is a potential benefit for backups /
operations

I have run following query


SELECT blocks as BLOCKS_USED, empty_blocks
FROM dba_tables
WHERE table_name='TASK'

It shows me I have no empty blocks

Is there a query that shows me deleted rows / non-deleted rows
within the used blocks for this table

My aim is to assess wether a table export / import would be beneficial
for

1) rman backups
2) database operations


regards
Chris B

On Apr 18, 7:48*am, dba cjb <chris.br... (AT) providentinsurance (DOT) co.uk>
wrote:
I don't understand why knowing what rows were deleted affects whether
or not you perform an export; possibly you could explain that to us.
Oracle provides a least two tools you could use to identify which rows
have been deleted: the DBMS_LOGMNR package or Change Data Capture.
There is (to my knowledge) no way to scan each data block associated
with a table to find the rows currentlty marked as deleted, and using
either of the above methods of deleted row identification could still
not show an accurate 'image' of the table data as slots vacated by
deleted rows could be filled by newly inserted data. You could also
query the table for rowids then pass those through the DBMS_ROWID
package to extract the file, block and row address then visually scan
that for 'holes' (a time-consuming task). Again I don't understand
why this is necessary information in the decision process on whether
or not to perform an export.


David Fitzjarrell

On Apr 18, 8:48*am, ddf <orat... (AT) msn (DOT) com> wrote:
It seems apparent to me he's asking about sparseness. There's plenty
of discussion about index sparseness like
http://jonathanlewis.wordpress.com/2...ndex-analysis/

Table sparseness could be inferred by looking at how many rows there
are, times the average row length (plus row overhead), and compare
that to how much space is taken up. It could get more precise if you
worked out pctfree, pctused and block overhead effects, but I think
that isn't really necessary for a general "is there a lot of wasted
space here?" A small amount of space is taken up by deletion markers,
so it is possible to fragment blocks, depending on deletion patterns,
which is entirely app dependent.

The decision about whether to use exp/imp to reorder a segment is
complicated by any implicit ordering the data gets from the app. It
is entirely possible that an update process fills up blocks fully
utilizing them, so an exp/imp with default pctfree could make a table
larger, leaving unwanted free space. It's also possible a gazillion
block splits have happened, which could be fixed by an exp/imp. Row
chaining, row migration, large deletes could add up to needing this
kind of operation. Of course, a table move could fix these too,
except where rows just are too big to fit.

The better way to answer this might be to duplicate the db with rman
to a test system, run through the exp/imp and watch what happens.
I've found some things become obvious with the dbconsole tablespace
map option, others require actual testing to see whether any
difference in space or performance actually happens. In general, with
modern LMT's everything works fairly well, but there are apps that
work against it and some features/bugs that can be wasteful, as the
above link demonstrates. Just generally doing exp/imp to "defragment"
tablespaces is almost certainly a waste of time at best.

Also, beware of the exp compress=y option and the initial value for
the segment, and you might want to consider temporarily changing
pctfree.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...nder-registry/

"dba cjb" <chris.brown (AT) providentinsurance (DOT) co.uk> wrote


It depends how much work you want Oracle to do, what sort of precision you
want in your answer, and what assumptions you are prepared to make about
your data. For example, if you're prepared to assume that every row in a
table is about the same size then there's a query on my blog that could be
helpful: http://jonathanlewis.wordpress.com/2...ows-per-block/

Slice and dice it various ways, but if you have an expected number of rows
per block this gives you some idea of how many blocks are not filled.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

On Apr 18, 1:28*pm, "Jonathan Lewis" <jonat... (AT) jlcomp (DOT) demon.co.uk>
wrote:

Based on the OP's question about doing an export/import sounds to me
as if he thinks he has allocated/unused blocks taken up by a
particular table and/or index most typically caused by large batch
inserts followed by large batch of deletes. With LMT, once space is
allocated to an OBJECT, it is never reclaimed for general use until
you 1) drop the object, (export/import would take care of that and was
the traditional way to reclaim space on a variety of databases and OS
platforms) , 2) SHRINK the object, or 3) move the object to another
tablespace.

You can spend a lot of time researching to find allocated but unused
blocks or you can just look up ALTER {TABLE|INDEX} SHRINK and attempt
to fix the problem ONLINE without interruption of day-to-day
activities.

if you have 1000000 data blocks with only one 1K row, you might
reduce the overall size of your RMAN backup as well as reduce your
overall backup times. That being said, using disk backups for RMAN
and using a minimum of 4 threads, I had (a few companies ago) a 2TB
backed up in < 3hrs. 5 or more threads seem to push the I/O bandwidth
to the point my < 3hr backup would take > 6. YMMV.

]"onedbguru" <onedbguru (AT) yahoo (DOT) com> wrote

]On Apr 18, 1:28 pm, "Jonathan Lewis" <jonat... (AT) jlcomp (DOT) demon.co.uk>
]wrote:
]>
]> It depends how much work you want Oracle to do, what sort of precision
you
]> want in your answer, and what assumptions you are prepared to make about
]> your data. For example, if you're prepared to assume that every row in a
]> table is about the same size then there's a query on my blog that could
be
]> helpful: http://jonathanlewis.wordpress.com/2...ows-per-block/
]>
]> Slice and dice it various ways, but if you have an expected number of
rows
]> per block this gives you some idea of how many blocks are not filled.
]>
]
]You can spend a lot of time researching to find allocated but unused
]blocks or you can just look up ALTER {TABLE|INDEX} SHRINK and attempt
]to fix the problem ONLINE without interruption of day-to-day
]activities.


You could indeed attempt to fix the problem without knowing what the
problem was and what the impact might be, but then you might end up as one
of the unhappy individuals posting the question: "I did a shrink space on
a table and its been running for the last 24 hours and has generated over
75 GB of redo; what should I do ?"

Joel Garry's comment about 'average row length' x number of rows might be a
good enough first approximation for the OP to allow him to decide how much
more time he's going to spend on understanding the problem. Your
hypothesis of a batch job that deletes lots of old rows while inserting new
ones might give him the idea that SHRINK is the worst way he could have to
reclaim his space because in that scenario he might end up copying every
row (one at a time - with index maintenance) from the "new" end of the
table to the "old" end of the table because all the empty space is
currently at the old end of the table.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

On Apr 18, 4:48*pm, dba cjb <chris.br... (AT) providentinsurance (DOT) co.uk>
wrote:
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/E...tm#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-.../dp/1430226684

On Apr 19, 10:59*am, Randolf Geist <mah... (AT) web (DOT) de> wrote:
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

1) Reducing the space that a table / + indexes takes up
2) 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

On Apr 19, 2:59*am, Randolf Geist <mah... (AT) web (DOT) de> wrote:
Thanks to all for the various clarifications.

The 10g license doc says you need the tuning pack to reorganize
objects, the diagnostics pack to use the tuning pack, and if you click
the setup link on the EM home page, then the management packs link,
you will see check boxes that will take away any links or tabs you
can't use. Doing this grays out the performance tab on the home page,
so you can't get to any of the useful screens.
http://download.oracle.com/docs/cd/B...99/options.htm

The 11g license doc is more clear about accessing the functionality
"whether through Enterprise Manager Console, Desktop Widgets, command-
line APIs, or direct access to the underlying data":
http://download.oracle.com/docs/cd/E...s.htm#CIHGFIAF

For my largest tables which periodically show up and I then set up a
shrink job, it appears to be wildly overoptimistic about how much
space it will get, much like those who waste time rebuilding indices
every weekend.

Looking at the dba_feature_usage_statistics is left as an exercise for
the paranoid, or realists, as the case may be.

jg
--
@home.com is bogus.
http://web.signonsandiego.com/news/2...e-digital-age/

On Apr 19, 12:20*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:

<soapbox=on>
If anyone didn't negotiate these packages for free, they are in
serious need of lessons in negotiating with Oracle. These "packages"
are an integral part of the database engine and cannot be removed and
you cannot not install them. And because they use my CPU and my
Memory and my Storage for something that I cannot use is ludicrous.
Later versions of Oracle use this whether or not you license it to
keep itself intact. Try billing Oracle for the 1-3% CPU/memory and
sometimes Gigs of storage that these "licensed options" take out of
your system for which you get no real benefit. Never EVER take
Oracle's first quote
</soapbox>