Steve- Sub-optimal RBS query

From: Mandar Ghosalkar <MandarG_at_gsr-inc.com>
Date: Mon, 23 Apr 2001 14:35:59 -0700
Message-ID: <F001.002EFB1D.20010423143029@fatcity.com>

Hello Guys,

i hv been trying to tune our RBS. i was using Steve's query http://www.ixora.com.au/scripts/sql/sub_optimal.sql to calculate the optimal size.

on the page http://www.ixora.com.au/scripts/rbs.htm it says "This script suggests raising the OPTIMAL setting if rollback segments of that size have been shrinking more than once per reuse cycle, and have shrunk at least twice. "
What does he mean by "reuse cycle"?

Also if i comment a line from the query it shows some output, otherwise no rows are returned.

where
  s.shrinks > 1 and
-- s.shrinks > s.wraps / ceil(s.optsize / ((s.rssize + p.value) /
s.extents)) and

  r.optsize = s.optsize and
  r.status = 'ONLINE' and
  n.usn = r.usn

can someone(Steve/?) xplain me what is the significance of the comment line

Thanks
Mandar

the query is

---

select
  n.name,
  s.optsize,
  ( ceil(s.extents * (s.optsize + s.aveshrink)/(s.rssize + p.value))

• (s.rssize + p.value) / s.extents ) - p.value new_opt from ( select optsize, avg(rssize) rssize, avg(extents) extents, max(wraps) wraps, max(shrinks) shrinks, avg(aveshrink) aveshrink from sys.v_$rollstat where optsize is not null and status = 'ONLINE' group by optsize ) s, ( select kvisval value from sys.x_$kvis where kvistag = 'kcbbkl' ) p, sys.v_$rollstat r, sys.v_$rollname n where s.shrinks > 1 and s.shrinks > s.wraps / ceil(s.optsize / ((s.rssize + p.value) / s.extents)) and r.optsize = s.optsize and r.status = 'ONLINE' and n.usn = r.usn

  ---

  
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Mandar Ghosalkar INET: MandarG_at_gsr-inc.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). Received on Mon Apr 23 2001 - 16:35:59 CDT