Calculating effects of increasing PCTUSED
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 3 Dec 2008 09:19:07 -0600 (CST)
Hey all,
In 10.1.0.5.0 on AIX I have a ledger-type table in our ERP database that's
growing much faster than expected. It's been found that a business practice
is causing many rows with duplicate data to be written to it, but it would
be impractical to prevent the rows from being inserted (it's the F42199
table in JDEdwards, if that matters).
With an 8K blocksize, no nulls in the row, and only static-sized columns,
each row is 2507 bytes. Using the (ancient) calculation at:
http://download-east.oracle.com/docs/cd/A57673_01/DOC/server/doc/SAD73/apA.htm
...I've determined that lowering the PCTFREE to 5 allows me to pack 3
rows/block.
Since it would be impractical to prevent the extra rows from being inserted,
the best alternative we could come up with is to remove the rows shortly
after being inserted (JDE commits after every DML). We've ruled out a
database trigger for business reasons.
Since there is going to be a relatively large number of deletes, I'd like to
raise the PCTUSED in order to pack the blocks with these large rows. If I
understand PCTUSED and the block calcs, I'd raise it to 70 in order to reuse
the block when even one of the three potential rows is deleted.
What I'm not sure of is how to test the performance impact of this change.
I had it in my head that I'd need to monitor the freelist size so I can
extrapolate performance impact for the next "x" months/years, but I'm not
sure how to find that.
Thoughts on this mess?
TIA!
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
