How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized? (Doc ID 1453350.1)
In this Document
| Goal |
| Solution |
| BASICFILES |
| SECUREFILES |
| CASE STUDY #1: BASICFILE |
| CASE STUDY #2: SECUREFILES |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.8 and laterInformation 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
1) Determine the segment name of the column to be examined
SELECT SEGMENT_NAME FROM DBA_LOBS WHERE OWNER = '<owner>' AND TABLE_NAME = '<table name>' AND COLUMN_NAME = '<column name>';
2) Determine the storage size of the LOBSEGMENT
SELECT SUM(BYTES) "STORAGE" FROM DBA_EXTENTS WHERE SEGMENT_NAME = '<segment name from #1>';
3) Determine the size of the LOB DATA
* BLOBs use the following query
select sum(dbms_lob.getlength(<lob column name>)) from <table_name>;
* 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) ... #2
* The size of the LOB DATA ... #2
* The size of the storage that is not LOB DATA #3
It is often assumed that if #2 is larger than #3 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 = '<segment name from #1>' GROUP BY BYTES ORDER BY 2;
If the results of #4 are found to be <greater than> the biggest extents ever allocated for the shrink / re org to reclaim space ...
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 OWNER = '<owner>' AND TABLE_NAME = '<table name>' AND COLUMN_NAME = '<column name>';
2) Determine the storage usage in the LOBSEGMENT using DBMS_SPACE.SPACE_USAGE
See Case Study #2 for syntax
Sample output:
Unused Blocks/Bytes = 114 / 933888
Used Blocks/Bytes = 12800 / 104857600
Expired Blocks/Bytes = 1446 / 11845632
Unexpired Blocks/Bytes = 0 / 0
===========================================================================
NON Data Blocks/Bytes = 1560 / 12779520
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 = '<segment name from #1>' GROUP BY BYTES ORDER BY 2;
If NON Data Bytes is <greater than> 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/dir1';
-- 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 OWNER = 'TEST' AND TABLE_NAME = 'TEST' AND COLUMN_NAME = 'PHOTO';
------------------------------
SYS_LOB0000067591C00002$$
-- #2 DETERMINE THE STORAGE SIZE OF THE LOBSEGMENT
SELECT SUM(BYTES) "STORAGE" FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000067591C00002$$';
----------
109051904
-- #3 DETERMINE THE SIZE OF THE LOB DATA
SELECT SUM(DBMS_LOB.GETLENGTH(PHOTO)) "LOB DATA" FROM TEST;
----------
68596208
-- #4 DETERMINE THE SIZE OF THE DATA THAT IS NOT LOB DATA
SELECT 109051904-68596208 "non LOB DATA SIZE" FROM DUAL;
-----------------
40455696
SELECT BYTES, COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000067591C00002$$' GROUP BY BYTES ORDER BY 2;
---------- ----------
8388608 5
65536 16
1048576 63
-- 5) DETERMINE IF THE LOBSEGMENT IS A CANDIDATE FOR SHRINK / REORGANIZATIONn
-- There are 40,455,696 bytes of storage that are <not> 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$$';
----------
75694080
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/dir1';
-- 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;
/
NOTE: If the target object is a partitioned lob column then SEGMENT_TYPE should be LOB PARTITION.
-- #1 DETERMINE THE SEGMENT NAME OF THE COLUMN TO BE EXAMINED
SELECT SEGMENT_NAME FROM DBA_LOBS WHERE OWNER = 'TEST' AND TABLE_NAME = 'TEST' AND COLUMN_NAME = 'PHOTO';
------------------------------
SYS_LOB0000067626C00002$$
-- EXAMINE THE STORAGE USAGE IN THE LOBSEGMENT USING DBMS_SPACE_USAGE (BEFORE GENERATING UNDO)
exec check_space_securefile('TEST','SYS_LOB0000067626C00002$$');
Unused Blocks/Bytes = 119 / 974848
Used Blocks/Bytes = 12800 / 104857600
Expired Blocks/Bytes = 2081 / 17047552
Unexpired Blocks/Bytes = 0 / 0
===========================================================================
NON Data Blocks/Bytes = 2200 / 18022400
-- 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$$');
Unused Blocks/Bytes = 119 / 974848
Used Blocks/Bytes = 8576 / 70254592
Expired Blocks/Bytes = 2081 / 17047552
Unexpired Blocks/Bytes = 4224 / 34603008
===========================================================================
NON Data Blocks/Bytes = 6424 / 52625408
-- #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;
---------- ----------
131072 1
65536 1
8388608 6
1048576 69
-- There are 52,625,408 bytes bytes of storage that are <not> 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 OWNER = 'TEST' AND TABLE_NAME = 'TEST' AND COLUMN_NAME = 'PHOTO';
SEGMENT_NAME
------------------------------
SYS_LOB0000067633C00002$$
exec check_space_securefile('TEST','SYS_LOB0000067633C00002$$');
Unused Blocks/Bytes = 105 / 860160
Used Blocks/Bytes = 8576 / 70254592
Expired Blocks/Bytes = 1447 / 11853824
Unexpired Blocks/Bytes = 0 / 0
===========================================================================
NON Data Blocks/Bytes = 1552 / 12713984
Comments
Post a Comment