Question: I want to be able to accurately
estimate the amount of disk space used by an Oracle index. I
assume that the PCTFREE=10 and I know the blocksize and average key
length, but I wonder if Oracle has a routine that will estimate the
amount of space consumed by a new index?
Answer: The spaced used by an index depends
on several factors:
- Blocksize: You can use between a 2k
to 32k
blocksize and there is a slight variation in space usage between
index blocksizes.
- Key Length: The size of the index key
will obviously effect the size of the resulting index.
- PCTFREE: The amount of space left for index
row
expansion
You can use the
Oracle capacity planning spreadsheets to estimate table and
index size, and this formula
estimates the space requirements for a single Oracle index.
You can also invoke the dbms_metadata.get_ddl procedure to
estimate the current size of your indexes:
Franck Pachot has a method where you can use the plan table to
estimate the size of all indexes with dbms_metadata. The
PL/SQL below loops though all indexes in user_indexes and
uses the get_ddl procedure to gather the total estimated
space used for all indexes in the schema:
begin
for r in ( select index_name from user_indexes ) loop
execute immediate 'explain plan set statement_id='''||r.index_name||'''
for '||
dbms_metadata.get_ddl('INDEX',r.index_name);
end
loop;
end;
/
select
object_owner,
object_name,
cast(extractvalue(xmltype(other_xml),'/other_xml/info[@type="index_size"]')
as number) estimated_index_size
from
plan_table p
where
id=1;
You can use the dbms_space package create_index_cost [procedure to
estimate the size of an index. The
dbms_space.create_index_cost accepts the schema owner, the "create
index statement" (which includes the table and key names, and the
amount of space used and allocated.
used- The number of bytes representing the actual
index data.
alloc - Size of the index when
created in the tablespace.
SQL> variable used
number
SQL> variable
alloc number
SQL>
SQL> set autoprint on
SQL> exec
dbms_space.create_index_cost('create index scott.idx_test on
scott.emp (emp_id, emp_hire_date) LOCAL', :used, :alloc );
For global sizing estimate of tables and indexes, I recommend the
Oracle capacity planning spreadsheets.
To estimate the size of an index for the
storage clause, you can
use the following procedure:
1. Calculate the required database block header size (BHS).
BHS = fixed header + variable transaction header
where:
fixed header = 113
variable transaction header = 24 *INITRANS
2. Calculate available data space (ADS).
ADS = ((Blocksize - BHS) * (PCTFREE/100))
3. Calculate the average data length (ADL) per row.
Note:
Step 3 is the same as step 3 in the table-sizing section in Chapter
5. However, size only for those columns in the index.
4. Calculate the average row length (ARL).
bytes/entry = entry header + ROWID length + F + V + D
where:
entry header = 2
ROWID = 6
F = Total length of bytes of all columns that store 127 bytes or
fewer—one header byte per column.
V = Total length of bytes of all columns that store more than 127
bytes—two header bytes per column.
For UNIQUE indexes, the entry header is 0.
5. Calculate number of blocks for index.
# of Blocks = 1.05 * (((# of NOT NULL rows) / (ADS) / ARL))
The 1.05 factor allows for branch space and is an empirically (SWAG)
derived value.
6. Calculate the number of bytes required.
Size in bytes = BLOCKSIZE - number of blocks
Also see
measuring average rows per block in an Oracle index.
 |
|
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.
|
