Re: 10046 Trace file waits

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" <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).