| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PCT_USED/PCT_FREE : Storage of 32mb images
I'm trying to understand this. How many records are in the table? Is it four? If so 115,437,005 / 4 gives an avg_row_len
of about 27.5 MB. AVG_ROW_LEN for a table does not include the lob segment; the number is terribly high. What is your
table definition including the definition of your lob segment?
Here's one of mine, slightly altered. The database version is 8.1.6.3
CREATE TABLE x
(FILE_ID NUMBER(7,0) NOT NULL,FILE_NAME VARCHAR2(80) NOT NULL
,FILE_TYPE VARCHAR2(1) NOT NULL
,FILE_FORMAT VARCHAR2(1) NOT NULL
It appears you are trying to apply table physical attribute parameters to the lob segment. Take a look at pctversion defined in the docs as ....
The maximum percentage of overall LOB storage space used for creating new versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until 10% of the overall LOB storage space is used.
You would set your pctversion to 0 if no updates will take place.
Ian
-----Original Message-----
Sent: Wednesday, March 06, 2002 9:54 AM
To: Multiple recipients of list ORACLE-L
I am in the process of storing 32Mb image files into a 8.1.7 database, I
have set
pct_free to 0, but I left pct_used at 90 and the tablespace is locally
managed with a
uniform extent of 32Mb. The database has a db_block_size of 8k. I am
considering moving it
to 16k, as opposed to 32k, as it has a mix of smaller files ( > 8k, < 7Mb)
and then the larger tables ( 32 Mb).
When I loaded 4 images (to create one image), and analyzed the tables, I received the following output.
Size (numrow*avg_row_len) = 115,437,005 Bytes (Segments) = 167,772,160 Blocks = 20,480 Extents = 5
As you can see, I am wasting 50Mbs of space for 4 images. I realize that if
I add another image to the larger
one, I most likely only waste 18 Mbs.
I have figured out that this wastage is due to the pct_used parameter.
After re-re-re-reading the documents,
I should have set the pct_used higher then 90.
Question : Should I set pct_used to 100 or 99 ?
Once these images are loaded,the tablespace will be set to READONLY. No updates will occur.
Thanks
Darren
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: dbrowett_at_city.coquitlam.bc.ca Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: ian_at_SLAC.Stanford.EDU Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Mar 06 2002 - 20:53:20 CST
 |
 |