DBMS_SPACE

最新推荐文章于 2023-09-02 01:04:02 发布

原创最新推荐文章于 2023-09-02 01:04:02 发布

· 317 阅读

0 ·

版权

文章标签：

#算法

Oracle 专栏收录该内容

193 篇文章

订阅专栏

（一）WILL COST

1. index cost需要先分析对应表：
execute dbms_stats.gather_table_stats(ownname =>'owner', tabname => 'table_name');
DBMS_SPACE.CREATE_INDEX_COST (
ddl IN VARCHAR2,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER,
plan_table IN VARCHAR2 DEFAULT NULL);

2. table cost:
DBMS_SPACE.CREATE_TABLE_COST (
tablespace_name IN VARCHAR2,
avg_row_size IN NUMBER,
row_count IN NUMBER,
pct_free IN NUMBER,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER);

DBMS_SPACE.CREATE_TABLE_COST (
tablespace_name IN VARCHAR2,
colinfos IN CREATE_TABLE_COST_COLUMNS,
row_count IN NUMBER,
pct_free IN NUMBER,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER);

CREATE TYPE create_table_cost_colinfo IS OBJECT (
COL_TYPE VARCHAR(200),
COL_SIZE NUMBER);
type create_table_cost_columns is varray(50000) of create_table_cost_colinfo;

The used_bytes represent the actual bytes used by the data. This includes the overhead due to the block metadata, pctfree etc.
The alloc_bytes represent the size of the table when it is created in the tablespace. This takes into account, the size of the extents in the tablespace and tablespace extent management properties.

set serveroutput on
DECLARE
ub NUMBER;
ab NUMBER;
cl sys.create_table_cost_columns;
BEGIN
cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10),
sys.create_table_cost_colinfo('VARCHAR2',30),
sys.create_table_cost_colinfo('VARCHAR2',30),
sys.create_table_cost_colinfo('DATE',NULL));

DBMS_SPACE.CREATE_TABLE_COST('SYSTEM',cl,100000,0,ub,ab);

DBMS_OUTPUT.PUT_LINE('Used Bytes: ' || TO_CHAR(ub));
DBMS_OUTPUT.PUT_LINE('Alloc Bytes: ' || TO_CHAR(ab));
END;
/

3. 数据增长趋势：
DBMS_SPACE.OBJECT_GROWTH_TREND (
object_owner IN VARCHAR2,
object_name IN VARCHAR2,
object_type IN VARCHAR2,
partition_name IN VARCHAR2 DEFAULT NULL,
start_time IN TIMESTAMP DEFAULT NULL, ----依赖统计数据的开始时间
end_time IN TIMESTAMP DEFAULT NULL, ---依赖统计数据的结束时间
interval IN DSINTERVAL_UNCONSTRAINED DEFAULT NULL, --The interval at which to sample
skip_interpolated IN VARCHAR2 DEFAULT 'FALSE',   --Whether interpolation of missing values should be skipped
timeout_seconds IN NUMBER DEFAULT NULL,   --The time-out value for the function in seconds
single_datapoint_flag IN VARCHAR2 DEFAULT 'TRUE')    --Whether in the absence of statistics the segment should be sampled
RETURN object_growth_trend_table PIPELINED;

TYPE object_growth_trend_row IS RECORD(
timepoint TIMESTAMP,
space_usage NUMBER,
space_alloc NUMBER,
quality VARCHAR(20));
TYPE object_growth_trend_table IS TABLE OF object_growth_trend_row;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（二）SPACE USAGE

1. 查询MSSM表空间segment freelist上的free blocks
variable free_blocks number;
exec DBMS_SPACE.FREE_BLOCKS(segment_owner=>'SCOTT', segment_name=>'CLUS', segment_type=>'CLUSTER', freelist_group_id=>3, partition_name=>'p1', free_blks=>:free_blocks);
print free_blocks;

2. 查询ASSM表空间segment:
1) The first form of the procedure shows the space usage of data blocks under the segment High Water Mark.
You can calculate usage for LOBs, LOB PARTITIONS and LOB SUBPARTITIONS.
This procedure can only be used on tablespaces that are created with auto segment space management.
The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. Note that this overload cannot be used on SECUREFILE LOBs.
For LOB segments, the number of blocks that is returned from full_blocks and unformatted_blocks is actually the number of chunks for the LOB segment.

DBMS_SPACE.SPACE_USAGE(
segment_owner IN VARCHAR2,
segment_name IN VARCHAR2,
segment_type IN VARCHAR2,
unformatted_blocks OUT NUMBER, --For LOB segments, the number of blocks that is returned from unformatted_blocks is actually the number of chunks for the LOB segment.
unformatted_bytes OUT NUMBER,
fs1_blocks OUT NUMBER,   --Number of blocks having at least 0 to 25% free space
fs1_bytes OUT NUMBER,
fs2_blocks OUT NUMBER,   --Number of blocks having at least 25 to 50% free space
fs2_bytes OUT NUMBER,
fs3_blocks OUT NUMBER,   --Number of blocks having at least 50 to 75% free space
fs3_bytes OUT NUMBER,
fs4_blocks OUT NUMBER,   --Number of blocks having at least 75 to 100% free space
fs4_bytes OUT NUMBER,
full_blocks OUT NUMBER,   --The number of blocks that is returned from full_blocks is actually the number of chunks for the LOB segment
full_bytes OUT NUMBER,
partition_name IN VARCHAR2 DEFAULT NULL);

EXAMPLE:
variable unf number;
variable unfb number;
variable fs1 number;
variable fs1b number;
variable fs2 number;
variable fs2b number;
variable fs3 number;
variable fs3b number;
variable fs4 number;
variable fs4b number;
variable full number;
variable fullb number;
begin
dbms_space.space_usage('U1','T',
'TABLE',
:unf, :unfb,
:fs1, :fs1b,
:fs2, :fs2b,
:fs3, :fs3b,
:fs4, :fs4b,
:full, :fullb);
end;
/
print unf ;
print unfb ;
print fs4 ;
print fs4b;
print fs3 ;
print fs3b;
print fs2 ;
print fs2b;
print fs1 ;
print fs1b;
print full;
print fullb;

2) The second form of the procedure returns information about SECUREFILE LOB space usage.
It will return the amount of space in blocks being used by all the SECUREFILE LOBs in the LOB segment.
The procedure displays the space actively used by the LOB column, freed space that has retention expired, and freed space that has retention unexpired.
Note that this overload can be used only on SECUREFILE LOBs.

DBMS_SPACE.SPACE_USAGE(
segment_owner IN VARCHAR2,
segment_name IN VARCHAR2,
segment_type IN VARCHAR2,
segment_size_blocks OUT NUMBER,   --Number of blocks allocated to the segment
segment_size_bytes OUT NUMBER,
used_blocks OUT NUMBER,       --Number blocks allocated to the LOB that contains active data
used_bytes OUT NUMBER,
expired_blocks OUT NUMBER,       --Number of expired blocks used by the LOB to keep version data
expired_bytes OUT NUMBER,
unexpired_blocks OUT NUMBER,   --Number of unexpired blocks used by the LOB to keep version data
unexpired_bytes OUT NUMBER,
partition_name IN VARCHAR2 DEFAULT NULL);

3. 查询unused blocks

DBMS_SPACE.UNUSED_SPACE (
segment_owner IN VARCHAR2,
segment_name IN VARCHAR2,
segment_type IN VARCHAR2,
total_blocks OUT NUMBER,       --Returns total number of blocks in the segment
total_bytes OUT NUMBER,
unused_blocks OUT NUMBER,       --Returns number of blocks which are not used
unused_bytes OUT NUMBER,
last_used_extent_file_id OUT NUMBER,   --Returns the file ID of the last extent which contains data
last_used_extent_block_id OUT NUMBER,   --Returns the starting block ID of the last extent which contains data
last_used_block OUT NUMBER,    --Returns the last block within this extent which contains data
partition_name IN VARCHAR2 DEFAULT NULL);

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（三）DEPENDENT

返回对象的dependent object
DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS(
objowner IN VARCHAR2,
objname IN VARCHAR2,
partname IN VARCHAR2, ---partition_name
objtype IN NUMBER)
RETURN dependent_segments_table PIPELINED;

TYPE object_dependent_segment IS RECORD (
segment_owner VARCHAR2(100),
segment_name VARCHAR2(100),
segment_type VARCHAR2(100),
tablespace_name VARCHAR2(100),
partition_name VARCHAR2(100),
lob_column_name VARCHAR2(100));

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（四）自定义函数方便查询
效果：
SQL> set serveroutput on
SQL> exec show_space('T1');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 1,419
Total Blocks............................ 1,536
Total Bytes............................. 12,582,912
Total MBytes............................ 12
Unused Blocks........................... 85
Unused Bytes............................ 696,320
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 1,664
Last Used Block......................... 43

set define off
create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
l_segment_space_mgmt varchar2(255);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number; l_fs1_bytes number;
l_fs2_blocks number; l_fs2_bytes number;
l_fs3_blocks number; l_fs3_bytes number;
l_fs4_blocks number; l_fs4_bytes number;
l_full_blocks number; l_full_bytes number;

   -- inline procedure to print out numbers nicely formatted
   -- with a simple label
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
to_char(p_num,'999,999,999,999') );
end;
begin
-- this query is executed dynamically in order to allow this procedure
-- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary.
-- NOTE: at runtime, the invoker MUST have access to these two
-- views!
-- this query determines if the object is a ASSM object or not
begin
execute immediate
'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
           and seg.segment_type = :p_type
and seg.tablespace_name = ts.tablespace_name'
into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner, p_type;
exception
when too_many_rows then
dbms_output.put_line
( 'This must be a partitioned table, use p_partition => ');
return;
end;

-- if the object is in an ASSM tablespace, we must use this API
-- call to get space information, else we use the FREE_BLOCKS
-- API for the user managed segments
if l_segment_space_mgmt = 'AUTO'
then
dbms_space.space_usage
( p_owner, p_segname, p_type, l_unformatted_blocks,
l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);

p( 'Unformatted Blocks ', l_unformatted_blocks );
p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
p( 'FS4 Blocks (75-100)', l_fs4_blocks );
p( 'Full Blocks ', l_full_blocks );
else
dbms_space.free_blocks(
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);

p( 'Free Blocks', l_free_blks );
end if;

-- and then the unused space API call to get the rest of the
-- information
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );

p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
set define on