It does not look like increasing the fetch size is something you should be worried about -- you're doing a lot of executions with each executions returning only a handful of rows according to your data. Your PIO figures might be a candidate for driving the times up -- do you happen to have the results from a trace file with wait events breakdown? On Fri, Feb 3, 2012 at 1: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 > > -- Alex Fatkulin, http://afatkulin.blogspot.com http://www.linkedin.com/in/alexfatkulin -- //www.freelists.org/webpage/oracle-l