Re: TKPROF output
- From: Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 31 Mar 2011 08:26:28 +0200
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--
> //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--
//www.freelists.org/webpage/oracle-l
Other related posts:
