| space and clob's [message #528938] |
Thu, 27 October 2011 12:44  |
 |
BeefStu
Messages: 208
Registered: October 2011
|
Senior Member |
|
|
CREATE TABLE C0HARPA.CLOB_TEST
(
ID NUMBER(20),
C1 CLOB
)
LOB (C1) STORE AS (
TABLESPACE TABLES
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
TABLESPACE PBH_TABLES
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 15
INITRANS 20
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
select segment_name, sum(bytes) BYTES, count(*) EXTENTS
from user_extents group by segment_name;
SYS_LOB0001699764C00002$$
10485760 1
SYS_IL0001699764C00002$$
10485760 1
CLOB_TEST
10485760 1
SQL> alter table clob_test modify lob (c1) (PCTVERSION 0);
Table altered.
SQL> select count(1) from clob_test;
COUNT(1)
----------
0
load data with sqlldr
Each clob should have more than 100 bytes and less than 5000 bytes
SQL*Loader: Release 11.2.0.2.0 - Production on Thu Oct 27 12:00:53 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Load completed - logical record count 60000.
SQL> select count(1) from clob_test;
COUNT(1)
----------
60000
select segment_name, sum(bytes) BYTES, count(*) EXTENTS
from user_extents group by segment_name;
SYS_LOB0001699764C00002$$
10485760 1
SYS_IL0001699764C00002$$
10485760 1
CLOB_TEST
199229440 19
Note the number of extents and bytes for the CLOB_TEST table
increased but not the size of the CLOB SYS_LOB0001699764C00002$$ did not,
How come?
As you can see there are block allocated for the CLOB.
set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('C0HARPA', 'SYS_LOB0001699755C00002$$',
'LOB',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
TOTAL_BLOCKS = 1280
UNUSED_BLOCKS = 1258
SQL> SELECT NVL((SUM(DBMS_LOB.GETLENGTH(C1))),0) AS BYTES FROM CLOB_TEST;
BYTES
----------
116807216
delete clob_test where id>100;
commit;
59900 rows deleted.
I would not expect to see a change in size here since the HWM has not been reset yet.
===============
set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('C0HARPA',
'SYS_LOB0001699755C00002$$',
'LOB',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
TOTAL_BLOCKS = 1280
UNUSED_BLOCKS = 1258
This tells me that there were bytes freed from the clob as
the previous value was 116807216
SQL> SELECT NVL((SUM(DBMS_LOB.GETLENGTH(C1))),0) AS BYTES FROM CLOB_TEST;
BYTES
----------
187621
Reset HWM
SQL> alter table clob_test enable row movement;
Table altered.
SQL> alter table clob_test shrink space cascade;
Table altered.
select segment_name, sum(bytes) BYTES, count(*) EXTENTS
from user_extents group by segment_name;SQL> 2
SYS_LOB0001699764C00002$$
10485760 1
SYS_IL0001699764C00002$$
10485760 1
CLOB_TEST
10485760 1
How come I don't see a reduction in total blocks and yet
I see the number of bytes decreased for the clob and the
number extents decreased for the table clob_test
set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('C0HARPA', 'SYS_LOB0001699755C00002$$',
'LOB',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
TOTAL_BLOCKS = 1280
UNUSED_BLOCKS = 1258
|
|
|
|
|
|
| Re: space and clob's [message #528950 is a reply to message #528938] |
Thu, 27 October 2011 13:16  |
John Watson
Messages: 8928
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
It looks as though you are using the old basicfile LOB structure. As you are using release 11.2, you should be using securefuile LOBs, which will probably solve your problem. What is your setting for the DB_SECUREFILE instance parameter?
|
|
|
|
|
|
|
|
|
|
| Re: space and clob's [message #528971 is a reply to message #528961] |
Thu, 27 October 2011 15:24 |
John Watson
Messages: 8928
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You have found a problem that occurs when you are using out-dated technology. Oracle Corp did not re-implement the entire LOB storage for fun, you know. Even if your problem is fixable on the old technology, there are many other things that aren't. Re-building your LOBs as secure files is a standard procedure when you upgrade to 11g. So why don't you do it?
|
|
|
|
|
|
| Re: space and clob's [message #529002 is a reply to message #528976] |
Fri, 28 October 2011 02:03 |
John Watson
Messages: 8928
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - I've just had a look at your code.
Are you wondering why every segment always has at least one extent of 10M? If that is the question, it looks to me as though your tablespace was created with UNIFORM SIZE 10M, which will take precedence over any storage clause specified per segments. Every extent will always be exactly that size, full or empty. Check thhis with
select ALLOCATION_TYPE, TABLESPACE_NAME,min_extlen from dba_tablespaces;
And what I tihnk is your other question, the LOBs are going into the table segment (not the LOB segment) because you have ENABLE STORAGE IN ROW, so only LOBs over (I think) 4K go into the LOB segment.
Hope this helps.
edit: corrected "Every segment..." to "Every extent...". Sorry about that.
[Updated on: Fri, 28 October 2011 02:33] Report message to a moderator |
|
|
|
|
|
| space and clob's [message #530072 is a reply to message #529061] |
Fri, 04 November 2011 08:22 |
|
BeefStu
Messages: 208
Registered: October 2011
|
Senior Member |
|
|
Apologies for bringing this subject backup but I stil have
a few questions.
I have a tablespace that is defined as uniform with initial
extent 100MB and next extent 100MB
CREATE TABLESPACE DE100M DATAFILE
'/db1/dbf/de100m_01.dbf' SIZE 29696M AUTOEXTEND OFF
...
...
...
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
SEGMENT SPACE MANAGEMENT AUTO;
I have a CLOB stored in this tablespace along with the
table definition
CREATE TABLE IFD_OWN.ETM_RAW_XML
(
ID NUMBER(20),
ETM_XML CLOB,
IFD_XML CLOB
)
LOB (ETM_XML) STORE AS (
TABLESPACE DE100M
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 100M
NEXT 100M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
LOB (IFD_XML) STORE AS (
TABLESPACE DE100M
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 100M
NEXT 100M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
TABLESPACE DE100M
PCTUSED 0
PCTFREE 15
INITRANS 20
MAXTRANS 255
STORAGE (
INITIAL 100M
NEXT 100M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;
Here are the sizes of the semgments and the query I used
to get them
set numformat 999999999999
select segment_name, sum(bytes) BYTES, count(*) EXTENTS
from dba_extents where
segment_name in
( 'SYS_LOB0007260522C00011$$', 'SYS_LOB0007260522C00012$$', 'ETM_RAW_XML')
group by segment_name;
ETM_RAW_XML 3355443200 32 ~3.125GB
SYS_LOB0007260522C00011$$ 125724262400 1199 ~117.08GB (ETM_XML)
SYS_LOB0007260522C00012$$ 88185241600 841 ~82.12GB (IFD_XML)
SQL> alter table ifd_own.etm_raw_xml enable row movement;
SQL> alter table ifd_own.etm_raw_xml shrink space cascade;
set numformat 999999999999
select segment_name, sum(bytes) BYTES, count(*) EXTENTS
from dba_extents where
segment_name in
( 'SYS_LOB0007260522C00011$$', 'SYS_LOB0007260522C00012$$', 'ETM_RAW_XML')
group by segment_name;
ETM_RAW_XML 3355443200 32 ~3.125GB
SYS_LOB0007260522C00011$$ 8598323200 82 ~8.00GB (ETM_XML)
SYS_LOB0007260522C00012$$ 88290099200 842 ~82.22GB(IFD_XML)
I am running Oracle 11.1.7.0 on Solaris 2.10.
My questions are:
1) I thought segment shrinking only worked with ASSM tablespaces
As you can see from my TS definition I am using uniform. So
how did the segment (ETM_XML) SYS_LOB0007260522C00011$$ shrink
from ~117.08GB to ~8.00GB. Is segment shrinking avaailable
in 11g now for uniform tablespaces
2) How come my second segment SYS_LOB0007260522C00012$$(IFD_XML)
did not shrink? Is it becuase 82GB is less than my iniital
extent size of 100MB therefore the segments can't be
released?
3) I just can't switch from uniform to ASSM as my monitoring
scripts will fail. What I can do is create another tablespace
wit smaller initial and next extents.. Based on my sizes
of the clobs before/after the shrink is there a way I can
find the optimal value for the initial and next extents if
I want to put these clobs into another tablespace.
Apologies for re-opening this case... I think I am missing
something.
Thanks to all who answer.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
