RE: measuring the size of updates

You could invest in a log-reading utility and then you could profile off of the exact update statements that went against the tables (requires money, but then again, your time spent working on this is money as well...it's all a matter of justification). I usually don't get that precise with my calculations for PCTFREE unless there is a problem that wasn't accounted for at design time (like the DB_BLOCK_SIZE being to small for the database needs or application driven denormalizations that require huge amounts of updates to "fill in" data from the application to the database). These are just some things you might want to look into...

Jeffery Stevenson
Chief Database Geek
Medical Present Value, Inc.
Austin, TX

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Boivin, Patrice J
Sent: Monday, July 10, 2000 8:34 AM
To: Multiple recipients of list ORACLE-L Subject: measuring the size of updates

In my Oracle7.3. Performance Tuning course, they mentioned that to reduce migration we should set PCTFREE properly.

They also provided the following formula:

PCTFREE = 100 * [ avg. size of updates / (avg. size of updates + initial row length) ]

It occurred to me that since Oracle didn't explain how we should go about doing this, maybe I should ask. Our instructor said I should use Pre Update and Post Update triggers to keep track of this, but he didn't go into detail.

Has anyone done this successfully?

What was the impact on performance?

I want to incorporate this calculation into my weekly PT script, if possible.

TIA
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Systems Admin & Operations | Admin. et Exploit. des systèmes
Technology Services        | Services technologiques
Informatics Branch         | Direction de l'informatique
Maritimes Region, DFO      | Région des Maritimes, MPO

E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>

--
Author: Boivin, Patrice J
  INET: BoivinP_at_mar.dfo-mpo.gc.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).