RE: Poor performance after moving Extempore database from 7.3.4 to 8.1.7

For optimizer_index_caching start with your hit ratio (sorry everyone) expressed as on integer - eg 85.57% would gove you 86. The parameter means how often do you expect index blocks to be found in memory, hit ratio is a start. For o_i_c_a use the ratio of db_file_sequential_read wait times to db_file_scattered_read wait times (v$system_event) expressed as an integer (most of mine on compaq systems come out at around 30.)

The stat is saying how much cheaper are disk accesses for sequential read blocks (index) compared to disk accesses for scattered read blocks (fts). Comments along the lines of 'but surely that's the wrong way around' agreed with, but as I understand it the wait interface was a late night - off topic affair so hey who is arguing for decent nomenclature.

Niall

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On
> Behalf Of Smith, Ron L.
> Sent: 11 August 2003 22:04
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Poor performance after moving Extempore database
> from 7.3.4 to 8.1.7
>
>
> Can you give me starting values to try in the optimizer
> parameters? Ron
>
> -----Original Message-----
> Sent: Monday, August 11, 2003 3:44 PM
> To: Multiple recipients of list ORACLE-L
> to 8.1.7
>
>
> Yes it does. 8i has parameters optimizer_index_cost_adj and
> optimizer_index_caching, which don't exist in 7.3. And these
> parameters' default values aren't optimal.
>
> Also, when testing your app, be sure that you're comparing
> oranges with oranges, e.g. if your app uses bind variables
> but your sqlplus test doesn't, the comparision is quite pointless...
>
> Tanel.
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Monday, August 11, 2003 11:24 PM
> 8.1.7
>
>
> > The old and new versions were both set to CHOOSE. Both are
> analyzed.
> > Are you saying 8.1.7 handles this differently?
> >
> > Thanks!
> > Ron
> >
> > -----Original Message-----
> > Sent: Monday, August 11, 2003 3:04 PM
> > To: Multiple recipients of list ORACLE-L
> > to 8.1.7
> >
> >
> > RBO, CBO, the old story. If you plan to continue using 8.1.7 for a
> > While, then set the parameter OPTIMIZER_MODE to RULE. Your
> performance
>
> > will be normal again and programmers will be able to override the
> > parameter By specifying any hint except /*+ RULE */. Before
> switching
> > to CBO, please read the article about the intelligent life in the
> > optimizer. The article itself is a good one (thanks, Tim)
> but reading
> > the book from JL (Jonathan
> > Lewis, not Jeniffer Lopez) wouldn't hurt, either.
> >
> > --
> > Mladen Gogala
> > Oracle DBA
> >
> >
> >
> > -----Original Message-----
> > Smith, Ron L.
> > Sent: Monday, August 11, 2003 3:14 PM
> > To: Multiple recipients of list ORACLE-L
> > 8.1.7
> >
> >
> >
> > We recently upgraded an application (Extempore) database from Oracle
> > 7.3.4 to 8.1.7. The clients say the test database, still on Oracle
> > 7.3.4 is much faster than the production database, now on
> 8.1.7. The
> > server is newer and faster on the production database. The Oracle
> > init parameters are pretty much identical.
> >
> > When I access the database through SQL*PLUS or TOAD, the response is
> > instantaneous. The app uses an ODBC connection which is
> also 8.1.7.
> > I have tried attaching the tables in MS Access through
> ODBC. This too
>
> > is instantaneous.
> >
> > Any ideas?
> >
> > Ron Smith
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Smith, Ron L.
> > INET: rlsmith_at_kmg.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web
> hosting services
> >
> ---------------------------------------------------------------------
> > 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).
> >
> > --------------------------------------------------------
> > Note:
> > This message is for the named person's use only. It may contain
> > confidential, proprietary or legally privileged information. No
> > confidentiality or privilege is waived or lost by any
> mistransmission.
>
> > If you receive this message in error, please immediately
> delete it and
>
> > all copies of it from your system, destroy any hard copies
> > of it and notify the sender. You must not, directly or indirectly,
> > use, disclose, distribute, print, or copy any part of this
> message if
> > you are not the intended recipient. Wang Trading LLC and any of its
> > subsidiaries each reserve the right to monitor all e-mail
> > communications through its networks.
> >
> > Any views expressed in this message are those of the individual
> > sender, except where the message states otherwise and the sender is
> > authorized to state them to be the views of any such entity.
> > ---------------------------------------------------------
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Mladen Gogala
> > INET: mladen_at_wangtrading.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web
> hosting services
> >
> ---------------------------------------------------------------------
> > 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.net
> > --
> > Author: Smith, Ron L.
> > INET: rlsmith_at_kmg.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web
> hosting services
> >
> ---------------------------------------------------------------------
> > 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.net
> --
> Author: Tanel Poder
> INET: tanel.poder.003_at_mail.ee
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
> --
> Author: Smith, Ron L.
> INET: rlsmith_at_kmg.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
-- 
Author: Niall Litchfield
  INET: niall.litchfield_at_dial.pipex.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).