Re: performance question

On Nov 12, 12:14 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Nov 12, 2:42 pm, rogv <rvaede..._at_gmail.com> wrote:
>
>
>
>
>
> >    I have a QA and PROD server and has loaded identical Oracle DB 10
> > i.
> >    This query runs fast on (10 Minutes) QA and slow on (3 hours)
> > PROD.
> >     The servers are Red hat Linux.
>
> >   INSERT INTO xyz_ime_candidate
> >                   (proj_key, xref_item_key, xyz_prod_key, match_type,
> >                    match_score)
> >          SELECT pn_proj_key, x.xref_item_key, m.xyz_prod_key, 'V',
> > 400
> >            FROM xyz_ime_xref_data x, xyz_ime_gfy_data1 m
> >           WHERE x.proj_key = pn_proj_key --and m.proj_key =
> > pn_proj_key
> >             AND x.gfy_supp_key = m.gfy_supp_key
> >             AND x.part_no = m.part_no;
>
> >   Both machines performance load are OK, production server load is a
> > little high.
> >   What could possibly cause this performance difference?
> >   Any hints?  Thanks
>
> I suggest trying to enable a 10053 trace at level 1 and a 10046 trace
> at level 8 in a session connected to each server, and then execute the
> SQL statement (you must make certain that a hard parse of the SQL
> statement is performed, possibly by changing the spacing in the SQL
> statement or by adding a comment).  The 10053 portion of the trace
> file that will be generated (near the beginning of the trace file
> located in the server's udump directory) will list the optimizer
> parameters influencing the query optimizer (assuming that the cost
> based optimizer is in use), the statistics found for the objects
> involved in the query, the cost based decisions for the expected
> execution plan, and the expected execution plan (I believe that this
> is output starting with version Oracle 10g).  When the query
> completes, execute a simple SQL statement, such as SELECT SYSDATE FROM
> DUAL;  and then disconnect from the database instance.
>
> Enabling the traces:
> ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
>
> Decoding the 10046 portion of the trace file:http://forums.oracle.com/forums/thread.jspa?messageID=2549168
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

I thought the white-space thingy was relaxed for comparing two sql statements - or is that just for stored outlines?

rogv: Be sure you don't have any invalid objects (like an index you need), also try doing a full-table scan of the table to eliminate the possibility of bind-peeking (google if you don't know it).

jg

--
@home.com is bogus.
http://www.darkreading.com/security/attacks/showArticle.jhtml?articleID=212001872