Re: TKPROF output
From: Jamey Johnston <jj_at_jameyj.com>
Date: Wed, 30 Mar 2011 14:21:52 -0500
Message-Id: <EE4D1892-521D-4BDE-9F0D-83FB2D327611_at_jameyj.com>
Probably just néed an index on you weight_uom column (note Full Table Scan).
Date: Wed, 30 Mar 2011 14:21:52 -0500
Message-Id: <EE4D1892-521D-4BDE-9F0D-83FB2D327611_at_jameyj.com>
Probably just néed an index on you weight_uom column (note Full Table Scan).
jbj2
-- Jamey Johnston On Mar 30, 2011, at 2:11 PM, "Taylor, Chris David" <ChrisDavid.Taylor_at_ingrambarge.com> wrote:Received on Wed Mar 30 2011 - 14:21:52 CDT
> Have you tried running the SQL directly on the server itself?
>
>
>
> Tom Kyte has several posts on this I think
>
> Google:
>
> site:asktom.oracle.com + +SQL*Net message from client +fetch
>
>
>
> Here’s some of his summaries to have something to think about:
>
>
>
> This could be indicative of a client application inefficiently processing data - if you think the client should be
>
> a) getting data
> b) doing something very fast
> c) getting more data and repeat
>
> this would indicate the client is not achieving B
>
> So, I would say the report is getting hung up doing something, perhaps it was starved for cpu, perhaps it get stuck writing to disk.
>
> sqlnet message from client - we are waiting to be told what to do next in the database. we are not currently doing anything in the database.
>
>
>
> &&
>
>
>
> set arraysize larger, that'll cut down on the round trips - if the problem is that remote is on a high bandwidth - high latency network (like a satellite connection), then this will help.
>
>
>
> Chris Taylor
>
> Sr. Oracle DBA
>
> Ingram Barge Company
>
> Nashville, TN 37205
>
> Office: 615-517-3355
>
> Cell: 615-663-1673
>
> Email: chris.taylor_at_ingrambarge.com
>
>
>
> CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.
>
>
>
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of lyallbarbour_at_sanfranmail.com
> Sent: Wednesday, March 30, 2011 1:55 PM
> 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