Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: DATABASE TUNING


From: Rachel Carmichael <>
Date: Mon, 18 Sep 2000 13:28:01 GMT
Message-Id: <>

Just to expand... the "new feature" Stephane is referring to is an init.ora parameter called "cursor_sharing"

you can set it in the init.ora by including the line:


you can also set it "on the fly" while the database is up:

alter system set cursor_sharing=force;

What it does is convert the hard-coded sql (constants and literals in the statements) to bind variables so that the SQL can be reused and you don't parse the statement again.

Works like a charm. I've set it on here, and went from 27,000+ statements in my sql_area to 168.

EVERY statement was hard-coded with constants. my programmers are Java programmers, not SQL people and I DID tell them they could get away with it as we are on 8.1.6 and I knew I could work around it :)



>From: Stephane Faroult <>
>To: Multiple recipients of list ORACLE-L <>
>Date: Mon, 18 Sep 2000 03:50:23 -0800
>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.
> Stephane Faroult
> email:
> Oriole Corporation
> Voice: +44 (0) 7050-696-269
> Fax: +44 (0) 7050-696-449
> Performance Tools & Free Scripts
>, 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
>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: (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).

Get Your Private, Free E-mail from MSN Hotmail at Received on Mon Sep 18 2000 - 08:28:01 CDT

Original text of this message