Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query Slow in 10g, runs very fast in 8i

Re: Query Slow in 10g, runs very fast in 8i

From: EscVector <Junk_at_webthere.com>
Date: 5 Jan 2007 07:25:27 -0800
Message-ID: <1168010727.742769.119720@i15g2000cwa.googlegroups.com>

Charles Hooper wrote:
> DBdude_From_NY_at_yahoo.com wrote:
> > We have a 8i DB on VMS-VAX machine. We copied the DB to Unix on Oracle
> > 10g. Unix machine is more powerful then VMS and its 10g. A SP which was
> > taking less then 10 minutes on 8i is taking close to 11 hours (Yes,
> > Hours) on Unix, 10g. From where should I start looking in order to make
> > it faster.
> > On Oracle 8i statistics was not being gathered and on 10 g we are
> > gathering stats.
> > BTW, the SP is written real bad, but it was finishing its job in less
> > then 10 minutes and that also with 3 times more data because we have
> > not migrated entire data. We do have migrated all tables, indexes, SPs
> > etc.
>
> A lot has changed between 8i and 10g which could influence the
> performance of the SQL code. You may wish to pick up a copy of
> "Cost-Based Oracle Fundamentals" to better understand the differences,
> and how to work around the issues. Some of the complex SQL statements
> that I wrote (most involving multiple nested inline views), which
> executed in seconds on 8i, were taking upwards of five minutes to
> execute on 10g R2. You can look at this as an opportunity to clean up
> the SQL code, learn quite a bit about the cost based optimizer, and to
> carefully examine the initialization parameters.
>
> Something to try:
> Start a SQLPlus session and connect to the database.
> Add the following immediately after the first SELECT statement in the
> SQL statement (this method of invoking DBMS_XPLAN was copied from
> Jonathan Lewis' blog):
> /*+ GATHER_PLAN_STATISTICS */
> Execute the query with the above hint added.
> Execute the following:
> SELECT
> *
> FROM
> TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
> Save the output of the above.
> Execute the following:
> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='8.1.7';
> Execute your modified query again (include the GATHER_PLAN_STATISTICS
> hint). Did the query execute as fast it did in 8i?
> Execute the following:
> SELECT
> *
> FROM
> TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
> Save the output of the above.
> Compare the output of the two DBMS_XPLANs.
>
> Are the execution plans from the two DBMS_XPLANs the same? Are the
> access and filter predicates the same? Did the performance improve
> after adjusting the OPTIMIZER_FEATURES_ENABLE?
>
> More information can be obtained from a 10046 trace at level 8, or from
> a 10053 trace at level 1. It takes a bit of practice to read these
> trace files.
>
> The above is just something to help you get started.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Certainly look at the xplan, but I'd try the simple first.

alter session set optimizer_index_cost_adj=10000;

run the sql in sqlplus or add this line to the procedure.

You can set it back after the procedure is complete.

See what happens. Received on Fri Jan 05 2007 - 09:25:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US