| |
 |
|
Oracle Tips by Burleson |
Oracle-10g Storage
Diagnostics
With Oracle10g, a new segment advisor has been made available to
help troubleshoot object storage problems. Part of the dbms_advisor
package series, the new advisor is graphically available through the
new Oracle Enterprise Manager. It can be issued in an ad-hoc fashion
to find problem objects, or it can be set up to run on a scheduled
basis. If the advisor in OEM finds problems with one or more
objects, it provides the ability to reorganize them.
Oracle 10g ASSM
An Oracle10g database can reclaim space within data segments online
without affecting the end users access to their data. The only thing
the DBA must ensure before using the on-line segment reorganization
capability is that the tablespaces have the Automatic Segment Space
Management (ASSM) feature enabled. Oracle10g introduces the ability
to reclaim space from a segment by shrinking the segment. Shrinking
a segment will make unused space available to other segments in the
tablespace and may improve the performance of queries and DML
operations.
With the introduction of the powerful ASSM tool, the DBA can
effectively and easily manage database space. However, it must be
known which data segments experience high space wastage in order to
reclaim free space to the database and shrink segments. The
awr_list_seg_block_space.sql sample function reports percentages of
free space for data segments:
* awr_list_seg_block_space.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
-- *************************************************
drop type BlckFreeSpaceSet;
drop type BlckFreeSpace;
create type BlckFreeSpace as object
(
seg_owner varchar2(30),
seg_type varchar2(30),
seg_name varchar2(100),
fs1 number,
fs2 number,
fs3 number,
fs4 number,
fb number
);
create type BlckFreeSpaceSet as table of BlckFreeSpace;
create or replace function BlckFreeSpaceFunc (seg_owner IN varchar2,
seg_type in varchar2 default null) return BlckFreeSpaceSet
pipelined
is
outRec BlckFreeSpace :=
BlckFreeSpace(null,null,null,null,null,null,null,null);
fs1_b number;
fs2_b number;
fs3_b number;
fs4_b number;
fs1_bl number;
fs2_bl number;
fs3_bl number;
fs4_bl number;
fulb number;
fulbl number;
u_b number;
u_bl number;
begin
for rec in (select s.owner,s.segment_name,s.segment_type from
dba_segments s where owner = seg_owner and segment_type =
nvl(seg_type,segment_type) )
loop
dbms_space.space_usage (
segment_owner => rec.owner,
segment_name => rec.segment_name,
segment_type => rec.segment_type,
fs1_bytes => fs1_b,
fs1_blocks => fs1_bl,
fs2_bytes => fs2_b,
fs2_blocks => fs2_bl,
fs3_bytes => fs3_b,
fs3_blocks => fs3_bl,
fs4_bytes => fs4_b,
fs4_blocks => fs4_bl,
full_bytes => fulb,
full_blocks => fulbl,
unformatted_blocks => u_bl,
unformatted_bytes => u_b
);
outRec.seg_owner := rec.owner;
outRec.seg_type := rec.segment_type;
outRec.seg_name := rec.segment_name;
outRec.fs1 := fs1_bl;
outRec.fs2 := fs2_bl;
outRec.fs3 := fs3_bl;
outRec.fs4 := fs4_bl;
outRec.fb := fulbl;
Pipe Row (outRec);
end loop;
return;
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 |
