Re: Varied block density for fixed length row tables
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "rjoralist2@xxxxxxxxxxxxxxxxxxxxx" <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 Sep 2011 10:31:57 -0700 (PDT)
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(*)
------------------------------------ ----------
2092 69
2093 71
2096 71
1666385 4
1666391 72
1666392 68
6 rows selected.
Using this query would, in my mind, eliminate any ambiguity in interpreting the
raw rowid values.
David Fitzjarrell
From: Rich Jesse <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
