| 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 should have noticed you were using long raws. I would definitely move to BLOBS if your system supports it. The lob segment should be in a different tablespace than the data segment. This also allows you to name the lob segment which is quite useful. I would suggest a 4MB uniform extent for the lob segment tablespace. Remember Oracle does not read data according to the extent size. You may want to use a 32k chunk size. More data is written/read that way, but it there is more wasted space. But I cannot say for sure.
Do talk to the Vendor about using BLOB's. They may be able to suggest the best lob storarge parameters for your situation.
What was the problem when you tried to use a BLOB? Were you receiving Oracle errors?
I am very new at using BLOBS as well.
Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu
-----Original Message-----
Sent: Thursday, March 07, 2002 10:39 AM
To: Multiple recipients of list ORACLE-L
Sorry about being vague about what I am trying to do, I will try to explain it better.
We are implementing a GIS system called Arc/Info, thats allows us to
store,manipulate spatially
referenced data, one of the "layers" of information is orthophoto's, these
are rectified aerial
photographs that are scalable. Each ortho covers an area of 1600m by 1000m
on the ground with a
pixel resolution of .25m, each source tiff image is approx 25Mb in size.
During my initial testing of loading one image at a time, it appeared to
created table size
of approx 32Mb, hence my decision to use a uniform extent of 32Mb. Arc/Info
creates one table per image,
and the "raster" data is stored in the "long raw" column. It appears that
the table can have
anywhere from 8000 to 9000 rows, with an avg_row_len of 3467 Bytes, I am
assuming as with
lobs,
Arc/Info has the ability to merge all your images together into one "layer"
giving you a seamless
layer, it also creates a series of "pyramids" which are resampled versions
of the base layer at
a larger pixel resolution. This helps speed up the drawing on the screen
when you are at a
smaller scale (1:1,000,000). If you have 4 source images, they are merged
into a single table, with
multiple rows, the avg_row_len becomes 13261.
The table that the information is stored in has the following structure :
RASTERBAND_ID NOT NULL NUMBER(38) RRD_FACTOR NOT NULL NUMBER(38) ROW_NBR NOT NULL NUMBER(38) COL_NBR NOT NULL NUMBER(38) BLOCK_DATA NOT NULL LONG RAW
As you can see, the block_data field is currently defined as a "long raw".
It does have the option
of using a LOB, which prompts the question what is better long raw or LOB ?
will have to some
RTFM on that. Unfortunately I cannot get the LOB option working, time to
phone support on that one.
Where I am stuck, is figuring out is how to best configure my tablespace, I
realize there is no
"right" way to do it. I think 32Mb is incorrect, as this represents an
image with the pyramids
built, I will have to test loading an image that has no pyramids.
Should I set an extent that represents an average of the orthophotos, say
25Mb, or
some lesser value, and have more extents. From your example, I am assuming
I should use a smaller extent
size, maybe 5Mb, use the LOB option (Once I can get it working!) , and have
multiple tablespaces.
Darren
-----Original Message-----
Sent: March 6, 2002 6:53 PM
To: Multiple recipients of list ORACLE-L
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). -- 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 Thu Mar 14 2002 - 00:58:31 CSTContent-Type: text/plain; name="ReadMe.txt"; charset="us-ascii" Content-Transfer-Encoding: 7bit The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact Postmaster_at_fatcity.com for clarification. ------_=_NextPart_001_01C1C75E.6FD27000-- -- 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).
- application/ms-tnef attachment: winmail.dat
 |
 |