Oracle-L: Correct way to determine freelists and freelists groups

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 07 Jun 2002 07:04:05 -0800
Message-ID: <F001.0047779D.20020607070405@fatcity.com>

Hi all,

We have a bunch of TEMP tables in our 8161 databases. They are actually permenant tables but have temporary data in them. Some of these are small (less than a meg) to few large ones (100M or more). I truncate these tables once a week.

Until now these used to reside with other regular tables in a common tablespace and cause fragmentation. I have now created a new tablespace to store these temp tables and associated indexes. This new tablespace is created with 1M extents (basically simulation LMT with fixed extent size), space is not a concern.

After reading metalink note 107090.1, which recommends as follows ...

" Tips for PCTUSED and PCTFREE
* If the application frequently performs UPDATES that alter
  sizes of rows greatly, then PCTFREE can be set high and   PCTUSED can be set low. This would allow for large amount   of space in data blocks for row size growth.
* If there is more INSERT activity with less UPDATES, the
  PCTFREE can be set low with average value for PCTUSED to   avoid chaining of rows.
* If the main concern is performance and more space is available,
  then PCTFREE can be set very high and PCTUSED very low.
* If the main concern in space and not performance,
  then PCTFREE can set very low and PCTUSED very high."

I am more concerned about performance than space usage, so point 4 stands out.

Most of these tables will be used by multiple sessions to insert/update/delete data multiple times a day (and night too). Now that I have a chance to reorg these tables, I want to come up with appropriate values for

INITTRANS (current value is 1)
FREELISTS (current value is 1 and 5 on some)
FREELIST GROUPS (current value is 1 and 6 on some)

Any ideas on how to do this? Are there any scripts that I can run or monitor something that would help me?

My DB version is 8161.

Thanks in advance
Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

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).



text/plain attachment: ESPN_Disclaimer.txt

Received on Fri Jun 07 2002 - 10:04:05 CDT