what storage is used in a LOBSEGMENT and should it be shrunk / reorg-1453350.1

How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized? (文档 ID 1453350.1)

 

In this Document

Goal

Solution

References

---

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.8 and later
Information in this document applies to any platform.

Goal

Determine and demonstrate space usage within a LOBSEGMENT (CLOB / BLOB) and once determined decide if a shrink / reorganization is needed.
 

Solution

Older notes discussed the High Watermark within a LOBSEGMENT ... In reality ... this is only partially related to the issue of what is stored in the segment

There are currently (as of Oracle 11.2.0.3) two types of LOBSEGMENTS with regard to storage ... BASCIFILEs and SECUREFILEs

This note will address both types of storage

 

BASICFILES

Storage determination within a BASICFILE LOB cannot be determined with complete accuracy ... the following method may be used to determine the best information possible

DBMS_SPACE.SPACE_USAGE may not be used on basicfile lobs ... it is a new feature beginning in 11g and is only available for use on securefile lobs

1) Determine the segment name of the column to be examined

SELECT SEGMENT_NAME FROM DBA_LOBS WHERE WNER = '' AND TABLE_NAME = '

storage size of the LOBSEGMENT

SELECT SUM(BYTES) "STORAGE" FROM DBA_EXTENTS WHERE SEGMENT_NAME = '';

3) Determine the size of the LOB DATA

* BLOBs use the following query

     select sum(dbms_lob.getlength()) from ;

* CLOBs use the following note

     How to Return CLOB Size in Bytes like LENGTHB Function of CHAR/VARCHAR2 ( Document  790886.1)

4) Determine the size of the data that is not LOB DATA

     Subtract the result of #3 (the size of the LOB DATA) from #2 (the size of the LOB SEGMENT) ... this tells us the Undo Data size (expired + unexpired OR pctversion) + Unused space

5) Determine if the LOBSEGMENT is a candidate for shrink / reorganization

Information gathered

* The storage size of the LOBSEGMENT (how much space the LOB is occupying in the tablesapce) ... #1
* The size of the LOB DATA ... #2
* The size of the storage that is not LOB DATA #3

It is often assumed that if #3 is larger than #2 that space is being wasted ...
   This may or may not be true as it is not possible to tell the breakdown of space that is not LOB DATA

The following query will show the extents allocated for the LOBSEGMENT

SELECT BYTES, COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_NAME = ''  GROUP BY BYTES ORDER BY 2;

If the results of #3 are found to be one or more of the extent sizes from this query ...
    then this segment is a candiate for a shrink / reorganization as this process will likely be able to reduce the size of the LOBSEGMENT by at least one extent

WARNING : shrinking / reorganizing BASICFILE lobs can cause performance problems due to "enq: HW contention" waits

6) If #5 is true ... then one of the methods in the following note may be used to shrink / reorganize the lob segment

    How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? (Document 1451124.1)

For an example of this process .. see  Case Study #1

---

SECUREFILES

Storage determination became much easier with the introduction of SECUREFILES

1) Determine the segment name of the column to be examined

SELECT SEGMENT_NAME FROM DBA_LOBS WHERE WNER = '' AND TABLE_NAME = '

     See Case Study #2 for syntax

Sample output:

3) Determine if the LOBSEGMENT is a candidate for shrink / reorganization

The following query will show the extents allocated for the LOBSEGMENT

SELECT BYTES, COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_NAME = ''  GROUP BY BYTES ORDER BY 2;

If NON Data Bytes is one or more of the extent sizes from this query ...
     then this segment is a candiate for a shrink / reorganization as this process will likely be able to reduce the size of the LOBSEGMENT by at least one extent

4) If #3 is true ... then the method in the following note may be used to shrink / reorganize the lob segment

     How to Shrink a SECUREFILE LOB using Online Redefinition (DBMS_REDEFINITION)? (Document  1394613.1)

NOTE: The securefile lob space allocation algorithm is designed such that extra space is allocated to prevent enq: HW Contention waits ... as such ... it may be found that after shrinking / reorganizing a LOB ... the
           extra space shrunk may be quickly reallocated in order to prevent waits ... this is normal behavior.


For an example of this process .. see  Case Study #2

 

---

CASE STUDY #1: BASICFILE

create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;

-- CREATE THE TEST TABLES

CREATE TABLE test ( ID NUMBER, PHOTO BLOB ) ;
CREATE TABLE test_bfile ( B_FILE BFILE) ;

ALTER TABLE TEST MODIFY LOB (PHOTO) (RETENTION);

-- CREATE THE DIRECTORY IN WHICH THE BLOB (PHOTO) RESIDES

CREATE or REPLACE DIRECTORY test as '/home/oracle/kbcook';

-- INSERT THE BFILE LOCATOR FOR THE PHOTO

insert into test_bfile values ( bfilename('TEST','1.jpg'));

commit;

-- INSERT 100 COPIES OF THE PHOTO INTO THE TEST TABLE

declare
    tmp_blob blob default EMPTY_BLOB();
    tmp_bfile bfile:=null;
    dest_offset integer:=1;
    src_offset integer:=1;
begin
     select b_file into tmp_bfile from test_bfile;
     DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
     dbms_lob.createtemporary(tmp_blob, TRUE);
     DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
     for i in 1..100 loop
          insert into test values(i,tmp_blob);
          commit;
     end loop;
     DBMS_LOB.CLOSE(tmp_bfile);
end;
/

-- EXAMINE THE SIZE OF THE LOB DATA (BEFORE GENERATING UNDO)

SELECT SUM(DBMS_LOB.GETLENGTH(PHOTO)) "LOB DATA" FROM TEST;

  LOB DATA
----------
 102382400

-- GENERATE UNDO COPIES FOR 33 ROWS

DELETE FROM TEST WHERE (ID/3) = TRUNC(ID/3);

COMMIT;

-- #1 DETERMINE THE SEGMENT NAME OF THE COLUMN TO BE EXAMINED

SELECT SEGMENT_NAME FROM DBA_LOBS WHERE WNER = 'TEST' AND TABLE_NAME = 'TEST' AND COLUMN_NAME = 'PHOTO';

-- #2 DETERMINE THE STORAGE SIZE OF THE LOBSEGMENT

SELECT SUM(BYTES) "STORAGE" FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000067591C00002$$';

-- #3 DETERMINE THE SIZE OF THE LOB DATA

SELECT SUM(DBMS_LOB.GETLENGTH(PHOTO)) "LOB DATA" FROM TEST;

-- #4 DETERMINE THE SIZE OF THE DATA THAT IS NOT LOB DATA

SELECT 109051904-68596208 "non LOB DATA SIZE" FROM DUAL;

SELECT BYTES, COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000067591C00002$$'  GROUP BY BYTES ORDER BY 2;

-- 5) DETERMINE IF THE LOBSEGMENT IS A CANDIDATE FOR SHRINK / REORGANIZATIONn

-- There are 40,455,696 bytes of storage that are being used by LOB data ... there are many extents small than this size ... this LOBSEGMENT is a candidate for shrink / reorganization

-- 6) If #5 is true ... then one of the methods in the following note may be used to shrink / reorganize the lob segment
--     How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? (Document 1451124.1)

ALTER TABLE TEST ENABLE ROW MOVEMENT;

ALTER TABLE TEST SHRINK SPACE CASCADE;

SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000067591C00002$$';

CASE STUDY #2: SECUREFILES

create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;

-- CREATE THE TEST TABLES

CREATE TABLE test ( ID NUMBER, PHOTO BLOB) LOB (PHOTO) STORE AS SECUREFILE ;
CREATE TABLE test_bfile ( B_FILE BFILE) ;

ALTER TABLE TEST MODIFY LOB (PHOTO) (RETENTION);

-- CREATE THE DIRECTORY IN WHICH THE BLOB (PHOTO) RESIDES

CREATE or REPLACE DIRECTORY test as '/home/oracle/kbcook';

-- INSERT THE BFILE LOCATOR FOR THE PHOTO

insert into test_bfile values ( bfilename('TEST','1.jpg'));

commit;

-- INSERT 100 COPIES OF THE PHOTO INTO THE TEST TABLE

declare
    tmp_blob blob default EMPTY_BLOB();
    tmp_bfile bfile:=null;
    dest_offset integer:=1;
    src_offset integer:=1;
begin
     select b_file into tmp_bfile from test_bfile;
     DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
     dbms_lob.createtemporary(tmp_blob, TRUE);
     DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
     for i in 1..100 loop
          insert into test values(i,tmp_blob);
          commit;
     end loop;
     DBMS_LOB.CLOSE(tmp_bfile);
end;
/

-- CREATE A PROCEDURE TO EXAMINE THE SPACE USAGE OF THE SECUREFILE LOB SEGMENT

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE check_space_securefile (u_name in varchar2, v_segname varchar2 ) IS
     l_segment_size_blocks NUMBER;
     l_segment_size_bytes NUMBER;
     l_used_blocks NUMBER;
     l_used_bytes NUMBER;
     l_expired_blocks NUMBER;
     l_expired_bytes NUMBER;
     l_unexpired_blocks NUMBER;
     l_unexpired_bytes NUMBER;
     l_unused_blocks NUMBER;
     l_unused_bytes NUMBER;
     l_non_data_blocks NUMBER;
     l_non_data_bytes NUMBER;

     BEGIN
          DBMS_SPACE.SPACE_USAGE( segment_owner =>u_name,
                                  segment_name => v_segname,
                                  segment_type => 'LOB',
                                  segment_size_blocks => l_segment_size_blocks,
                                  segment_size_bytes => l_segment_size_bytes,
                                  used_blocks => l_used_blocks,
                                  used_bytes => l_used_bytes,
                                  expired_blocks => l_expired_blocks,
                                  expired_bytes => l_expired_bytes,
                                  unexpired_blocks => l_unexpired_blocks,
                                  unexpired_bytes => l_unexpired_bytes
           );

          l_unused_blocks := l_segment_size_blocks - (l_used_blocks + l_expired_blocks + l_unexpired_blocks);
          l_unused_bytes := l_segment_size_bytes - (l_used_bytes + l_expired_bytes + l_unexpired_bytes);

          l_non_data_blocks := l_unused_blocks + l_expired_blocks + l_unexpired_blocks;
          l_non_data_bytes :=  l_unused_bytes + l_expired_bytes + l_unexpired_bytes;

          DBMS_OUTPUT.ENABLE;
          DBMS_OUTPUT.PUT_LINE(' Segment Blocks/Bytes   = '||l_segment_size_blocks||' / '||l_segment_size_bytes);
          DBMS_OUTPUT.PUT_LINE(' Unused Blocks/Bytes    = '||l_unused_blocks||' / '||l_unused_bytes);
          DBMS_OUTPUT.PUT_LINE(' Used Blocks/Bytes      = '||l_used_blocks||' / '||l_used_bytes);
          DBMS_OUTPUT.PUT_LINE(' Expired Blocks/Bytes   = '||l_expired_blocks||' / '||l_expired_bytes);
          DBMS_OUTPUT.PUT_LINE(' Unexpired Blocks/Bytes = '||l_unexpired_blocks||' / '||l_unexpired_bytes);
          DBMS_OUTPUT.PUT_LINE('===========================================================================');
          DBMS_OUTPUT.PUT_LINE(' NON Data Blocks/Bytes  = '||l_non_data_blocks||' / '||l_non_data_bytes);
     END;
/

-- #1 DETERMINE THE SEGMENT NAME OF THE COLUMN TO BE EXAMINED

SELECT SEGMENT_NAME FROM DBA_LOBS WHERE WNER = 'TEST' AND TABLE_NAME = 'TEST' AND COLUMN_NAME = 'PHOTO';

-- EXAMINE THE STORAGE USAGE IN THE LOBSEGMENT USING DBMS_SPACE_USAGE (BEFORE GENERATING UNDO)

exec check_space_securefile('TEST','SYS_LOB0000067626C00002$$');

-- GENERATE UNDO COPIES FOR 33 ROWS

DELETE FROM TEST WHERE (ID/3) = TRUNC(ID/3);

COMMIT;

-- #2 DETERMINE THE STORAGE USAGE IN THE LOBSEGMENT USING DBMS_SPACE_USAGE

exec check_space_securefile('TEST','SYS_LOB0000067626C00002$$');

-- #3 DETERMINE IF THE LOBSEGMENT IS A CANDIDATE FOR SHRINK / REORGANIZATION

SELECT BYTES, COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000067626C00002$$'  GROUP BY BYTES ORDER BY 2;

-- There are 52,625,408 bytes bytes of storage that are being used by LOB data ... there are many extents smaller than this size ... this LOBSEGMENT is a candidate for shrink / reorganization

-- 4) If #3 is true ... then the method in the following note may be used to shrink / reorganize the lob segment

--      How to Shrink a SECUREFILE LOB using Online Redefinition (DBMS_REDEFINITION)? (Document  1394613.1)

-- DETERMINE IF THE TABLE CAN BE REDEFINED ONLINE

BEGIN
    DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','TEST', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

PL/SQL procedure successfully completed.

-- CREATE THE INTERIM TABLE

CREATE TABLE interim ( ID NUMBER, PHOTO BLOB) LOB (PHOTO) STORE AS SECUREFILE ;
ALTER TABLE interim MODIFY LOB (PHOTO) (RETENTION);

-- START THE REDEFINITION

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
                uname => 'TEST',
                orig_table => 'TEST',
                int_table => 'INTERIM',
                options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- FINISH THE REDEFINITION

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST','TEST','INTERIM');

-- DROP THE INTERIM TABLE

DROP TABLE INTERIM;

--  THE REDEFINITION HAS CREATED A NEW TABLE AS SUCH THE SEGMENT NAME OF THE COLUMN OF THE NEW TABLE TO BE EXAMINED NEEDS TO BE DETERMINED

SELECT SEGMENT_NAME FROM DBA_LOBS WHERE WNER = 'TEST' AND TABLE_NAME = 'TEST' AND COLUMN_NAME = 'PHOTO';

SEGMENT_NAME
------------------------------
SYS_LOB0000067633C00002$$

exec check_space_securefile('TEST','SYS_LOB0000067633C00002$$');

References

NOTE:1451124.1 - How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)?
NOTE:820043.1 - Why is no space released after an ALTER TABLE ... SHRINK?
NOTE:1394613.1 - How to Shrink a SECUREFILE LOB Using Online Redefinition (DBMS_REDEFINITION)?

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17252115/viewspace-773465/，如需转载，请注明出处，否则将追究法律责任。

<%=items[i].content%>

<%if(items[i].items.items.length) { %> <%}%> <%}%>