On top of Cary's comment regarding how long the interval was between the execute and the fetch, there is the implied question "What was being sent to the client".
Looking at the stats, I think the answer is "NOT the data" as we see r=0 - no rows returned by the query. My guess would be that the client (which is at the end of a TCP link) is a tool running an OCI based program that is capable of separated all the steps of a query individually - so we have (if I manage to remember them all) parse, describe, define, bind, execute and fetch calls. In this environment, it gets messy figuring out what might have happened, especially since Oracle keeps changing their code strategy to optimise network traffic, but my guess is that the "more data to client", totalling about 4K could be descriptions of the columns that will be sent. (Look at the query, there are a lot of columns in the select list - it is a possibility). One thing that puzzles me, though, is the absence of a BINDS #4: line - which ought to be there given the ":1" that appears in the WHERE clause. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) ____UK_______April 8th ____UK_______April 22nd ____Denmark May 21-23rd ____USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____UK_(Manchester)_May ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 18 March 2003 14:08 > We are having some performance problems at a client. I ran a 10046 trace, > level 8 on the DB for about 5 minutes of test operations. From the trace > files generated I find someting of the following: > > SELECT Tm_trade_in_error.trade_id, Tm_trade_in_error.account_number, > Tm_trade_in_error.aps_groupid, Tm_trade_in > _error.broker_receipt_source_code, > Tm_trade_in_error.broker_receipt_timestamp, > Tm_trade_in_error.business_cycle_ > code, Tm_trade_in_error.buy_sell_ind, Tm_trade_in_error.cabinet_ind, > Tm_trade_in_error.card_code, Tm_trade_in_er > ror.card_order, Tm_trade_in_error.clearing_cycle_id, > Tm_trade_in_error.clearing_date, Tm_trade_in_error.commodit > y_code, Tm_trade_in_error.contract_day, Tm_trade_in_error.contract_month, > Tm_trade_in_error.contract_type, Tm_tr > ade_in_error.contract_year, Tm_trade_in_error.contra_match_code, > Tm_trade_in_error.cti_code, Tm_trade_in_error.c > tr_card_seq, Tm_trade_in_error.entry_clearing_date, > Tm_trade_in_error.entry_source_code, Tm_trade_in_error.entry > _time, Tm_trade_in_error.error_codes, Tm_trade_in_error.exch_code, > Tm_trade_in_error.execution_timestamp, Tm_tra > de_in_error.execution_timestamp_source_cod, > Tm_trade_in_error.exec_broker_code, Tm_trade_in_error.exercise_price > , Tm_trade_in_error.exercise_style, Tm_trade_in_error.external_tradeid, > Tm_trade_in_error.fee_code, Tm_trade_in_ > error.firm_entered_memo, Tm_trade_in_error.firm_id, > Tm_trade_in_error.floor_broker_code, Tm_trade_in_error.legac > y_id, Tm_trade_in_error.make_up_code, Tm_trade_in_error.matched_ind, > Tm_trade_in_error.match_block_seq, Tm_trade > _in_error.mod_date, Tm_trade_in_error.mod_program, > Tm_trade_in_error.mod_user, Tm_trade_in_error.open_close_ind, > Tm_trade_in_error.opposing_broker_code, Tm_trade_in_error.opposing_firm_id, > Tm_trade_in_error.order_execution_t > ime, Tm_trade_in_error.order_type, Tm_trade_in_error.origin_code, > Tm_trade_in_error.prematched_seq_no, Tm_trade_ > in_error.put_call_ind, Tm_trade_in_error.session_id, > Tm_trade_in_error.spread_differential, Tm_trade_in_error.sp > read_sign, Tm_trade_in_error.spread_type, Tm_trade_in_error.timestamp_in, > Tm_trade_in_error.timestamp_in_source_ > code, Tm_trade_in_error.timestamp_out, > Tm_trade_in_error.timestamp_out_source_code, Tm_trade_in_error.time_brack > et_code, Tm_trade_in_error.trade_day, Tm_trade_in_error.trade_gu_ind, > Tm_trade_in_error.trade_month, Tm_trade_in > _error.trade_price, Tm_trade_in_error.trade_qty, > Tm_trade_in_error.trade_route_ind, Tm_trade_in_error.trade_sour > ce, Tm_trade_in_error.trade_status, Tm_trade_in_error.trade_year, > Tm_trade_in_error.transfer_reason_code, Tm_tra > de_in_error.trans_type, Tm_trade_in_error.underlying_exercise_price, > Tm_trade_in_error.underlying_exercise_price > _sign, Tm_trade_in_error.underlying_expiration_day, > Tm_trade_in_error.underlying_expiration_month, Tm_trade_in_e > rror.underlying_expiration_year, Tm_trade_in_error.underlying_product_type, > Tm_trade_in_error.underlying_put_cal > l FROM TM_TRADE_IN_ERROR WHERE MATCH_BLOCK_SEQ = :1 > END OF STMT > PARSE #4:c=0,e=148,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=108983137752 > EXEC #4:c=0,e=95,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=108983138287 > WAIT #4: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 > WAIT #4: nam='SQL*Net more data to client' ela= 78 p1=1413697536 p2=2001 > p3=0 > WAIT #4: nam='SQL*Net more data to client' ela= 41 p1=1413697536 p2=2002 > p3=0 > WAIT #4: nam='SQL*Net message from client' ela= 23743 p1=1413697536 p2=1 > p3=0 > FETCH #4:c=0,e=78,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=4,tim=108983162738 > WAIT #4: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 > WAIT #4: nam='SQL*Net message from client' ela= 22030 p1=1413697536 p2=1 > p3=0 > STAT #14 id=1 cnt=1 pid=0 pos=1 obj=6471 op='TABLE ACCESS BY INDEX ROWID > REF_SESSION (cr=2 r=0 w=0 time=59 us)' > STAT #14 id=2 cnt=1 pid=1 pos=1 obj=6472 op='INDEX UNIQUE SCAN > XPKREF_SESSION (cr=1 r=0 w=0 time=27 us)' > > Now, what catchs my eye is the first SQL*Net message from client wait, > before fetch#4. Is this a real wait, or is this truly just an idle wait. I > find it odd that it happens *before* the fetch, but maybe I'm reading this > wrong. Can someone enlighten me please? > > RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
