 |
|
Oracle Tips by Burleson |
Database objects that
support SHRINK SPACE operation.
Oracle10g provides the ability to perform an in-place reorganization
of data for optimal space utilization by shrinking it. This feature
also provides the ability to both compact the space used in a
segment and then de-allocate it from the segment.
The de-allocated space is returned to the tablespace and is
available to other objects in the tablespace. Sparsely populated
tables may cause a performance problem for full table scans. By
performing shrink, data in the table is compacted and the HWM of the
segment is pushed down. This makes full table scans read less
blocks. As a result, they run faster. Also, during compaction, row
chaining is eliminated whenever possible.
The ASSM tool allows DBAs to estimate the sizes of indexes they
intend to create on a table. The procedure, create_index_cost, from
the dbms_space package allows the DBA to get an estimate for space
usage of the future index. This can be accomplished by using the
awr_estimate_index_cost.sql query that follows:
* awr_estimate_index_cost.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
declare
u_bytes number;
a_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index stats$sysstat_idx on stats$sysstat '||
'(value) tablespace sysaux',
used_bytes => u_bytes,
alloc_bytes => a_bytes
);
dbms_output.put_line ('Used Bytes = '|| u_bytes);
dbms_output.put_line ('Allocated Bytes = '|| a_bytes);
end;
/
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
