-- calcspac.sql
--
-- This sql proc extimates the initial amount of space required by a
-- non-clustered table in an Oracle database. When using these estimates,
-- keep in mind the following sources of inaccuracy:
-- 1. The space used by updates and deletes does not free up immediately.
-- 2. Trailing null columns and length bytes are not stored.
-- 3. Inserts, updates, and deletes cause fragmentation and chained row
-- pieces.
--
-- This procedure assumes that there is a table with a sample set of data
-- already created. It allows you to specify the number of rows the actual
-- table would grow to.
--
-- Inputs:
-- table name
-- table owner
-- number of rows
--
set serveroutput on
set verify off
set feedback off
prompt Estimate Space Used by a table.
prompt
prompt Method based on instructions in chapter 8 of Oracle7 Server Administrators Guide
prompt
prompt Add 10% to any results to give room for error.
prompt
accept owner prompt "Enter Table Owner (default user) : "
accept table prompt "Enter Table Name : "
accept numrows number prompt "Estimate of total number of rows (0 for existing count) : "
declare
-- Step 1. Calculate Total Block Header Size
--
-- block_header_part_a = fixed_header + variable_trans_header
block_header_part_a number := 0;
-- fixed_header = 57 bytes
fixed_header constant number := 57;
-- variable_trans_header = 23 * I
-- where I is the INITRANS for the table.
variable_trans_header number := 0;
variable_trans_header_overhead constant number := 23;
-- block_header_part_b = table_directory + row_directory
block_header_part_b number := 0;
-- table_directory = 4 bytes
table_directory constant number := 4;
-- row_directory = 2 * number_of_rows_per_block
row_directory number := 0;
row_directory_overhead constant number := 2;
-- total_block_header = block_header_part_a + block_header_part_b
total_block_header number := 0;
-- Step 2: Calculate available data space per data block.
--
-- available_data_space = (block_size - total_block_header) -
-- free_space_overhead
--
-- free_space_overhead = (block_size - block_header_part_a)*(pctfree/100)
available_data_space number := 0;
free_space_overhead number := 0;
pctfree_ number := 0;
block_size number := 0;
-- Step 3: Calculate combined data space.
average_row_data number := 0;
-- Step 4: Calculate total average row size.
--
-- avg_row_size = row_header + F + V + average_row_data
-- F = number of columns which store 250 bytes or less * 1
-- V = number of columns which store more than 250 byes * 3
avg_row_size number := 0;
row_header constant number := 3;
F number := 0;
F_overhead constant number := 1;
V number := 0;
V_overhead constant number := 3;
min_avg_row_size constant number := 9;
-- Step 5: Calculate average rows per block
--
-- number_of_rows_per_block = available_data_space / avg_row_size
--
-- but since the number of rows per block is used in available_data_space,
-- we must solve the above equation for number_of rows per block.
--
-- number_of_rows_per_block =
-- [(block_size - total_block_header) -
-- free_space_overhead] / avg_row_size
--
-- number_of_rows_per_block =
-- [(block_size - (block_header_part_a +
-- block_header_part_b)) -
-- free_space_overhead] / avg_row_size
--
-- number_of_rows_per_block =
-- [(block_size - (block_header_part_a +
-- table_directory + row_directory)) -
-- free_space_overhead] / avg_row_size
--
-- number_of_rows_per_block =
-- [(block_size - (block_header_part_a +
-- table_directory +
-- (row_directory_overhead *
-- number_of_rows_per_block))) -
-- free_space_overhead] / avg_row_size
--
-- avg_row_size * number_of_rows_per_block =
-- (block_size - (block_header_part_a +
-- table_directory +
-- (row_directory_overhead *
-- number_of_rows_per_block))) -
-- free_space_overhead
--
-- avg_row_size * number_of_rows_per_block =
-- block_size -
-- block_header_part_a -
-- table_directory -
-- (row_directory_overhead *
-- number_of_rows_per_block) -
-- free_space_overhead
--
-- (avg_row_size * number_of_rows_per_block) +
-- (row_directory_overhead * number_of_rows_per_block) =
-- block_size -
-- block_header_part_a -
-- table_directory -
-- free_space_overhead
--
-- (avg_row_size + row_directory_overhead) * number_of_rows_per_block =
-- block_size -
-- block_header_part_a -
-- table_directory -
-- free_space_overhead
--
-- number_of_rows_per_block =
--
-- block_size - block_header_part_a - table_directory - free_space_overhead
-- ------------------------------------------------------------------------
-- (avg_row_size + row_directory_overhead)
--
number_of_rows_per_block number := 0;
-- Step 6: Calculate Number of Blocks and Bytes
--
-- number_of_blocks = number_of_rows / number_of_rows_per_block;
number_of_blocks number := 0;
number_of_rows number := 0;
-- number_of_bytes = number_of_blocks * block_size
number_of_bytes number := 0;
--
begin
dbms_output.put_line ('.');
dbms_output.put_line ('Analyzing '|| upper(nvl ('&&owner',user)) || '.' ||
upper('&&table') || '.');
dbms_output.put_line ('.');
-- Analyze the table to get the most current information
dbms_ddl.analyze_object (type => 'TABLE',
schema => upper (nvl ('&&owner',user)),
name => upper ('&&table'),
method => 'ESTIMATE');
-- Get information about the table from the data dictionary.
select ini_trans * variable_trans_header_overhead,
num_rows,
pct_free,
avg_row_len
into variable_trans_header,
number_of_rows,
pctfree_,
average_row_data
from all_tables
where table_name = upper ('&&table')
and owner = upper (nvl ('&&owner',user));
select to_number(value)
into block_size
from v$parameter
where name = 'db_block_size';
if nvl (&&numrows, 0) <> 0 then
number_of_rows := &&numrows;
end if;
-- Step 1. Calculate Total Block Header Size
block_header_part_a := fixed_header + variable_trans_header;
dbms_output.put_line (' Block header part A = ' ||
to_char(block_header_part_a));
-- Step 2: Calculate available data space per data block.
--
free_space_overhead := ceil ((block_size - block_header_part_a)
*(pctfree_/100));
dbms_output.put_line (' Free space overhead = ' ||
to_char(free_space_overhead));
-- Step 3: Calculate combined data space. (obtained from analyze command.)
-- Step 4: Calculate total average row size.
--
-- F = number of columns which store 250 bytes or less * 1
select count(*) * F_overhead
into F
from all_tab_columns
where owner = upper (nvl ('&&owner', user))
and table_name = upper ('&&table')
and ((data_type in ('NUMBER','DATE'))
or (data_type in ('CHAR','VARCHAR2','RAW') and
data_length <= 250));
dbms_output.put_line (' No. columns <= 250 (F) = ' ||
to_char(F));
-- V = number of columns which store more than 250 byes * 3
select count(*) * V_overhead
into V
from all_tab_columns
where owner = upper (nvl ('&&owner',user))
and table_name = upper ('&&table')
and ((data_type in ('LONG','LONG RAW'))
or (data_type in ('CHAR','VARCHAR2','RAW') and
data_length > 250));
dbms_output.put_line (' No. columns > 250 (V) = ' ||
to_char(V));
avg_row_size := row_header + F + V + average_row_data;
dbms_output.put_line (' Average row size = ' ||
to_char(avg_row_size));
-- Step 5: Calculate average rows per block
-- block_size - block_header_part_a - table_directory - free_space_overhead
-- ------------------------------------------------------------------------
-- (avg_row_size + row_directory_overhead)
--
number_of_rows_per_block := floor (
(block_size - block_header_part_a - table_directory - free_space_overhead)/
(avg_row_size + row_directory_overhead));
dbms_output.put_line (' No. rows per block = ' ||
to_char(number_of_rows_per_block));
-- Step 6: Calculate Number of Blocks and Bytes
dbms_output.put_line ('.');
number_of_blocks := ceil (number_of_rows / number_of_rows_per_block);
dbms_output.put_line (' No. of blocks = ' ||
to_char(number_of_blocks));
number_of_bytes := number_of_blocks * block_size;
dbms_output.put_line (' No. of bytes = ' ||
to_char(number_of_bytes));
end;
/
set verify on
set feedback on
Hiç yorum yok:
Yorum Gönder