Oracle-L: RE: PCT_USED/PCT_FREE : Storage of 32mb images

From: Browett, Darren <dbrowett_at_city.coquitlam.bc.ca>
Date: Wed, 06 Mar 2002 15:38:46 -0800
Message-ID: <F001.00421729.20020306153846@fatcity.com>

The reason I created an extent size of 32 Mb is because each image is approx 32Mb, and
I wanted to keep each image within one extent, or at least minmize the number of extents.

The process actually combines the images and stores them in one table, each row in the table
represents one image. I am just at the testing stage right now, when I do my final "load"
I will be loading up to 50 images into one table, so I am trying to figure out the best way
to do this.

-----Original Message-----
Sent: March 6, 2002 2:48 PM
To: Multiple recipients of list ORACLE-L

Darren,

Are these 4 images in separate tables?

Why an extent size of 32 meg?

Jared

"Browett, Darren" <dbrowett_at_city.coquitlam.bc.ca> Sent by: root_at_fatcity.com
03/06/02 09:53 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        PCT_USED/PCT_FREE : Storage of 32mb images

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

Darren Browett P.Eng                             This message
was transmitted
Data Administrator                               using 100%

recycled electrons
Information and Communication Technology City of Coquitlam
P:(604)927 - 3614
E:dbrowett_at_city.coquitlam.bc.ca

--

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:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------