One of the most important features of Oracle 10g and
beyond is its ability to predict the growth of the segments. The
dbms_space.object_growth_trend prediction mechanism is based on data
collected and stored by the AWR, and the growth trend reporting is also
built into the Oracle database kernel and is available by default.
DBMS_SPACE
The
dbms_space package provides a collection of procedures and
functions to assist with the analysis of segment growth and space needs.
This package can greatly assist with the key DBA task commonly referred
to as capacity planning. There are a few enumerated constants that one
must know to use this package:
OBJECT_TYPE_TABLE
constant positive := 1;
OBJECT_TYPE_NESTED_TABLE
constant positive := 2;
OBJECT_TYPE_INDEX
constant positive := 3;
OBJECT_TYPE_CLUSTER
constant positive := 4;
OBJECT_TYPE_LOB_INDEX
constant positive := 5;
OBJECT_TYPE_LOBSEGMENT
constant positive := 6;
OBJECT_TYPE_TABLE_PARTITION
constant positive := 7;
OBJECT_TYPE_INDEX_PARTITION
constant positive := 8;
OBJECT_TYPE_TABLE_SUBPARTITION
constant positive := 9;
OBJECT_TYPE_INDEX_SUBPARTITION
constant positive := 10;
OBJECT_TYPE_LOB_PARTITION
constant positive := 11;
OBJECT_TYPE_LOB_SUBPARTITION
constant positive := 12;
OBJECT_TYPE_MV constant
positive := 13;
OBJECT_TYPE_MVLOG
constant positive := 14;
OBJECT_TYPE_ROLLBACK_SEGMENT
constant positive := 15;
Asa_recommendations
is a function that returns the findings from the automatic segment
advisor. However, note that this requires proper OEM licensing;
namely, that the optional OEM Diagnostics Pack has been purchased.
It returns that information as type
dbms_space.
asa_reco_row_tb, as shown below.
|
Argument
|
Type
|
In
/ Out
|
Default Value
|
|
ALL_RUNS
|
VARCHAR2
|
IN
|
TRUE
|
|
SHOW_MANUAL
|
VARCHAR2
|
IN
|
TRUE
|
|
SHOW_FINDINGS
|
VARCHAR2
|
IN
|
FALSE
|
Table
6.117:
Asa_recommendations Parameters
type asa_reco_row is record (
tablespace_name
varchar2(30),
segment_owner
varchar2(30),
segment_name
varchar2(30),
segment_type
varchar2(18),
partition_name
varchar2(30),
allocated_space
number,
used_space
number,
reclaimable_space
number,
chain_rowexcess
number,
recommendations
varchar2(1000),
c1
varchar2(1000),
c2
varchar2(1000),
c3
varchar2(1000),
task_id
number,
mesg_id
number
);
type asa_reco_row_tb is table of asa_reco_row;
Create_index_cost
is a procedure that calculates or estimates the storage required to
create an index on an existing table.
|
Argument
|
Type
|
In
/ Out
|
Default Value
|
|
DDL
|
VARCHAR2
|
IN
|
|
|
USED_BYTES
|
NUMBER
|
OUT
|
|
|
ALLOC_BYTES
|
NUMBER
|
OUT
|
|
|
PLAN_TABLE
|
VARCHAR2
|
IN
|
NULL
|
Table
6.118:
Create_index_cost Parameters
Create_table_cost
is an overloaded procedure that calculates or estimates the storage
required to create a new table. However, remember that the answer can
vary widely based upon items such as tablespace storage attributes,
tablespace block size, and such. Thus,
create_table_cost offers two versions and/or methods of
operation to provide for better estimates. The first method is based on
the average row size and row count, and so this method is only as good
as the estimates for those values.
|
Argument
|
Type
|
In
/ Out
|
Default Value
|
|
TABLESPACE_NAME
|
VARCHAR2
|
IN
|
|
|
AVG_ROW_SIZE
|
NUMBER
|
IN
|
|
|
ROW_COUNT
|
NUMBER
|
IN
|
|
|
PCT_FREE
|
NUMBER
|
IN
|
|
|
USED_BYTES
|
NUMBER
|
OUT
|
|
|
ALLOC_BYTES
|
NUMBER
|
OUT
|
|
Table
6.119:
Create_table_cost Parameters
The second
create_table_cost method looks at the actual column
properties like data type and size, and can often yield fairly reliable
predictions. Note that the second parameter for column information
requires using the
create_table_cost_columns data type.
|
Argument
|
Type
|
In
/ Out
|
Default Value
|
|
TABLESPACE_NAME
|
VARCHAR2
|
IN
|
|
|
COLINFOS
|
SYS. CREATE_TABLE_COST_COLUMNS
|
IN
|
|
|
ROW_COUNT
|
NUMBER
|
IN
|
|
|
PCT_FREE
|
NUMBER
|
IN
|
|
|
USED_BYTES
|
NUMBER
|
OUT
|
|
|
ALLOC_BYTES
|
NUMBER
|
OUT
|
|
Table
6.120:
Create_table_cost_columns Parameters
type SYS.CREATE_TABLE_COST_COLINFO is object (
col_type varchar(200),
col_size number
);
type SYS.CREATE_TABLE_COST_COLUMNS is
varray(50000) of create_table_cost_colinfo;
Free_blocks
is a procedure that returns information about the free blocks in an
object or segment. This represents all the free space and not just the
free space above the high water mark. Use the
scan_limit parameter only if one is interested in the
question, "Are there X blocks on the free list?"
|
Argument
|
Type
|
In
/ Out
|
Default Value
|
|
SEGMENT_OWNER
|
VARCHAR2
|
IN
|
|
|
SEGMENT_NAME
|
VARCHAR2
|
IN
|
|
|
SEGMENT_TYPE
|
VARCHAR2
|
IN
|
|
|
FREELIST_GROUP_ID
|
NUMBER
|
IN
|
|
|
FREE_BLKS
|
NUMBER
|
OUT
|
|
|
SCAN_LIMIT
|
NUMBER
|
IN
|
NULL
|
|
PARTITION_NAME
|
VARCHAR2
|
IN
|
NULL
|
Table
6.121:
Free_blocks Parameters
