Re: TKPROF output
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: lyallbarbour@xxxxxxxxxxxxxxx
- Date: Wed, 30 Mar 2011 21:05:02 +0100
Lyall
You are fetching (on average) about 6 rows per round trip on the new server
- what's the story on the existing server, do you also do 27k round trips
there as well? f not then likely it's fetch size (or array size) or similar
on the new server.
On Wed, Mar 30, 2011 at 7:55 PM, <lyallbarbour@xxxxxxxxxxxxxxx> wrote:
> 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
>
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
