RE: TKPROF output
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "lyallbarbour@xxxxxxxxxxxxxxx" <lyallbarbour@xxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Mar 2011 15:57:46 -0400
'Fetch' is a database call. When you execute the SQL statement, you'll do a
'parse', an 'execute' and then one or more 'fetch' calls.
Depending on the array size (which is defined and controlled on the client side
only), and the number of rows you have to return, will control how many fetch
calls you do.
In your case, you retrieved 164,741 rows in 27,457 fetches. That's 6
rows/fetch, so, your array size was 6. If you increase the size of your array,
you'll do less fetches, and probably accrue significantly less SQL*Net waits,
because you'll do a lot less round trips.
Hope that helps,
-Mark
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of lyallbarbour@xxxxxxxxxxxxxxx
Sent: Wednesday, March 30, 2011 2: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:
