Re: performance question

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&#2549168

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Nov 12 2008 - 14:14:26 CST