Re: Varied block density for fixed length row tables
Date: Wed, 14 Sep 2011 10:31:57 -0700 (PDT)
Message-ID: <1316021517.31785.YahooMailNeo_at_web65407.mail.ac4.yahoo.com>
Why are you not using the DBMS_ROWID functions?
select dbms_rowid.rowid_block_number(rowid), count(*) from <table_name>
group by dbms_rowid.rowid_block_number(rowid) order by 1;
Sample output follows (formatting may be off):
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------6 rows selected.
2092 69
2093 71
2096 71
1666385 4
1666391 72
1666392 68
Using this query would, in my mind, eliminate any ambiguity in interpreting the raw rowid values.
David Fitzjarrell
From: Rich Jesse <rjoralist2_at_society.servebeer.com>
To: oracle-l_at_freelists.org
Sent: Wednesday, September 14, 2011 9:56 AM
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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 14 2011 - 12:31:57 CDT