RE: sudden datafile growth with autoextend of 10Mbs

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 11 Dec 2013 14:12:20 -0500
Message-ID: <020e01cef6a4$eb049aa0$c10dcfe0$_at_rsiz.com>

IF you had multiple threads inserting rows at the time of extension I seem to recall a situation where more than one of the threads might request an extend before the first completes. I didn't do the look-aside to see whether the referenced doc is that particular issue. IF I recall correctly hanging one insert session to wait for a storage allocation request by another session was not a favored solution, and at the time I implemented a work-around since the client couldn't wait for a fix in any case.  

As a workaround, to avoid dynamic extends at peak load you *may* want to (perhaps daily) check available storage against a tracked peak daily extend and artificially drive an extend sufficient to hold your biggest (relevant period, perhaps daily) size increase requirement during an ebb load period.  

This is relevant to your problem IF it is driven by multiple inserts in parallel in that the pre-allocation during ebb load should prevent dynamic allocation the rest of the time.  

Oracle *being able* to dynamically extend is an important feature to prevent several failure modes. Actually dynamically extending at peak load is not a performance bonus. How much energy to invest in preventing dynamic extends is a question of balancing needs and cost. (See Compulsive Tuning Disorder (CTD), ref: Gaja)  

Combining this with something like my old extmon script set can quite possibly be used to avoid peak load allocation AND project future resource requirements of historical growth patterns.  

Your mileage may vary. If your growth is consistent, it might be more convenient to do a larger artificial extend less often (say weekly) to balance *used* storage out versus need and to reduce the number of time points you need to collect to make a future storage requirements plan.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Uzzell, Stephan
Sent: Wednesday, December 11, 2013 12:25 PM To: 'jpatterson_at_entint.com'; oracle-l_at_freelists.org Cc: Uzzell, Stephan
Subject: RE: sudden datafile growth with autoextend of 10Mbs  

You don't give quite enough information to rule this in or out, but have you looked at AUTOEXTEND Grows To Full Size Without Reason (Doc ID 1459097.1)?    

Stephan Uzzell  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Patterson, Joel
Sent: Wednesday, 11 December, 2013 09:50 To: oracle-l_at_freelists.org
Subject: sudden datafile growth with autoextend of 10Mbs  

My sysadmin verifies that all of a sudden 13Gbs of free space was lost overnight. (and we have narrowed down the file).  

The database is 11.2.0.3.3 and the datafiles autoextend at 10Mbs ( dba_data_files - 1280 increment by _at_ 8192 blocksize). The tablespace holds almost exclusively one CLOB column of one table.  

Sure enough, I can check the high water mark and subtract from the smallest hwm and see there is empty space, and I can shrink the datafile back down accordingly. This happens periodically. The growth is not an issue, but I am trying to understand why the growth is 13Gbs over night? Related to the CLOB field? Why not 10Mbs only? A majority of my DB's are set this way, and I do not see that type of sudden extending.  

Thanks,    

Joel Patterson

Senior Oracle Database Administrator

jpatterson_at_entint.com

Office: 904 928-2790

Enterprise Integration Corporation / www.entint.com <http://www.entint.com/>

7601 Centurion Parkway | Jacksonville, FL 32256    

-- 

Joel Patterson

Sr. Database Administrator | Enterprise Integration

Phone: 904-928-2790 | Fax: 904-733-4916 

 <http://www.entint.com/> www.entint.com

 

 <http://www.entint.com/> 

 

 <http://www.facebook.com/pages/Enterprise-Integration/212351215444231>
http://www.facebook.com/pages/Enterprise-Integration/212351215444231


<http://twitter.com/#!/entint> http://twitter.com/#!/entint

<http://www.linkedin.com/company/18276?trk=tyah>

http://www.linkedin.com/company/18276?trk=tyah


<http://www.youtube.com/user/ValueofIT>

http://www.youtube.com/user/ValueofIT

 

This message (and any associated files) is intended only for the use 
of the addressee and may contain information that is confidential, 
subject to copyright or constitutes a trade secret. If you are not the 
intended recipient, you are hereby notified that any dissemination, 
copying or distribution of this message, or files associated with this 
message, is strictly prohibited. If you have received this message in 
error, please notify us immediately by replying to the message and 
deleting it from your computer. Messages sent to and from us may be 
monitored. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of the company. [v.1.1] 



--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 11 2013 - 20:12:20 CET