dbms_space.free_space

最新推荐文章于 2021-07-16 09:52:39 发布

youbo2004

最新推荐文章于 2021-07-16 09:52:39 发布

阅读量3.6k

点赞数

分类专栏： Oracle管理优化文章文章标签： table sql user performance system ext

本文链接：https://blog.csdn.net/youbo2004/article/details/107839

版权

Oracle管理优化文章专栏收录该内容

11 篇文章 0 订阅

订阅专栏

What is the use of this stored proc?
It tells you how many blocks have free space for updates, right ?
But it does not tell you how much free space in each block. We can
get free space info. from dba_free_space.
Can you show how this proc can be of value to us?
   Another procs in this package is unused_space.
If it reports 35 blocks. Does it mean 35 blocks have never
had data in it ?
   It seems that it doesn't report any empty blocks above
the high water mark, does it?
   How can we make use of this info ? Can you give some examples
that we can use these procedures to help manage space.

Thanks, Tom.

and we said...

Here is an example showing how to use dbms_space and how to interpret the 
output.  Basically between the 2 procedures free blocks and unused space, we'll 
be able to get:

Free Blocks......  Number of blocks on the freelist
Total Blocks.....  Total blocks allocated to the table
Total Bytes......  Total bytes allocated to the table
Unused Blocks....  Blocks that have never contained data
Unused Bytes.....  The above in bytes

It does not tell you how many blocks have free space for updates.  We can tell 
you how many blocks are candidates for INSERTS (they are on the freelist) and 
blocks on the freelist have space for updates -- but -- there are blocks in the 
table that have space for updates but that are not on the freelist.  We 
cannot see them in any report.

It does not tell you how much space is free in each block (nothing does, 
typically there are thousands or hundreds of thousands of blocks in a table -- 
an analysis of the free space block by block is not practical.  We can get an 
average free space but not block by block).

This report does show blocks above the high water mark.  Unused Blocks are 
exactly the block above the high water mark.  

You can get most of the information supplied by this package by analyzing the 
table and using queries against user_tables and user_segments.  The freelist 
analysis is more detailed using this package as you can look at each freelist 
independently.

Below is a procedure you can use to make using dbms_space a little easier.  
After that I create a table and show how space is being used in it after various 
operations.  Comments in bold explain the output.

ops$tkyte@8i> create or replace
  2  procedure show_space
  3  ( p_segname in varchar2,
  4    p_owner   in varchar2 default user,
  5    p_type    in varchar2 default 'TABLE' )
  6  as
  7      l_free_blks                 number;
  8  
  9      l_total_blocks              number;
 10      l_total_bytes               number;
 11      l_unused_blocks             number;
 12      l_unused_bytes              number;
 13      l_LastUsedExtFileId         number;
 14      l_LastUsedExtBlockId        number;
 15      l_LAST_USED_BLOCK           number;
 16      procedure p( p_label in varchar2, p_num in number )
 17      is
 18      begin
 19          dbms_output.put_line( rpad(p_label,40,'.') ||
 20                                p_num );
 21      end;
 22  begin
 23      dbms_space.free_blocks
 24      ( segment_owner     => p_owner,
 25        segment_name      => p_segname,
 26        segment_type      => p_type,
 27        freelist_group_id => 0,
 28        free_blks         => l_free_blks );
 29  
 30      dbms_space.unused_space
 31      ( segment_owner     => p_owner,
 32        segment_name      => p_segname,
 33        segment_type      => p_type,
 34        total_blocks      => l_total_blocks,
 35        total_bytes       => l_total_bytes,
 36        unused_blocks     => l_unused_blocks,
 37        unused_bytes      => l_unused_bytes,
 38        LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 39        LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 40        LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 41  
 42      p( 'Free Blocks', l_free_blks );
 43      p( 'Total Blocks', l_total_blocks );
 44      p( 'Total Bytes', l_total_bytes );
 45      p( 'Unused Blocks', l_unused_blocks );
 46      p( 'Unused Bytes', l_unused_bytes );
 47      p( 'Last Used Ext FileId', l_LastUsedExtFileId );
 48      p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
 49      p( 'Last Used Block', l_LAST_USED_BLOCK );
 50  end;
 51  /

Procedure created.

ops$tkyte@8i> 
ops$tkyte@8i> create table t ( x int, y char(2000) default '*' )
  2  storage ( initial 40k next 40k minextents 5 )
  3  tablespace system;

Table created.

I create a table with >1 extent to make it interesting.  I also put a 
char(2000) in there to make the minimum row length be 2000 bytes (chars always 
take their max space right away).  This just makes my rows "big"

ops$tkyte@8i> insert into t (x) values ( 1 );
1 row created.

I create one row just to use a little space in the table

ops$tkyte@8i> analyze table t compute statistics;
Table analyzed.

ops$tkyte@8i> compute sum of blocks on report
ops$tkyte@8i> break on report
ops$tkyte@8i> select extent_id, bytes, blocks
  2    from user_extents
  3   where segment_name = 'T'
  4     and segment_type = 'TABLE'
  5  /

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         2      40960          5
         3      81920         10
         4      57344          7
         0      40960          5
         1      40960          5
                      ----------
sum                           32

This shows that there are 32 blocks allocated in 5 extents to this table (as 
expected)

ops$tkyte@8i> clear breaks
ops$tkyte@8i> select blocks, empty_blocks,
  2         avg_space, num_freelist_blocks
  3    from user_tables
  4   where table_name = 'T'
  5  /

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
         1           30       6091                   1

Since I analyzed the table, I have acccess to the above information.  You'll 
find that it maps exactly to the data below.  There are a total of 32 blocks 
allocated to the table (below and as confirmed by user_extents above).  There 
are 30 EMPTY_BLOCKS (above)/ UNUSED_BLOCKS (below).  These are blocks above the 
HWM.   This leaves 2 blocks unaccounted for -- 1 block has data in it, the other 
has the extent map for the table (the first block of each table is used by the 
system itself).

ops$tkyte@8i> exec show_space( 'T' )
Free Blocks.............................1
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................30
Unused Bytes............................245760
Last Used Ext FileId....................1
Last Used Ext BlockId...................64816
Last Used Block.........................2

PL/SQL procedure successfully completed.

ops$tkyte@8i> insert into t (x)
  2  select rownum
  3    from all_users
  4   where rownum < 50
  5  /

49 rows created.

ops$tkyte@8i> commit;
Commit complete.

So now we have 50 rows with 2k each -- I'm using a 8k blocksize so I expect 
about 3 rows / block.  That means about 18 blocks of data plus 1 for the system 
= about 19 blocks should be "used" now.  Below I see that I have

o 3 blocks on the freelist.  they have more space for new inserts (they have not 
hit their pctused yet and may be inserted into)

o 12 unused blocks, leaving 20 blocks "used".  Since I have 3 on the freelist -- 
we probably used a little more then the 18 for data -- we used 19 for the 50 
rows.  We have one for the system -- all accounted for.


ops$tkyte@8i> exec show_space( 'T' )
Free Blocks.............................3
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................12
Unused Bytes............................98304
Last Used Ext FileId....................1
Last Used Ext BlockId...................64681
Last Used Block.........................5

PL/SQL procedure successfully completed.

ops$tkyte@8i> delete from t;
50 rows deleted.

ops$tkyte@8i> commit;
Commit complete.

Now we can see what a delete does to our utilization.

ops$tkyte@8i> exec show_space( 'T' )
Free Blocks.............................19
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................12
Unused Bytes............................98304
Last Used Ext FileId....................1
Last Used Ext BlockId...................64681
Last Used Block.........................5

PL/SQL procedure successfully completed.

The above shows that the delete simply put all of our blocks on the free list. 
 We have 19 blocks on the free list + 12 unused blocks + 1 system block = 32 
blocks.  All accounted for.  Note that the HWM stayed the same -- we don't have 
31 unused blocks -- we have 12 as before.  The HWM for a table will never 
decrease unless we.....


ops$tkyte@8i> truncate table t;
Table truncated.

ops$tkyte@8i> exec show_space( 'T' )
Free Blocks.............................0
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................31
Unused Bytes............................253952
Last Used Ext FileId....................1
Last Used Ext BlockId...................64816
Last Used Block.........................1

PL/SQL procedure successfully completed.

Truncate it.  That puts all of the blocks below the HWM.  Now we have 31 
unused blocks + 1 system block = 32 blocks total.  None on the free list since 
none of them have any data.

Reviews