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

I should have explained it the first time, because now I don't recall exactly what I had in mind.

Probably something along the line of 'if waits on "table fetch continued row"
were very low, I probably wouldn't bother with unchaining'. But that's just
me.

I'm by nature lazy and avoid 'unnecessary' work. :)

Jared

"Reddy, Madhusudana" <Madhusudana.Reddy_at_bestbuy.com> Sent by: root_at_fatcity.com
 01/21/2003 08:24 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: Calculating new PCTFREE and PCTUSED !!!!!1

Thank You all for your replies !!!

Jared, if you dont mind would you please explain " HOW ITS BEING " ACCESSED"
" .....

Thanks again
Madhu

-----Original Message-----
Sent: Monday, January 20, 2003 8:54 PM
To: Multiple recipients of list ORACLE-L

I might not change either. Chaining can happen regardless of PCTFREE and PCTUSED.

The answer is 'it depends'. The dependency being the data and how it's being accessed.

Jared

On Saturday 18 January 2003 15:28, Reddy, Madhusudana wrote:
> 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:
>
> - pctused.sql
> - © 1999 by Donald Keith Burleson
> set heading off;
> set pages 9999;
> set feedback off;
>
> 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: Jared Still
  INET: jkstill_at_cybcon.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: 
  INET: Jared.Still_at_radisys.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).