Hi Rachel,
I apply this parameter to my database . But i still
see following when I query v$sqlarea..
Do you know how can i check wheather this parameter
worked or not??
Thanks
Ashish
SELECT sum(points) FROM comp_log where user_id =
969349180381 1
SELECT sum(points) FROM comp_log where user_id =
969349370971 1
SELECT sum(points) FROM comp_log where user_id =
969349705904 2
SELECT sum(points) FROM comp_log where user_id =
969351378016 1
SELECT sum(points) FROM comp_log where user_id =
969351955905 2
SELECT sum(points) FROM comp_log where user_id =
969353225696 2
SELECT sum(points) FROM comp_log where user_id =
969354297234 1
SELECT sum(points) FROM comp_log where user_id =
969354393529 1
SELECT sum(points) FROM comp_log where user_id =
969354419965 2
SELECT sum(points) FROM comp_log where user_id =
969361677862 1
SELECT sum(points) FROM comp_log where user_id =
969362198558 1
SELECT sum(points) FROM comp_log where user_id =
969363278714 2
SELECT sum(points) FROM comp_log where user_id =
969363525335 1
SELECT sum(points) FROM comp_log where user_id =
969363555834 1
SELECT sum(points) FROM comp_log where user_id =
969363603381 1
- Rachel Carmichael <carmichr_at_hotmail.com> wrote:
> 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:
>
> cursor_sharing=force
>
> 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
> :)
>
> hth
>
> Rachel
>
> >From: Stephane Faroult <sfaroult_at_oriolecorp.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> >Subject: RE: DATABASE TUNING
> >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.
> >--
> >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).
>
>
> Get Your Private, Free E-mail from MSN Hotmail at
> http://www.hotmail.com.
>
> Share information about yourself, create your own
> public profile at
> http://profiles.msn.com.
>
> --
> Author: Rachel Carmichael
> INET: carmichr_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
Received on Wed Sep 20 2000 - 10:24:37 CDT
