hi Stephane,
What parameter will replace hard-coded values
with bind variables on
the fly ?
Please let me know.
Thanks
- Stephane Faroult <sfaroult_at_oriolecorp.com> wrote:
> As always, this kind of information depends on many
> factors. Your having
> a not null chained fetch ratio may mean that you
> have a lot of 'long'
> (not in the datatype acceptance - VARCHAR2(4000)
> belongs to the
> category) columns, especially if you have settled
> for a medium to small
> data block size (Oracle defaults are smaller than
> they should be). If
> the average row size is bigger than what you can
> reasonably squeeze into
> a datablock, there is not much you can do short of
> rebuilding your
> database with a bigger block size, which is not
> worth the trouble if
> only Toad complains ans if users are satisfied with
> performance. Now,
> you may indeed have 'truly chained' tables - this
> may occur if you
> insert rows with most columns set to NULL, and if
> the columns are filled
> up later. Basically, you should adjust PCTFREE so
> that the number of
> rows you insert is not bigger than the number which
> fits in the block
> once all the updates have been performed and the
> rows have reached their
> final size. It is not that easy to fix, because if
> you alter your table
> to set PCTFREE to a higher value, this will work for
> future inserts
> only. The best thing to do would probably be to let
> PCTFREE unchanged or
> possibly lower it, export your table(s), reimport -
> this will fix
> everything for 'old' data - THEN change PCTFREE to
> the value computed as
> adequate (something higher than the default 10) -
> and it should answer
> the question of 'future' data.
> Parse/execute ratio is another problem, which may
> come from having a lot
> of 'hard-coded' statemenst - since you are on 8.1.6
> you should activate
> this new feature which replaces hard-coded values
> with bind variables on
> the fly - or your shared pool is too small. Check
> the relevant V$ views
> (typically V$LIBRARYCACHE and V$ROWCACHE) to see
> whether you have a lot
> of reloads. Pinning statements/packages etc. in
> memory (the
> DBMS_SHARED_POOL package) may also be a good idea.
> --
> HTH,
>
> Stephane Faroult
> email: sfaroult_at_oriolecorp.com
> Oriole Corporation
> Voice: +44 (0) 7050-696-269
> Fax: +44 (0) 7050-696-449
> Performance Tools & Free Scripts
>
> http://www.oriolecorp.com, designed by Oracle DBAs
> for Oracle DBAs
>
> >
> > I get the following information from Toad.
> >
> > Chained fetch ratio: 0.0464 (PCTFREE TOO LOW for
> a table)
> > parse/execute ratio 77.8308 (high parse to execute
> ratio)
> >
> >
> > to increase the performance and to avoid the
> warning : PCTFREE TOO LOW for a table &high parse to
> execute ratio
> > What should be the optimum values ?
> > What are parameters related to these values ?
> >
> > Environment:
> > ==========
> > ORACLE 8.1.6
> > Sun OS. 5.7
> > RAM 1GB
> > 400 x 2 processor
> >
> --
> Author: Stephane Faroult
> INET: sfaroult_at_oriolecorp.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).
Ashish
Toronto, Canada
Received on Mon Sep 18 2000 - 09:54:17 CDT
