Home » RDBMS Server » Server Administration » space and clob's
space and clob's [message #528938] Thu, 27 October 2011 12:44 Go to next message
BeefStu
Messages: 192
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 #528942 is a reply to message #528938] Thu, 27 October 2011 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel
Re: space and clob's [message #528950 is a reply to message #528938] Thu, 27 October 2011 13:16 Go to previous messageGo to next message
John Watson
Messages: 4683
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?
space and clob's [message #528952 is a reply to message #528950] Thu, 27 October 2011 13:23 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
This is what show paramters returned. I am just not getting
why I can't see the the number of block decreased.

Any other ideas would be greatly appreciated.

db_securefile                        string      PERMITTED


Re: space and clob's [message #528953 is a reply to message #528952] Thu, 27 October 2011 13:41 Go to previous messageGo to next message
John Watson
Messages: 4683
Registered: January 2010
Location: Global Village
Senior Member
Some reading for you:

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e18294/toc.htm
Re: space and clob's [message #528961 is a reply to message #528953] Thu, 27 October 2011 14:44 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
Thanks for the link, it contains a lot of good info but
unfortuneatly I could not find anything pertaining to
my test case so I will keep looking.

Does this happen to anybody else running on 11.2.0.2.0 AIX 6.1?

Re: space and clob's [message #528971 is a reply to message #528961] Thu, 27 October 2011 15:24 Go to previous messageGo to next message
John Watson
Messages: 4683
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 #528976 is a reply to message #528971] Thu, 27 October 2011 17:02 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
John,

Thanks for your expertise and advice. I would like to go
forward with new technology but this requires a lot of due
diligence on my part.

Right now I just need to generate a simple test case to
see if I can address the issue of freeing up space.

Having said that, would this be a suitable way of testing
my space issue?

1) create a tablespace with ASSM (T1)

2) Use this command to create a table in that tablespace

CREATE TABLE clob_test ( id (number), c1 CLOB)
LOB(c1) STORE AS SECUREFILE (
DEDUPLICATE
CACHE
) tablespace T1;


3) Repeat my test as I posted earlier?

If you beleive there is a better way to proceed would
you mind posting it?

Again, thanks for your help and expertise.
Re: space and clob's [message #529002 is a reply to message #528976] Fri, 28 October 2011 02:03 Go to previous messageGo to next message
John Watson
Messages: 4683
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

Re: space and clob's [message #529061 is a reply to message #529002] Fri, 28 October 2011 09:25 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
John,

Thanks!! You answered both my questions. Mystery now understood
space and clob's [message #530072 is a reply to message #529061] Fri, 04 November 2011 08:22 Go to previous message
BeefStu
Messages: 192
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.




Previous Topic: pgs allocated issue
Next Topic: ORA-08103
Goto Forum:
  


Current Time: Thu Oct 23 01:46:17 CDT 2014

Total time taken to generate the page: 0.12367 seconds