Re: WHICH TABLE NEED TO INCREASE FREELISTS

One suggestion to "suggest" which table(s) might need increased FREELISTS is to run the following query against V$SQLAREA:

    select substr(upper(sql_text),1,30), sum(executions)     from v$sqlarea
    where command_type = 2 /* insert */
    group by substr(upper(sql_text),1,30)     order by 2 desc;

The basic idea is that it is only INSERTs that are affected by freelists. The query will display the most frequently executed INSERT statements. Of course, there are many ways this query can provide the wrong picture (i.e. lack of bind-variable use causing unshareable SQL) but that is mitigated somewhat by the SUBSTR() of SQL_TEXT.

Anyway, as Anjo says, I wouldn't use the results from this query as proof positive of a need to increase the number of freelists, but I think further cogitation upon the results of this query might lead to an "ah ha!" situation. As he said, if you know the object, you know it makes sense...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, July 12, 2002 4:08 PM

> You want to do something before you know if it would make sense. If you
know
> the object you know it makes sense.
>
> Anjo.
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Friday, July 12, 2002 10:38 PM
>
>
> > Hi
> > Is any way which tables need to increase more freelists?
> > Thx
> > -Seema
> >
> >
> >
> > _________________________________________________________________
> > Send and receive Hotmail on your mobile device: http://mobile.msn.com
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Seema Singh
> > INET: oracledbam_at_hotmail.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).
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Anjo Kolk
> INET: anjo_at_oraperf.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.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).