Hi All,
I have Oracle 9.2.1.0 database running on windows 2003 server and One VB 6.0 based application (Purchase Order) is pointing to my prod database.I have some ASP.NET web based applications as well which are pointing to this database.
Yesterday, i traced Purchase Order processing and i found below results w.r.t network round trips.
With Fetchsize=1000
********************************************************************************
SQL-1
SELECT substr(PO_NO,4,12) DOCNo
FROM
TXN_PO_HDR WHERE UPPER(substr(PO_NO,1,3)) = 'KEW' ORDER BY substr(PO_NO,4,12)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 82 1.03 1.13 0 500 0 80147
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 86 1.03 1.14 0 500 0 80147
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67
Rows Row Source Operation
------- ---------------------------------------------------
40072 SORT ORDER BY (cr=250 r=0 w=0 time=107314 us)
40072 INDEX FAST FULL SCAN PK_PO_HDR (cr=250 r=0 w=0 time=30430 us)(object id 39102)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 82 0.00 0.00
SQL*Net more data to client 564 0.01 0.05
SQL*Net message from client 82 0.25 2.41
********************************************************************************
SQL-2
********************************************************************************
SELECT '0' as isselect,substr(I.ITEMCODE, 1, 9) as itemcode,I.ITEMNM as
ItemDesc, substr(I.itemcode, 10, length(I.itemcode)) as brandcode,(Select
BrandNM
from
mst_Brand where brandcode = nvl(to_number(trim(substr(I.itemcode, 10,
length(I.itemcode)))), 0)) as brandnm, IP.DOQQTY, um.uomdesc as uom,
up.uomdesc as uop, ip.ucf, (case ip.PATTERN_FLAG when 1 then 'Yes' else ''
end) as Pattern, (case ip.NEWITEM_FLAG when 1 then 'Yes' else '' end) NEW,
ip.hmlgrp as HML,ip.abcgrp as ABC, (case ip.ADV_PLANNING_FLAG when 1 then
'Yes' else '' end) Adv_Plan,(case ip.BIN_FLAG when 1 then 'Yes' else '' end)
as Bin, (SELECT NARHD FROM MST_NARMS_DTL WHERE NARCD = STKLEVEL) as
Lvl FROM MST_ITEM I left outer join mst_uom um on um.uomcode = i.uomcode
LEFT OUTER JOIN mst_itemven_hdr VEN ON VEN.ITEMCODE =I.ITEMCODE left
outer join mst_item_pur ip on ip.itemcode = i.itemcode left outer join
mst_uom up on up.uomcode = ip.uopcode WHERE ip.purType='BO' AND
((buyercode IN 109 ) or ( ALTBUYERCODE in 109 )) ORDER BY I.itemcode
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 26 2.23 3.07 954 29667 0 25746
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28 2.23 3.07 954 29667 0 25746
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67
Rows Row Source Operation
------- ---------------------------------------------------
25746 SORT ORDER BY (cr=29667 r=954 w=0 time=2333479 us)
25746 NESTED LOOPS OUTER (cr=29661 r=954 w=0 time=2097946 us)
25746 HASH JOIN OUTER (cr=3913 r=954 w=0 time=1995148 us)
25746 HASH JOIN OUTER (cr=3906 r=954 w=0 time=1891982 us)
25746 HASH JOIN (cr=3899 r=954 w=0 time=1809682 us)
25747 TABLE ACCESS FULL OBJ#(38546) (cr=1823 r=703 w=0 time=1061115 us)
136895 TABLE ACCESS FULL OBJ#(38507) (cr=2076 r=251 w=0 time=498278 us)
56 TABLE ACCESS FULL OBJ#(38724) (cr=7 r=0 w=0 time=112 us)
56 TABLE ACCESS FULL OBJ#(38724) (cr=7 r=0 w=0 time=140 us)
9565 INDEX UNIQUE SCAN OBJ#(38535) (cr=25748 r=0 w=0 time=54476 us)(object id 38535)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 26 0.00 0.00
db file scattered read 162 0.08 0.42
db file sequential read 245 0.01 0.34
SQL*Net more data to client 508 0.00 0.02
SQL*Net message from client 26 41.95 45.91
********************************************************************************
SQL-3
********************************************************************************
SELECT DISTINCT HDR.TYPE_CODE, HDR.TYPE_DESC AS STOCKKEY
FROM
MST_ISSRESTYPE_DTL DTL INNER JOIN MST_ISSRESTYPE_HDR HDR ON HDR.TYPE_CODE=
DTL.TYPE_CODE WHERE SUBSTR(UPPER(HDR.TYPE_CODE),1,1)='I' AND
UPPER(HDR.PARTY_TYPE)='V' AND DOC_TYPE_ID=(SELECT DOC_TYPE_ID FROM
MST_DOCUMENTTYPE WHERE DOC_TYPE LIKE 'PURCHASE ORDER%') AND REG_ID IS NOT
NULL AND REG_ID=(SELECT REG_ID FROM MST_REGISTER WHERE REG_NM LIKE
'DESPATCH%' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.01 0.00 0 60 0 24
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.01 0.00 0 60 0 24
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67
Rows Row Source Operation
------- ---------------------------------------------------
6 SORT UNIQUE (cr=15 r=0 w=0 time=1041 us)
6 NESTED LOOPS (cr=15 r=0 w=0 time=984 us)
6 TABLE ACCESS FULL MST_ISSRESTYPE_HDR (cr=13 r=0 w=0 time=946 us)
1 TABLE ACCESS BY INDEX ROWID MST_DOCUMENTTYPE (cr=2 r=0 w=0 time=32 us)
1 INDEX RANGE SCAN SYS_C003072 (cr=1 r=0 w=0 time=20 us)(object id 38426)
1 VIEW (cr=4 r=0 w=0 time=735 us)
1 HASH JOIN (cr=4 r=0 w=0 time=731 us)
1 INDEX RANGE SCAN MST_REGISTER_UK81066225482984 (cr=1 r=0 w=0 time=7 us)(object id 38647)
181 INDEX FAST FULL SCAN SYS_C0055879 (cr=3 r=0 w=0 time=103 us)(object id 38645)
6 INDEX RANGE SCAN PK_TYPECODESTOCKKEY (cr=2 r=0 w=0 time=22 us)(object id 38504)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 0.07 0.12
********************************************************************************
SQL-4
********************************************************************************
SELECT /*+ RULE */ '', b.owner, b.table_name, b.column_name, b.position,
b.constraint_name
FROM
ALL_CONSTRAINTS a, ALL_CONS_COLUMNS b WHERE (b.table_name=
'TXN_PO_ITEM_CHG_DTL' and b.owner='KETFINAL') AND (a.table_name=
'TXN_PO_ITEM_CHG_DTL' and a.owner='KETFINAL' and a.constraint_type='P')
AND (a.constraint_name = b.constraint_name) ORDER BY b.owner, b.table_name,
b.position
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.18 0.16 0 89676 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.18 0.17 0 89676 0 12
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 67
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 0.01 0.03
********************************************************************************
All above sqls are from same trace file for which i set fetchsize to 1000.
I am wondering how Oracle behaves differentely fetch sizes for SQL-1/2 (1000) and for 3/4?
I want to set adequete value for fetchsizes for both of these applications.
With what value i should start and how should i progress keeping in mind the optimal value?
I found that by default fetchsize is 10 for both applications.Before running this trace, i increased it to 1000.
I know that no table in my database is having more that 0.5 Million records.I have most accurate stats collected.
It would be great if i get proc and cons of setting this to high values.
Regards,
Bhavik Desai