Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> TABLESPACE Not re utilize empty BLOB Space, TABLESPACE SIZE KEEP GROWING
Sachin Butala
post Apr 7 2017, 08:46 AM
Post #1


Newbie
*

Group: Members
Posts: 8
Joined: 22-June 10
Member No.: 43,325



Hello,

We configured Table with BLOB Column with seperate TableSpace.

CREATE TABLE EV_LOG
( "ID" FLOAT(63),
"MESSAGE_ID" VARCHAR2(128 BYTE),
"EVENT_TYPE" VARCHAR2(128 BYTE) CONSTRAINT "SYS_C0014415" NOT NULL ENABLE,
"CLASSIFIER" VARCHAR2(128 BYTE),
"EVENT_TIME" TIMESTAMP (6) CONSTRAINT "SYS_C0014416" NOT NULL ENABLE,
"EVENT_DATA" BLOB
)
LOB ("EVENT_DATA") STORE AS "EV_LOG_LOB"( TABLESPACE dwh_data04 DISABLE STORAGE IN ROW )
PARTITION BY RANGE ("EVENT_TIME")
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN (dwh_data03, dwh_data02)
(
PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE('01-01-2012', 'DD-MM-YYYY')),
PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-02-2012', 'DD-MM-YYYY')),
PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('01-03-2012', 'DD-MM-YYYY'))
);


We configured Scheduled Job to update BLOB Column to EMPTY, This Job runs every night and update 30 days old rows.

SET event_data = empty_blob()

Despite this we can see spike in tablespace usage, We have not 3month data and Every day it require 2-3 GB More space for inserting 0.4 to 0.42 millions of rows every day.

RUN_TIME NAME ALLOC_SIZE_GB CURR_USED_SIZE_GB PREV_USED_SIZE_GB VARIANCE
---------- --------------- ------------- ----------------- ----------------- --------------------
2017-03-29 dwh_data04 480 224.07 220.88 3.19
2017-03-30 dwh_data04 480 227.51 224.07 3.44
2017-03-31 dwh_data04 480 230.05 227.51 2.54
2017-04-01 dwh_data04 480 233.26 230.05 3.21
2017-04-02 dwh_data04 480 236.07 233.26 2.81
2017-04-03 dwh_data04 480 239.5 236.07 3.43
2017-04-04 dwh_data04 480 242.75 239.5 3.25
2017-04-05 dwh_data04 480 244.88 242.75 2.13


What could be the possible reason for space consumption, As per my understanding ORACLE should re-utilize empty space.

Regadrs,
Sachin
Go to the top of the page
 
+Quote Post
burleson
post Apr 7 2017, 10:16 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,368
Joined: 26-January 04
Member No.: 13



Hi Sachin,

>> We configured Scheduled Job to update BLOB Column to EMPTY

But the column still exists in the table row!

Try:

alter table
ev_log
modify lob
event_data
(shrink space);

http://www.dba-oracle.com/t_update_blob_reclaim_space.htm

**************************************************************

>> As per my understanding ORACLE should re-utilize empty space.

Yes, but it depends on your table and tablespace settings (e.g. ASSM).

Are you defining multiple freelists, by chance?


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Sachin Butala
post Apr 8 2017, 06:54 AM
Post #3


Newbie
*

Group: Members
Posts: 8
Joined: 22-June 10
Member No.: 43,325



Thanks Burleson for the reply.. I will try with your suggestion.

When I check Description of the table, LOG segment space is managed by RETENTION clause .

CODE
LOB ("EVENT_DATA") STORE AS BASICFILE "EV_LOG_LOB"(
TABLESPACE "dwh_data04" DISABLE STORAGE IN ROW CHUNK 16384 RETENTION
NOCACHE LOGGING


We are using Oracle 12c.

Regards,
Sachin
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th April 2017 - 02:11 PM