Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Calculating new PCTFREE and PCTUSED !!!!!1

RE: Calculating new PCTFREE and PCTUSED !!!!!1

From: Reddy, Madhusudana <Madhusudana.Reddy_at_bestbuy.com>
Date: Sat, 18 Jan 2003 15:28:42 -0800
Message-ID: <F001.00533D82.20030118152842@fatcity.com>


Charlie,

Would you please post your PL/SQL code here.....I might wanted to use/tweak it .

Also your formula for PCTFREE is pctfree = pctfree + 5 , if table exceeds 5% of chained rows and some DBAs advocate to average row length.

Jared, whats u r idea on this , what would you do to alter PCTFREE or PCTUSED .. Anybody ??

Thanks in advance
Madhu

-----Original Message-----

[mailto:Charlie_Mengler_at_HomeDepot.com]
Sent: Friday, January 17, 2003 3:56 PM
To: Multiple recipients of list ORACLE-L

What I've done, is I have some PL/SQL code which looks for chained rows. When the number of chained rows exceeds 5% it proceeds to unchain the rows. Upon completion it increases the PCTFREE by 5% & decrease PCTUSED by 5%. This process continues until they reach values which don't induce chaining. This process runs once a month after our month-end processing completes.  

                      DENNIS WILLIAMS

                      <DWILLIAMS_at_LIFETO        To:       Multiple recipients
of list ORACLE-L <ORACLE-L_at_fatcity.com>            
                      UCH.COM>                 cc:

                      Sent by:                 Subject:  RE: Calculating new
PCTFREE and PCTUSED !!!!!1                         
                      root_at_fatcity.com

 

 

                      01/17/2003 01:04

                      PM

                      Please respond to

                      ORACLE-L

 

 





Reddy - No I have not used that script. But most of Don's stuff is quite good. I thought it might illuminate some issues for you. Sorry if it didn't help.

   The PCTFREE and PCTUSED parameters mainly need tweaked when your data is volatile, when existing rows are updated with additional data. Is your data very volatile?

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----

Sent: Friday, January 17, 2003 12:50 PM
To: Multiple recipients of list ORACLE-L

Dennis,

Have you used the script ??

I have gone thru the material u have pointed , also executed the script in it, but it doesn't make any sense to me . Performance is the issue for me not the SPACE .

here is the script:

spool pctused.lst;

define spare_rows = 2;

define blksz = 4096; ( I used 8192 )

select

' alter table '||owner||'.'||table_name||
' pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
' '||
' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
';'

from
dba_tables
where
avg_row_len > 1
and
avg_row_len < 2000
and
table_name not in
(select table_name from dba_tab_columns b where
data_type in ('RAW','LONG RAW')
)
order by owner, table_name
;

spool off;

Sample o/p: alter table <schema>.TSFDETAIL pctused 95 pctfree 5;

And previous value for PCTFREE is 20 and the chained rows are 1054757 in that table..

Does anyone have good idea to calculate PCTFREE would like to share with me ???

I need help !!!

Thanks
Madhu

-----Original Message-----

Sent: Wednesday, January 15, 2003 3:26 PM To: Multiple recipients of list ORACLE-L

Madhu

   Here is a good article that discusses the various aspects: http://www.dba-oracle.com/art_pctfree.htm

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----

Sent: Wednesday, January 15, 2003 3:06 PM To: Multiple recipients of list ORACLE-L

Hello All,
I have found some of the tables are heavily chained in one of the database .
I want to fix them by exp and imp, but before that I would like to have a formula or some better method to identify the new PCTFREE and PCTUSED for each individual table.

Many of you have might have done this in the past , would you pl share your ideas on this ??

Thanks in advance,
Madhu

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reddy, Madhusudana
  INET: Madhusudana.Reddy_at_bestbuy.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services

---------------------------------------------------------------------
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.net -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net -- Author: Reddy, Madhusudana INET: Madhusudana.Reddy_at_bestbuy.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net -- Author: INET: Charlie_Mengler_at_HomeDepot.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net -- Author: Reddy, Madhusudana INET: Madhusudana.Reddy_at_bestbuy.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Sat Jan 18 2003 - 17:28:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US