Question: I need to get the length of a
particular row in Oracle. I am not aware of how to collect
size(bytes) for a particular row.
I know that I can use dbms_stats to get the
but I need to compute the actual row length for a specific row.
I don't just need the data space used by a row, I want to know
the actual space consumed, a real row length. I need to actual row
length, not a guess or an average.
Answer: For detailed row length internals,
you must use BBED
and look inside the block data structures, count the internal
pointer and data space used by the row.
Here is a working example of computing the row length in a case
where the name and datatype of each row are known in advance:
nvl(vsize(CASE_NUMBER ),0)+1 +
"Total bytes per row"
case = 123;
You will also see free-space at the end of a data block (as
defined by PCTFREE or ASSM, and you may also need to dump multiple
data blocks of the row is chained (fragmented) into multiple data
Here is a PL/SQL procedure that
individual row lengths to compute the average row length on
For any particular row, you can compute the row length by summing
these values for each row column, and guess by simply looking at the
- VARCHAR row size: A VARCHAR2 you can use a length
function, and you can also use instr and nvl(vsize) to see a
where emp_id = 123;
emp where emp_id = 123;
- NUMBER row size: For a number data type, you can see
the precision, and guess the actual bytes used
- For a CLOB you can use the
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.