RE: Varied block density for fixed length row tables
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 Sep 2011 15:16:49 -0400
Others have already commented on optimizing your diagnostic query. I think
it gives the correct results though, so I'll address your question about
"variable density."
The vsize of a number is related how many significant digits it contains. So
if some rows have number columns with the value 0 and some have a lot of
digits they will vary in physical length. Numbers that are even 10, 1000,
100000, etc. are 1 byte smaller, for example than other integers with the
same number of digits (except for even 100, 10000, 1000000, etc. which are
two bytes smaller).
So sometimes that extra row might fit and sometime not. You're probably
getting the data as dense as you can for the block size you're using.
(You'll get marginally more density with a bigger block size because of less
block overhead per usable data space plus the odds improving that fewer
blocks will fit one less row.) Beyond that you could compute the sum of the
vsizes of rows, determine the max that will exactly fit in a given block
size, and chose row length combinations to fit as exactly as possible. Gimme
twelve normals and an extra long, or eleven normal and two longs, Joe. If
you think of words as rows and lines in a paragraph as blocks in ragged
right formatting, some of your words are longer, so sometimes more or fewer
words fit. You've actually stated a concern about the number of rows per
block (words per line), but I believe your root concern is the ragged empty
space on the end of many lines. Sorry. Unless the contents of your rows are
designed to exactly fit in a block that is just going to happen. And except
for demonstration cases for block dumping I can't think of a reason to
design your row lengths.
All such machinations are unlikely to be net positive uses of the machine or
your brain, but I suppose there are cases where it might work out.
But if this all about storage size, then doing the copy in the order of some
index so the index that matches the physical row order ends up smaller (yes
it does - though this is an artifact of the recent implementations).
Although that all begs the question of how you're executing this copy. I
would suspect in the race between dropping prices in cpus and storage you'd
still win staying a little bigger and using some physical copy strategy
(redo application family including roll it yourself and dataguard, or
storage vendor based duplication) rather than reloading. My expectation of
this being a win is energy expenditure to actually run a reload operation
versus copying the data. Obviously this is just a speculation and I have not
tested this.
Likewise, I'm wondering for what you use the copy. Changing density and
ordering might well have performance implications.
Good luck!
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Rich Jesse
Sent: Wednesday, September 14, 2011 12:57 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Varied block density for fixed length row tables
Hey all,
I've got a schema in a 10.1 DB that gets a full copy of our 10.1 prod ERP
schema on a monthly basis. To help reduce storage, I've dropped the PCTFREE
on the largest tables to increase the density of rows per block. In
reviewing how well this is working, I calculate the rows per block using:
SELECT
rowcount,count(*)
FROM
(
SELECT /*+ parallel (mytab 4) nocache (mytab) */
count(*) ROWCOUNT
FROM myschema.mytable MYTAB
GROUP BY SUBSTR(ROWIDTOCHAR(ROWID),8,8)
)
GROUP BY rowcount
ORDER BY 1;
On a few tables, the above query shows that lots (30%+) of blocks have 1
less row than the rest. This translates to Gigs of wasted space as far as
I'm concerned.
These tables only have NCHAR and NUMBER datatypes, and do not have NULLs, so
the rowsize should be static. The rows were all loaded with a single direct
load INSERT pulling data across a DB link, and after a TRUNCATE DROP
STORAGE. This particular table would have pulled the remote table using a
parallel query, DOP of 2. The NLS NCHAR set is AL16UTF16. Blocksize 8K.
Row length 843 bytes (from table stats).
I've rechecked my ROWIDTOCHAR calculation to group by block number, and it
seems correct, but I could be misinterpreting the rowid format.
Thoughts?
Rich
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
