Re: TKPROF output
- From: Jamey Johnston <jj@xxxxxxxxxx>
- To: "ChrisDavid.Taylor@xxxxxxxxxxxxxxx" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- Date: Wed, 30 Mar 2011 14:21:52 -0500
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@xxxxxxxxxxxxxxx> wrote:
> 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@xxxxxxxxxxxxxxx
>
>
>
> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of lyallbarbour@xxxxxxxxxxxxxxx
> Sent: Wednesday, March 30, 2011 1:55 PM
> 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
>
>
>
>
Other related posts:
