Varied block density for fixed length row tables
- From: "Rich Jesse" <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 14 Sep 2011 11:56:59 -0500 (CDT)
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
Other related posts:
