Question: I need to know the data dictionary
query to find the size of each partition in a partitioned table.
I tried dba_tab_partitions but it does not provide the
partition sizing.
Will running this script give me the partition size?
dbms_stats.gather_table_stats('schema_name,'table_name,'partition_name')
Answer: There are several ways to get a
partition "size":
- You can look at the size of the physical data files
within the partition.
- You can count-up all of the extents in the partition.
- You can get the size of data within all segments of the
partition.
The dbms_stats does not give partition sizing
information, it only recomputes optimizer statistics. This could be
dangerous because it will change SQL execution plans.
However, dbms_stats will populate columns
dba_tables, num_rows and avg_row_len which
can be used to estimate the amount of row data within a partition.
You can get partition sizing information from
dba_segments:
select
sum(bytes/1024/1024)
from
dba_segments
where
segment_name='MYTABLE';
select
owner,
segment_name,
partition_name,
segment_type,
bytes / 1024/1024 "MB"
from
dba_segments
where
see code depot for full script
segment_name in
('SEG1','SEG2','SEG3');
select
owner,
segment_name,
partition_name,segment_type,bytes/1024/1024 "MB"
from
dba_segments
where
see code depot for full script
segment_type = 'TABLE PARTITION';
There are MANY other ways of seeing a partition size.
You can also use the
dbms_space.space_usage procedure to get the number of blocks in
a partition:
set serveroutput on
declare
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks
number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
v_segname varchar2(500);
begin
see code depot for full script
dbms_space.space_usage(
segment_owner => 'SCOTT,
segment_name => 'EMP,
segment_type => 'TABLE',
fs1_bytes =>
l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes =>
l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes =>
l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes =>
l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes =>
l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes =>
l_unformatted_bytes
);
dbms_output.enable;
dbms_output.put_line('=============================================');
dbms_output.put_line('total blocks = '||to_char(l_fs1_blocks +
l_fs2_blocks + l_fs3_blocks + l_fs4_blocks + l_full_blocks)|| ' ||
total bytes = '|| to_char(l_fs1_bytes + l_fs2_bytes + l_fs3_bytes +
l_fs4_bytes + l_full_bytes));
end;
/
You can also use
dbms_space.object_growth_trend and run a trend for a growing
partition size.
There are many other ways to compute the size of an Oracle
partition. If you don't want to write your own scripts, see the
Oracle script
collection.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
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
buy it
for 30% off directly from the publisher.
|
