Re: performance question
Date: Wed, 12 Nov 2008 17:31:28 -0800 (PST)
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).
-- @home.com is bogus. http://www.darkreading.com/security/attacks/showArticle.jhtml?articleID=212001872Received on Wed Nov 12 2008 - 19:31:28 CST