Re: TKPROF output
Date: Thu, 31 Mar 2011 08:26:28 +0200
Message-ID: <4D941E94.4050506_at_nordea.com>
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_at_uptime.be
> tel +32(0)3 451 23 82
> http://www.uptime.be
> disclaimer: www.uptime.be/disclaimer
> ---
> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of lyallbarbour_at_sanfranmail.com [lyallbarbour_at_sanfranmail.com]
> Sent: 30 March 2011 20:55
> To: oracle-l_at_freelists.org
> 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_at_nordea.com> 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-lReceived on Thu Mar 31 2011 - 01:26:28 CDT