This SQL is executed 36k times once for each emplid using bind variables. select .....FROM table FILL where FLAG ='P' and EMPLID = :1 and EMPL_RCD = :2 and DEPENDENT_BENEF <> '00'; ________________________________ From: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx> To: ca_raj@xxxxxxxxx Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> Sent: Friday, February 3, 2012 2:10 PM Subject: Re: fetch calls I don't see arraysize being the problem here. - Your 'application' is submitting the SQL (btw. what is the sql-text?) 36K times. - It is even parsing it 36K times. - It fetches on average 1.3 rows per execution. - For that it requires a bit over 10 block visits, which is not too bad at all. - And it does all this in about 0.001 CPU second per execution. - There is a lot of physical I/O going on...: which explains the huge difference between CPU and elapsed time. - Might be worthwile to run this tracefile through Method-R's profiler, to see what skew is going on or not. Your issue is it is executing the SQL 36k times... On Fri, Feb 3, 2012 at 7:54 PM, Antony Raj <ca_raj@xxxxxxxxx> wrote: Hi All, > >99% of the response time spent on the Fetch call.I know changing the arraysize >from SQL*PLUS would reduce the number of fetch calls. >But this sql is generated from a third-party application's application server >on which the maximum fetch size configured as unlimited. >Is there any other ways to reduce the number of fetch calls? > > >Rows Operation >1 TABLE ACCESS BY INDEX ROWID ODSTEST (cr pr=3 pw=0 time 036 us cost=9 >size#5 card=1) >1 INDEX RANGE SCAN ODSTESTIDX (cr pr=3 pw=0 time 991 us cost=8 size=0 >card=1) (object id 684849) >Database Call Statistics >Call Count Misses CPU [s] Elapsed [s] PIO [b] LIO [b] Consistent [b] > Current [b] Rows >Parse 36,826 1 0.140 1.390 0 0 0 0 0 >Execute 36,826 1 2.130 10.326 0 2 2 0 0 >Fetch 36,826 0 42.890 802.626 123,585 390,806 390,806 0 43,918 >Total 110,478 2 45.160 814.342 123,585 390,808 390,808 0 43,918 >Average (per execution) 3 0 0.001 0.022 3 10 10 0 1 >Average (per row) 2 0 0.001 0.019 2 8 8 0 1 > >Thanks >-- >//www.freelists.org/webpage/oracle-l > > > -- Toon Koppelaars RuleGen BV Toon.Koppelaars@xxxxxxxxxxx www.RuleGen.com TheHelsinkiDeclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" www.rulegen.com/am4dp-backcover-text -- //www.freelists.org/webpage/oracle-l