Re: TKPROF output

If talking about sql*net message from client - it seems it consists of
the whole time taken from sending data to client and receiving the
answer in contrary to the event names. There is a good article by Tanel
Poder about it -
http://blog.tanelpoder.com/2008/02/07/sqlnet-message-to-client-wait-gotcha/

Regards
Remigiusz

W dniu 03/31/11 08:01, D'Hooge Freek pisze:
> Hi,
> 
> The main wait time is "sql*net message from client", meaning that you are 
> waiting on response from the aplication server.
> Can you check in the raw trace file if these waits are happening between the 
> fetches or not?
> 
> If they are happening between the fetches, it means that the application 
> server is slow to request more rows from the query result.
> Reason for this could be some processing that happens on the application 
> server or maybe a network problem (like a wrong dns server on the application 
> server).
> 
> If the waits are happening after all the fetches are completed, then these 
> waits are probably not relevant (unless you are sure that the trace was 
> stopped at the moment the application showed the result of the query).
> How did you trace this session?
> 
> 
> regards,
> 
> Freek D'Hooge
> Uptime
> Oracle Database Administrator
> email: freek.dhooge@xxxxxxxxx
> tel +32(0)3 451 23 82
> http://www.uptime.be
> disclaimer: www.uptime.be/disclaimer
> ---
> From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf 
> Of lyallbarbour@xxxxxxxxxxxxxxx [lyallbarbour@xxxxxxxxxxxxxxx]
> Sent: 30 March 2011 20:55
> To: oracle-l@xxxxxxxxxxxxx
> Subject: TKPROF output
> 
> 
> Trying to understand Fetch in a TKPROF output.  We have an application on 
> Oracle Apps Server 10.1  Database 10.2.0.4  On production, a specific query 
> runs in about 3 seconds.  On this new database server we created, it runs 
> about 30 secs.  Looks like the query does the same thing in the database, but 
> we have a ton of SQL*Net message waits on the query below.  What are Fetches? 
>  What are reasons why waits for SQL*Net messaging happens that relate to 
> Fetches?  See below...
> 
> Here it is:
> SELECT ROWID,SCRAP_ID,TX_ID,SHIFT_ID,ON_TX_ID,SCRAP_COMP_CODE,WEIGHT_UOM,
>   DEPT_CODE,INV_COMP_CODE,INV_ITEM_CODE,SCRAP_CODE,TYPE,CUST_NUM,PART,
>   QUANTITY,LENGTH,SCRAP_WEIGHT,TX_START_DT,RESPONSIBILITY_CODE,DEFECT_CODE,
>   NOTES
> FROM
>  ST_PRODTX_SCRAP WHERE (WEIGHT_UOM=:1)
> 
> 
> call     count       cpu    elapsed       disk      query    current        
> rows
> ------- ------  -------- ---------- ---------- ---------- ----------  
> ----------
> Parse        1      0.00       0.00          0          0          0          
>  0
> Execute      1      0.00       0.00          0          0          0          
>  0
> Fetch    27457      0.91       0.90          0      29757          0      
> 164741
> ------- ------  -------- ---------- ---------- ---------- ----------  
> ----------
> total    27459      0.91       0.90          0      29757          0      
> 164741
> 
> Misses in library cache during parse: 1
> Misses in library cache during execute: 1
> Optimizer mode: ALL_ROWS
> Parsing user id: 677  (LBARBOUR)
> 
> Rows     Row Source Operation
> -------  ---------------------------------------------------
>  164741  TABLE ACCESS FULL ST_PRODTX_SCRAP (cr=29757 pr=0 pw=0 time=165118 us)
> 
> 
> Rows     Execution Plan
> -------  ---------------------------------------------------
>       0  SELECT STATEMENT   MODE: ALL_ROWS
>  164741   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'ST_PRODTX_SCRAP' (TABLE)
> 
> 
> 
> Elapsed times include waiting on following events:
>   Event waited on                             Times   Max. Wait  Total Waited
>   ----------------------------------------   Waited  ----------  ------------
>   SQL*Net message to client                   27457        0.00          0.01
>   SQL*Net message from client                 27457        1.07        
> 100.33--
> http://www.freelists.org/webpage/oracle-l
> 
> 
> 


-- 
Pole nakazi

----------------------------------------------------------------------
Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
pos   : DBA at DIiUSI
addr  : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
phone : +48 58 667 17 43
mobile: +48 602 42 42 77
Nordea Bank Polska S.A. z siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia, 
wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod numerem: 
0000021828, 
dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc w Gdansku, 
VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego, 
o kapitale zakladowym i wplaconym w wysokosci: 277.493.500,00 zlotych, 
NIP: 586-000-78-20, REGON: 190024711--
http://www.freelists.org/webpage/oracle-l

Other related posts: